Using @@Identity with access

16 Apr 2006 6:52 PM

Dana King

I primarily work with SQL (thank god) so I have an Access question. Thanks
in advance for some insight.

I am trying to insert a new record and have the identity returned. I have
been using the code below but no luck. It always returns 0
As you can see I'm trying to return it on the same open connection. I've
tried various other things but no luck either.

working with ADO.net 2.0
Dim newID As String = String.Empty

Dim cn As New
OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
Dim cmd As New OleDbCommand(sql.ToString, cn)
cmd.CommandType = Data.CommandType.Text

cn.Open()

cmd.ExecuteNonQuery()

cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
cmd.CommandType = Data.CommandType.Text
cmd.Connection = cn

newID = cmd.ExecuteScalar.ToString

cn.Close()

16 Apr 2006 7:12 PM

Carl Prothman

Dana,
Check out this KB article:

Retrieving Identity or Autonumber Values
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

Note how they use OnRowUpdated event to get the new value
for each row.

Thanks,
Carl Prothman

Show quote

"Dana King" <bushido***@hotmail.com> wrote in message
news:e1DWQdYYGHA.1192@TK2MSFTNGP04.phx.gbl...
>I primarily work with SQL (thank god) so I have an Access question. Thanks
>in advance for some insight.
>
> I am trying to insert a new record and have the identity returned. I have
> been using the code below but no luck. It always returns 0
> As you can see I'm trying to return it on the same open connection. I've
> tried various other things but no luck either.
>
> working with ADO.net 2.0
> Dim newID As String = String.Empty
>
> Dim cn As New
> OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
> Dim cmd As New OleDbCommand(sql.ToString, cn)
> cmd.CommandType = Data.CommandType.Text
>
> cn.Open()
>
> cmd.ExecuteNonQuery()
>
> cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
> cmd.CommandType = Data.CommandType.Text
> cmd.Connection = cn
>
> newID = cmd.ExecuteScalar.ToString
>
> cn.Close()
>
>

16 Apr 2006 8:29 PM

Otis Mukinfus

Show quote

On Sun, 16 Apr 2006 12:12:05 -0700, "Carl Prothman" <c***@prothman.online.org>
wrote:

>Dana,
>Check out this KB article:
>
>Retrieving Identity or Autonumber Values
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
>
>Note how they use OnRowUpdated event to get the new value
>for each row.
>
>Thanks,
>Carl Prothman
>
>
>"Dana King" <bushido***@hotmail.com> wrote in message
>news:e1DWQdYYGHA.1192@TK2MSFTNGP04.phx.gbl...
>>I primarily work with SQL (thank god) so I have an Access question. Thanks
>>in advance for some insight.
>>
>> I am trying to insert a new record and have the identity returned. I have
>> been using the code below but no luck. It always returns 0
>> As you can see I'm trying to return it on the same open connection. I've
>> tried various other things but no luck either.
>>
>> working with ADO.net 2.0
>> Dim newID As String = String.Empty
>>
>> Dim cn As New
>> OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)

I just examined your code a little closer. Where is sql (in the command
creation below) defined? I don't see the declaration. If you are passing an
empty command string that might be why you get back a zero.

Show quote

>> Dim cmd As New OleDbCommand(sql.ToString, cn)
>> cmd.CommandType = Data.CommandType.Text
>>
>> cn.Open()
>>
>> cmd.ExecuteNonQuery()
>>
>> cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
>> cmd.CommandType = Data.CommandType.Text
>> cmd.Connection = cn
>>
>> newID = cmd.ExecuteScalar.ToString
>>
>> cn.Close()
>>
>>
>

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

16 Apr 2006 7:28 PM

Otis Mukinfus

Show quote

On Sun, 16 Apr 2006 15:52:24 -0300, "Dana King" <bushido***@hotmail.com> wrote:

>I primarily work with SQL (thank god) so I have an Access question. Thanks
>in advance for some insight.
>
>I am trying to insert a new record and have the identity returned. I have
>been using the code below but no luck. It always returns 0
>As you can see I'm trying to return it on the same open connection. I've
>tried various other things but no luck either.
>
>working with ADO.net 2.0
>Dim newID As String = String.Empty
>
>Dim cn As New
>OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
>Dim cmd As New OleDbCommand(sql.ToString, cn)
>cmd.CommandType = Data.CommandType.Text
>
>cn.Open()
>
>cmd.ExecuteNonQuery()
>
>cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
>cmd.CommandType = Data.CommandType.Text
>cmd.Connection = cn
>
>newID = cmd.ExecuteScalar.ToString
>
>cn.Close()
>

This works for me...

        static int Main(string[] args)
        {
            OleDbConnection cn = new OleDbConnection(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Access.Databases" +
                @"\test.mdb");
            OleDbCommand cmdIns =
                new OleDbCommand("INSERT INTO Test (Message) VALUES('TEST')", cn);

            OleDbCommand cmdID = new OleDbCommand("Select @@IDENTITY", cn);

            int ID = 0;

            try
            {
                cn.Open();
                cmdIns.ExecuteNonQuery();
                ID = (int)cmdID.ExecuteScalar();
            }
            catch
            {
                return 1; // Error
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                }
            }
            return 0; // success
        }

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

16 Apr 2006 10:36 PM

Dana King

Thanks for the replies, daaah on my part, the identity field for this table is a Replication ID, (GUID) that's why it was returning 0. Using a number field, it would work fine.

127             Dim newID As String = String.Empty

128             Dim cn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)

129

130             '== insert sql is defined above in a stringbuilder

131             Dim cmdInsert As New OleDbCommand(sql.ToString, cn)

132             cmdInsert.CommandType = Data.CommandType.Text

133

134             Dim cmdId As New OleDbCommand("SELECT @@IDENTITY", cn)

135             cmdId.CommandType = Data.CommandType.Text

136

137             Try

138                 cn.Open()

139                 cmdInsert.ExecuteNonQuery()

140                 newID = cmdId.ExecuteScalar.ToString

141                 cn.Close()

142             Catch ex As Exception

143                 If cn.State = Data.ConnectionState.Open Then cn.Close()

144             End Try

145

146             Me.LabelArticleID.Text = newID

Show quote

"Otis Mukinfus" <ph***@emailaddress.com> wrote in message news:uj65421j2fbqsk3inbltjqgd6hds83d29r@4ax.com...
> On Sun, 16 Apr 2006 15:52:24 -0300, "Dana King" <bushido***@hotmail.com> wrote:
>
>>I primarily work with SQL (thank god) so I have an Access question. Thanks
>>in advance for some insight.
>>
>>I am trying to insert a new record and have the identity returned. I have
>>been using the code below but no luck. It always returns 0
>>As you can see I'm trying to return it on the same open connection. I've
>>tried various other things but no luck either.
>>
>>working with ADO.net 2.0
>>Dim newID As String = String.Empty
>>
>>Dim cn As New
>>OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
>>Dim cmd As New OleDbCommand(sql.ToString, cn)
>>cmd.CommandType = Data.CommandType.Text
>>
>>cn.Open()
>>
>>cmd.ExecuteNonQuery()
>>
>>cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
>>cmd.CommandType = Data.CommandType.Text
>>cmd.Connection = cn
>>
>>newID = cmd.ExecuteScalar.ToString
>>
>>cn.Close()
>>
> This works for me...
>
> static int Main(string[] args)
> {
> OleDbConnection cn = new OleDbConnection(
> @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Access.Databases" +
> @"\test.mdb");
> OleDbCommand cmdIns =
> new OleDbCommand("INSERT INTO Test (Message) VALUES('TEST')", cn);
>
> OleDbCommand cmdID = new OleDbCommand("Select @@IDENTITY", cn);
>
> int ID = 0;
>
> try
> {
> cn.Open();
> cmdIns.ExecuteNonQuery();
> ID = (int)cmdID.ExecuteScalar();
> }
> catch
> {
> return 1; // Error
> }
> finally
> {
> if (cn.State != ConnectionState.Closed)
> {
> cn.Close();
> }
> }
> return 0; // success
> }
>
> Good luck with your project,
>
> Otis Mukinfus
> http://www.arltex.com
> http://www.tomchilders.com

17 Apr 2006 5:31 AM

Cor Ligthert [MVP]

Dana,

With a Guid you don't need the identity. You just set it in advance at a new record.

http://www.vb-tips.com/default.aspx?ID=6b05b025-2ace-4ad0-9eae-a95385888e22

I hope this helps,

Cor

"Dana King" <bushido***@hotmail.com> schreef in bericht news:OiqjtYaYGHA.3496@TK2MSFTNGP05.phx.gbl...

Thanks for the replies, daaah on my part, the identity field for this table is a Replication ID, (GUID) that's why it was returning 0. Using a number field, it would work fine.

    127             Dim newID As String = String.Empty

    128             Dim cn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)

    129

    130             '== insert sql is defined above in a stringbuilder

    131             Dim cmdInsert As New OleDbCommand(sql.ToString, cn)

    132             cmdInsert.CommandType = Data.CommandType.Text

    133

    134             Dim cmdId As New OleDbCommand("SELECT @@IDENTITY", cn)

    135             cmdId.CommandType = Data.CommandType.Text

    136

    137             Try

    138                 cn.Open()

    139                 cmdInsert.ExecuteNonQuery()

    140                 newID = cmdId.ExecuteScalar.ToString

    141                 cn.Close()

    142             Catch ex As Exception

    143                 If cn.State = Data.ConnectionState.Open Then cn.Close()

    144             End Try

    145

    146             Me.LabelArticleID.Text = newID

Show quote

"Otis Mukinfus" <ph***@emailaddress.com> wrote in message news:uj65421j2fbqsk3inbltjqgd6hds83d29r@4ax.com...
> On Sun, 16 Apr 2006 15:52:24 -0300, "Dana King" <bushido***@hotmail.com> wrote:
>
>>I primarily work with SQL (thank god) so I have an Access question. Thanks
>>in advance for some insight.
>>
>>I am trying to insert a new record and have the identity returned. I have
>>been using the code below but no luck. It always returns 0
>>As you can see I'm trying to return it on the same open connection. I've
>>tried various other things but no luck either.
>>
>>working with ADO.net 2.0
>>Dim newID As String = String.Empty
>>
>>Dim cn As New
>>OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
>>Dim cmd As New OleDbCommand(sql.ToString, cn)
>>cmd.CommandType = Data.CommandType.Text
>>
>>cn.Open()
>>
>>cmd.ExecuteNonQuery()
>>
>>cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
>>cmd.CommandType = Data.CommandType.Text
>>cmd.Connection = cn
>>
>>newID = cmd.ExecuteScalar.ToString
>>
>>cn.Close()
>>
> This works for me...
>
> static int Main(string[] args)
> {
> OleDbConnection cn = new OleDbConnection(
> @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Access.Databases" +
> @"\test.mdb");
> OleDbCommand cmdIns =
> new OleDbCommand("INSERT INTO Test (Message) VALUES('TEST')", cn);
>
> OleDbCommand cmdID = new OleDbCommand("Select @@IDENTITY", cn);
>
> int ID = 0;
>
> try
> {
> cn.Open();
> cmdIns.ExecuteNonQuery();
> ID = (int)cmdID.ExecuteScalar();
> }
> catch
> {
> return 1; // Error
> }
> finally
> {
> if (cn.State != ConnectionState.Closed)
> {
> cn.Close();
> }
> }
> return 0; // success
> }
>
> Good luck with your project,
>
> Otis Mukinfus
> http://www.arltex.com
> http://www.tomchilders.com

17 Apr 2006 12:37 PM

Ron Allen

Dana,
You need to retrieve the identity on the same open connection as you did
the insert. Just reconnecting using the same connection string will lose
the state. Leave the connection open after the insert and retrieve the new
value then using that open connection.

Ron Allen

"Dana King" <bushido***@hotmail.com> wrote in message
news:OiqjtYaYGHA.3496@TK2MSFTNGP05.phx.gbl...

Thanks for the replies, daaah on my part, the identity field for this table
is a Replication ID, (GUID) that's why it was returning 0. Using a number
field, it would work fine.

127             Dim newID As String = String.Empty
128             Dim cn As New
OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
129
130             '== insert sql is defined above in a stringbuilder
131             Dim cmdInsert As New OleDbCommand(sql.ToString, cn)
132             cmdInsert.CommandType = Data.CommandType.Text
133
134             Dim cmdId As New OleDbCommand("SELECT @@IDENTITY", cn)
135             cmdId.CommandType = Data.CommandType.Text
136
137             Try
138                 cn.Open()
139                 cmdInsert.ExecuteNonQuery()
140                 newID = cmdId.ExecuteScalar.ToString
141                 cn.Close()
142             Catch ex As Exception
143                 If cn.State = Data.ConnectionState.Open Then
cn.Close()
144             End Try
145
146             Me.LabelArticleID.Text = newID

Show quote

"Otis Mukinfus" <ph***@emailaddress.com> wrote in message
news:uj65421j2fbqsk3inbltjqgd6hds83d29r@4ax.com...
> On Sun, 16 Apr 2006 15:52:24 -0300, "Dana King" <bushido***@hotmail.com>
> wrote:
>
>>I primarily work with SQL (thank god) so I have an Access question. Thanks
>>in advance for some insight.
>>
>>I am trying to insert a new record and have the identity returned. I have
>>been using the code below but no luck. It always returns 0
>>As you can see I'm trying to return it on the same open connection. I've
>>tried various other things but no luck either.
>>
>>working with ADO.net 2.0
>>Dim newID As String = String.Empty
>>
>>Dim cn As New
>>OleDbConnection(ConfigurationManager.ConnectionStrings("ArticlesConnection").ToString)
>>Dim cmd As New OleDbCommand(sql.ToString, cn)
>>cmd.CommandType = Data.CommandType.Text
>>
>>cn.Open()
>>
>>cmd.ExecuteNonQuery()
>>
>>cmd.CommandText = "SELECT @@IDENTITY" '== DOES NOT WORK
>>cmd.CommandType = Data.CommandType.Text
>>cmd.Connection = cn
>>
>>newID = cmd.ExecuteScalar.ToString
>>
>>cn.Close()
>>
> This works for me...
>
> static int Main(string[] args)
> {
> OleDbConnection cn = new OleDbConnection(
> @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Access.Databases" +
> @"\test.mdb");
> OleDbCommand cmdIns =
> new OleDbCommand("INSERT INTO Test (Message) VALUES('TEST')", cn);
>
> OleDbCommand cmdID = new OleDbCommand("Select @@IDENTITY", cn);
>
> int ID = 0;
>
> try
> {
> cn.Open();
> cmdIns.ExecuteNonQuery();
> ID = (int)cmdID.ExecuteScalar();
> }
> catch
> {
> return 1; // Error
> }
> finally
> {
> if (cn.State != ConnectionState.Closed)
> {
> cn.Close();
> }
> }
> return 0; // success
> }
>
> Good luck with your project,
>
> Otis Mukinfus
> http://www.arltex.com
> http://www.tomchilders.com