p2p.wrox.com: Archives - asp

asp_databases thread: Insert into (question)

Message #1 by srod@f... on Tue, 16 Jan 2001 22:05:47 -0000

I am getting an error:



Error Type:

       Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

       [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT

INTO statement.

       /webpub/ESS/ess/softwareA.asp, line 169



The command that I use for my insert statement:



strSQL = "Insert into Product Information (SerialNum, Manufacture, " &

"ProductName, Version, " & "Description, Status," & "UserID, Comment," &

"LicenseLocation) " & "Values('" & Request.Form("txtSerialNum") & "'," &

Request.Form("txtManufacture") & "," & Request.Form("txtProductName") &

"," & Request.Form("txtVersion") & "," & Request.Form("txtDescription") &

"," & Request.Form("txtStatus") & "," & Request.Form("txtUserID") & "," &

Request.Form("txtComment") & ",'" & Request.Form("txtLicenseLocation") &

"')"



Also, can I use this same statement for multiple tables?



example:

 

strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)



PLEASE help me!!! This is my first time using ASP.

Message #2 by Imar Spaanjaars <Imar@S...> on Tue, 16 Jan 2001 23:39:18 +0100

It looks like you need to delimit your fields with an ' if they are text 

fields in the database.



You already did that with the serial num field, but not with the others.



In Access you can't add multiple rows at once. The best thing you could do 

is execute multiple statements right after each other over the same connection.



(In SQL server you can add multiple statements by separating them with a ; 

and a line break)





HtH





Imar









At 10:05 PM 1/16/2001 +0000, you wrote:

>I am getting an error:

>

>Error Type:

>        Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

>        [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT

>INTO statement.

>        /webpub/ESS/ess/softwareA.asp, line 169

>

>The command that I use for my insert statement:

>

>strSQL = "Insert into Product Information (SerialNum, Manufacture, " &

>"ProductName, Version, " & "Description, Status," & "UserID, Comment," &

>"LicenseLocation) " & "Values('" & Request.Form("txtSerialNum") & "'," &

>Request.Form("txtManufacture") & "," & Request.Form("txtProductName") &

>"," & Request.Form("txtVersion") & "," & Request.Form("txtDescription") &

>"," & Request.Form("txtStatus") & "," & Request.Form("txtUserID") & "," &

>Request.Form("txtComment") & ",'" & Request.Form("txtLicenseLocation") &

>"')"

>

>Also, can I use this same statement for multiple tables?

>

>example:

>

>strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

>strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

>strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

>

>PLEASE help me!!! This is my first time using ASP.

Message #3 by "Savonnya D. Rodrigues" <srod@f...> on Wed, 17 Jan 2001 09:45:17 -0600

I did the delimited my fields, thanks. Now, I am testing each insert into

statement. I got 1 out of 3. I am having problems with one. The error is:



Error Type:

       Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

       [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in

criteria expression.

       /webpub/ESS/ess/softwareA.asp, line 169



I check my actual database and deleted the query statements I had in there.

Then I ran it again and got the same error. Can you please help? The insert

into statement that I am having errors with is:



strSQL = "Insert into PurchaseOrder (POComment,PCQuote , " & "DateReceived,

DateApproved," & "Cost, MaintenanceAgree) " & "Values('" &

Request.Form("txtPOComment") & "','" & Request.Form("txtPCQuote") & "','" &

Request.Form("txtDateReceived") & "','" & Request.Form("txtDateApproved") &

"','" & Request.Form("txtCost") & "','" &

Request.Form("txtMaintenanceAgree") & "')"



Also,  I checked my values in my statement verses my table in my database

and it is fine.







Thanks!!!





----- Original Message -----

From: "Imar Spaanjaars" <Imar@S...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, January 16, 2001 4:39 PM

Subject: [asp_databases] Re: Insert into (question)





> It looks like you need to delimit your fields with an ' if they are text

> fields in the database.

>

> You already did that with the serial num field, but not with the others.

>

> In Access you can't add multiple rows at once. The best thing you could do

> is execute multiple statements right after each other over the same

connection.

>

> (In SQL server you can add multiple statements by separating them with a ;

> and a line break)

>

>

> HtH

>

>

> Imar

>

>

>

>

> At 10:05 PM 1/16/2001 +0000, you wrote:

> >I am getting an error:

> >

> >Error Type:

> >        Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

> >        [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT

> >INTO statement.

> >        /webpub/ESS/ess/softwareA.asp, line 169

> >

> >The command that I use for my insert statement:

> >

> >strSQL = "Insert into Product Information (SerialNum, Manufacture, " &

> >"ProductName, Version, " & "Description, Status," & "UserID, Comment," &

> >"LicenseLocation) " & "Values('" & Request.Form("txtSerialNum") & "'," &

> >Request.Form("txtManufacture") & "," & Request.Form("txtProductName") &

> >"," & Request.Form("txtVersion") & "," & Request.Form("txtDescription") &

> >"," & Request.Form("txtStatus") & "," & Request.Form("txtUserID") & "," &

> >Request.Form("txtComment") & ",'" & Request.Form("txtLicenseLocation") &

> >"')"

> >

> >Also, can I use this same statement for multiple tables?

> >

> >example:

> >

> >strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

> >strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

> >strSQL= "Insert into tablename(col1,col2,..)values(value1,value2,..)

> >

> >PLEASE help me!!! This is my first time using ASP.

>

Message #4 by Gregory_Griffiths@c... on Wed, 17 Jan 2001 16:21:49 +0000

add a 



response.write(strSQL)



before you execute the code and then look at it to check that it is 

correct then run it against the db.



> -----Original Message-----

> From: srod@f... [mailto:srod@f...]

> Sent: 17 January 2001 15:45

> To: asp_databases@p...

> Cc: srod@f...

> Subject: [asp_databases] Re: Insert into (question)

> 

> 

> I did the delimited my fields, thanks. Now, I am testing each 

> insert into

> statement. I got 1 out of 3. I am having problems with one. 

> The error is:

> 

> Error Type:

>        Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

>        [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in

> criteria expression.

>        /webpub/ESS/ess/softwareA.asp, line 169

> 

> I check my actual database and deleted the query statements I 

> had in there.

> Then I ran it again and got the same error. Can you please 

> help? The insert

> into statement that I am having errors with is:

> 

> strSQL = "Insert into PurchaseOrder (POComment,PCQuote , " & 

> "DateReceived,

> DateApproved," & "Cost, MaintenanceAgree) " & "Values('" &

> Request.Form("txtPOComment") & "','" & 

> Request.Form("txtPCQuote") & "','" &

> Request.Form("txtDateReceived") & "','" & 

> Request.Form("txtDateApproved") &

> "','" & Request.Form("txtCost") & "','" &

> Request.Form("txtMaintenanceAgree") & "')"

> 

> Also,  I checked my values in my statement verses my table in 

> my database

> and it is fine.

> 

> 

> 

> Thanks!!!

> 

> 

> ----- Original Message -----

> From: "Imar Spaanjaars" <Imar@S...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Tuesday, January 16, 2001 4:39 PM

> Subject: [asp_databases] Re: Insert into (question)

> 

> 

> > It looks like you need to delimit your fields with an ' if 

> they are text

> > fields in the database.

> >

> > You already did that with the serial num field, but not 

> with the others.

> >

> > In Access you can't add multiple rows at once. The best 

> thing you could do

> > is execute multiple statements right after each other over the same

> connection.

> >

> > (In SQL server you can add multiple statements by 

> separating them with a ;

> > and a line break)

> >

> >

> > HtH

> >

> >

> > Imar

> >

> >

> >

> >

> > At 10:05 PM 1/16/2001 +0000, you wrote:

> > >I am getting an error:

> > >

> > >Error Type:

> > >        Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

> > >        [Microsoft][ODBC Microsoft Access Driver] Syntax 

> error in INSERT

> > >INTO statement.

> > >        /webpub/ESS/ess/softwareA.asp, line 169

> > >

> > >The command that I use for my insert statement:

> > >

> > >strSQL = "Insert into Product Information (SerialNum, 

> Manufacture, " &

> > >"ProductName, Version, " & "Description, Status," & 

> "UserID, Comment," &

> > >"LicenseLocation) " & "Values('" & 

> Request.Form("txtSerialNum") & "'," &

> > >Request.Form("txtManufacture") & "," & 

> Request.Form("txtProductName") &

> > >"," & Request.Form("txtVersion") & "," & 

> Request.Form("txtDescription") &

> > >"," & Request.Form("txtStatus") & "," & 

> Request.Form("txtUserID") & "," &

> > >Request.Form("txtComment") & ",'" & 

> Request.Form("txtLicenseLocation") &

> > >"')"

> > >

> > >Also, can I use this same statement for multiple tables?

> > >

> > >example:

> > >

> > >strSQL= "Insert into 

> tablename(col1,col2,..)values(value1,value2,..)

> > >strSQL= "Insert into 

> tablename(col1,col2,..)values(value1,value2,..)

> > >strSQL= "Insert into 

> tablename(col1,col2,..)values(value1,value2,..)

> > >

> > >PLEASE help me!!! This is my first time using ASP.

> >

Message #5 by Imar Spaanjaars <Imar@S...> on Wed, 17 Jan 2001 17:16:50 +0100

As a general tips for post like this: not only post the strSQL = " bla bla 

bla", but also post the outcome of it to this list.

Do a Response.Write(strSQL) just before you execute it.



Now as for your problem: I think it's the DateReceived and DateApproved 

fields that's causing the error.

Dates in Microsoft Access need to be delimited by a # (it's a ' in SQL 

server, so if you are planning to upgrade, it's better to define a CONST 

DATE_DELIMITER that holds the character, so you can easily switch between 

the two).



Also, when you list the fields, there is no need to break up the string 

(Insert into PurchaseOrder (POComment,PCQuote , " & "DateReceived,)



So try this instead:



strSQL = "Insert into PurchaseOrder (POComment,PCQuote, " & _

         "DateReceived, DateApproved, Cost, MaintenanceAgree) " & _

         "Values('" & _

         Request.Form("txtPOComment") & "', '" & _

         Request.Form("txtPCQuote") & "', #" & _

         Request.Form("txtDateReceived") & "#, #" & _

         Request.Form("txtDateApproved") & "#, '" & _

         Request.Form("txtCost") & "', '" & _

         Request.Form("txtMaintenanceAgree") & "')"



If Cost is a number in the database, leave out the ' on both sites....





HtH



Imar







At 09:45 AM 1/17/2001 -0600, you wrote:

>I did the delimited my fields, thanks. Now, I am testing each insert into

>statement. I got 1 out of 3. I am having problems with one. The error is:

>

>Error Type:

>        Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

>        [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in

>criteria expression.

>        /webpub/ESS/ess/softwareA.asp, line 169

>

>I check my actual database and deleted the query statements I had in there.

>Then I ran it again and got the same error. Can you please help? The insert

>into statement that I am having errors with is:

>

>strSQL = "Insert into PurchaseOrder (POComment,PCQuote , " & "DateReceived,

>DateApproved," & "Cost, MaintenanceAgree) " & "Values('" &

>Request.Form("txtPOComment") & "','" & Request.Form("txtPCQuote") & "','" &

>Request.Form("txtDateReceived") & "','" & Request.Form("txtDateApproved") &

>"','" & Request.Form("txtCost") & "','" &

>Request.Form("txtMaintenanceAgree") & "')"

>

>Also,  I checked my values in my statement verses my table in my database

>and it is fine.

>

>

>

>Thanks!!!

Message #6 by "Savonnya D. Rodrigues" <srod@f...> on Wed, 17 Jan 2001 11:14:37 -0600

Thanks!! You are a saint. I will give it a try.



sdr

Message #7 by "Wally Burfine" <oopconsultant@h...> on Wed, 17 Jan 2001 22:03:19 -0000

you have date received as a text field. If this is a date field you should 

put the # as the delimiter rather than the single quote.

Message #8 by "Ken Schaefer" <ken@a...> on Thu, 18 Jan 2001 13:05:26 +1100

www.adopenstatic.com/faq/80040e07.asp



Cheers

Ken



----- Original Message -----

From: "Savonnya D. Rodrigues" <srod@f...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, January 18, 2001 2:45 AM

Subject: [asp_databases] Re: Insert into (question)





> I did the delimited my fields, thanks. Now, I am testing each insert into

> statement. I got 1 out of 3. I am having problems with one. The error is:

>

> Error Type:

>        Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

>        [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in

> criteria expression.

>        /webpub/ESS/ess/softwareA.asp, line 169

>

> I check my actual database and deleted the query statements I had in

there.

> Then I ran it again and got the same error. Can you please help? The

insert

> into statement that I am having errors with is:

>

> strSQL = "Insert into PurchaseOrder (POComment,PCQuote , " &

"DateReceived,

> DateApproved," & "Cost, MaintenanceAgree) " & "Values('" &

> Request.Form("txtPOComment") & "','" & Request.Form("txtPCQuote") & "','"

&

> Request.Form("txtDateReceived") & "','" & Request.Form("txtDateApproved")

&

> "','" & Request.Form("txtCost") & "','" &

> Request.Form("txtMaintenanceAgree") & "')"

>

> Also,  I checked my values in my statement verses my table in my database

> and it is fine.

>

>

Return to Index

P2P Archives