HELP!! syntax error in INSERT INTO statement

snow1

Member
Joined
Apr 21, 2006
Messages
17
Programming Experience
Beginner
hi. please help me!! i am trying to insert a new record into an access database, and the following keeps popping up: syntax error in INSERT INTO statement. however, i cannot find any error with my insert into statement. can anyone please help me? im desperateeeeeeeee. im so stuck!!!!
VB.NET:
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] cmd_Insert_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] cmd_Insert.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] TableName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = txt_TableName2.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Field [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = txt_Field2.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Values [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = txt_Value2.Text
InsertData(TableName, Field, Values)
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
''''''''''this is the function that does the "inserting"
 
Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] InsertData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] TableName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Field [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Values [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Val() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ValStr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] i, j [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Str [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
 
[/COLOR][/SIZE][SIZE=2]Val = Split(Values, ",")
j = UBound(Val)
 
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] j
[/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]ValStr = ValStr & "'" & Val(i) & "'"
[/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] i < j [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]ValStr = ValStr & ","
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]Str = "INSERT INTO " & TableName & "(" & Field & ") VALUES (" & ValStr & ")"
 
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ObjConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection
ObjConnection = MyConnection()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ObjCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand
ObjCommand.Connection = ObjConnection
ObjCommand.CommandType = CommandType.Text
ObjCommand.CommandText = Str
 
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ObjDataAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(ObjCommand)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ObjDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
ObjDataAdapter.Fill(ObjDataSet, TableName)
ObjCommand.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function
[/COLOR][/SIZE]
 
Last edited by a moderator:
Check the actual string you are using:
msgbox(Str)

My guess is that there is a problem with the & Field & part.
Are you dealing with just one Field name and trying to insert multiple values?
 
You are inserting data. THis is known as an Action Query. Since you are not selecting data, you don't need the dataadapter, the dataset, and should NOT be trying to fill it. Just run the ExecuteNonQuery on the Command object.

-tg
 
Thank you...
i removed the dataadapter and dataset, but still the same error occurs. what should i do? has this something to do with the input values? (strings must have single quotations while integers just remains as is)
 
hard code this to test if it works:

replace this:
"INSERT INTO " & TableName & "(" & Field & ") VALUES (" & ValStr & ")"

with for example this:

"INSERT INTO YourTableName (Column1) VALUES (100)"

error is probably in your field name or value. Can you post what TableName , Field , ValStr do you enter.
 
I've tried "INSERT INTO YourTableName (Column1) VALUES (100)" in a different table and yes, it does work. however when i did it in the same table as the one im using (User) same error occurs.
TableName : User
Field : UserID,UserName,PWord
Values : 123,name,trial

these are my input. there are no mis-spelled fieldnames/tablename
 
Do you have access right to that table "User" ?

And did you check the datatype matching ?

beside what is the error message ?
 
Last edited:
TableName : User
Field : UserID,UserName,PWord
Values : 123,name,trial

ManicCw, above info is given by snow1. Base on the info i guess the Field and ValStr may carry more that 1 field data. If so, i think ur way may not be work.
 
euww.. if you must use raw sql statements without parameters then it's what string.format was invented for:
VB.NET:
strSQL = "INSERT INTO tblTable(userID,username,password) VALUES('{0}','{1}','{2}'; "
 
dim execSQL as string
execSQL = String.Format(strSQL, txtUserID.Text, txtUserName.Text, txtPassword.Text)
so lets get rid of all these confusing "'" & variable &"'"""'" & "'" & blah &"";'"

note for more than 3 params to string.format you must use an array
 
Last edited by a moderator:
Back
Top