updating database

texasraven · August 26th, 2003, 04:35 PM

I am trying to do an update on the database using sqldataadapter.update method. I build my own datatable that I need to update the database with. Here is a part of the code:

Dim dt As New DataTable()
Dim ds As New DataSet()
Dim dr As DataRow

Dim keys(2) As DataColumn
Dim col1 As New DataColumn("LMC", GetType(String))
Dim col2 As New DataColumn("UserID", GetType(Short))
keys(0) = col1
keys(1) = col2

dt.Columns.Add(col1)
dt.Columns.Add(col2)

dt.PrimaryKey = keys

dr = dt.NewRow()
dr("UserID") = CType(12, Short)
dr("LMC") = New String("2111150029284")

dt.Rows.Add(dr)
ds.Tables.Add(dt)

I use a dataservice layer blackbox which makes the sqlconnection and gets the selectcommand from an XML file. It doens't explicitly build any updatecommand, but I know that update should still work, because the same dataservice layer update works for another page.

When I try to do an update for this page, sqldataadapter.update fails in the dataservice layer, and I can't figure out why.

All the working examples of update I have seen, have to do with a dataset that has been changed and then used for update, as opposed to create a new one on the fly like I'm doing. I wonder if that might have anything to do with it. What about the acceptchanges method of dataset? Might that have some effect?

I know this is not very specific, but I wonder if anyone might have some ideas to explore. I have tried doing this with and without the primary key.

I will appreciate any help.

Thanks

eitans · September 1st, 2004, 05:42 AM

I have the same problem.
If you found any solution

I will appreciate any help.

Thanks, eitan shapir

Quote:

quote:Originally posted by texasraven
I am trying to do an update on the database using sqldataadapter.update method. I build my own datatable that I need to update the database with. Here is a part of the code:

Dim dt As New DataTable()
Dim ds As New DataSet()
Dim dr As DataRow

Dim keys(2) As DataColumn
Dim col1 As New DataColumn("LMC", GetType(String))
Dim col2 As New DataColumn("UserID", GetType(Short))
keys(0) = col1
keys(1) = col2

dt.Columns.Add(col1)
dt.Columns.Add(col2)

dt.PrimaryKey = keys

dr = dt.NewRow()
dr("UserID") = CType(12, Short)
dr("LMC") = New String("2111150029284")

dt.Rows.Add(dr)
ds.Tables.Add(dt)

I use a dataservice layer blackbox which makes the sqlconnection and gets the selectcommand from an XML file. It doens't explicitly build any updatecommand, but I know that update should still work, because the same dataservice layer update works for another page.

When I try to do an update for this page, sqldataadapter.update fails in the dataservice layer, and I can't figure out why.

All the working examples of update I have seen, have to do with a dataset that has been changed and then used for update, as opposed to create a new one on the fly like I'm doing. I wonder if that might have anything to do with it. What about the acceptchanges method of dataset? Might that have some effect?

I know this is not very specific, but I wonder if anyone might have some ideas to explore. I have tried doing this with and without the primary key.

I will appreciate any help.

Thanks

texasraven · September 21st, 2004, 05:14 PM

It does have to do with the fact that the dataset is new. Since the dataset does not have the metadata of the sql table, it doesn't know how to call the updates.

I'm sure you can do this by somehow retrieving the sql table schema and updating the dataset metadata. That would be the elegant solution, but I'm afraid I don't know how.

Another, perhaps less elegant solution is to do this.

Retrieve a dataset by querying the datatable that you want to update. You can even use a query that returns nothing, such as
SELECT * FROM TABLEIWANTTOUPDATE WHERE 1 = 2.

If you make changes to this dataset and call the update, the update will work since the dataset will now have the schema of the sql table, and it will know how to call updates on the table.

HTH,

texasraven