Syntax error in Update statement

[Bodhi147]

I can't figure out this error, please help. Its giving me a syntax error in my update statement at Set RS = Conn.Execute (SQLStmt)


if strIndex = "0" then

SQLStmt = ""
SQLStmt = "INSERT INTO Parts (PNumber, Grade, MfgGrade, Size, Weight, Offcuts, GradeType, FullBlockDivider,BaseGradePart,ProcessCode,NominalInches)"
SQLStmt = SQLStmt + " VALUES ('"+strNumber+"', '"+strGrade+"', '"+strMfgGrade+"', '"+strSize+"', "+strWeight+", '"+strOffcuts+"', "+strGradeType+", "+strFullBlockDiv+",'"+strMfgGradePart+"','"+strProcessCode+"','"+strNInches+"')"
else
SQLStmt = "Update Parts Set PNumber ='"+strNumber+"', Grade = '"+strGrade+"', MfgGrade = '" + strMfgGrade + "', "
SQLStmt = SQLStmt + "Size ='"+strSize+ "', Weight ="+strWeight+", Offcuts = '"+strOffcuts+"', GradeType = " + strGradeType + ", FullBlockDivider = " + strFullBlockDiv
SQLStmt = SQLStmt + ", BaseGradePart = '"+strMfgGradePart+"', ProcessCode = '" + strProcessCode + "', NominalInches = '" + strNInches + "', Where Index = "+strIndex

end if
'Response.Write SQLStmt
'Response.End
Set RS = Conn.Execute (SQLStmt)
conn.Close
set conn = nothing
set RS = nothing

 

[onpnt]

I see the resposne.end.

can you post the output from that statement.

it will be easier to see the error (typo more then likely)

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[Bodhi147]

Update Parts Set PNumber ='03-0412', Grade = '2890', MfgGrade = 'No', Size ='540 x 540 x 1830', Weight =1184, Offcuts = 'Yes', GradeType = 1, FullBlockDivider = 1, BaseGradePart = '03-0136', ProcessCode = '03-5526', NominalInches = '21.5 x 21.5 x 72', Where Index = 73

 

[onpnt]

last comma should be taken off
NominalInches = '21.5 x 21.5 x 72', <--
Where Index = 73

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[Bodhi147]

Thanks, but still getting the same error.

 

[onpnt]

have you tested it in SQL View(Analyzer) in the DB yet?

you took this quote off right

+ strNInches + "', Where Index = "+strIndex



___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[Bodhi147]

My problem was that certain words are reserved and you can't use them. I just put brackets around all of them and it worked. Don't ask me why but it does. Thanks for your help though.

SQLStmt = "INSERT INTO Parts ([PNumber], [Grade], [MfgGrade], [Size], [Weight], [Offcuts], [GradeType], [FullBlockDivider], [BaseGradePart], [ProcessCode], [NominalInches])"
SQLStmt = SQLStmt + " VALUES ('"+strNumber+"', '"+strGrade+"', '"+strMfgGrade+"', '"+strSize+"', "+strWeight+", '"+strOffcuts+"', "+strGradeType+", "+strFullBlockDiv+", '"+strMfgGradePart+"', '"+strProcessCode+"', '"+strNInches+"')"
'Response.Write SQLStmt
'Response.End
else
SQLStmt = "Update Parts Set [PNumber] = '"+strNumber+"', [Grade] = '"+strGrade+"', [MfgGrade] = '" + strMfgGrade + "', "
SQLStmt = SQLStmt + "[Size] ='"+strSize+ "', [Weight] ="+strWeight+", [Offcuts] = '"+strOffcuts+"', [GradeType] = " + strGradeType + ", [FullBlockDivider] = " + strFullBlockDiv
SQLStmt = SQLStmt + ", [BaseGradePart] = '"+strMfgGradePart+"', [ProcessCode] = '" + strProcessCode + "', [NominalInches] = '" + strNInches + "' Where [Index] = "+strIndex

end if
'Response.Write SQLStmt
'Response.End
Set RS = Conn.Execute (SQLStmt)

 

[onpnt]

Size was probably the bad apple

Glad you got it going

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064