p2p.wrox.com: Archives - access

access_asp thread: Problem with INSERT statement to Access 2000

Message #1 by "Hanson Lam" <hansonlam@h...> on Mon, 22 Oct 2001 05:08:41

Hello:



I am going blind trying to see what is wrong with the following insert 

statement:



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim strInsertSQL

		

strInsertSQL = "INSERT INTO tbl_Contact(firstname, lastname, position, 

email, phonenumber, company_ID) " & _

"VALUES('" & _

CStr(Request.Form("txtFirstName")) & "', '" & _

CStr(Request.Form("txtLastName")) & "', '" & _

CStr(Request.Form("txtPosition")) & "', '" & _

CStr(Request.Form("txtEmail")) & "', '" & _

CStr(Request.Form("txtPhone")) & "', " & _			

CInt(Request.Form("cboCompany")) & ")"

				

Dim objComm



Set objComm = Server.CreateObject("ADODB.Command")



objComm.ActiveConnection = strConnect

objComm.CommandText = strInsertSQL

objComm.CommandType = adCmdText

objComm.Execute

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



Thanks

Message #2 by "Tony DiNucci" <tonydinucci@h...> on Tue, 23 Oct 2001 00:19:00

try using the code below, personally I would request the form fields 

before hand and then use variables in the SQL statement..........thats 

just my preference though.

Hope this Helps

Tony

<b>

strInsertSQL = "INSERT INTO tbl_Contact " &_

  "(firstname, lastname, position, " &_

  " email, phonenumber, company_ID) " & _

  "VALUES( " & _

  " '" & CStr(Request.Form("txtFirstName")) & "', " &_

  " '" & CStr(Request.Form("txtLastName")) & "', " &_

  " '" & CStr(Request.Form("txtPosition")) & "', " &_

  " '" & CStr(Request.Form("txtEmail")) & "', " &_

  " '" & CStr(Request.Form("txtPhone")) & "', " &_

  " '" & CInt(Request.Form("cboCompany")) & "' " &_

    ")"

Connection.Execute strInsertSQL  

</b>









> Hello:

> 

> I am going blind trying to see what is wrong with the following insert 

> statement:

> 

> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> Dim strInsertSQL

> 		

> strInsertSQL = "INSERT INTO tbl_Contact(firstname, lastname, position, 

> email, phonenumber, company_ID) " & _

> "VALUES('" & _

> CStr(Request.Form("txtFirstName")) & "', '" & _

> CStr(Request.Form("txtLastName")) & "', '" & _

> CStr(Request.Form("txtPosition")) & "', '" & _

> CStr(Request.Form("txtEmail")) & "', '" & _

> CStr(Request.Form("txtPhone")) & "', " & _			

> CInt(Request.Form("cboCompany")) & ")"

> 				

> Dim objComm

> 

> Set objComm = Server.CreateObject("ADODB.Command")

> 

> objComm.ActiveConnection = strConnect

> objComm.CommandText = strInsertSQL

> objComm.CommandType = adCmdText

> objComm.Execute

> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'

> 

> Thanks

Message #3 by "Ken Schaefer" <ken@a...> on Tue, 23 Oct 2001 12:22:32 +1000

Do this:



Response.Write(strInsertSQL)

Response.End



and post the output to the list, so that we can see exactly what you're

sending to the database.

As well, please post your error and error message - are we supposed to

*guess* what problem you are having?



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

From: "Hanson Lam" <hansonlam@h...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, October 22, 2001 3:08 PM

Subject: [access_asp] Problem with INSERT statement to Access 2000





: Hello:

:

: I am going blind trying to see what is wrong with the following insert

: statement:

:

: '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

: Dim strInsertSQL

:

: strInsertSQL = "INSERT INTO tbl_Contact(firstname, lastname, position,

: email, phonenumber, company_ID) " & _

: "VALUES('" & _

: CStr(Request.Form("txtFirstName")) & "', '" & _

: CStr(Request.Form("txtLastName")) & "', '" & _

: CStr(Request.Form("txtPosition")) & "', '" & _

: CStr(Request.Form("txtEmail")) & "', '" & _

: CStr(Request.Form("txtPhone")) & "', " & _

: CInt(Request.Form("cboCompany")) & ")"

:

: Dim objComm

:

: Set objComm = Server.CreateObject("ADODB.Command")

:

: objComm.ActiveConnection = strConnect

: objComm.CommandText = strInsertSQL

: objComm.CommandType = adCmdText

: objComm.Execute

: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

:

: Thanks

Message #4 by "Hanson Lam" <hansonlam@h...> on Wed, 24 Oct 2001 06:43:12

Hello:



I've Response.Write the strInsertSQL and this is what it gives me (after 

filling out the form):



INSERT INTO tbl_Contact(firstname, lastname, position, email, phonenumber, 

company_ID) VALUES

('Joe', 'Smith', 'CEO', 'jsmith@e...', '1234567890', 2) 



The error is:



Error Type:

Microsoft JET Database Engine (0x80040E14)

Syntax error in INSERT INTO statement.

/contactNetworkASP/contact_addHandler.asp, line 114



Line 114 is the objComm.Execute line.



Thanks!



Hanson

Message #5 by "Zimmer Computer Consulting" <zee@t...> on Wed, 24 Oct 2001 11:56:40 -0700

I would try putting spaces before all opening parentheses. This may not be

the problem, but it is worth a try:



    SqlString = "INSERT INTO tbl_Contact (firstname, lastname," _

                    & " position, email, phonenumber, company_ID)" _

                    & " VALUES ('Joe', 'Smith', 'CEO', " _

                    & " 'jsmith@e...', '1234567890', 2) "







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

From: Hanson Lam <hansonlam@h...>

To: Access ASP <access_asp@p...>

Sent: Wednesday, October 24, 2001 6:43 AM

Subject: [access_asp] Re: Problem with INSERT statement to Access 2000





> Hello:

>

> I've Response.Write the strInsertSQL and this is what it gives me (after

> filling out the form):

>

> INSERT INTO tbl_Contact(firstname, lastname, position, email, phonenumber,

> company_ID) VALUES

> ('Joe', 'Smith', 'CEO', 'jsmith@e...', '1234567890', 2)

>

> The error is:

>

> Error Type:

> Microsoft JET Database Engine (0x80040E14)

> Syntax error in INSERT INTO statement.

> /contactNetworkASP/contact_addHandler.asp, line 114

>

> Line 114 is the objComm.Execute line.

>

> Thanks!

>

> Hanson

Message #6 by "Ken Schaefer" <ken@a...> on Thu, 25 Oct 2001 11:17:24 +1000

http://www.adopenstatic.com/faq/80040e14.asp#scenario2a



I just looked in Access online help and Position is a reserved word.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Hanson Lam" <hansonlam@h...>

Subject: [access_asp] Re: Problem with INSERT statement to Access 2000





: Hello:

:

: I've Response.Write the strInsertSQL and this is what it gives me (after

: filling out the form):

:

: INSERT INTO tbl_Contact(firstname, lastname, position, email, phonenumber,

: company_ID) VALUES

: ('Joe', 'Smith', 'CEO', 'jsmith@e...', '1234567890', 2)

:

: The error is:

:

: Error Type:

: Microsoft JET Database Engine (0x80040E14)

: Syntax error in INSERT INTO statement.

: /contactNetworkASP/contact_addHandler.asp, line 114

:

: Line 114 is the objComm.Execute line.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Return to Index

P2P Archives