Proper Syntax

1. Senior Member
Join Date
Dec 1969
Posts
251

## Proper Syntax

I am not the best with the SQL syntax, so can someone tell me why I keep getting the error: Type Mismatch.<BR><BR>I know that means that I am tring to dump a string into a integar and visa versa.<BR><BR>the error appears at the line named: CustId<BR><BR>&#039;Insert the new baby information into the DB based on the new customers id number<BR><BR>For x = 1 to Babycount<BR> babyinst = "Insert into babies"<BR> babyinst = babyinst & "(babynumber, bfname, bmname, blname, bdue, bdate, eyecolor, haircolor, sex, birthweight, length, customerid)"<BR><BR> babyinst = babyinst & "values (&#039;"<BR> babyinst = babyinst & CustId & "&#039; , &#039;"<BR> babyinst = babyinst & x & "&#039; , &#039;"<BR> babyinst = babyinst & frmarray(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm2array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm3array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm4array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm5array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm6array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm7array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm8array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm9array(x) & "&#039; , &#039;"<BR> babyinst = babyinst & frm10array(x) & "&#039;)"<BR> <BR> conn.execute(babyinst)<BR>Next

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## What DB? What field types?

What kind of DB is this?<BR><BR>And what is the field type of EACH field?<BR><BR>And where&#039;s your DEBUG code?<BR><BR>You should have a <BR> Response.Write "DEBUG SQL: " & babyinst & "&#060;HR&#062;"<BR>just before the conn.execute line.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
251

## RE: Access

Most all fields are Text but the CustId and babynumber fields are Number.<BR><BR>I tried your debug, but my code dosent even get that far....

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, that's informative...

...truly.<BR><BR>Let&#039;s see what we can pull off...<BR><BR>Ummm...how come you have CustID *first* in the list of values when it is *LAST* in the list of field names????<BR><BR>And did you realize you should *NOT* have &#039;...&#039; around numerical values.<BR><BR>How come the "bdate" field is a Text field????? Why isn&#039;t it a DateTime field? Same maybe for "bdue"??<BR><BR>I&#039;ll give it a shot, but no guarantees...<BR><BR>**********<BR><BR>For x = 1 to Babycount <BR> babyinst = "Insert into babies" <BR> babyinst = babyinst & "(babynumber, bfname, bmname, blname, bdue, bdate, eyecolor, haircolor, sex, birthweight, length, customerid)" <BR><BR> babyinst = babyinst & "values (" <BR> babyinst = babyinst & x & ", &#039;" &#039; babynumber<BR> babyinst = babyinst & frmarray(x) & "&#039; , &#039;" &#039; brname<BR> babyinst = babyinst & frm2array(x) & "&#039; , &#039;" &#039; bmname<BR> babyinst = babyinst & frm3array(x) & "&#039; , &#039;" &#039; blname<BR> babyinst = babyinst & frm4array(x) & "&#039; , &#039;" &#039; bdue<BR> babyinst = babyinst & frm5array(x) & "&#039; , &#039;" &#039; bdate<BR> babyinst = babyinst & frm6array(x) & "&#039; , &#039;" &#039; beyecolor<BR> babyinst = babyinst & frm7array(x) & "&#039; , &#039;" &#039; haircolor<BR> babyinst = babyinst & frm8array(x) & "&#039; , &#039;" &#039; sex<BR> babyinst = babyinst & frm9array(x) & "&#039; , &#039;" &#039; birthweight<BR> babyinst = babyinst & frm10array(x) & "&#039;, " &#039; length<BR> babyinst = babyinst & CustId & " )" &#039; custid<BR> <BR> Response.Write "DEBUG SQL: " & babyinst & "&#060;HR&#062;"<BR> <BR> conn.execute(babyinst) <BR>Next <BR><BR>How come you gave those arrays undescriptive names like "frm6array" instead of "eyeColorArray" ???<BR><BR>The more you can write self-documenting code, the better off you are in the long run...and to some degree even the short run.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
251

## RE: I made a mistake

Here is the code I have I didnt give the correct code in the begginging, sorry.<BR><BR>babyinst = "Insert into babies"<BR>babyinst = babyinst & "(customerid, babynumber, bfname, bmname, blname, bdue, bdate, eyecolor, haircolor, sex, birthweight, length)"<BR><BR>babyinst = babyinst & "values ("<BR>babyinst = babyinst & CustId & " , "<BR>babyinst = babyinst & x & " , &#039;"<BR>babyinst = babyinst & frmarray(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm2array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm3array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm4array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm5array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm6array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm7array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm8array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm9array(x) & "&#039; , &#039;"<BR>babyinst = babyinst & frm10array(x) & "&#039;)"<BR><BR>Response.Write "DEBUG SQL: " & babyinst & "&#060;HR&#062;"<BR> <BR>conn.execute(babyinst)<BR><BR>I unerstand the no &#039; around numbers but I can never get the syntax just right. I took your advie and changed the code but still somethig isnt right...

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## That looks okay to me...

Should have a space in front of "value", but it&#039;s likely okay without it.<BR><BR>Okay, so what&#039;s the error message with that code???<BR><BR>Copy/paste only please.<BR><BR>And if it gets as far as the DEBUG, then what&#039;s the debug output show you. Again, copy/paste only.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
251

## RE: Error

Microsoft VBScript runtime error &#039;800a000d&#039; <BR><BR>Type mismatch <BR><BR>/birth/new_create.asp, line 86 <BR><BR><BR>Line 86 is the line with CustId...<BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ahhh....then let's find out...

&nbsp;<BR>Put these lines just *BEFORE* that one with the CustID:<BR><BR> Response.Write "TypeName(custID) is " & TypeName(custID) & "&#060;P&#062;"<BR> Response.Write "Value of custID is :::" & custID & ":::&#060;HR&#062;"<BR><BR>What does that show you?<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
251

## RE: Its a Recordset

TypeName(custID) is Recordset

10. Senior Member
Join Date
Dec 1969
Posts
251

## RE: is it possible to do this

on one page:<BR><BR>insert into a db<BR><BR>select a field from that just inserted data<BR><BR>and do another insert based off that select

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•