10 Replies - 1577 Views - Last Post: 04 August 2010 - 08:54 AM

#1 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

INSERT Command Syntax Error

Posted 04 August 2010 - 07:55 AM

Hi.

I have been trying for over an hour to figure why i am receiving a syntax error on one of my INSERT commands. The code i have so far is below. What it does or is suppose to do is populate the variables with the shown data and then insert this data from the variables into the database table. I have checked all the names etc and they seem fine. I ahve also used the same peice of code except with different variables for another data insert and that worked fine. Any help would be greatly appreciated. Many thanks, Mike.

Error Shown:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Code

 
'Inserts default starting data for new user
icredits = "0"
imoney = "1000"
ihealth = "100"
irank = "Slave"
iarmour = "None"
ixp = "0"
iuserid = session.sessionid
icurrentjob = "No Jobs Yet Undertaken"

cnn.execute "INSERT INTO tbluserdetails (username, credits, money, health, rank, armour, xp, userid, currentjob) VALUES ('" & username & "','" & icredits & "','" & imoney & "','" & ihealth & "','"  & irank & "','" & iarmour & "','" & ixp & "','" & iuserid & "','" & icurrentjob & "')" 



Is This A Good Question/Topic? 0
  • +

Replies To: INSERT Command Syntax Error

#2 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:13 AM

Just tried not specifying where to insert the data and it inserts without error however it only inserts "0" for number fields and blank for text fields. Mike.
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:17 AM

enclose money in brackets (ie: INSERT INTO tbluserdetails (username, credits, [money]

I think it's a keyword in access.
Was This Post Helpful? 1
  • +
  • -

#4 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:20 AM

I have changed the code back to the orginal posted in Post 1. I have placed the brackets and no error is shown however the problem of only "0" appearing in number fields and blank spaces for text fields still occurs. Thank you for your reply.

cnn.execute "INSERT INTO tbluserdetails (username, credits, [money], health, rank, armour, xp, userid, currentjob) VALUES ('" & username & "','" & icredits & "','" & imoney & "','" & ihealth & "','"  & irank & "','" & iarmour & "','" & ixp & "','" & iuserid & "','" & icurrentjob & "')"


Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:25 AM

Well, numeric fields do not use the apostrophe to surround them, and if your text fields are blank, then the variables are not holding any strings.

Try putting this prior to what you have
dim myStmt as string = "INSERT INTO tbluserdetails (username, credits, [money], health, rank, armour, xp, userid, currentjob) VALUES ('" & username & "','" & icredits & "','" & imoney & "','" & ihealth & "','"  & irank & "','" & iarmour & "','" & ixp & "','" & iuserid & "','" & icurrentjob & "')"
messagebox.show(myStmt)


What does the messagebox display?

This post has been edited by CharlieMay: 04 August 2010 - 08:25 AM

Was This Post Helpful? 0
  • +
  • -

#6 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:31 AM

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/michaelcomputing/registeractivationprocess.asp, line 71

dim myStmt as string = "INSERT INTO tbluserdetails (username, credits, [money], health, rank, armour, xp, userid, currentjob) VALUES ('" & username & "','" & icredits & "','" & imoney & "','" & ihealth & "','" & irank & "','" & iarmour & "','" & ixp & "','" & iuserid & "','" & icurrentjob & "')"



-----------^ (points to a in "as string")

I'm using classic ASP.
Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:32 AM

sorry, I forgot I'm not in vb.net :(
I'm trying to have you display what the string you built is holding, I'm not sure what the equivalent to that is for ASP.
Was This Post Helpful? 0
  • +
  • -

#8 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:36 AM

I reckon it would most likely be response. something
Was This Post Helpful? 0
  • +
  • -

#9 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:49 AM

I have wrote the variables to the screen before they are used in the sql insert statement and nothing appears showing they do not conatin no data but they have been declared and populated correclty. Mike.

They now display there data when written to the screen. Mike.
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:49 AM

Well, the Database portion seems correct, you might need to show your code in the asp forum to see why your variables aren't populated.
Was This Post Helpful? 0
  • +
  • -

#11 mikie12   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-February 09

Re: INSERT Command Syntax Error

Posted 04 August 2010 - 08:54 AM

Thank you for all your help. I have solved the issue. The allow zero length property on the table was set to "Yes" which for some reason stopped the database from obtaining the variable data. Many thanks, Mike.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1