Member Avatar for shuey79

When I run this I get an error saying that the sql syntax is wrong.
Field types are:
Time: Text
Date: Text
SalespersonID: Integer

Dim sqlWrite As String = "INSERT INTO Audit (Time, Date, SalespersonID) VALUES('" & CType(lblTime.Text, String) & "','" & CType(lblDate.Text, String) & "','" & SalespersonID & "')"
da = New OleDb.OleDbCommand(sqlWrite, Databaseconnection)
Dim temp_num As Integer
temp_num = da.ExecuteNonQuery()

Any suggestions on why this is giving me a syntax error?

I have tried this as my sqlWrite and it works fine so I know it has nothing to do with SalespersonID:

'Dim sqlWrite As String = "INSERT INTO Audit (SalespersonID) VALUES('" & SalespersonID & "')"
  • 2 Contributors
  • 3 Replies
  • 324 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by shuey79

Recommended Answers

All 3 Replies

Member Avatar for Teme64

SalespersonID is INT, remove quotes:

Dim sqlWrite As String = "INSERT INTO Audit (Time, Date, SalespersonID) VALUES('" & CType(lblTime.Text, String) & "','" & CType(lblDate.Text, String) & "'," & SalespersonID & ")"
da = New OleDb.OleDbCommand(sqlWrite, Databaseconnection)
Dim temp_num As Integer
temp_num = da.ExecuteNonQuery()

Use quotes only with textual and datetime data,

Member Avatar for shuey79

I originally had SalespersonID without quotes. But just to make sure I tried it again and it still didn't work.
I noticed that the Time and Date fields I am trying to write to are Text and my variables in the program are ctyped to string, could this be the issue?

Member Avatar for shuey79

I figured out the issue. My field names were using reserved words I guess. I changed the Time and Date field names and that fixed it.

Thanks Teme64 for posting!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.