INSERT syntax error

[netbumbler]

Anyone see any problems with the following:
strupdate2 = "INSERT INTO logstatistics(filename, countertype, refid1, refid2, counter) VALUES ('"
strupdate2 = strupdate2 & filename1 & "','I1','" & ins1name & "'," & "' '," & 1 & ")"

Thanks for any insight!
Chris

 

[jrbarnett]

There's no data going into the fourth field - refid2. Add it into the values clause and it should be fine.

John

 

[RoyVidar]

Why should we bother use our time in a guessing contest, if you don't bother telling us what the problem is (faq181-2886, #14)

Roy-Vidar

 

[RoyVidar]

BTW - the fourth field is there, a space between two single quotes, could it be that you need using Null in stead? What is the result of

[tt]debug.print strupdate2[/tt]

does all variables have a value, does the delimiters match the datatype of the fields...

...you might need brackets around [counter] as it is probably a reserved word, perhaps [filename] too?...

Roy-Vidar

 

[netbumbler]

Thanks for the response guys. I was reading another link here in the forum that actually answered my question. There are 5 datapoints - there's a ' ' which represents a blank entry for the refid2.

The problem I was getting was a SYNTAX error (stated in the subject). There was no information available to me other than that.
The SOLUTION was to bracket my field names with []s. So my final statement looks like this:
strupdate2 = "INSERT INTO logstatistics([filename], [countertype], [refid1], [refid2], [counter]) VALUES ('"
strupdate2 = strupdate2 & filename1 & "','I1','" & ins1name & "'," & "' '," & 1 & ")"

Every reference I could find showed using the field names unbracketed. I guess it must be a minor ADO version change or something like that - works like a champ now!
Thanks guys,
Chris

 

[PHV]

I guess it must be a minor ADO version change
I don't think so.
I guess that every reference you could find don't use reserved word as field name ...
In fact, only [counter] should be bracketted.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886