Velocity Reviews > access insert statement

# access insert statement

Curt_C [MVP]
Guest
Posts: n/a

 08-31-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)
http://www.Darkfalz.com
---------------------------------------------------------
...Offering free scripts & code snippits for everyone...
---------------------------------------------------------

"middletree" <(E-Mail Removed)> wrote in message
news:uCKy63$(E-Mail Removed)... > 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 > > middletree Guest Posts: n/a  08-31-2003 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 Barrows Guest Posts: n/a  08-31-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 Guest Posts: n/a  09-01-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$(E-Mail Removed)...
> 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)
> http://www.Darkfalz.com
> ---------------------------------------------------------
> ..Offering free scripts & code snippits for everyone...
> ---------------------------------------------------------
>
>
> "middletree" <(E-Mail Removed)> wrote in message
> news:uCKy63\$(E-Mail Removed)...
> > 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,
> >
> > 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 Barrows
Guest
Posts: n/a

 09-01-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
Guest
Posts: n/a

 09-01-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 CWINNT drive. I

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

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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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 Barrows
Guest
Posts: n/a

 09-01-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 CWINNT
> drive. I simply had this:
>
> objConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>
> 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
Guest
Posts: n/a

 09-01-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

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 CWINNT
> > drive. I simply had this:
> >
> > objConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> >
> > 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
Guest
Posts: n/a

 09-02-2003

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
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 Barrows
Guest
Posts: n/a

 09-02-2003
middletree wrote:
> "Bob Barrows" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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