By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,364 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,364 IT Pros & Developers. It's quick & easy.

Save changes to database using datagrid

P: 37
Hello i m using vb 2005 express to do my project. I m suppose to create a datagrid to allow user to make changes to the database. The program display the database in a datagrid where users can juz make changes there. I encountered a problem when user are trying save changes. The following code is executed when the save button is click:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
  2.  
  3.         Dim SQLCon As New SqlClient.SqlConnection("Data Source=BICU111AA\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True")
  4.         SQLCon.Open()
  5.         Dim SQLCom As New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
  6.         Dim DataAdapter As New SqlClient.SqlDataAdapter(SQLCom)
  7.         Dim CommandBuilder As New SqlClient.SqlCommandBuilder(DataAdapter)
  8.         Dim changes As Integer
  9.         changes = DataAdapter.Update(dt)
  10.         DataAdapter.Dispose()
  11.         If changes > 0 Then
  12.             MsgBox(changes & " changed rows were made.")
  13.         Else
  14.             MsgBox("No changes made")
  15.         End If
  16.     End Sub

It reports an error when it reaches "changes = DataAdapter.Update(dt)". Can someone advise me how to debug it?
Nov 16 '07 #1
Share this Question
Share on Google+
11 Replies


debasisdas
Expert 5K+
P: 8,127
Can you please post the exact error message for reference of our experts.
Nov 16 '07 #2

kunal pawar
100+
P: 297
DataAdapter.update() this method expect the Data set as Agumant not DataTable. Plz do tht it may solve your Problem
Nov 16 '07 #3

Frinavale
Expert Mod 5K+
P: 9,731
Hello i m using vb 2005 express to do my project. I m suppose to create a datagrid to allow user to make changes to the database. The program display the database in a datagrid where users can juz make changes there. I encountered a problem when user are trying save changes. The following code is executed when the save button is click:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
  2.  
  3.         Dim SQLCon As New SqlClient.SqlConnection("Data Source=BICU111AA\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True")
  4.         SQLCon.Open()
  5.         Dim SQLCom As New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
  6.         Dim DataAdapter As New SqlClient.SqlDataAdapter(SQLCom)
  7.         Dim CommandBuilder As New SqlClient.SqlCommandBuilder(DataAdapter)
  8.         Dim changes As Integer
  9.         changes = DataAdapter.Update(dt)
  10.         DataAdapter.Dispose()
  11.         If changes > 0 Then
  12.             MsgBox(changes & " changed rows were made.")
  13.         Else
  14.             MsgBox("No changes made")
  15.         End If
  16.     End Sub

It reports an error when it reaches "changes = DataAdapter.Update(dt)". Can someone advise me how to debug it?
First of all...what is your variable dt?
I don't see it declared anywhere in this code.
Secondly, are you actually grabbing any data from your DataGrid while updating?

Thirdly, could you please post the exact error that you are getting so that we can help you solve this problem.

Thanks,

-Frinny
Nov 16 '07 #4

P: 37
The error message is Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information and it points to the line "changes=DataAdapter.Update(dt)"

anyway, the variable, dt is a datatable that i declared in the beginning of the program.
Nov 18 '07 #5

P: 37
The error message is Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information and it points to the line "changes=DataAdapter.Update(dt)"

The variable, dt is a datatable that i declared in the beginning of the program.

Lastly, i need to the program to return the number of rows that has changed after the database is saved...
Hope u can help me out thx
Nov 18 '07 #6

P: 1
Hello, you may do two things to solve:
1)Create the update method yourself, look "Update method data adapter" on google and you'll have plenty.
2)Retrieve the key of the table if any, if you don't have a key you have to create the update method.

C U
Davide
Nov 18 '07 #7

P: 37
hello, i have the code to the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  2.         Dim changes As Integer
  3.         Dim SQLCon As New SqlClient.SqlConnection("Data Source=TP-TM3282WXMI\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True;Pooling=False")
  4.         SQLCon.Open()
  5.          Dim DataAdapter As New SqlClient.SqlDataAdapter()
  6.                DataAdapter.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
  7.         Dim CommandBuilder As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(DataAdapter)
  8.         DataAdapter.Fill(dt)
  9.         'CommandBuilder.GetUpdateCommand()
  10.         changes = DataAdapter.Update(dt)
  11.  
  12.         DataAdapter.Dispose()
  13.         If changes > 0 Then
  14.             MsgBox(changes & " changed rows were made.")
  15.         Else
  16.             MsgBox("No changes made")
  17.         End If
  18.     End Sub
  19.  



But it still displays the same error message
Nov 18 '07 #8

Frinavale
Expert Mod 5K+
P: 9,731
Sorry, but there's no mention here....are you developing a desktop application or a web application?

If you are using a web application and create the DataTable at the beginning of the program, then this table could possibly be lost during PostBack during the next page request. This could explain the error, as you would be trying to update an empty table.
Nov 18 '07 #9

P: 37
I m developing a desktop application
Nov 19 '07 #10

Expert 100+
P: 390
DataAdapter.update() method excepts a Dataset as parameter

Dim da As DataAdapter
Dim dataSet As DataSet
Dim returnValue As Integer

returnValue = da.Update(dataSet)
Nov 19 '07 #11

P: 37
How do i convert my datatable to a dataset?
Nov 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.