saving Datagrid edits to the Database

[eljapo4]

Hi - I'm creating an admin application that will allow specific users to manage users login credentials for an application. On a form I have a datagrid that is populated by selecting * from Operator table on the DB. I want the admin users to be able to add, edit and delete records from the datagrid and i have enabled these properties on the grid but any changes need to be confirmed by clicking a 'Saves Changes' button. Here's the code behind this button:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Const cSUBNAME As String = "btnSave_Click"
 
        Try
 
            'Open the DataBase connection which will be used to execute the SP
            If DatabaseConnectionOpen() = False Then
                Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
                GoTo ExitNow
            End If
 
 
            Dim cb As New SqlCommandBuilder(da)
 
            cb.GetUpdateCommand()
            da.Update(dt)
 
            DatabaseConnectionClose()
 
            FillOperatorGrid()
ExitNow:
        Catch ex As Exception
            Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
        End Try
 
    End Sub

I have added a new record but when I save these changes I am getting this error: "02/12/2011 11:43:56,frmOperator,btnSave_Click,5,Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

Can anyone enlighten me as to what is wrong please?

[.paul.]

there's no key column information because you're using:

SELECT *

you need to use:

SELECT field1, field2, field3 FROM tableName

then you should be able to generate an updateCommand

[eljapo4]

I have changed my Select and am still getting the same error, here's my revised code to fill the datagrid

Public Function FillOperatorGrid()
        Const cSUBNAME As String = "FillOperatorGrid"
 
        Dim lstrSQL As String
 
        Try
            If gDebugging Then Debugging("Start")
 
            'Open the DataBase connection which will be used to execute the SP
            If DatabaseConnectionOpen() = False Then
                Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
                GoTo ExitNow
            End If
 
            lstrSQL = "SELECT Operator, Description, Password, Protean_ID, Site, Area, Flag_Schedule, Flag_Declaration, Flag_Movement, Flag_PickShip FROM dbo.mp_SUFrance_Operator"
 
            'Create New Sql Command to execute Insert statement
            Dim cmd As New SqlCommand(lstrSQL, gSQLcn)
            Dim value As Integer = 600
            cmd.CommandTimeout = value
            cmd.ExecuteNonQuery()
 
            da = New SqlDataAdapter(cmd)
            da.Fill(dt)
 
            dgOperator.DataSource = dt
 
            cmd.Dispose()
 
            DatabaseConnectionClose()
 
            dgOperator.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            If gDebugging Then Debugging("Start")
ExitNow:
            Exit Function
        Catch ex As Exception
            Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
        End Try
    End Function

[.paul.]

How to update a database from a DataSet object by using Visual Basic .NET:

http://support.microsoft.com/kb/301248

[eljapo4]

it turns out that the Database table to be updated needs to have a primary key set for dataset edits and deletes to update the DB table. Thanks to your link above my code for retrieving the data and doing the updates now looks like this:

Private Sub frmAppParameters_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Const cSUBNAME As String = "frmAppParameters_Load"
 
        Try
 
            If DatabaseConnectionOpen() = False Then
                Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
                GoTo ExitNow
            End If
 
            lstrSQL = "SELECT Printer AS 'Id imprimante'," & _
                  " IPAddress AS 'Adresse IP'," & _
                  " Description," & _
                  " Comment AS 'Commentaires' " & _
                  " FROM dbo.mp_SUFrance_Printer"
 
            Dim cmd As New SqlCommand(lstrSQL, gSQLcn)
            Dim value As Integer = 600
            cmd.CommandTimeout = value
            cmd.ExecuteNonQuery()
 
            adapter = New SqlDataAdapter(cmd)
            adapter.Fill(ds)
            DatabaseConnectionClose()
            dg.DataSource = ds.Tables(0)
 
ExitNow:
            Exit Sub
        Catch ex As Exception
            Call WriteError(cMODULENAME, cSUBNAME, Err.Number, Err.Description)
        End Try
    End Sub
 
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
 
        Try
            cmdBuilder = New SqlCommandBuilder(adapter)
            changes = ds.GetChanges()
            If changes IsNot Nothing Then
                adapter.Update(changes)
            End If
            MsgBox("These changes have been updated in the Database")
            dg.RefreshEdit()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
 
    End Sub
 
    Private Sub btnReturn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReturn.Click
 
        dg.EndEdit()
        Me.Close()
        frmMain.Show()
 
    End Sub

[jmcilhinney]

Why are you executing your query twice? Why are you executing your query with a call to ExecuteNonQuery?

Is it really reasonable to wait 10 minutes for your query to execute? How much data are you retrieving?

[eljapo4]

at present the data being returned is not a lot but over time the table size will increase but reducing the timeout value is something I will consider.

As for executing the query twice, I had used the ExecuteNonQuery in another application and had copied it across without actually realising that I was doing this twice, thanks for highlighting that.