HELP!! syntax error in INSERT INTO statement

[snow1]

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!!!!

[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]

 

[DavidT_macktool]

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?

 

[TechGnome]

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

 

[ManicCW]

you are filling with INSERT INTO????? you should call executenonquery.

btw: do not concatanate commandtext from user input!!! use parameters.

 

[snow1]

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)

 

[ManicCW]

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.

 

[snow1]

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

 

[Kalvin]

Do you have access right to that table "User" ?

And did you check the datatype matching ?

beside what is the error message ?

 

[ManicCW]

i think that table name cannot be User. It confuses Access . Try MyUsers or something as table name if it works.

 

[R_K_T_ASHOKA]

Try entering these values into ur text boxes

TableName : [User]
Field : UserID,UserName,PWord
Values : 123,'name','trial'

 

[albertkhor]

To insert string into database you need to put '

 

[ManicCW]

he is right try this:

"INSERT INTO " & TableName & "(" & Field & ") VALUES ('" & ValStr & "')"

 

[albertkhor]

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.

 

[ManicCW]

have you tried renaming table User?

 

[cjard]

euww.. if you must use raw sql statements without parameters then it's what string.format was invented for:

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