access insert statement

C

Curt_C [MVP]

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.
 
M

middletree

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
 
M

middletree

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 said:
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 said:
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
 
B

Bob Barrows

middletree said:
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
 
M

middletree

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 said:
middletree said:
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
 
B

Bob Barrows

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
 
M

middletree

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?
 
M

middletree

Bob Barrows said:
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.
 
B

Bob Barrows

middletree said:
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
 
M

middletree

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top