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

Obs: it's more than two parameters, and more than 50 also. it's a summarization of my code. — Marcelo, Dec 10 '09 at 16:51
50 parameters seems like an awful lot for a stored procedure.. Not that that's in any way relevant as an answer, just another obs. — Mike Dinescu, Dec 10 '09 at 22:46
I did not down vote you (although I felt compelled to) but I guess the reason you got downvoted is because the question was not clearly formulated and misleading to those that spent their time trying to help you out — Mike Dinescu, Dec 14 '09 at 01:17
But i mean it when i say i'm new, i started programming like two months ago, and i didn't know that could cause it, i'm sorry for the question then =( i'm trying to improve my programming skills — Marcelo, Dec 14 '09 at 13:34
@Marcelo, don't take it personally. Nobody has anything against you, or the fact that you are new to SO or programming. It's just that questions generally get voted up/down based on how helpful they could be to other people - that's all. Of course some people here are quicker to use downvoting to express their opinions than others but in the end positive votes count more than negative ones and even the highest ranking members have had their share of down-votes. — Mike Dinescu, Dec 14 '09 at 14:27

score 3 · Answer 1 · answered Dec 10 '09 at 16:45

3

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..

answered Dec 10 '09 at 16:45

Mike Dinescu

54,171
16
118
151

it was an example.. i don't have only two, or else i wouldn't mind asking a way to find where it is. – Marcelo Dec 10 '09 at 16:47

score 2 · Answer 2 · answered Dec 10 '09 at 16:45

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...

answered Dec 10 '09 at 16:45

Charles Bretana

143,358
22
150
216

it's way more than two.. i just want to know which one is being passed wrong without having to see each one. – Marcelo Dec 10 '09 at 16:50

score 1 · Answer 3 · answered Dec 10 '09 at 17:27

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);

score 1 · Answer 4 · answered Dec 10 '09 at 17:55

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.

Marcelo · Accepted Answer · 2009-12-14T13:36:51.347

0

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!

edited Dec 14 '09 at 13:36

answered Dec 10 '09 at 19:33

Marcelo

3,371
10
45
76

Probably because someone thought you could have counted your parameters in the SQL and VB code, rather than create a question. No, I didn't down vote you, but I am sure this is the reason. – Wade73 Dec 14 '09 at 13:16

Exception on ExecuteNonQuery method about unsuccessful value convertion

5 Answers5