access insert statement

Discussion in 'ASP General' started by Curt_C [MVP], Aug 31, 2003.

  1. Curt_C [MVP]

    Curt_C [MVP] Guest

    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" <> wrote in message
    news:uCKy63$...
    > 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 [MVP], Aug 31, 2003
    #1
    1. Advertising

  2. Curt_C [MVP]

    middletree Guest

    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, Aug 31, 2003
    #2
    1. Advertising

  3. Curt_C [MVP]

    Bob Barrows Guest

    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
     
    Bob Barrows, Sep 1, 2003
    #3
  4. Curt_C [MVP]

    middletree Guest

    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$...
    > 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" <> wrote in message
    > news:uCKy63$...
    > > 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, Sep 1, 2003
    #4
  5. Curt_C [MVP]

    Bob Barrows Guest

    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, Sep 1, 2003
    #5
  6. Curt_C [MVP]

    middletree Guest

    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" <> wrote in message
    news:#...
    > 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, Sep 1, 2003
    #6
  7. Curt_C [MVP]

    Bob Barrows Guest

    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.
    >
     
    Bob Barrows, Sep 1, 2003
    #7
  8. Curt_C [MVP]

    middletree Guest

    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" <> wrote in message
    news:...
    > 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, Sep 1, 2003
    #8
  9. Curt_C [MVP]

    middletree Guest

    "Bob Barrows" <> wrote in message
    news:#...
    > 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.
     
    middletree, Sep 2, 2003
    #9
  10. Curt_C [MVP]

    Bob Barrows Guest

    middletree wrote:
    > "Bob Barrows" <> wrote in message
    > news:#...
    >> 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
     
    Bob Barrows, Sep 2, 2003
    #10
  11. Curt_C [MVP]

    middletree Guest

    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" <> wrote in message
    news:...
    > middletree wrote:
    > > "Bob Barrows" <> wrote in message
    > > news:#...
    > >> 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, Sep 2, 2003
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    823
  2. =?Utf-8?B?TGVlIEhvbHNlbmJlY2s=?=

    help please on insert statement into Access from webpage !

    =?Utf-8?B?TGVlIEhvbHNlbmJlY2s=?=, Mar 10, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    337
    =?Utf-8?B?TGVlIEhvbHNlbmJlY2s=?=
    Mar 10, 2005
  3. Jay McGavren
    Replies:
    11
    Views:
    1,169
    Alan Krueger
    Jan 16, 2006
  4. Jason Melville
    Replies:
    4
    Views:
    135
    PB4FUN
    Sep 16, 2003
  5. J. Muenchbourg
    Replies:
    3
    Views:
    254
    Aaron Bertrand - MVP
    Sep 30, 2003
Loading...

Share This Page