INSERT INTO syntax error when saving another file

Question

I am using OLE db connection for a payroll system in vb. at first save of a transaction it will successfully save on the database but when i get back to pick another account to compute for its wage and try to save it,it will return an error saying there is a "syntax error on insert into".

here is my code:

    com5 = "INSERT INTO PayrollReport Values('" & empno.Text & "','" & ename.Text & "','" & startdate.Text & "','" & _
        cutdate.Text & "'," & bpay.Text & "," & computation.Text & "," & grosse.Text & "," & _
        lwop.Text & "," & tardiness.Text & "," & tax.Text & "," & sssp.Text & "," & _
        philp.Text & "," & hdmfp.Text & "," & sssl.Text & "," & hdmfl.Text & "," & _
        advance.Text & "," & taxdue.Text & "," & nete.Text & "," & deduction.Text & "," & _
        netpay.Text & "," & Employee.Esss.Text & "," & Employee.Ephil.Text & "," & Employee.Ehdmf.Text & ")"


    MsgBox(" The " & ename.Text & "'s Record has been save!")

    connect5()

Hope you'll help me with this please...

what is data type for column number 5 (bpay.Text) ? can you post your table structure? — Iswanto San, Feb 13 '13 at 01:53
**SQL Injection alert!** - start using **parametrized queries!** — marc_s, Feb 13 '13 at 05:46

score 1 · Answer 1 · edited May 23 '17 at 11:56

If you convert your code to use what I mention below, your error will become apparent.

You really need to use code like the following to insert data into the database.

Parameterized SQL - You need to utilize SQL Parameters, despite the fact that you are not using a Stored Procedure. This is not the cause of your error. Your original code dumps values straight from the user into a SQL Statement. Malicious users will steal your data unless you use SQL Parameters.

    Dim CMD As New SqlCommand("Select * from MyTable where BookID = @BookID")
    CMD.Parameters.Add("@BookID", SqlDbType.Int).Value = CInt(TXT_BookdID.Text)

Note that not only can you establish placeholders to feed data safely into your database, but you can also establish the data type. This can help catch data-conversion errors. Example, if a user types 4E into a field that is expecting an integer, the code that I posted will give you a very explicit error about failure to convert to int.

Reference

This link will provide you more detail on the proper way to connect to a database in VB.NET.

INSERT INTO syntax error when saving another file

1 Answers1