CODEWITHSUNDEEP
databasevb.netdatagridviewupdating
User59
User59

Reputation: 517

Updating datagridview via textboxes

I have written code to allow me to update values in a datagridview via textboxes however, it isnt working. During runtime, when i press the update button, nothing happens not even an error message popus up.

here is the code:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Connection.ConnectionString = "provider= Microsoft.ACE.OLEDB.12.0;Data Source= SRTDB.accdb"
    Dim connectionString As String = "provider= Microsoft.ACE.OLEDB.12.0;Data Source= SRTDB.accdb"
    Dim SQLQuery As String = "UPDATE Students SET StudentFirstName = @StudentFirstName, " & _
                     "StudentSurname = @StudentSurname, StudentPassword= @StudentPassword, StudentGroup = @Studentgroup " & _
                     "WHERE StudentID = @StudentID"

    Using Connection = New System.Data.OleDb.OleDbConnection(connectionString)
        Using cmd = New System.Data.OleDb.OleDbCommand(SQLQuery, Connection)
            Connection.Open()
            cmd.Parameters.AddWithValue("@StudentID", txtStudentID.Text)
            cmd.Parameters.AddWithValue("@StudentFirstName", txtStudentFirstname.Text)
            cmd.Parameters.AddWithValue("@StudentSurname", txtStudentSurname.Text)
            cmd.Parameters.AddWithValue("@StudentPassword", txtStudentPassword.Text)
            cmd.Parameters.AddWithValue("@StudentGroup", cbxStudentGroup.Text)
            Dim rowsInserted = cmd.ExecuteNonQuery
            If rowsInserted > 0 Then
                MessageBox.Show("Record successfully updated!", "Updated!")
                ShowItems()
            Else
                MessageBox.Show("Failure to update new record!", "Failure!")
            End If
        End Using
    End Using
End Sub

Upvotes: 1

Views: 475

Answers (1)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38865

OleDB does not use named parameters as named parameters - you need to assign the values in the exact order that they appear in the SQL. Your SQL specifies the StudentID last but you assign it as the first parameter. MSDN suggests that you use ? placeholders rather than named ones.

This means when it executes, it will try to update the record WHERE StudentID = cbxStudentGroup.Text. Since there is unlikely to be such a record, it simply doesnt update anything and rowsInserted will be 0.

There is one other thing in your code, plus a few tips:

Private Sub btnUpdate_Click...
    Connection.ConnectionString = "provider= Microsoft.ACE.OLEDB.12.0;..."
    Dim connectionString As String = "provider= Microsoft.ACE...."
    Dim SQLQuery As String = "UPDATE Students SET StudentFirstName = @StudentFirstName, " & _ ...

That first Connection object may be Nothing at times. Since it will be overwritten by the one in the USING block, just get rid of it. You can make your SQL easier to read using XML literals:

Dim SQLQuery As String = <sql>
                UPDATE Students 
                     SET StudentFirstName = @StudentFirstName, 
                         StudentSurname = @StudentSurname, 
                         StudentPassword= @StudentPassword, 
                         StudentGroup = @Studentgroup 
                     WHERE StudentID = @StudentID
                    </sql>.Value

if you add Imports System.Data.OleDb to the top of the code file, you can shorten those references:

Using Connection = New OleDbConnection(connectionString)
    Using cmd = New OleDbCommand(SQLQuery, Connection)
        Connection.Open()
        '...

The likely problem is the order of the parameters:

        cmd.Parameters.AddWithValue("@StudentFirstName", txtStudentFirstname.Text)
        cmd.Parameters.AddWithValue("@StudentSurname", txtStudentSurname.Text)
        cmd.Parameters.AddWithValue("@StudentPassword", txtStudentPassword.Text)
        cmd.Parameters.AddWithValue("@StudentGroup", cbxStudentGroup.Text)
        cmd.Parameters.AddWithValue("@StudentID", txtStudentID.Text)

Obligatory warning: Passwords should never be stored as plaintext, but hashed.

See also:
Can we stop using AddWithValue() already?
GetConnection Method to avoid having to paste your connection string everywhere

Upvotes: 1

Related Questions