no value given for one or more required parameters in updating deleting records

Question

I cant figure out whats wrong with this,when i update a record from my list view a pop up appears saying no value given for one or more required parameters.

heres my code:

 Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
    Try
        Dim SqlQuery As String = "UPDATE UsersTable Set AccountType = '" & CmbAccountType.Text & "' , Username = '" & TxtUsername.Text & "' , UserPassword = '" & TxtPassword.Text & "' , Firstname = '" & TxtFirstname.Text & "' , Lastname = '" & TxtLastname.Text & "' , Sex = '" & CmbSex.Text & "',Birthdate = '" & DateTimePickerBirthdate.Text & "' , ContactNumber = '" & TxtContact.Text & "' , Address = '" & TxtAddress.Text & "' WHERE UserID = " & id & ";"
        Dim SqlCommand As New OleDbCommand
        With SqlCommand
            .CommandText = SqlQuery
            .Connection = Conn
            .ExecuteNonQuery()
        End With
        MsgBox("Account successfully updated!")
        loadlistview()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
    Try
        Dim SqlQuery As String = "DELETE FROM ProductTable WHERE UserID = " & id & ";"
        Dim SqlCommand As New OleDbCommand
        With SqlCommand
            .CommandText = SqlQuery
            .Connection = Conn
            .ExecuteNonQuery()
        End With
        MsgBox("Account deleted.")
        loadlistview()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

http://stackoverflow.com/a/26258610/1070452 fix your SQL to use parameters and you likely be able to see the error easily. — Ňɏssa Pøngjǣrdenlarp, Oct 12 '14 at 01:16
Can you give me example with update statement?im just a newbie cant follow it. — jeromeee, Oct 12 '14 at 03:21
it is a full and complete itemized description for SQL Parameters. INSERTS work the same — Ňɏssa Pøngjǣrdenlarp, Oct 12 '14 at 11:17

score 0 · Answer 1 · answered Oct 12 '14 at 04:40

0

The problem is in the WHERE condition which you used in your query. so you can correct it by using the following query

In update query :

 Dim SqlQuery As String = "UPDATE UsersTable Set AccountType = '" & CmbAccountType.Text & "'....................." & _
 "WHERE UserID = '" & id & "';" '<----- you miss a single quote in where clause.

In Delete query :

Dim SqlQuery As String = "DELETE FROM ProductTable WHERE UserID = '" & id & "';"

answered Oct 12 '14 at 04:40

Thanks for your response i did what you say now im getting different error in my program it says:syntax error in string query expression 'UserId = '1;'. – jeromeee Oct 12 '14 at 06:02
check whether you are giving like this `"';"` or like this `";'"` – Oct 12 '14 at 06:28
its like this >WHERE User ID = ' " & id & " ';" – jeromeee Oct 12 '14 at 08:50

no value given for one or more required parameters in updating deleting records

1 Answers1