"INSERT INTO
in [JavaScript]
|
Prev: CFP Applied Computing 2010: submissions until 28 May 2010
Next: FAQ Topic - How do I modify the content of the current page? (2010-04-14)
From: Rob Christiansen on 13 Apr 2010 15:09 My 1st *.mdb file works great, My 2nd doesn't. I get a lot of "data type mismatch" error messages. The problem as i see it is, whereas all the fields in the 1st file are 'text,' the 2nd is 'text,' 'date/time,' and 'currency.' The software is apparently having trouble reading the different types. Below is the sql statement I'm using. How can i fix this? ------------------------------------- fdate is set as "date/time" type fdebit is set as "currency" type fcredit is set as "currency" type fprevrec and fnextrec are number type newid is set as "auto" type all the rest are "text" type SQL = "INSERT INTO books1 VALUES( "+ newid +", "+ fprevrec +", "+ fnextrec +", '"+ fforegrnd +"', '"+ fbackgrnd +"', '"+ fyear+"', '"+ fmonth +"', '"+ fbank+"', '"+ finitials +"', '"+ ftype+"', '"+ fcheck+"', '"+ fdate+"', '"+ fdescription+"', "+ fdebit+", "+ fcredit+", '"+ fproject+"', '"+ fmsc1 +"', '"+ fmsc2 +"', '"+ fmsc3 +"', '"+ fmsc4 +"', '"+ fmsc5 +"', '"+ fmsc6 +"' )"; //, '"+ fmsc7 +"' '"+ fmsc8 +"' )"; // crazyswede *** Sent via Developersdex http://www.developersdex.com ***
From: Joe Nine on 13 Apr 2010 15:25 Rob Christiansen wrote: > My 1st *.mdb file works great, My 2nd doesn't. I get a lot of "data > type mismatch" error messages. The problem as i see it is, whereas all > the fields in the 1st file are 'text,' the 2nd is 'text,' 'date/time,' > and 'currency.' The software is apparently having trouble reading the > different types. Below is the sql statement I'm using. How can i fix > this? SQL forum?
From: Evertjan. on 13 Apr 2010 17:29 Rob Christiansen wrote on 13 apr 2010 in comp.lang.javascript: > My 1st *.mdb file works great, My 2nd doesn't. I get a lot of "data > type mismatch" error messages. The problem as i see it is, whereas all > the fields in the 1st file are 'text,' the 2nd is 'text,' 'date/time,' > and 'currency.' The software is apparently having trouble reading the > different types. Below is the sql statement I'm using. How can i fix > this? > ------------------------------------- > fdate is set as "date/time" type > fdebit is set as "currency" type > fcredit is set as "currency" type > fprevrec and fnextrec are number type > newid is set as "auto" type > all the rest are "text" type > > SQL = "INSERT INTO books1 VALUES( "+ newid +", "+ fprevrec +", "+ > fnextrec +", '"+ fforegrnd +"', '"+ fbackgrnd +"', '"+ fyear+"', '"+ > fmonth +"', '"+ fbank+"', '"+ finitials +"', '"+ ftype+"', '"+ > fcheck+"', '"+ fdate+"', '"+ fdescription+"', "+ fdebit+", "+ fcredit+", > '"+ fproject+"', '"+ fmsc1 +"', '"+ fmsc2 +"', '"+ fmsc3 +"', '"+ fmsc4 > +"', '"+ fmsc5 +"', '"+ fmsc6 +"' )"; //, '"+ fmsc7 +"' '"+ fmsc8 +"' > )"; // > First try to make it readable: =================================== SQL = "INSERT INTO books1 VALUES( "+ newid +", "+ fprevrec +", "+ fnextrec +", '"+ fforegrnd +"', '"+ fbackgrnd +"', '"+ fyear+"', '"+ fmonth +"', '"+ fbank+"', '"+ finitials +"', '"+ ftype+"', '"+ fcheck+"', '"+ fdate+"', '"+ fdescription+"', "+ fdebit+", "+ fcredit+",'"+ fproject+"', '"+ fmsc1 +"', '"+ fmsc2 +"', '"+ fmsc3 +"', '"+ fmsc4 +"', '"+ fmsc5 +"', '"+ fmsc6 +"' )"; // =================================== New variables should be "var"-ed There is no need for the spaces in "', '", use "','" Somtimes you have a space behind the variable, and sometimes not, inconsistent! Datetime should be between #..#, not '..' It should reconstruct to: #2010/04/13# now it should look like: =================================== var SQL = "INSERT INTO books1 VALUES( "+ newid + "," + fprevrec + "," + fnextrec + ",'" + fforegrnd + "','" + fbackgrnd + "','" + fyear + "','" + fmonth +"','"+ fbank + "','" + finitials + "','" + ftype + "','" + fcheck + "',#" + fdate + "#,'" + fdescription + "'," + fdebit + "," + fcredit + ",'" + fproject + "','" + fmsc1 + "','" + fmsc2 + "','" + fmsc3 + "','" + fmsc4 + "','" + fmsc5 + "','" + fmsc6 + "')"; // =================================== However I would use string building functions, this gives more visible typing errors and is much better debuggable, and more reliable and reusable: =================================== var SQL = ''; setSQLnum(newid); setSQLnum(fprevrec); setSQLnum(fnextrec); setSQLstr(fforegrnd); setSQLstr(fbackgrnd); setSQLstr(fyear); setSQLstr(fmonth); setSQLstr(fbank); setSQLstr(finitials); setSQLstr(ftype); setSQLstr(fcheck); setSQLdate(fdate); setSQLstr(fdescription); setSQLnum(fdebit); setSQLnum(fcredit); setSQLstr(fproject); setSQLstr(fmsc1); setSQLstr(fmsc2); setSQLstr(fmsc3); setSQLstr(fmsc4); setSQLstr(fmsc5); setSQLstr(fmsc6); SQL = 'INSERT INTO books1 VALUES (' + SQL + ')'; function setSQLstr(theVar) { if (SQL!='') SQL += ','; SQL += "'" + theVar + "'"; }; function setSQLdate(theVar) { if (SQL!='') SQL += ','; SQL += '#' + theVar + '#'; }; function setSQLnum(theVar) { if (SQL!='') SQL += ','; SQL += theVar; }; Response.write{SQL); // assuming you are on ASP =================================== -- Evertjan. The Netherlands. (Please change the x'es to dots in my emailaddress)
From: Jeff North on 14 Apr 2010 02:53 On 13 Apr 2010 19:09:01 GMT, in comp.lang.javascript Rob Christiansen <robb_christiansen(a)q.com> <4bc4c14d$0$89865$815e3792(a)news.qwest.net> wrote: >| >| My 1st *.mdb file works great, My 2nd doesn't. I get a lot of "data >| type mismatch" error messages. The problem as i see it is, whereas all >| the fields in the 1st file are 'text,' the 2nd is 'text,' 'date/time,' >| and 'currency.' The software is apparently having trouble reading the >| different types. Below is the sql statement I'm using. How can i fix >| this? >| ------------------------------------- >| fdate is set as "date/time" type >| fdebit is set as "currency" type >| fcredit is set as "currency" type >| fprevrec and fnextrec are number type >| newid is set as "auto" type >| all the rest are "text" type >| >| SQL = "INSERT INTO books1 VALUES( "+ newid +", "+ fprevrec +", "+ >| fnextrec +", '"+ fforegrnd +"', '"+ fbackgrnd +"', '"+ fyear+"', '"+ >| fmonth +"', '"+ fbank+"', '"+ finitials +"', '"+ ftype+"', '"+ >| fcheck+"', '"+ fdate+"', '"+ fdescription+"', "+ fdebit+", "+ fcredit+", >| '"+ fproject+"', '"+ fmsc1 +"', '"+ fmsc2 +"', '"+ fmsc3 +"', '"+ fmsc4 >| +"', '"+ fmsc5 +"', '"+ fmsc6 +"' )"; //, '"+ fmsc7 +"' '"+ fmsc8 +"' >| )"; // >| >| crazyswede 1. This is a javascript newsgroup and nothing to do with sql syntax 2. mdb = MSAccess database and its formatting rules apply 3. when inserting data always include the list of fields that you are writing to. This helps with debugging. A field might have been added or removed from the table an is causing the insert to fail.
From: Evertjan. on 14 Apr 2010 04:33
Jeff North wrote on 14 apr 2010 in comp.lang.javascript: > On 13 Apr 2010 19:09:01 GMT, in comp.lang.javascript Rob Christiansen > <robb_christiansen(a)q.com> > <4bc4c14d$0$89865$815e3792(a)news.qwest.net> wrote: > >>| >>| My 1st *.mdb file works great, My 2nd doesn't. I get a lot of "data >>| type mismatch" error messages. The problem as i see it is, whereas >>| all the fields in the 1st file are 'text,' the 2nd is 'text,' >>| 'date/time,' and 'currency.' The software is apparently having >>| trouble reading the different types. Below is the sql statement I'm >>| using. How can i fix this? >>| ------------------------------------- >>| fdate is set as "date/time" type >>| fdebit is set as "currency" type >>| fcredit is set as "currency" type >>| fprevrec and fnextrec are number type >>| newid is set as "auto" type >>| all the rest are "text" type >>| >>| SQL = "INSERT INTO books1 VALUES( "+ newid +", "+ fprevrec +", "+ >>| fnextrec +", '"+ fforegrnd +"', '"+ fbackgrnd +"', '"+ fyear+"', '"+ >>| fmonth +"', '"+ fbank+"', '"+ finitials +"', '"+ ftype+"', '"+ >>| fcheck+"', '"+ fdate+"', '"+ fdescription+"', "+ fdebit+", "+ >>| fcredit+", '"+ fproject+"', '"+ fmsc1 +"', '"+ fmsc2 +"', '"+ fmsc3 >>| +"', '"+ fmsc4 +"', '"+ fmsc5 +"', '"+ fmsc6 +"' )"; //, '"+ fmsc7 >>| +"' '"+ fmsc8 +"' )"; // >>| >>| crazyswede > > 1. This is a javascript newsgroup and nothing to do with sql syntax I do not agree. The above code is written in Javascript. ASP-javascript can be a valuable tool for preparing and executing sql strings. See my other posting, with functions like ths one: function setSQLstr(theVar) { if (SQL!='') SQL += ','; SQL += "'" + theVar + "'"; }; -- Evertjan. The Netherlands. (Please change the x'es to dots in my emailaddress) |