VB.NET Cannot Update Access DB

[mikeg71]

Hi All... I am going a little crazy with this today. Cannot seem to get my 'Edit' code to work for updating my Access DB file. I get "No given value for one or more parameters" error when attempting to update. My 'Save' code works just fine and I am using DataGridView to see each record. All items load to my textboxes fine, but when trying to change something and update, I cannot get beyond that without errors. I have stared at it forever trying to determine if there is a type-O. Here is my 'Save' & 'Edit'

Sub save()

        Try

            If MsgBox("Are You Sure Insert This Record", vbQuestion + vbYesNo) = vbYes Then
                conn.Open()
                Dim cmd As New OleDb.OleDbCommand("Insert into HAZREC(`Channel`,`Status`,`Identified_Hazard`,`Regulatory_Standard_Reference`,`Primary`,`Location`,`Date_Reported`
                                                   ,`Planned_Completion`,`Date_Completed`,`Owner`,`Assisting`,`Action_Taken`,`Age`,`Comments`) values(@Channel,@Status,@Identified_Hazard,
                                                   @Regulatory_Standard_Reference,@Primary,@Location,@Date_Reported,@Planned_Completion,@Date_Completed,@Owner,@Assisting,@Action_Taken,@Age,@Comments)", conn)
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Channel", txt_Channel.Text)
                cmd.Parameters.AddWithValue("@Status", txt_Status.Text)
                cmd.Parameters.AddWithValue("@Identified_Hazard", txt_IdentHaz.Text)
                cmd.Parameters.AddWithValue("@Regulatory_Standard_Reference", txt_RegStdRef.Text)
                cmd.Parameters.AddWithValue("@Primary", txt_Primary.Text)
                cmd.Parameters.AddWithValue("@Location", txt_Location.Text)
                cmd.Parameters.AddWithValue("@Date_Reported", txt_DateReport.Text)
                cmd.Parameters.AddWithValue("@Planned_Completion", txt_PLComplete.Text)
                cmd.Parameters.AddWithValue("@Date_Completed", txt_DateComplete.Text)
                cmd.Parameters.AddWithValue("@Owner", txt_Owner.Text)
                cmd.Parameters.AddWithValue("@Assisting", txt_Assist.Text)
                cmd.Parameters.AddWithValue("@Action_Taken", txt_Action.Text)
                cmd.Parameters.AddWithValue("@Age", txt_Age.Text)
                cmd.Parameters.AddWithValue("@Comments", txt_Comments.Text)

                i = cmd.ExecuteNonQuery
                If i > 0 Then
                    MsgBox("Record Saved!", vbInformation)
                Else
                    MsgBox("Record Failed to Save!", vbCritical)
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        conn.Close()
        loadingDatagridView()
        clear()
End Sub


Sub edit()

        Try
            conn.Open()
            Dim cmd As New OleDb.OleDbCommand("UPDATE HAZREC Set `Channel`=@Channel,`Status`=@Status,`Identified_Hazard`=@Identified_Hazard,`Regulatory_Standard_Reference`=@Regulatory_Standard_Reference,
                                              `Primary`=@Primary,`Location`=@Location,`Date_Reported`=@Date_Reported,`Planned_Completion`=@Planned_Completion,`Date_Completed`=@Date_Completed,
                                              `Owner`=@Owner,`Assisting`=@Assisting,`Action_Taken`=@Action_Taken,`Age`=@Age,`Comments`=@Comments Where ID=@ID", conn)
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@Channel", txt_Channel.Text)
            cmd.Parameters.AddWithValue("@Status", txt_Status.Text)
            cmd.Parameters.AddWithValue("@Identified_Hazard", txt_IdentHaz.Text)
            cmd.Parameters.AddWithValue("@Regulatory_Standard_Reference", txt_RegStdRef.Text)
            cmd.Parameters.AddWithValue("@Primary", txt_Primary.Text)
            cmd.Parameters.AddWithValue("@Location", txt_Location.Text)
            cmd.Parameters.AddWithValue("@Date_Reported", txt_DateReport.Text)
            cmd.Parameters.AddWithValue("@Planned_Completion", txt_PLComplete.Text)
            cmd.Parameters.AddWithValue("@Date_Completed", txt_DateComplete.Text)
            cmd.Parameters.AddWithValue("@Owner", txt_Owner.Text)
            cmd.Parameters.AddWithValue("@Assisting", txt_Assist.Text)
            cmd.Parameters.AddWithValue("@Action_Taken", txt_Action.Text)
            cmd.Parameters.AddWithValue("@Age", txt_Age.Text)
            cmd.Parameters.AddWithValue("@Comments", txt_Comments.Text)

            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("Record Update Success !", vbInformation)
            Else
                MsgBox("Failed", vbCritical)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        conn.Close()
        loadingDatagridView()
        clear()
End Sub

[OptionBase1]

Your update command has a parameter of @ID that you are never adding.

[mikeg71]

Thanks for the reply. Do I need to have something there at all?

Would this be correct?

Where ID=" & Me.txt_ID.Text, conn

[jmcilhinney]

You need to set the value of the parameter, just like you're doing for all the others. Why would it be different?

[mikeg71]

I am auto-incrementing the ID, so I don't want to allow for any changes to that field. But I am not certain how I should be dealing with that.

[Shaggy Hiker]

It's an UPDATE query, so the ID won't change. You have the parameter in the WHERE clause, so you are using it to select which record you want to update. You have it as a parameter, so you need to supply a value for it just like you do for all the other parameters.

You showed an alternative approach in your post #3, which would not use a parameter, but that's a really bad idea. Concatenating user content directly into a query leaves you open to SQL injection exploits. You are right to use a parameter in the WHERE clause, as you are, but you do have to supply the value for that parameter.

[mikeg71]

Thanks All for the replies, this is making more sense now. I added the Parameter and it seems to be working now.