Error when updating database using the dataadapter.update method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get an Error when i try to update a row in a table using the
dataadapter.update method.

The table in this case is only two columns, the 1st col is the key
(bucket_status) and
the 2nd column is a string (Comments) and can be null.
this is a parent table to another child table, my data base contains 7
tables that are all related.

when a change is done to the data in the datagrid, i extract the changed
records from the dataset,
then i pass the new dataset (with the changed rows) to the
dataadapter.update method.

When i try to change the value of the comments column, i get the following
error:
"[DB2/LINUX390] SQL0418N A statement contains a use of a parameter marker
that is not valid. SQLSTATE=42610"

The SQL code for the dataadapter was auto generated and it looks like this:
UPDATE dbname.Bucket_tbl
SET BUCKET_STATUS = ?, COMMENTS = ?
WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS IS NULL)

my code is fairly simple:
daBucketStatus.Update(dsChanges)

This is a DB2 database and i am using an OleDb connection.
* inserts and selects work ok, problem is with the update and delete methods.

I can run the generated SQL code in the command window with the values
substitued for the parms and it works ok, however, the update method always
returns the above error?!!

Please help!
Thanks
Riad Amro
 
Salam Malik,

As you can see from my post, the sytax was auto generated by the
dataadapter. I am not sure how much leverage i have over how the code is
generated.
I am assuming the parameter substitution occurs in the application prior to
sending the SQL to the DB2 db, right?

Thanks!
Riad

Sahil Malik said:
I suggested instead of using "?" parameters use Db2Parameter and "@" like
Db2 specific syntax.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Riad said:
I get an Error when i try to update a row in a table using the
dataadapter.update method.

The table in this case is only two columns, the 1st col is the key
(bucket_status) and
the 2nd column is a string (Comments) and can be null.
this is a parent table to another child table, my data base contains 7
tables that are all related.

when a change is done to the data in the datagrid, i extract the changed
records from the dataset,
then i pass the new dataset (with the changed rows) to the
dataadapter.update method.

When i try to change the value of the comments column, i get the following
error:
"[DB2/LINUX390] SQL0418N A statement contains a use of a parameter marker
that is not valid. SQLSTATE=42610"

The SQL code for the dataadapter was auto generated and it looks like
this:
UPDATE dbname.Bucket_tbl
SET BUCKET_STATUS = ?, COMMENTS = ?
WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS IS
NULL)

my code is fairly simple:
daBucketStatus.Update(dsChanges)

This is a DB2 database and i am using an OleDb connection.
* inserts and selects work ok, problem is with the update and delete
methods.

I can run the generated SQL code in the command window with the values
substitued for the parms and it works ok, however, the update method
always
returns the above error?!!

Please help!
Thanks
Riad Amro
Click to expand...
Click to expand...
 
But you can specify your own command on the
InsertCommand/DeleteCommand/UpdateCommand properties right?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------------------------

Riad said:
Salam Malik,

As you can see from my post, the sytax was auto generated by the
dataadapter. I am not sure how much leverage i have over how the code is
generated.
I am assuming the parameter substitution occurs in the application prior
to
sending the SQL to the DB2 db, right?

Thanks!
Riad

Sahil Malik said:
I suggested instead of using "?" parameters use Db2Parameter and "@" like
Db2 specific syntax.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Riad said:
I get an Error when i try to update a row in a table using the
dataadapter.update method.

The table in this case is only two columns, the 1st col is the key
(bucket_status) and
the 2nd column is a string (Comments) and can be null.
this is a parent table to another child table, my data base contains 7
tables that are all related.

when a change is done to the data in the datagrid, i extract the
changed
records from the dataset,
then i pass the new dataset (with the changed rows) to the
dataadapter.update method.

When i try to change the value of the comments column, i get the
following
error:
"[DB2/LINUX390] SQL0418N A statement contains a use of a parameter
marker
that is not valid. SQLSTATE=42610"

The SQL code for the dataadapter was auto generated and it looks like
this:
UPDATE dbname.Bucket_tbl
SET BUCKET_STATUS = ?, COMMENTS = ?
WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS
IS
NULL)

my code is fairly simple:
daBucketStatus.Update(dsChanges)

This is a DB2 database and i am using an OleDb connection.
* inserts and selects work ok, problem is with the update and delete
methods.

I can run the generated SQL code in the command window with the values
substitued for the parms and it works ok, however, the update method
always
returns the above error?!!

Please help!
Thanks
Riad Amro
Click to expand...
Click to expand...
Click to expand...
 
Yes, you can change the command text at design time, however, it does not
accept "@" as a parameter marker.
The whole point of the dataadapter is to generate accurate sql code that
will work without having to change it, are you saying there is a bug in the
dataadapter?

the problem is i am limited in how much debuging i can do, i am unable to
debug the update method itself to see how it is substituting the values for
the parameters.
My feeling is that it is passing the SQL text and the parameter collection
seperatly causing DB2 not to recognize/like the parameter markers.
This should not happen though considering the oledbadapter is designed to
handle db2 ....

this really sucks, not sure where to go from here.

thanks
Riad

Sahil Malik said:
But you can specify your own command on the
InsertCommand/DeleteCommand/UpdateCommand properties right?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------------------------

Riad said:
Salam Malik,

As you can see from my post, the sytax was auto generated by the
dataadapter. I am not sure how much leverage i have over how the code is
generated.
I am assuming the parameter substitution occurs in the application prior
to
sending the SQL to the DB2 db, right?

Thanks!
Riad

Sahil Malik said:
I suggested instead of using "?" parameters use Db2Parameter and "@" like
Db2 specific syntax.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I get an Error when i try to update a row in a table using the
dataadapter.update method.

The table in this case is only two columns, the 1st col is the key
(bucket_status) and
the 2nd column is a string (Comments) and can be null.
this is a parent table to another child table, my data base contains 7
tables that are all related.

when a change is done to the data in the datagrid, i extract the
changed
records from the dataset,
then i pass the new dataset (with the changed rows) to the
dataadapter.update method.

When i try to change the value of the comments column, i get the
following
error:
"[DB2/LINUX390] SQL0418N A statement contains a use of a parameter
marker
that is not valid. SQLSTATE=42610"

The SQL code for the dataadapter was auto generated and it looks like
this:
UPDATE dbname.Bucket_tbl
SET BUCKET_STATUS = ?, COMMENTS = ?
WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS
IS
NULL)

my code is fairly simple:
daBucketStatus.Update(dsChanges)

This is a DB2 database and i am using an OleDb connection.
* inserts and selects work ok, problem is with the update and delete
methods.

I can run the generated SQL code in the command window with the values
substitued for the parms and it works ok, however, the update method
always
returns the above error?!!

Please help!
Thanks
Riad Amro
Click to expand...
Click to expand...
Click to expand...
 
I cannot definitively say there is a bug in anything without actually seeing
it. Sorry I don't have access to a DB2 database to help.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Riad said:
Yes, you can change the command text at design time, however, it does not
accept "@" as a parameter marker.
The whole point of the dataadapter is to generate accurate sql code that
will work without having to change it, are you saying there is a bug in
the
dataadapter?

the problem is i am limited in how much debuging i can do, i am unable to
debug the update method itself to see how it is substituting the values
for
the parameters.
My feeling is that it is passing the SQL text and the parameter collection
seperatly causing DB2 not to recognize/like the parameter markers.
This should not happen though considering the oledbadapter is designed to
handle db2 ....

this really sucks, not sure where to go from here.

thanks
Riad

Sahil Malik said:
But you can specify your own command on the
InsertCommand/DeleteCommand/UpdateCommand properties right?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------------------------

Riad said:
Salam Malik,

As you can see from my post, the sytax was auto generated by the
dataadapter. I am not sure how much leverage i have over how the code
is
generated.
I am assuming the parameter substitution occurs in the application
prior
to
sending the SQL to the DB2 db, right?

Thanks!
Riad

:

I suggested instead of using "?" parameters use Db2Parameter and "@"
like
Db2 specific syntax.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I get an Error when i try to update a row in a table using the
dataadapter.update method.

The table in this case is only two columns, the 1st col is the key
(bucket_status) and
the 2nd column is a string (Comments) and can be null.
this is a parent table to another child table, my data base contains
7
tables that are all related.

when a change is done to the data in the datagrid, i extract the
changed
records from the dataset,
then i pass the new dataset (with the changed rows) to the
dataadapter.update method.

When i try to change the value of the comments column, i get the
following
error:
"[DB2/LINUX390] SQL0418N A statement contains a use of a parameter
marker
that is not valid. SQLSTATE=42610"

The SQL code for the dataadapter was auto generated and it looks
like
this:
UPDATE dbname.Bucket_tbl
SET BUCKET_STATUS = ?, COMMENTS = ?
WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND
COMMENTS
IS
NULL)

my code is fairly simple:
daBucketStatus.Update(dsChanges)

This is a DB2 database and i am using an OleDb connection.
* inserts and selects work ok, problem is with the update and delete
methods.

I can run the generated SQL code in the command window with the
values
substitued for the parms and it works ok, however, the update method
always
returns the above error?!!

Please help!
Thanks
Riad Amro
Click to expand...
Click to expand...
Click to expand...
 
Back
Top