SqlException after Adapter.Update: how to determine the source columnand parameter involved?

[TR]

I am at this point (<<<) in my Insert logic against a SQL2000 back-end:

Try

....

MyDataset.Tables(0).DefaultView.RowStateFilter = DataViewRowState.Added

If MyDataset.Tables(0).DefaultView.Count > 0 Then
Adapter.Update(MyDataset.Tables(0))
End If

Catch ex as Exception
RaiseEvent MyErrorEvent(ex) '<<< here
Finally
End Try

The code is raising a SqlClient.SqlException:


{"Syntax error converting the varchar value 'Test' to a column of data
type int."}

Since there are multiple integer-type columns in the table, it would be
very helpful to know which of them is the one involved in the
SqlException. === Is it possible to determine that in the debugger? ===

I think the exception may be spurious, for when I look at things in the
Immediate Window, it all looks OK:

?adapter.InsertCommand.Parameters("@addr1").Value
"Test" {String}
String: "Test"


The correct column is being updated with the value 'Test'. I don't see
any parameters with incorrect SourceColumn mappings, and "addr1" the
source column for parameter("@addr1") is not used more than once in the
code where the parameters are instantiated. And in the stored procedure,
the insert(column-list) values(param-list) has correct column-parameter
match up.

Thanks
TR

 

[Cor Ligthert[MVP]]

TR,

Use strongly typed datasets, those are to prefent this kind of trouble.

Cor

 

[RobinS]

You could try removing all of the fields from the query except one, and see
if that works. Then add them back one at a time until you figure out which
one it is.

It looks like you are putting the value 'Test' into a column that is an
integer in the database, from your error message. So what column are you
putting 'Test' into? Maybe your parameters are off.

RobinS.
GoldMail.com

 

[TR]

Hi Robin,
Yes, the error does indicate that an integer column is being assigned
the string 'Test':

{"Syntax error converting the varchar value 'Test' to a column of data
type int."}

The problem is, I don't see any place, either in the code or in the
stored procedure, where that is happening. I've stepped through every
parameter in the InsertCommand.Parameter collection, and looked at their
values in the debugger, and the only parameter that has 'Test' for its
value is a varchar parameter size=40 tied to a varchar(40) parameter in
the stored proc which updates a varchar(40) column in the underlying table.

I would really like to know What integer column has been mapped to a
varchar parameter. Is it possible to determine the /name/ of the integer
column where the datatype mismatch is happening, after the Update raises
an exception?


Regards
TR