error in submit button

Question

I have an error in the execution of my code. It says "Syntax error in INSERT INTO statement". This is my code:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click GridView1.Visible = True

Dim myConn As OleDbConnection      
Dim sqlString, takenby, dest, client, car As String     
Dim recordno As Integer     
Dim dte, exptime As String      
recordno = TextBox4.Text     
dte = TextBox1.Text     
car = ComboBox1.SelectedValue.ToString()     
takenby = ComboBox2.SelectedValue.ToString     
dest = ComboBox3.SelectedValue.ToString     
client = TextBox2.Text     
exptime = TextBox3.Text      
myConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\student\WebSite3\App_Data\Database.mdb;Persist Security Info=False;")      
myConn.Open()     
sqlString = "INSERT INTO DETAILED GISTEC CARS(Record No, Date, Car, Taken By, Destination, Client, Expected Time to Return)VALUES(?recordno, ?dte, ?car, ?takenby, ?dest, ?client, ?exptime);"      
Dim cmd = New OleDbCommand(sqlString, myConn)      
cmd.ExecuteNonQuery()     
myConn.Close() 

End Sub

You have tagged this VB6 but the code you posted is VB.NET - which is it? — Matt Wilko, Jun 27 '12 at 12:22

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

You should change your query to use question mark placeholders and then add paramters to prevent (amongst other things) sql injection issues.

You also need to add square brackets to your column names if they have spaces in them:

sqlString = "INSERT INTO [DETAILED GISTEC CARS] ([Record No], [Date], [Car], [Taken By], [Destination], [Client], [Expected Time to Return]) VALUES (?, ?, ?, ?, ?, ?, ?);"

Dim cmd = New OleDbCommand(sqlString, myConn)
cmd.Parameters.Add(New OleDbParameter("Record No", recordno))
cmd.Parameters.Add(New OleDbParameter("Date", dte))
'etc
'etc
cmd.ExecuteNonQuery()

See this page about OleDbParameters for more information.

score 0 · Answer 2 · edited Jun 27 '12 at 12:16

0

Try to execute query directly in Access until it works.

From here it looks like

You have no spaces around VALUES
Your table name contain spaces so better use [] to surround table name
Same with some column names.
Not sure of ?recordno syntax in vb so better use + operator

sqlString = "INSERT INTO [DETAILED GISTEC CARS]([Record No], [Date], [Car], [Taken By], [Destination], [Client], [Expected Time to Return]) VALUES (" + recordno + ", " + dte + ", " + car +", " + takenby, " + dest + ", " + client + ", " + exptime + ");"

edited Jun 27 '12 at 12:16

Matt Wilko

26,994
10
93
143

answered Jun 27 '12 at 09:00

Oscar Foley

6,817
8
57
90

i tried using the ? and even @ but it dsnt work.... any idea? the problem is always at the execution. what do u mean execute query directly in access? how can i do that? – Tahany90 Jun 27 '12 at 09:13
Better use string concatenation as I have put. (Just edited). Look for content of sqlstring and post it here so I can see final content. You can also execute in ms-access you can open access file directly (Check this [link](http://office.microsoft.com/en-us/access-help/run-a-query-HA010077157.aspx) to see how to do it) – Oscar Foley Jun 27 '12 at 09:23
Good point, but SQL Injection is not suitable yet for someone doing vb6 + access :) – Oscar Foley Jun 27 '12 at 12:38
It is not just malicious SQL injection that you need to protect against. All it takes is one value to have a single quote in it for the whole query to fall over spectacularly. e.g. Surnames are a common source for this kind of error – Matt Wilko Jun 27 '12 at 12:42
I agree totally. Only that I thought it was too hard for someone that probably is learning at school. Upvoted your solution anyway because is better than mine :) – Oscar Foley Jun 27 '12 at 12:49

Antagony · Answer 3 · 2012-06-27T13:00:42.513

0

An alternative to cad's concatenation method would be to use curly brace array placeholders. Something like this should do it:

sqlString = String.Format("INSERT INTO [DETAILED GISTEC CARS] ([Record No], [Date], [Car], [Taken By], [Destination], [Client], [Expected Time to Return]) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6});", recordno, dte, car, takenby, dest, client, exptime)

You should also put single quotes around non-numeric values, escape any user entered single quotes, and call the Access CDATE function for Date/Time columns. So, assuming dte is user entered data and exptime is a Date\Time column, those two variables might be set like this:

dte = "'" + TextBox1.Text.Replace("'", "''") + "'"
exptime = "CDATE('" + TextBox3.Text + "')"

and so on and so forth...

edited Jun 27 '12 at 13:00

answered Jun 27 '12 at 12:17

Antagony

1,750
12
17

What do you think will happen if `dest` contains a single quote? – Matt Wilko Jun 27 '12 at 12:25
It would cause a syntax error in the query. You would essentially have a value that ends at the single quote and any subsequent characters would be unrecognised by the database engine. – Antagony Jun 27 '12 at 12:37
Exactly my point! This is why you should use parameterised queries. – Matt Wilko Jun 27 '12 at 12:39
Um, look at my post again. I recommended using the `Replace` function to escape single quotes on user entered values. I fail to see how that's any less valid than using a parameterised query. – Antagony Jun 27 '12 at 12:52
I would say less valid because it requires you to remember to escape single quotes, also what format is the db expecting the date variable dte to be in? Depending on locales you could at best get month and year mixed up and at worst the query could fail if it is not in the correct format. – Matt Wilko Jun 27 '12 at 12:59
Well again, I recommended using the Access CDATE function within the query, which correctly formats most date strings to the expected locale in exactly the same way a parameter does. I take your point about having to remember to do such things; although I must admit I wasn't aware parameters would deal with single quotes for you... we live and learn. :-) – Antagony Jun 27 '12 at 13:30

error in submit button

3 Answers3