Thanks Peter:

I tried all that but still not working.  It just persists to insert an 
auto
number and that's it. It's funny because I had the same insert more or 
less
working with an Oracle database, just the connection string was 
different
and of course the SEQ.nextval added. I tried a response.write with
request.form and the values like 'name' do show up on response / insert
page.

I don't understand the single quote thing. I am very new to ASP and
databases.  Do you mean to make anything like this ' into ' ' ?

dan
----------------------------------------------------------------------

Subject: RE: Can't insert values into Access, only auto nu
     mber
From: "Peter Foti (PeterF)" <PeterF@S...>
Date: Tue, 25 Feb 2003 16:19:48 -0500
X-Message-Number: 15

For one thing, you are using "+" to concat part of the string, and "&" 
to
concat another part.  Replace all of your "+" with "&".  If you still 
have a
problem, try doing a Response.Write on your SQL string to make sure it's
doing what you think it is... it could be that there is no value for 
those
session variables.

One last thing... if one of those session variables contains a single 
quote
(apostrophe), then that could mess up your SQL string.  You should be 
sure
to replace single quotes with 2 single quotes in all of the values that 
you
add to your SQL string.

Regards,
Peter Foti


> -----Original Message-----
> From: Dan Englander [mailto:danomatic12@e...]
> Sent: Tuesday, February 25, 2003 2:18 PM
> To: ASP Databases
> Subject: [asp_databases] Can't insert values into Access, only auto
> number
>
>
> I have a form page that goes to an ASP page that inserts the
> form data into
> an Access database. When I submit the form, I get the
> response page which
> does the insert but when I go back to the database, the only
> thing that has
> been inserted is the auto number ID.
>
> (However, this does indicate that the insert page is "seeing"
> the database)
>
> I Can't figure why other values aren't inserting like 'Name',
> 'Email' etc..
>
> Here is my insert code:
>
> 	dim connObj, connStr, sSQL
> 	set connObj =3D server.CreateObject ("adodb.connection")
>
>
> 	connStr =3D "DRIVER=3DMicrosoft Access Driver (*.mdb);" &_
>    		 "DefaultDir=3D" & Request.ServerVariables
> ("PATH_INFO")& ";"
> &_
>    		 "DBQ=3D" & server.MapPath ("ContactDan.mdb")
>
>    	 sSQL =3D "INSERT INTO ContactMe (Name, Email, State, FindSite,
> Comments) 	VALUES (" &_
> 	    "'" & session("Name") + "', " &_
> 	    "'" & session("Email") + "', " &_
>  	   "'" & session("State") + "', " &_
> 	"'" & session("FindSite") + "', " &_
>   	  "'" & session("Comments") + "')"
>
> 	connObj.Open connStr
> 	connObj.execute (sSQL)
> 	connObj.Close
>
> Dan
>
> Dan Englander
> http://www.danenglander.com/
>
> -----------------------------------

Hi Dan,

Please Response.Write() your SQL statement to the screen, and post it to
this list so that we can see what it looks like (as per Peter's request). We
need to see what is actually being sent to the database (not what you think
it being sent to the database!)

In answer to your second question, yes. Imagine the following SQL statement:

INSERT INTO myTable (Field1) VALUES ('My value with a ' in it')

The database engine will think that the string you want to insert is: My
value with a
and it won't know what to do with: in it'
and will generate an error. You need to format the string as:

INSERT INTO myTable(Field1) VALUES('My value with a '' in it')

where the '' is two single quotes (not a double quote). This tells the
database engine that you want a single quote inserted into the database.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Dan Englander" <danomatic12@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, February 26, 2003 1:54 PM
Subject: [asp_databases] RE: Can't insert values into Access



Thanks Peter:

I tried all that but still not working.  It just persists to insert an auto
number and that's it. It's funny because I had the same insert more or less
working with an Oracle database, just the connection string was different
and of course the SEQ.nextval added. I tried a response.write with
request.form and the values like 'name' do show up on response / insert
page.

I don't understand the single quote thing. I am very new to ASP and
databases.  Do you mean to make anything like this ' into ' ' ?

dan
----------------------------------------------------------------------

Subject: RE: Can't insert values into Access, only auto nu
     mber
From: "Peter Foti (PeterF)" <PeterF@S...>
Date: Tue, 25 Feb 2003 16:19:48 -0500
X-Message-Number: 15

For one thing, you are using "+" to concat part of the string, and "&" to
concat another part.  Replace all of your "+" with "&".  If you still have a
problem, try doing a Response.Write on your SQL string to make sure it's
doing what you think it is... it could be that there is no value for those
session variables.

One last thing... if one of those session variables contains a single quote
(apostrophe), then that could mess up your SQL string.  You should be sure
to replace single quotes with 2 single quotes in all of the values that you
add to your SQL string.

Regards,
Peter Foti


> -----Original Message-----
> From: Dan Englander [mailto:danomatic12@e...]
> Sent: Tuesday, February 25, 2003 2:18 PM
> To: ASP Databases
> Subject: [asp_databases] Can't insert values into Access, only auto
> number
>
>
> I have a form page that goes to an ASP page that inserts the
> form data into
> an Access database. When I submit the form, I get the
> response page which
> does the insert but when I go back to the database, the only
> thing that has
> been inserted is the auto number ID.
>
> (However, this does indicate that the insert page is "seeing"
> the database)
>
> I Can't figure why other values aren't inserting like 'Name',
> 'Email' etc..
>
> Here is my insert code:
>
> dim connObj, connStr, sSQL
> set connObj = server.CreateObject ("adodb.connection")
>
>
> connStr = "DRIVER=Microsoft Access Driver (*.mdb);" &_
>    "DefaultDir=" & Request.ServerVariables
> ("PATH_INFO")& ";"
> &_
>    "DBQ=" & server.MapPath ("ContactDan.mdb")
>
>    sSQL = "INSERT INTO ContactMe (Name, Email, State, FindSite,
> Comments) VALUES (" &_
>     "'" & session("Name") + "', " &_
>     "'" & session("Email") + "', " &_
>     "'" & session("State") + "', " &_
> "'" & session("FindSite") + "', " &_
>     "'" & session("Comments") + "')"
>
> connObj.Open connStr
> connObj.execute (sSQL)
> connObj.Close
>
> Dan
>
> Dan Englander
> http://www.danenglander.com/
>
> -----------------------------------