Insert Error 2

[schase]

Hi, can anybody see what is wrong with the code below? I go from type mismatch error to insert error.

What it does is on a previous page - if a checkbox is checked, the values along that row (in the form) are inserted - if no checked box - then go onto the next row.
Response Writing the values pulls the rowcount and property ID a-ok.

Thank you in advance.


<% Dim strRowCount
strRowCount=(Session(&quot;svRowcount&quot)
%>
<% Dim rs,i
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;DSN=mydsn;&quot;
for i=1 to strRowCount

IF Request.Form(&quot;txtExistant&quot;&i)<>&quot;&quot; THEN
SQLstmt = &quot;Insert into tblDetail&quot;
SQLstmt = SQLstmt & &quot;SET fldPropertyID=&quot; & Session(&quot;svPropertyID&quot
SQLstmt = SQLstmt & &quot;,fldDetailTitle='&quot; & Request.Form(&quot;txtName&quot;&i)
SQLstmt = SQLstmt & &quot;',fldDimensionsWidth=&quot; & Request.Form(&quot;txtWidth&quot;&i)
SQLstmt = SQLstmt & &quot;,fldDimensionsLength=&quot; & Request.Form(&quot;txtLength&quot;&i)
SQLstmt = SQLstmt & &quot;,fldWidePoints='&quot; & Request.Form(&quot;txtWidest&quot;&i)
SQLstmt = SQLstmt & &quot;',fldRoomCat=&quot; & Request.Form(&quot;txtType&quot;&i)
SQLstmt = SQLstmt & &quot;,fldDescription='&quot; & Request.Form(&quot;txtDescription&quot;&i)
Set RS = Conn.Execute(SQLstmt)
End IF
NEXT
%>
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Tarwn]

Your insert stmt should be formulated like so:
INSERT INTO tablename VALUES(value,value,value,value)
or to specify the fields your inserting into:
INSERT INTO tablename(fieldname,fieldname,fieldname) VALUES(value,value,value)

The stmt above is more a cross between an insert and an update:
UPDATE tablename SET field=value,field=value,field=value [WHERE field = value]

Hope that helps,
-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri

 

[schase]

Hey Tarwn - thank you for your response.

I rewrote - and get the insert error again. Any ideas? Thank you


% Dim rs,i
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;DSN=mydsn;&quot;
for i=1 to strRowCount

IF Request.Form(&quot;txtExistant&quot;&i)<>&quot;&quot; THEN
SQLstmt = &quot;Insert into tblDetail(fldPropertyID,fldDetailTitle,fldDimensionsW,fldDimensionsL,fldWidePoints,fldRoomCat,fldDescription)&quot;
SQLstmt = SQLstmt & &quot;VALUES (&quot; & Session(&quot;svPropertyID&quot
SQLstmt = SQLstmt & &quot;,'&quot; & Request.Form(&quot;txtName&quot;&i) & &quot;'&quot;
SQLstmt = SQLstmt & &quot;,&quot; & Request.Form(&quot;txtWidth&quot;&i) & &quot;&quot;
SQLstmt = SQLstmt & &quot;,&quot; & Request.Form(&quot;txtLength&quot;&i) & &quot;&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & Request.Form(&quot;txtWidest&quot;&i) & &quot;'&quot;
SQLstmt = SQLstmt & &quot;,&quot; & Request.Form(&quot;txtType&quot;&i) & &quot;&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & Request.Form(&quot;txtDescription&quot;&i) & &quot;'&quot; & &quot&quot;
Set RS = Conn.Execute(SQLstmt)
End IF
NEXT
%>
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[XgrinderX]

What is the exact error message you are getting?

 

[schase]

hi XgrinderX

Thank you for your reply.

I get

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

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

Any ideas? I removed all relationships and get the same error.

Thank you
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[XgrinderX]

Just for grins...

Throw a Response.Write(SQLstmt) in there just before you execute and make sure the SQL statement looks okay. I like to do this and then try to cut and paste the query into SQL Query Analyzer and see if it works or what error message that SQL may be throwing back.

If you are comfortable with it, post the SQL statement here and maybe we can help you out some more.

-Greg

 

[schase]

well I get this -

Insert into tblDetail(fldPropertyID,fldDetailTitle,fldDimensionsW,fldDimensionsL,fldWidePoints,fldRoomCat,fldDescription)VALUES (25,'Master Bedroom',10,20,'Yes',1,'Master Bedroom Desc')Insert into tblDetail(fldPropertyID,fldDetailTitle,fldDimensionsW,fldDimensionsL,fldWidePoints,fldRoomCat,fldDescription)VALUES (25,'Bedroom 2',10,15,'',1,'')Insert into tblDetail(fldPropertyID,fldDetailTitle,fldDimensionsW,fldDimensionsL,fldWidePoints,fldRoomCat,fldDescription)VALUES (25,'Hall',,,'Yes',2,'Hall Desc') &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[XgrinderX]

Ok. See your last INSERT stmt:

Insert into tblDetail(fldPropertyID,fldDetailTitle,fldDimensionsW,fldDimensionsL,fldWidePoints,fldRoomCat,fldDescription)VALUES (25,'Hall',,,'Yes',2,'Hall Desc')

See those 3 commas with nothing between them? That is causing your error. You need some code in there that will put 0 in for fldDimensionsW and fldDimensionsL if their values ar null or &quot;&quot;. Be aware that all the INSERTS before that have probably been working and you may have multiple rows inserted in your database with those values.

Let me know if you need any other help/info.

-Greg

 

[schase]

ahhh shoot your right

Many thanks &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Tarwn]

One additional detail that may not be causing any errors at all, you are missing a space between )VALUES in the above statement. The databaswe might be letting you get away with it, but thats also a possible cause of error.

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri