Syntax error in INSERT INTO statement.

Question

hi

 i have written code in vb.net (shown below). while executing command i got the error "Syntax error in INSERT INTO statement." i have given value according to the database properties.

please tell me where i am getting wrong

my code:

(oledb connection is used)

Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        If CmbDonT.SelectedItem = "Monthly" Then
            Dim Com6 As New OleDbCommand("INSERT INTO Donation_Noti_Monthly (DID,DonName,Address,ContNo,Remark,DonDay,Status,EntBy) VALUES (" & Val(TxtDonorId.Text) & ",'" & TxtDonorName.Text & "', '" & TxtAddl1.Text & "'," & TxtConNoP.Text & ",'" & TxtRem.Text & "'," & Val(CmbMonth.Text) & ", 1,'" & Main.lblUname.Text & "')", Cn)
            Com6.ExecuteNonQuery()
            MessageBox.Show("saved", "Message By System", MessageBoxButtons.OK, MessageBoxIcon.Information)

        ElseIf CmbDonT.SelectedItem = "Yearly" Then
            Dim Com3 As New OleDbCommand("INSERT INTO Donation_Noti_Yearly (DID,DonName,Address,ContNo,Remark,DonDate,Status,EntBy) VALUES (" & Val(TxtDonorId.Text) & ",'" & TxtDonorName.Text & "', '" & TxtAddl1.Text & "'," & TxtConNoP.Text & ",'" & TxtRem.Text & "',#" & DtpYear.Value.ToString("yyyy-MM-dd") & "#, 1,'" & Main.lblUname.Text & "')", Cn)
            Com3.ExecuteNonQuery()
            MessageBox.Show("saved", "Message By System", MessageBoxButtons.OK, MessageBoxIcon.Information)

Answer 1

Hello,

I would recommend use parameters for your OleDbCommand object rather than using string concatenation to create your insert statement. String concatenation may be done but leaves you open to things like missing apostrophes, values not formatted correctly or embedded apostrophes.

Here is a simple example using parameters. Note the SQL statement is created with xml literals, .Value essentially strips out the tags and leaves you with the SQL INSERT statement.

Public Sub AddNew(ByVal CompanyName As String, ByVal ContactName As String, ByVal Process As Boolean)
    Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
        Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText =
                <SQL>
                    INSERT INTO Customer 
                    (
                        CompanyName,
                        ContactName,
                        Process
                    ) 
                    Values
                    (
                        @CompanyName,
                        @ContactName,
                        @Process
                    )
                </SQL>.Value

            cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
            cmd.Parameters.AddWithValue("@ContactName", ContactName)
            cmd.Parameters.AddWithValue("@Process", Process)

            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

Answer 2

I agree with Kevininstructor. Also, you don't want to convert the date to string. Building SQL statements in this way is a SQL injection vulnerabilty.

Dim Com3 As New OleDbCommand("INSERT INTO Donation_Noti_Yearly (DID, DonName, Address, ContNo, Remark, DonDate, Status, EntBy) VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7, @Param8)

Com3.Parameters.AddWithValue("@Param1", Val(TxtDonorId.Text))
Com3.Parameters.AddWithValue("@Param2", TxtDonorName.Text)
Com3.Parameters.AddWithValue("@Param3", TxtAddl1.Text)
Com3.Parameters.AddWithValue("@Param4", TxtConNoP.Text)
Com3.Parameters.AddWithValue("@Param5", TxtRem.Text)
Com3.Parameters.AddWithValue("@Param6", DtpYear.Value)
Com3.Parameters.AddWithValue("@Param7", 1)
Com3.Parameters.AddWithValue("@Param8", Main.lblUname.Text)

---

Paul ~~~~ Microsoft MVP (Visual Basic)