Update database from dataset

[Jimmy]

Hi all,

I am new to .NET, Any idea that I can't get the below to work?

It gave me this error:
<An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in system.data.dll
Additional information: System error.>

Any help would be greatly appreciated.


Assumption: myConnectionString is correctly defined.
----------------------------------------------------
SqlConnection myConn = new SqlConnection(myConnectionString);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM customer",
myConn);

SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);

myDataAdapter.UpdateCommand = custCB.GetUpdateCommand();

myConn.Open();

DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS,myTableName);

//code to modify data in dataset here
//**** NOTE: it go works fine if I take out this line ***//
custDS.Tables[0].Rows[0]["Description"] = "new description";

//**** Error generated here ***
myDataAdapter.Update(custDS, myTableName);

myConn.Close();
----------------------------------------------------------

 

[fbhcah]

Hi Jimmy,

FYI, you needn't explicitly set the data adapter's update command.

Does the table have at least a primary key or unique key column?

HTH,
fbhcah

 

[Jimmy Tieu]

The table definitely has a primary key. I have checked every possibility
of the error but stil couldn't get it working properly.

Love to more help from you.

 

[Ravichandran J.V.]

AcceptChanges is missing?

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[Jon Skeet [C# MVP]]

AcceptChanges is missing?

Usually, client code shouldn't call AcceptChanges itself - the data
adapter does it at the end of the Update call.

 

[Ron Allen]

Jimmy,
If one of the field names returned by the select is a reserved word you
will get an error. Try setting the QuotePrefix and QuoteSuffix properties
of the CommandBuilder with either backticks (`) or "[" and "]" and see if
that works.
If you don't modify the table the update won't do anything so it will
work.
If this still doen't work put the Update in a try block and catch the
SqlException to get its' message for more detail on the error.
Ron Allen

 

[Jimmy Tieu]

Thanks Ron,

It is as what you said, when I use "[" and "]" for prefix and postfix
then it works fine. Thanks alot for your help.

Now, I have another question. If we select a table that has no Primary
key defined, do we have to create our own SqlCommand, can't
CommandBuilder handle it. If it's the case then there is not much use
with CommandBuilder then.

Again, thank you all of you have participated in my post.

Regards,
Jimmy