Exception on ExecuteNonQuery method about unsuccessful value convertion

Question

i am using OleDbCommand.ExecuteNonQuery() to insert data into the database:

ObjCommand = New OleDbCommand
ObjCommand.CommandType = CommandType.StoredProcedure
ObjCommand.CommandText = StrSQL

ObjCommand.Parameters.Add("field1", OleDbType.VarChar).Value = <something1>
ObjCommand.Parameters.Add("field", OleDbType.VarChar).Value = <something2>
(...)
(...)    
ObjCommand.Parameters.Add("field50", OleDbType.VarChar).Value = <something50>

ObjCommand.Connection = GetDBConnection(StrConnectionString)
ObjCommand.Connection.Open()


<some integer> = ObjCommand.ExecuteNonQuery()

And there is a conversion exception that only shows up in the last line:

error converting datatype varchar to smallint

I'd like to know if it's the normal behavior, and how do i know where the conversion problem occurs.

update:

ObjCommand.Parameters.Add("@IdMunFatoGerador", OleDbType.VarChar).Value 
                = ObjNFe.idMunFatoGerador

i've found this line through commenting every line and uncommenting some, this one gives me the above said exception.

ObjNFe.idMunFatoGerador is a string and gives me "error converting datatype varchar to smallint" too

Answer 1

That implies that one of the parameters of the query is of the wrong type. Namely you are passing varchar when you should be passing a smallint (short in c#).

Without the definition of the stored procedure there's no way we can guess which one it is..

Answer 2

One of the parameters you are pasing to the stored procedure as a varChar is typed in the stored procedure as an smallint. And, in this case the value you are passing in cannot be converted implicitly by the server to an integer value. Look at the stored proc definition, Either lala, or lulu is typed as an smallint. Then look at actual values you are sending it...

Answer 3

If you use the DataSet designer, it will generate everything for you and you'll get a compiler error instead of a run-time error. Add a new DataSet to your project then add a Query to the DataSet.

You end up with something like this:

QueriesTableAdapter ta = new QueriesTableAdapter();
ta.Connection = myConnection;
ta.MySeveralParameterStoredProc(x0, x1, ..., xN);

Answer 4

I guess you could loop through the parameter collection and look at the value and see if it can be numberic (string.isnumeric). The use debug.assert to output a message that the parameter value is too big to be a small int as well as the parameter name. Even better is for you to set the parameter type to be oledbtype.smallint and then only look at those. Ultimately, you need to know your parameters and how they correspond to the underlying SQL. I would just narrow my search by typing my parameters correctly and then ensure I never passed anything to the command object that wouldn't work. HTH. Possible code:

For each parameter as SqlParameter in mycommandobject.parameters
     if isnumeric(parameter.value) then
          debug.assert(convert.int32(parameter.value) <= 32,767,"This parameter could have an issue - " & parameter.parametername & " value = " & parameter.value) 
     end if

loop

I haven't tested the code, but i believe this will work.

Answer 5

I've finally found it.

It was everything ok with the formats of the values. The problem was: one of the parameters was missing. I still didn't understand it completely, but the issue was that the missing parameter (smallint) was interpreted in the following one (varchar) and so the error i found was in the second one.

In other words, field~35 was missing (haha)

So the thing is: when mounting a command to a procedure, remember to always put the fields in the exact amount and order. =)

Thank you guys!