Save changes to database using datagrid

[gyap88]

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:


[code=vbnet]
Private Sub Save_Click(ByVa l sender As System.Object, ByVal e As System.EventArg s) Handles Save.Click

Dim SQLCon As New SqlClient.SqlCo nnection("Data Source=BICU111A A\SQLEXPRESS;In itial Catalog=Protein Database;Integr ated Security=True")
SQLCon.Open()
Dim SQLCom As New SqlClient.SqlCo mmand("SELECT * FROM Table_1", SQLCon)
Dim DataAdapter As New SqlClient.SqlDa taAdapter(SQLCo m)
Dim CommandBuilder As New SqlClient.SqlCo mmandBuilder(Da taAdapter)
Dim changes As Integer
changes = DataAdapter.Upd ate(dt)
DataAdapter.Dis pose()
If changes > 0 Then
MsgBox(changes & " changed rows were made.")
Else
MsgBox("No changes made")
End If
End Sub[/code]


It reports an error when it reaches "changes = DataAdapter.Upd ate(dt)". Can someone advise me how to debug it?

[debasisdas]

Can you please post the exact error message for reference of our experts.

[kunal pawar]

DataAdapter.upd ate() this method expect the Data set as Agumant not DataTable. Plz do tht it may solve your Problem

[Frinavale]

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:


[code=vbnet]
Private Sub Save_Click(ByVa l sender As System.Object, ByVal e As System.EventArg s) Handles Save.Click

Dim SQLCon As New SqlClient.SqlCo nnection("Data Source=BICU111A A\SQLEXPRESS;In itial Catalog=Protein Database;Integr ated Security=True")
SQLCon.Open()
Dim SQLCom As New SqlClient.SqlCo mmand("SELECT * FROM Table_1", SQLCon)
Dim DataAdapter As New SqlClient.SqlDa taAdapter(SQLCo m)
Dim CommandBuilder As New SqlClient.SqlCo mmandBuilder(Da taAdapter)
Dim changes As Integer
changes = DataAdapter.Upd ate(dt)
DataAdapter.Dis pose()
If changes > 0 Then
MsgBox(changes & " changed rows were made.")
Else
MsgBox("No changes made")
End If
End Sub[/code]


It reports an error when it reaches "changes = DataAdapter.Upd ate(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

[gyap88]

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=DataAd apter.Update(dt )"

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

[gyap88]

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=DataAd apter.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

[fenix71]

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

[gyap88]

hello, i have the code to the following:
[code=vbnet]
Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button2.Click
Dim changes As Integer
Dim SQLCon As New SqlClient.SqlCo nnection("Data Source=TP-TM3282WXMI\SQLE XPRESS;Initial Catalog=Protein Database;Integr ated Security=True;P ooling=False")
SQLCon.Open()
Dim DataAdapter As New SqlClient.SqlDa taAdapter()
DataAdapter.Sel ectCommand = New SqlClient.SqlCo mmand("SELECT * FROM Table_1", SQLCon)
Dim CommandBuilder As SqlClient.SqlCo mmandBuilder = New SqlClient.SqlCo mmandBuilder(Da taAdapter)
DataAdapter.Fil l(dt)
'CommandBuilder .GetUpdateComma nd()
changes = DataAdapter.Upd ate(dt)

DataAdapter.Dis pose()
If changes > 0 Then
MsgBox(changes & " changed rows were made.")
Else
MsgBox("No changes made")
End If
End Sub
[/code]




But it still displays the same error message

[Frinavale]

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.

[gyap88]

I m developing a desktop application

[mzmishra]

DataAdapter.upd ate() method excepts a Dataset as parameter

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

returnValue = da.Update(dataS et)

[gyap88]

How do i convert my datatable to a dataset?