1

I have this code that gives syntax error in INSERT INTO statement..any help ?

Dim lo As String = "INSERT INTO tblTrans(Book ID,Student ID,Date Borrowed,Returned )VALUES( @parm1, @parm2 , @parm3 , @parm4 )"
Dim cmd55 As New OleDb.OleDbCommand(lo, connectors2)
cmd55.Parameters.Add("@parm1", OleDb.OleDbType.WChar, 10).Value = str5
cmd55.Parameters.Add("@parm2", OleDb.OleDbType.WChar, 5).Value = std
cmd55.Parameters.Add("@parm3", OleDb.OleDbType.Date).Value = str11
cmd55.Parameters.Add("@parm4", OleDb.OleDbType.WChar, 255).Value = str12
cmd55.CommandType = CommandType.Text
cmd55.ExecuteNonQuery()
SnareChops
  • 13,175
  • 9
  • 69
  • 91
77981
  • 9
  • 2
  • str5,std,str12 are declared as strings and str11 is declared as date – 77981 Feb 06 '16 at 22:47
  • 3
    If you have poorly named columns with spaces in them, you need to escape them. `([Book ID], [Student ID]...)` You also need to escape Date because that is a reserved word. Advice: go back and use better column names – Ňɏssa Pøngjǣrdenlarp Feb 06 '16 at 22:50
  • you mean I should change my column-names to "ex: BookID instead of book ID " ?? – 77981 Feb 06 '16 at 22:55
  • I would. The alternative is remembering to escape them in every query (same for Date). See also [this answer about disposing of db objects](http://stackoverflow.com/a/29187199/1070452). FYI: One of several MSDN [List of Access Reserved Words](https://support.microsoft.com/en-us/kb/286335) – Ňɏssa Pøngjǣrdenlarp Feb 06 '16 at 22:58
  • well yes it worked...thank you very much :))) – 77981 Feb 06 '16 at 23:03
  • Column names are not according to standards. Also you need to put space around `VALUES`. – Hemal Feb 07 '16 at 07:00

1 Answers1

0

Your statement should be after changing column names,

Dim lo As String = "INSERT INTO tblTrans(Book_ID,Student_ID,Date_Borrowed,Returned ) VALUES ( @parm1, @parm2 , @parm3 , @parm4 )"

Also Convert str11 to date before inseting.

Hemal
  • 3,682
  • 1
  • 23
  • 54