468,549 Members | 2,070 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,549 developers. It's quick & easy.

Save changes to database using datagrid

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
11 3738
debasisdas
8,127 Expert 4TB
Can you please post the exact error message for reference of our experts.
Nov 16 '07 #2
kunal pawar
297 100+
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
9,735 Expert Mod 8TB
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
gyap88
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
gyap88
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
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
gyap88
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
9,735 Expert Mod 8TB
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
gyap88
37
I m developing a desktop application
Nov 19 '07 #10
mzmishra
390 Expert 256MB
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
gyap88
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.

Similar topics

2 posts views Thread by Angie | last post: by
3 posts views Thread by Islamegy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.