Start New Discussion within our Software Development Community

When tried to update the data I got this error

"incorrect syntax near '4' Unclosed quotation mark after the character string"
Can someone fixed this error

 Public Sub Save()
        Dim indate As Date = DateTimePicker1.Value.Date

        If String.IsNullOrEmpty(TextBox1.Text) Or String.IsNullOrEmpty(TextBox2.Text) Then
            'Show your message here
            MsgBox("Please fill all Data")
        Else
            'Show your message here

            If cek_code(TextBox1.Text) = False Then

                myConnection = New SqlConnection(connectionstring)
                myConnection.Open()

                Dim insert As SqlCommand = New SqlCommand(" INSERT INTO Mobilization (Fen_Telephone_No,Fen_Inovoice_No,Fen_VAT_No,Date,Fen_Fax_No ,Source_of_order  ,Totalamount,NBT_Value ,NBT_Amount,VAT_Value ,VAT_Amount ,Grand_total,Contract_Name,Client_Name,Moblization_Value,Moblization_Percentage) VALUES('" & TextBox3.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "','" & DateTimePicker1.Value.Date & "','" & TextBox4.Text & "','" & TextBox12.Text & "','" & TextBox11.Text & "','" & TextBox10.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & TextBox9.Text & "','" & TextBox17.Text & "')", myConnection)

                insert.ExecuteNonQuery()
                insert.Cancel()
                MessageBox.Show("New Contact  detail is Added")
                myConnection.Close()
            Else
                myConnection = New SqlConnection(connectionstring)
                myConnection.Open()
                Dim update_product As SqlCommand = New SqlCommand(" UPDATE Mobilization SET Fen_Telephone_No = '" & TextBox3.Text & "',Date = '" & DateTimePicker1.Value.Date & "',[Fen_Fax_No] = '" & TextBox4.Text & "' ,Source_of_order = '" & TextBox12.Text & "',Totalamount = '" & Val(TextBox11.Text) & "' ,NBT_Amount = '" & Val(TextBox5.Text) & "',VAT_Value = '" & Val(TextBox6.Text) & "' ,VAT_Amount = '" & Val(TextBox6.Text) & "',[Grand_total] = '" & Val(TextBox8.Text) & "',[Contract_Name] = '" & ComboBox1.Text & "',[Client_Name] = '" & ComboBox2.Text & "',[Moblization_Value] = '" & Val(TextBox17.Text) & "' ,[Moblization_Percentage] = '" & Val(TextBox9.Text) & "'' where Fen_Inovoice_No='" & TextBox1.Text & "'", myConnection)
                update_product.ExecuteNonQuery()

                update_product.Cancel()

                MsgBox("Updated succesfully", MsgBoxStyle.Information, "Informasi")

                myConnection.Close()

            End If

        End If

    End Sub

We can't tell you what is wrong with your query unless you show us what the query looks like. It's likely a problem with a missing, superfluous, or mismatched single quote. You should rewrite your code to use parameterized queries. This will make the code easier to read and debug.

Line No 15
Correct this one --> `,'" & TextBox17.Text & "')"`
     To this one --> `,'" & TextBox17.Text & "'")`

Line No 24
Correct this one --> ` "'' where Fen_Inovoice_No=`
     To this one --> ` "' where Fen_Inovoice_No=`

Using paramaterized queries your insert build becomes

cmd.CommandText = "INSERT INTO Mobilization (Fen_Telephone_No, Fen_Inovoice_No," _
                & "    Fen_VAT_No, [Date], Fen_Fax_No, Source_of_order, Totalamount," _
                & "    NBT_Value, NBT_Amount, VAT_Value, VAT_Amount, Grand_total," _
                & "    Contract_Name, Client_Name, Moblization_Value,Moblization_Percentage) " _
                & "VALUES(@Fen_Telephone_No, @Fen_Inovoice_No, @Fen_VAT_No, @Date," _
                & "       @Fen_Fax_No, @Source_of_order, @Totalamount, @NBT_Value," _
                & "       @NBT_Amount, @VAT_Value, @VAT_Amount, @Grand_total, @Contract_Name," _
                & "       @Client_Name, @Moblization_Value, @Moblization_Percentage"

cmd.Parameters.AddWithValue("@Fen_Telephone_No      ", textbox1.Text)
cmd.Parameters.AddWithValue("@Fen_Inovoice_No       ", textbox2.Text)
cmd.Parameters.AddWithValue("@Fen_VAT_No            ", textbox3.Text)
cmd.Parameters.AddWithValue("@Date                  ", textbox4.Text)
cmd.Parameters.AddWithValue("@Fen_Fax_No            ", textbox5.Text)
cmd.Parameters.AddWithValue("@Source_of_order       ", textbox6.Text)
cmd.Parameters.AddWithValue("@Totalamount           ", textbox7.Text)
cmd.Parameters.AddWithValue("@NBT_Value             ", textbox8.Text)
cmd.Parameters.AddWithValue("@NBT_Amount            ", textbox9.Text)
cmd.Parameters.AddWithValue("@VAT_Value             ", textbox10.Text)
cmd.Parameters.AddWithValue("@VAT_Amount            ", textbox11.Text)
cmd.Parameters.AddWithValue("@Grand_total           ", textbox12.Text)
cmd.Parameters.AddWithValue("@Contract_Name         ", textbox13.Text)
cmd.Parameters.AddWithValue("@Client_Name           ", textbox14.Text)
cmd.Parameters.AddWithValue("@Moblization_Value     ", textbox15.Text)
cmd.Parameters.AddWithValue("@Moblization_Percentage", textbox17.Text)

This not only makes it easier to match textboxes to specific fields but it automatically adds single quotes around fields that require quoting. Note that I replaced "Date" in the query with "[Date]" to prevent a conflict with a SQL reserved word. You can modify your update query accordingly.

You also might have a typo in the field name Fen_Inovoice_No. I suspect you want Fen_Invoice_No

This question has already been answered. Start a new discussion instead.