3 Replies - 796 Views - Last Post: 18 December 2010 - 01:18 PM Rate Topic: -----

#1 terrynbama  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 18-December 10

Syntax error in SQL

Posted 18 December 2010 - 10:13 AM

I'm working on a programm which is inserting data into an Access2000 database. The following code creates a "syntax error in the Sql"
Dim lngContractSchedule As Long
Dim intWearerNumber As Integer
Dim strSize As String
Dim strBeginDate As Date
Dim strStatusDate As Date
Dim strItemCode As String
Dim intQuantity As Integer
Dim strGrade As String
Dim intChanges As Integer
Dim intSets As Integer
Dim intCTDused As Integer
Dim intCTDhigh As Integer
Dim strSalesCode As String
Dim curPrice As Currency
Dim curReplacementCharge As Currency

strSize = "1"
strBeginDate = Date
intQuantity = 1
strGrade = "U"
intChanges = 1
intSets = 1
intCTDused = 1
intCTDhigh = 1
strSalesCode = "N"
curReplacementCharge = 0
curPrice = 6.50
intSets = 1
intChanges = 1
strItemCode = "8000"


SQLData = "INSERT INTO ContractWearerItem(ContractNumber,WearerNumber,ItemCode,Size,Qty,Grade," _
& "NumberofSets,NumberofChanges,Price,ReplacementCharge,CTDused,CTDHighQty,BeginServiceDate,SalesCode)" & "VALUES(  " & lngContract & " , " & intWearerNumber & " , '" & strItemCode & "' ," _
& " '" & strSize & "' , " & intQuantity & " ," _
& " '" & strGrade & "' , " & intSets & " , " & intChanges & " , " & curPrice & " ," _
& " " & curReplacementCharge & " ," _
& " " & intCTDused & " , " & intCTDhigh & " , '" & strBeginDate & "' , '" & strSalesCode & "');"
rsData.Open SQLData, cnData, 3, 3



Through trial and "error" I've determined the problem seems to be the table fielname "Size". It is a primary key field in the table. If I change the name in the table to "Sizea", the sql runs fine.

I really can't change the name because the database is part of a business system programm written by someone else. They set up the tables and have many programs using the tables. So that is out of the question.

Is "Size" a reserved name? If so, I don't see how the other programmer is using it.
Is there a way to fix this problem?

Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in SQL

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6058
  • View blog
  • Posts: 23,496
  • Joined: 23-August 08

Re: Syntax error in SQL

Posted 18 December 2010 - 10:54 AM

If you're having trouble with a dynamically-generated query, I suggest printing it out somewhere on error so you can verify that it's as you expect. As to Size being a reserved word, it does not appear so (found by a Google search of "access 2000 reserved words", first link returned).
Was This Post Helpful? 1
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Syntax error in SQL

Posted 18 December 2010 - 11:42 AM

Put the reserved word between square brackets and your query should run fine.

 INSERT INTO Table ([Size] .... 

Was This Post Helpful? 0
  • +
  • -

#4 terrynbama  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 18-December 10

Re: Syntax error in SQL

Posted 18 December 2010 - 01:18 PM

It must have been a reserved word. The brackets worked great. Thanks :rolleyes:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1