CODEWITHSUNDEEP
vb.netms-accesssql-update
Paulinus Lambert
Paulinus Lambert

Reputation: 11

Why does my UPDATE trigger "Syntax error in string in query expression" error?

I can execute an Access SQL INSERT statement without error, but when I attempt an UPDATE with the code below, it throws the error:

"Syntax error in string in query expression".

Why does that happen and how can I fix it?

Here is my code..

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    If txtName.Text = Nothing Or dtpDateBirth.Text = Nothing Or txtPhoneNumber.Text = Nothing Or txtEmail.Text = Nothing Or txtID.Text = Nothing Or mtbNPWP.Text = Nothing Or rtbAddress.Text = Nothing Or txtVillage.Text = Nothing Or txtSubDistrict.Text = Nothing Or txtCity.Text = Nothing Or txtPostalCode.Text = Nothing Or txtEmergencyName.Text = Nothing Or txtEmergencyNumber.Text = Nothing Or txtEmergencyEmail.Text = Nothing Then
        MsgBox("No data has been update", MsgBoxStyle.Information, "No data edit")
        Exit Sub
    Else
        Call koneksi()
        cmd = New OleDb.OleDbCommand("update t_customer set name='" & txtName.Text & "', date_birth='" & dtpDateBirth.Text & "', phone_number='" & txtPhoneNumber.Text & "', email='" & txtEmail.Text & "', id='" & txtID.Text & "', npwp='" & mtbNPWP.Text & "', address='" & rtbAddress.Text & "', village='" & txtVillage.Text & "', sub_district='" & txtSubDistrict.Text & "', city='" & txtCity.Text & "', postal_code='" & txtPostalCode.Text & "', gender='" & gen & "', emergency_name='" & txtEmergencyName.Text & "', phone_emergency='" & txtPhoneNumber.Text & "', email_emergency='" & txtEmergencyEmail.Text & "' where [codeCust]='" & txtIDCust.Text, conn)
        cmd.ExecuteNonQuery()
        MsgBox("Update data success!", MsgBoxStyle.Information, "Update")
    End If
    Call disabled()
    Call showData()
End Sub

Upvotes: 0

Views: 81

Answers (2)

Mr. Tripodi
Mr. Tripodi

Reputation: 827

As HansUp has suggested with the link you should be using parameters. Also you are not disposing the command. Also AFAIK (I thought) you needed the open the connection before you can ExecuteNonQuery. As for the Data Mismatch youre experiencing, I think it's because you have a ' in the where clause that shouldnt be there, assuming that the ID is an INT.

    Using OledbConn As New OleDbConnection("Conn String")

        OledbConn.Open()
        Using t_customerCMD As New OleDbCommand("UPDATE t_customer SET [Col1]=@Col1,[Col2]=@Col2 WHERE [codeCust]=@codeCust", OledbConn)
            With t_customerCMD
                .Parameters.Add("@Col1", SqlDbType.VarChar).Value = TextBox1.Text
                .Parameters.Add("@Col2", SqlDbType.VarChar).Value = TextBox2.Text
                .Parameters.Add("@codeCust", SqlDbType.Int).Value = CInt(txtIDCust.Text) 'Assumes ID is integer
                .ExecuteNonQuery()
            End With
        End Using
        OledbConn.Close()

    End Using
End Sub

Upvotes: 2

Paulinus Lambert
Paulinus Lambert

Reputation: 11

Thank you for everyone who helped me. I thank you very much. This problem has been resolved. this is my completion code, which I can finally do to update the database.

   Sub updateData()
    Call gender()
    If String.IsNullOrEmpty(txtName.Text) OrElse String.IsNullOrEmpty(dtpDateBirth.Text) OrElse String.IsNullOrEmpty(txtPhoneNumber.Text) OrElse String.IsNullOrEmpty(txtEmail.Text) OrElse String.IsNullOrEmpty(txtID.Text) OrElse String.IsNullOrEmpty(mtbNPWP.Text) OrElse String.IsNullOrEmpty(rtbAddress.Text) OrElse String.IsNullOrEmpty(txtVillage.Text) OrElse String.IsNullOrEmpty(txtSubDistrict.Text) OrElse String.IsNullOrEmpty(txtCity.Text) OrElse String.IsNullOrEmpty(txtPostalCode.Text) OrElse String.IsNullOrEmpty(txtEmergencyName.Text) OrElse String.IsNullOrEmpty(txtEmergencyNumber.Text) OrElse String.IsNullOrEmpty(txtEmergencyEmail.Text) Then
        MsgBox("No data has been update", MsgBoxStyle.Information, "No data edit")
        Exit Sub
    Else
        Try
            Call koneksi()
            Using cmd = New OleDb.OleDbCommand("update t_customer set name='" & txtName.Text & "', date_birth='" & dtpDateBirth.Text & "', phone_number='" & txtPhoneNumber.Text & "', email='" & txtEmail.Text & "', id='" & txtID.Text & "', npwp='" & mtbNPWP.Text & "', address='" & rtbAddress.Text & "', village='" & txtVillage.Text & "', sub_district='" & txtSubDistrict.Text & "', city='" & txtCity.Text & "', postal_code='" & txtPostalCode.Text & "', gender='" & gen & "', emergency_name='" & txtEmergencyName.Text & "', phone_emergency='" & txtPhoneNumber.Text & "', email_emergency='" & txtEmergencyEmail.Text & "' where [codeCust]=@codeCust", conn)
                cmd.Parameters.Add("codeCust", CType(txtIDCust.Text, String))
                cmd.ExecuteNonQuery()
                conn.Close()
                MsgBox("Update data success!", MsgBoxStyle.Information, "Update")
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End If
End Sub

Because I use the radio button for gender options, I added this so that gender can be changed.

Private Sub gender()
    Call koneksi()
    Dim str As String
    str = "Insert into t_customer([gender]) Values (?)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, conn)
    If rdnMale.Checked = True Then
        gen = rdnMale.Text
        cmd.Parameters.Add(New OleDbParameter("gender", CType(gen, String)))
    Else
        gen = rdnFemale.Text
    End If
End Sub

Upvotes: 0

Related Questions