Access database insert statement with an autonumber (identity) in vb.net

Discussion in 'Microsoft VB .NET' started by Cindy H, Jun 2, 2006.

  1. Cindy H

    Cindy H Guest

    Hi

    I'm having a problem getting the insert statement correct for an Access
    table I'm using.
    The Access table uses an autonumber for the primary key.
    I have tried this:
    INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
    vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    Tournament WHERE (ID = @@IDENTITY);"

    This works with a sql server database.

    I'm getting this error:

    System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    statement

    Does anyone know how to do this?

    Thanks,

    CindyH
     
    Cindy H, Jun 2, 2006
    #1
    1. Advertisements

  2. Cindy H

    Guest Guest

    RE: Access database insert statement with an autonumber (identity) in

    Cindy,

    Try executing the Insert, then execute Select @@Identity as a second command.

    Kerry Moorman


    "Cindy H" wrote:

    > Hi
    >
    > I'm having a problem getting the insert statement correct for an Access
    > table I'm using.
    > The Access table uses an autonumber for the primary key.
    > I have tried this:
    > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
    > vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > Tournament WHERE (ID = @@IDENTITY);"
    >
    > This works with a sql server database.
    >
    > I'm getting this error:
    >
    > System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > statement
    >
    > Does anyone know how to do this?
    >
    > Thanks,
    >
    > CindyH
    >
    >
    >
     
    Guest, Jun 2, 2006
    #2
    1. Advertisements

  3. Cindy H

    Cindy H Guest

    Re: Access database insert statement with an autonumber (identity) in

    I think I'm kind of doing that by putting a semicolon after the insert
    statement.
    That gives me error - Characters found after end of SQL statement.

    INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (?,
    ?, ?); SELECT ID, Tournament, MemberName, Score FROM Tournament WHERE
    (ID = @@IDENTITY);"


    Is this what you mean?





    "Kerry Moorman" <> wrote in message
    news:...
    > Cindy,
    >
    > Try executing the Insert, then execute Select @@Identity as a second

    command.
    >
    > Kerry Moorman
    >
    >
    > "Cindy H" wrote:
    >
    > > Hi
    > >
    > > I'm having a problem getting the insert statement correct for an Access
    > > table I'm using.
    > > The Access table uses an autonumber for the primary key.
    > > I have tried this:
    > > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES

    (vtournament,
    > > vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > > Tournament WHERE (ID = @@IDENTITY);"
    > >
    > > This works with a sql server database.
    > >
    > > I'm getting this error:
    > >
    > > System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > > statement
    > >
    > > Does anyone know how to do this?
    > >
    > > Thanks,
    > >
    > > CindyH
    > >
    > >
    > >
     
    Cindy H, Jun 2, 2006
    #3
  4. Cindy H

    Guest Guest

    Re: Access database insert statement with an autonumber (identity)

    Cindy,

    No, Access cannot process multiple sql statements in the same command.

    Here is an example:

    Dim cn As New
    OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=CourseInfo.mdb;")
    Dim cmd As New OleDb.OleDbCommand

    cmd.CommandText = "Insert Into Students (Name, Test1, Test2)
    Values (?, ?, ?)"
    cmd.Parameters.Add("Name", "Smith, Mary")
    cmd.Parameters.Add("Test1", 80)
    cmd.Parameters.Add("Test2", 90)

    cn.Open()
    cmd.Connection = cn
    cmd.ExecuteNonQuery()

    Dim ID As Integer
    cmd.CommandText = "Select @@IDENTITY"
    ID = cmd.ExecuteScalar

    cn.Close()

    MsgBox("ID = " & ID)

    Kerry Moorman


    "Cindy H" wrote:

    > I think I'm kind of doing that by putting a semicolon after the insert
    > statement.
    > That gives me error - Characters found after end of SQL statement.
    >
    > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (?,
    > ?, ?); SELECT ID, Tournament, MemberName, Score FROM Tournament WHERE
    > (ID = @@IDENTITY);"
    >
    >
    > Is this what you mean?
    >
    >
    >
    >
    >
    > "Kerry Moorman" <> wrote in message
    > news:...
    > > Cindy,
    > >
    > > Try executing the Insert, then execute Select @@Identity as a second

    > command.
    > >
    > > Kerry Moorman
    > >
    > >
    > > "Cindy H" wrote:
    > >
    > > > Hi
    > > >
    > > > I'm having a problem getting the insert statement correct for an Access
    > > > table I'm using.
    > > > The Access table uses an autonumber for the primary key.
    > > > I have tried this:
    > > > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES

    > (vtournament,
    > > > vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > > > Tournament WHERE (ID = @@IDENTITY);"
    > > >
    > > > This works with a sql server database.
    > > >
    > > > I'm getting this error:
    > > >
    > > > System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > > > statement
    > > >
    > > > Does anyone know how to do this?
    > > >
    > > > Thanks,
    > > >
    > > > CindyH
    > > >
    > > >
    > > >

    >
    >
    >
     
    Guest, Jun 2, 2006
    #4
  5. Cindy H

    Guest

    Cindy H wrote:
    > Hi
    >
    > I'm having a problem getting the insert statement correct for an Access
    > table I'm using.
    > The Access table uses an autonumber for the primary key.
    > I have tried this:
    > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
    > vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > Tournament WHERE (ID = @@IDENTITY);"


    It's news to me that Access supports the @@IDENTITY special variable.

    >
    > This works with a sql server database.
    >
    > I'm getting this error:
    >
    > System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > statement
    >
    > Does anyone know how to do this?
    >
    > Thanks,
    >
    > CindyH
     
    , Jun 2, 2006
    #5
  6. Cindy H

    GhostInAK Guest

    Hello Cindy,
    Access does not support any @@variables. None. Nada. Furthermore, Access
    has no reliable method for obtaining a key that was just inserted viat an
    Autonumber field. You could of course do something like: SELECT MAX(TableID)
    From Table, but that will ONLY work in a Single User, Single Threaded environment.
    As soon as you start using multiple threads (to do your database work) or
    allowing multiple users things begin to break down rather fast.

    I'd suggest Using Sql Server or Sql Server Express or MSDE.

    -Boo

    > Hi
    >
    > I'm having a problem getting the insert statement correct for an
    > Access
    > table I'm using.
    > The Access table uses an autonumber for the primary key.
    > I have tried this:
    > INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES
    > (vtournament,
    > vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > Tournament WHERE (ID = @@IDENTITY);"
    > This works with a sql server database.
    >
    > I'm getting this error:
    >
    > System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > statement
    >
    > Does anyone know how to do this?
    >
    > Thanks,
    >
    > CindyH
    >
     
    GhostInAK, Jun 4, 2006
    #6
  7. Cindy H

    Paul Clement Guest

    On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <> wrote:

    ¤ Hi
    ¤
    ¤ I'm having a problem getting the insert statement correct for an Access
    ¤ table I'm using.
    ¤ The Access table uses an autonumber for the primary key.
    ¤ I have tried this:
    ¤ INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
    ¤ vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    ¤ Tournament WHERE (ID = @@IDENTITY);"
    ¤
    ¤ This works with a sql server database.
    ¤
    ¤ I'm getting this error:
    ¤
    ¤ System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    ¤ statement
    ¤
    ¤ Does anyone know how to do this?

    See the following:

    HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual
    Basic .NET
    http://support.microsoft.com/default.aspx?scid=kb;en-us;815629


    Paul
    ~~~~
    Microsoft MVP (Visual Basic)
     
    Paul Clement, Jun 5, 2006
    #7
  8. Cindy H

    Guest

    MDB is friggin crap; spit on anyone that uses it anywhere.

    it's not scalable enough for a single record and a single user.

    -Aaron




    Paul Clement wrote:
    > On Fri, 2 Jun 2006 12:38:02 -0500, "Cindy H" <> wrote:
    >
    > ¤ Hi
    > ¤
    > ¤ I'm having a problem getting the insert statement correct for an Access
    > ¤ table I'm using.
    > ¤ The Access table uses an autonumber for the primary key.
    > ¤ I have tried this:
    > ¤ INSERT INTO Tournaments (Tournament, MemberName, Score) VALUES (vtournament,
    > ¤ vMemberName, vScore) SELECT ID, Tournament, MemberName, Score FROM
    > ¤ Tournament WHERE (ID = @@IDENTITY);"
    > ¤
    > ¤ This works with a sql server database.
    > ¤
    > ¤ I'm getting this error:
    > ¤
    > ¤ System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL
    > ¤ statement
    > ¤
    > ¤ Does anyone know how to do this?
    >
    > See the following:
    >
    > HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual
    > Basic .NET
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;815629
    >
    >
    > Paul
    > ~~~~
    > Microsoft MVP (Visual Basic)
     
    , Jun 5, 2006
    #8
    1. Advertisements

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

    insert query, autonumber lookup

    Snuyt, Mar 5, 2004, in forum: Microsoft VB .NET
    Replies:
    2
    Views:
    150
    Snuyt
    Mar 8, 2004
  2. Christopher Lusardi
    Replies:
    6
    Views:
    311
    Guest
    Jun 1, 2006
  3. CT

    identity /autonumber drives me nuts

    CT, Jun 14, 2006, in forum: Microsoft VB .NET
    Replies:
    4
    Views:
    206
    Cor Ligthert [MVP]
    Jun 16, 2006
  4. jmar
    Replies:
    7
    Views:
    274
    Armin Zingler
    Jan 21, 2007
  5. sandeep.damodar
    Replies:
    1
    Views:
    302
    Guest
    Mar 16, 2007
Loading...

Share This Page