Update a datatable to database
-
Hi guys, I got a datatable from a excel file, and try to update this table onto sql server, in other words, the users can change the table in sql server based on excel file. I use SqlDataAdapter to update the table, but the problem is no change happen on my database.
'... Dim cmdSql As SqlCommand = New SqlCommand cmdSql.Connection = connSql cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id" cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText)) cmdSql.Parameters("@notes").SourceColumn = "notes" cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int)) cmdSql.Parameters("@id").SourceColumn = "ID" ' modify the data for testing dtData.Rows(0)("notes") = "test2" Dim daSql As SqlDataAdapter = New SqlDataAdapter daSql.UpdateCommand = cmdSql daSql.UpdateCommand.Connection.Open() daSql.Update(dtData) '...Above is a piece of codes which i made for testing. Any suggestion would be appreciated! Ming -
Hi guys, I got a datatable from a excel file, and try to update this table onto sql server, in other words, the users can change the table in sql server based on excel file. I use SqlDataAdapter to update the table, but the problem is no change happen on my database.
'... Dim cmdSql As SqlCommand = New SqlCommand cmdSql.Connection = connSql cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id" cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText)) cmdSql.Parameters("@notes").SourceColumn = "notes" cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int)) cmdSql.Parameters("@id").SourceColumn = "ID" ' modify the data for testing dtData.Rows(0)("notes") = "test2" Dim daSql As SqlDataAdapter = New SqlDataAdapter daSql.UpdateCommand = cmdSql daSql.UpdateCommand.Connection.Open() daSql.Update(dtData) '...Above is a piece of codes which i made for testing. Any suggestion would be appreciated! MingBluebamboo wrote:
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"
I believe that the problem lies in here. You'll have to pass values with the variable enclosed between Quotes and Ampersand
'" & notes & "'Having said that,you might want to restructure your code as it is vulnerable to Sql Injections[^].Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime. Pradeep Joe
-
Bluebamboo wrote:
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"
I believe that the problem lies in here. You'll have to pass values with the variable enclosed between Quotes and Ampersand
'" & notes & "'Having said that,you might want to restructure your code as it is vulnerable to Sql Injections[^].Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime. Pradeep Joe
Thanks for your reply I don't think that causes the problem, you can check the solution provided by Micsoft support http://support.microsoft.com/kb/308055[^] and the solution declare a SqlCOmmand with such a query
DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)which is actually perform same function as what I did -
Thanks for your reply I don't think that causes the problem, you can check the solution provided by Micsoft support http://support.microsoft.com/kb/308055[^] and the solution declare a SqlCOmmand with such a query
DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)which is actually perform same function as what I didBluebamboo wrote:
Apologize for my ignorance. I didn't know that before. I tried out that sample that you've guided me with, it works like charm. Can you try providing a mapping table name when you fill and update the adapter (in case you are using a dataset) ?
da = New SqlDataAdapter("select * from CustTest order by CustId", cn) da.Fill(CustomersDataSet, "Customers") da.Update(CustomersDataSet, "Customers")If you already have that in place, kindly update me on how you got it working once you achieve it. Good Luck friend :-)Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime. Pradeep Joe
-
Bluebamboo wrote:
Apologize for my ignorance. I didn't know that before. I tried out that sample that you've guided me with, it works like charm. Can you try providing a mapping table name when you fill and update the adapter (in case you are using a dataset) ?
da = New SqlDataAdapter("select * from CustTest order by CustId", cn) da.Fill(CustomersDataSet, "Customers") da.Update(CustomersDataSet, "Customers")If you already have that in place, kindly update me on how you got it working once you achieve it. Good Luck friend :-)Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime. Pradeep Joe
Hi Joe, Thanks for you suggestion, Problem has been *solved*, I haven't found where exactly cause the problem, because it worked once suddenly, and now it always works. Amazing! I think it is not a problem with the code I pasted, probably it was something wrong with other potential reasons, which I don't know exactly, but one of them what I guess could be this[^] Thanks. Ming
-
Hi Joe, Thanks for you suggestion, Problem has been *solved*, I haven't found where exactly cause the problem, because it worked once suddenly, and now it always works. Amazing! I think it is not a problem with the code I pasted, probably it was something wrong with other potential reasons, which I don't know exactly, but one of them what I guess could be this[^] Thanks. Ming