Using @@Identity with access

  • Thread starter Thread starter Dana King
  • Start date Start date
D

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()
 
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()
Click to expand...
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
 
Dana,
Check out this KB article:

Retrieving Identity or Autonumber Values
http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp

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

Thanks,
Carl Prothman
Click to expand...

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.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
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




Otis Mukinfus said:
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()
Click to expand...
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
Click to expand...
 
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" <[email protected]> schreef in bericht 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




Otis Mukinfus said:
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()
Click to expand...
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
Click to expand...
 
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
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



Otis Mukinfus said:
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()
Click to expand...
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
Click to expand...
 
Back
Top