access insert statement

I am used to SQL Server, no Access, but this one thing has to be done in
Access. Can you tell me if this query will work, based on the syntax?

I am trying create a new row on the database, in one table, and the primary
key is an Autonumber called PersonalID. This is on the second page, which
shows after the personal has filled out some info on the first page, then
submitted the form using POST. Database name is Shape, and table is named
Personal.


-----------------------------------------------------------------------
DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
Server.Mappath("Shape.mdb") & ";"

Set objRecordset = Server.CreateObject("ADODB.Recordset")
objRecordset.Open "Shape", DB_CONNECTIONSTRING, adOpenStatic,
adLockPessimistic, adCmdTable

strFirstName = Replace(Trim(Request.Form("FirstName")),"'","''")
strLastname = Replace(Trim(Request.Form("LastName")),"'","''")

strSQL = "set nocount on; INSERT INTO Personal(FirstName,LastName) VALUES
(strFirstName,strLastName); select @@identity [newid];"
Set rs=objConnection.execute (strSQL)
strPersonalID = RS("newid")
rs.Close

Curt_C
Sun Aug 31 15:29:22 CDT 2003

have you tried? was there an error?

I'm not sure that Access has the "@@identity"....

I'd suggest trying and posting the specific errors.

--
----------------------------------------------------------
Curt Christianson (Software_AT_Darkfalz.Com)
Owner/Lead Designer, DF-Software
http://www.Darkfalz.com
---------------------------------------------------------
..Offering free scripts & code snippits for everyone...
---------------------------------------------------------


"middletree" <middletree@htomail.com> wrote in message
news:uCKy63$bDHA.620@TK2MSFTNGP11.phx.gbl...
> I am used to SQL Server, no Access, but this one thing has to be done in
> Access. Can you tell me if this query will work, based on the syntax?
>
> I am trying create a new row on the database, in one table, and the
primary
> key is an Autonumber called PersonalID. This is on the second page, which
> shows after the personal has filled out some info on the first page, then
> submitted the form using POST. Database name is Shape, and table is named
> Personal.
>
>
> -----------------------------------------------------------------------
> DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
> Server.Mappath("Shape.mdb") & ";"
>
> Set objRecordset = Server.CreateObject("ADODB.Recordset")
> objRecordset.Open "Shape", DB_CONNECTIONSTRING, adOpenStatic,
> adLockPessimistic, adCmdTable
>
> strFirstName = Replace(Trim(Request.Form("FirstName")),"'","''")
> strLastname = Replace(Trim(Request.Form("LastName")),"'","''")
>
> strSQL = "set nocount on; INSERT INTO Personal(FirstName,LastName)
VALUES
> (strFirstName,strLastName); select @@identity [newid];"
> Set rs=objConnection.execute (strSQL)
> strPersonalID = RS("newid")
> rs.Close
>
>

Bob
Sun Aug 31 18:10:33 CDT 2003

Curt_C [MVP] wrote:
> have you tried? was there an error?
>
> I'm not sure that Access has the "@@identity"....
>
FYI, it does as of Jet 4.0

Bob

middletree
Sun Aug 31 22:54:18 CDT 2003

I hadn't tried yet when I posted that. Was looking to see if there was
something obviously wrong.

As it turned out, it failed, but I have no idea why. What's more, it doesn't
seem to have anything to do with identity.

Here's the error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0x3b8 Thread 0x65c
DBC 0x1774064 Jet'.
/grace/shapethankyou.asp, line 11

Where line 11 is:

objRecordset.Open "Shape", DB_CONNECTIONSTRING, adOpenStatic,
adLockPessimistic, adCmdTable





"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:#Yuh15$bDHA.1656@TK2MSFTNGP10.phx.gbl...
> have you tried? was there an error?
>
> I'm not sure that Access has the "@@identity"....
>
> I'd suggest trying and posting the specific errors.
>
> --
> ----------------------------------------------------------
> Curt Christianson (Software_AT_Darkfalz.Com)
> Owner/Lead Designer, DF-Software
> http://www.Darkfalz.com
> ---------------------------------------------------------
> ..Offering free scripts & code snippits for everyone...
> ---------------------------------------------------------
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:uCKy63$bDHA.620@TK2MSFTNGP11.phx.gbl...
> > I am used to SQL Server, no Access, but this one thing has to be done in
> > Access. Can you tell me if this query will work, based on the syntax?
> >
> > I am trying create a new row on the database, in one table, and the
> primary
> > key is an Autonumber called PersonalID. This is on the second page,
which
> > shows after the personal has filled out some info on the first page,
then
> > submitted the form using POST. Database name is Shape, and table is
named
> > Personal.
> >
> >
> > -----------------------------------------------------------------------
> > DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
_
> > Server.Mappath("Shape.mdb") & ";"
> >
> > Set objRecordset = Server.CreateObject("ADODB.Recordset")
> > objRecordset.Open "Shape", DB_CONNECTIONSTRING, adOpenStatic,
> > adLockPessimistic, adCmdTable
> >
> > strFirstName = Replace(Trim(Request.Form("FirstName")),"'","''")
> > strLastname = Replace(Trim(Request.Form("LastName")),"'","''")
> >
> > strSQL = "set nocount on; INSERT INTO Personal(FirstName,LastName)
> VALUES
> > (strFirstName,strLastName); select @@identity [newid];"
> > Set rs=objConnection.execute (strSQL)
> > strPersonalID = RS("newid")
> > rs.Close
> >
> >
>
>

Bob
Mon Sep 01 06:36:30 CDT 2003

middletree wrote:
> I hadn't tried yet when I posted that. Was looking to see if there was
> something obviously wrong.
>
> As it turned out, it failed, but I have no idea why. What's more, it
> doesn't seem to have anything to do with identity.
>
> Here's the error:
>
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver]General error Unable to open
> registry key 'Temporary (volatile) Jet DSN for process 0x3b8 Thread
> 0x65c DBC 0x1774064 Jet'.
> /grace/shapethankyou.asp, line 11
>

This error is caused by a permissions problem. You could go read about it
here:
http://www.aspfaq.com/show.asp?id=2154
and try to correct it, or you could do the easy thing and switch to using
the native Jet OLEDB provider in your connection string. See
www.connectionstrings.com. You do need to make sure the IUSR account has
Change permissions on the folder containing the mdb file. Do not assume IUSR
is in the Everyone group.

Bob Barrows

middletree
Mon Sep 01 13:55:29 CDT 2003

Thanks, but this is geting harder and harder the more I look into it. I
copied the string from the second link you gave me. (The first one didn't
apply to my situation) and now I get this error:

Error Type:
Microsoft JET Database Engine (0x80004005)
Could not find file 'C:\WINNT\system32\Shape.mdb'.
/grace/shapethankyou.asp, line 8


The problem being that I didn't specify it to be in the C:|WINNT drive. I
simply had this:

objConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Shape.mdb;User
Id=admin;Password=;"

as my string, straight from the connections.com page (I only changed the
name of the database to Shape.mdb; everything else is the same)
I dont' have a password for this datbase, so I left it blank.

Anyway, this is frustrating. I have had a hard time understanding connection
strings for the 4 years I have been doing ASP. I wish someone would
standardize them so I could have one line that gets me connected, so I could
concentrate on the rest of the coding.

Any help would be appreciated.

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#F97S1HcDHA.2632@TK2MSFTNGP12.phx.gbl...
> middletree wrote:
> > I hadn't tried yet when I posted that. Was looking to see if there was
> > something obviously wrong.
> >
> > As it turned out, it failed, but I have no idea why. What's more, it
> > doesn't seem to have anything to do with identity.
> >
> > Here's the error:
> >
> > a.. Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > [Microsoft][ODBC Microsoft Access Driver]General error Unable to open
> > registry key 'Temporary (volatile) Jet DSN for process 0x3b8 Thread
> > 0x65c DBC 0x1774064 Jet'.
> > /grace/shapethankyou.asp, line 11
> >
>
> This error is caused by a permissions problem. You could go read about it
> here:
> http://www.aspfaq.com/show.asp?id=2154
> and try to correct it, or you could do the easy thing and switch to using
> the native Jet OLEDB provider in your connection string. See
> www.connectionstrings.com. You do need to make sure the IUSR account has
> Change permissions on the folder containing the mdb file. Do not assume
IUSR
> is in the Everyone group.
>
> Bob Barrows
>
>

Bob
Mon Sep 01 14:29:46 CDT 2003

You have to tell it where the database is! Put the path to the database in
the connection string!

" ... Data Source=p:\ath\to\database.mdb"

If you created an ODBC DSN, you had to have supplied the same information,
didn't you? Is it such a huge leap to deduce that you have to supply the
same information to the OLEDB provider?

The only semi-tricky part is realizing that you have to give a file-system
path to the database, not a url (the DSN required it also ...). One way to
make this a little easier is to use Server.Mappath().

Bob Barrows

middletree wrote:
> Thanks, but this is geting harder and harder the more I look into it.
> I copied the string from the second link you gave me. (The first one
> didn't apply to my situation) and now I get this error:
>
> Error Type:
> Microsoft JET Database Engine (0x80004005)
> Could not find file 'C:\WINNT\system32\Shape.mdb'.
> /grace/shapethankyou.asp, line 8
>
>
> The problem being that I didn't specify it to be in the C:|WINNT
> drive. I simply had this:
>
> objConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=Shape.mdb;User Id=admin;Password=;"
>
> as my string, straight from the connections.com page (I only changed
> the name of the database to Shape.mdb; everything else is the same)
> I dont' have a password for this datbase, so I left it blank.
>
> Anyway, this is frustrating. I have had a hard time understanding
> connection strings for the 4 years I have been doing ASP. I wish
> someone would standardize them so I could have one line that gets me
> connected, so I could concentrate on the rest of the coding.
>
> Any help would be appreciated.
>

middletree
Mon Sep 01 15:28:31 CDT 2003

I can tell by the one of your post that you assume I know a lot more than I
do. I barely am aware that ODBC and OLEDB exist, let alone know the
difference between them. At every job I have had, I came onto an existing
project, with several other team members, and the database connection was
already written.

I have tried researching this, but most of what I have read is not
elementary enough. It mentions OLE DB, ODBC, MDAC, DSN, DSN-less, and
several other alphabet soup items as if I have a slightest clue what they
are talking about.

I wish there was some sample code out there which gave me the whole page,
and all I'd have to do is change the name of the table and fields.

As for the question you are trying to answer for me, I guess I could put the
exact path in there(C:\inetpub\wwwroot\grace\shape.mdb), and it would work
on my machine, but then what happens when I put it on the host that is going
to be hosying this website? I can't know where they will put the files for
my website, can I?


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:utHsv9LcDHA.2460@TK2MSFTNGP10.phx.gbl...
> You have to tell it where the database is! Put the path to the database in
> the connection string!
>
> " ... Data Source=p:\ath\to\database.mdb"
>
> If you created an ODBC DSN, you had to have supplied the same information,
> didn't you? Is it such a huge leap to deduce that you have to supply the
> same information to the OLEDB provider?
>
> The only semi-tricky part is realizing that you have to give a file-system
> path to the database, not a url (the DSN required it also ...). One way to
> make this a little easier is to use Server.Mappath().
>
> Bob Barrows
>
> middletree wrote:
> > Thanks, but this is geting harder and harder the more I look into it.
> > I copied the string from the second link you gave me. (The first one
> > didn't apply to my situation) and now I get this error:
> >
> > Error Type:
> > Microsoft JET Database Engine (0x80004005)
> > Could not find file 'C:\WINNT\system32\Shape.mdb'.
> > /grace/shapethankyou.asp, line 8
> >
> >
> > The problem being that I didn't specify it to be in the C:|WINNT
> > drive. I simply had this:
> >
> > objConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=Shape.mdb;User Id=admin;Password=;"
> >
> > as my string, straight from the connections.com page (I only changed
> > the name of the database to Shape.mdb; everything else is the same)
> > I dont' have a password for this datbase, so I left it blank.
> >
> > Anyway, this is frustrating. I have had a hard time understanding
> > connection strings for the 4 years I have been doing ASP. I wish
> > someone would standardize them so I could have one line that gets me
> > connected, so I could concentrate on the rest of the coding.
> >
> > Any help would be appreciated.
> >
>
>

middletree
Tue Sep 02 10:36:46 CDT 2003


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#VBANJUcDHA.3708@tk2msftngp13.phx.gbl...
> middletree wrote:
> >
> > I have no idea what IUSR is. I have no way of controllling what the
> > host does or where they put the database.
>
> I'm not intending this to be a dig (although I am well-aware that it may
> come off that way): 4 years of asp development and you don't know what
IUSR
> is? I'm sorry, but I'm just a little surprised that this is even possible.
> Even if you're not involved with IIS administration. I'm not involved with
> IIS administration, but I could not have done my job of developing asp
> applications without becoming exposed to the IUSR and IWAM accounts during
> the first couple months of my asp learning curve. I realize my experience
> may not be typical, but I can't help but being surprised when someone says
> they've been doing this work for 4 years without ever encountering these
> concepts.


I imagine that it is hard to believe, but I was hired at my first company to
work on an existing web application, and that stuff was alreay built. I just
had to add the lines for the include file which had all this stuff, then go
to work on what I needed to do, mostly design issues, some ASP to add code
to, for example, have a loop for something which would display things
dynamically. The next company was the same situation, site already started
being built, I just had to add the include files at the top for styles,
database connection, etc. At my current job, ASP is not my main function,
but I have been writing a web-based app myself, but again, I was able to
copy code from the guy who does the Intranet, and never had to worry about
it.

Just like I have a vested interest in the idea that my car needs to be
running, I have never actually taken the engine apart to see what makes it
run, I have never felt the need to go into connection code to find out what
makes it run. I have, on occasion, tried to read up on ADO, OLE, Jet, and
all the other terms which are too numerous to mention, and it didn't really
click with me. Don't know why. I'm a smart guy. 2 college degrees. Won the
spelling bee in the 5th grade. But for some reason, I just can't get this
database connection stuff. Doesn't make a lick of sense to me.

For this project for which I am asking help, I am doing a thing on my
church's web site, and they have to use Access, which I have never used
before.

Hate to sound defensive, but since you said you had a hard time believing
it, well, now you know the boring details.

Bob
Tue Sep 02 10:45:35 CDT 2003

middletree wrote:
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:#VBANJUcDHA.3708@tk2msftngp13.phx.gbl...
>> middletree wrote:
>
> Hate to sound defensive, but since you said you had a hard time
> believing it, well, now you know the boring details.

Sorry to put you on the defensive. Thanks for the boring details. I was
trying to express surprise and lack of understanding rather than lack of
belief, so your details did help.

Bob

middletree
Tue Sep 02 11:04:59 CDT 2003

Never was any offense taken. Just try and understand that I am trying to
learn this stuff. That's why I am posting these questions.


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:uX8uElWcDHA.2572@TK2MSFTNGP12.phx.gbl...
> middletree wrote:
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:#VBANJUcDHA.3708@tk2msftngp13.phx.gbl...
> >> middletree wrote:
> >
> > Hate to sound defensive, but since you said you had a hard time
> > believing it, well, now you know the boring details.
>
> Sorry to put you on the defensive. Thanks for the boring details. I was
> trying to express surprise and lack of understanding rather than lack of
> belief, so your details did help.
>
> Bob
>
>