5 Replies - 2234 Views - Last Post: 20 October 2008 - 02:04 PM Rate Topic: -----

#1 StrategicBlob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-June 08

Access Insert Syntax Error....doh!

Post icon  Posted 10 October 2008 - 09:15 AM

So here is what I got going on. I have recently revamped some code I had wrote previously for my company's computer inventory system. I have gotten adventurous and used Access 2007 because it seems to play nicer than 2003. Anyway, I have run into this issue with the Insert command saying that something about my syntax is not correct. Here is a snippet of what the code looks like.

 Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\mike.mcgillivry\My Documents\mine1.accdb")
		conn.Open()
		'####################################################################  
		Dim addUser As String = "INSERT INTO Computer (ComputerS/N, Brand, User) VALUES (" + txtName2.Text + "','" + txtName5.Text + "','" + txtName1.Text + "')"
		Dim cmd As OleDbCommand = New OleDbCommand(addUser, conn)
		cmd.ExecuteNonQuery()
		MsgBox("User added successfully")
		'####################################################################
		conn.Close() 



Do note that I have many more fields that I "could" enter into this type of a command but I am starting small to avoid any other oddities. Other general notes would be that "Computer" is the name of the table (there are many more included in the database used) and txtName5.txt and Name1.text are just plain text boxes that users can enter data in.



I realize that these ODBC commands can be a real bear to deal with all the " 's so any help would be greatly appreciated and more code can be presented if necessary.

Is This A Good Question/Topic? 0
  • +

Replies To: Access Insert Syntax Error....doh!

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Access Insert Syntax Error....doh!

Posted 10 October 2008 - 10:43 AM

Moved to VB.NET.
Was This Post Helpful? 0
  • +
  • -

#3 Damage  Icon User is offline

  • Lord of Schwing
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,089
  • Joined: 05-June 08

Re: Access Insert Syntax Error....doh!

Posted 10 October 2008 - 02:22 PM

your missing a ' by your first value(txtname2)
" + txtName2.Text + "'


Was This Post Helpful? 0
  • +
  • -

#4 StrategicBlob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-June 08

Re: Access Insert Syntax Error....doh!

Posted 17 October 2008 - 07:00 AM

View PostDamage, on 10 Oct, 2008 - 02:22 PM, said:

your missing a ' by your first value(txtname2)
" + txtName2.Text + "'




Ah yes indeed, thank you damage! Those stinkin single quotes get me all the time. I am very close but now it is saying "Number of query values and destination fields are not the same.". My gut is telling me I need to include where the variables are going but I am not sure where to declare that.
Was This Post Helpful? 0
  • +
  • -

#5 rollout369  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 17-October 08

Re: Access Insert Syntax Error....doh!

Posted 20 October 2008 - 07:49 AM

It could be because USER is a reserved SQL keyword. Try changing this column name to something else. I would also avoid slashes in column names.
Was This Post Helpful? 0
  • +
  • -

#6 StrategicBlob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-June 08

Re: Access Insert Syntax Error....doh!

Posted 20 October 2008 - 02:04 PM

View Postrollout369, on 20 Oct, 2008 - 07:49 AM, said:

It could be because USER is a reserved SQL keyword. Try changing this column name to something else. I would also avoid slashes in column names.



Holy Cow that did the trick!! I thought since the name "User" was in a string it would not matter but that did it! Mucho Thanks for All Who Helped :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1