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:

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
 
        Dim SQLCon As New SqlClient.SqlConnection("Data Source=BICU111AA\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True")
        SQLCon.Open()
        Dim SQLCom As New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
        Dim DataAdapter As New SqlClient.SqlDataAdapter(SQLCom)
        Dim CommandBuilder As New SqlClient.SqlCommandBuilder(DataAdapter)
        Dim changes As Integer
        changes = DataAdapter.Update(dt)
        DataAdapter.Dispose()
        If changes > 0 Then
            MsgBox(changes & " changed rows were made.")
        Else
            MsgBox("No changes made")
        End If
    End Sub

It reports an error when it reaches "changes = DataAdapter.Update(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.update() 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:

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
 
        Dim SQLCon As New SqlClient.SqlConnection("Data Source=BICU111AA\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True")
        SQLCon.Open()
        Dim SQLCom As New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
        Dim DataAdapter As New SqlClient.SqlDataAdapter(SQLCom)
        Dim CommandBuilder As New SqlClient.SqlCommandBuilder(DataAdapter)
        Dim changes As Integer
        changes = DataAdapter.Update(dt)
        DataAdapter.Dispose()
        If changes > 0 Then
            MsgBox(changes & " changed rows were made.")
        Else
            MsgBox("No changes made")
        End If
    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

[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=DataAdapter.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=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

[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:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim changes As Integer
        Dim SQLCon As New SqlClient.SqlConnection("Data Source=TP-TM3282WXMI\SQLEXPRESS;Initial Catalog=ProteinDatabase;Integrated Security=True;Pooling=False")
        SQLCon.Open()
         Dim DataAdapter As New SqlClient.SqlDataAdapter()
               DataAdapter.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM Table_1", SQLCon)
        Dim CommandBuilder As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(DataAdapter)
        DataAdapter.Fill(dt)
        'CommandBuilder.GetUpdateCommand()
        changes = DataAdapter.Update(dt)
 
        DataAdapter.Dispose()
        If changes > 0 Then
            MsgBox(changes & " changed rows were made.")
        Else
            MsgBox("No changes made")
        End If
    End Sub
 

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.update() method excepts a Dataset as parameter

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

returnValue = da.Update(dataSet)

[gyap88]

How do i convert my datatable to a dataset?