ASP.NET 2.0 Return Values

[Kevin]

Have the following code to extract the ID of a member once a new member has
been inserted into the SQL 2005 Express database.

Dim myID As String = ""
Dim myConnString As String =
ConfigurationManager.ConnectionStrings("Connection String").ToString
Dim mySelectQuery As String = "SELECT IDENT_CURRENT('Members')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
Dim myReader As Data.SqlClient.SqlDataReader

myConnection.Open()
myReader = myCommand.ExecuteReader()
myID = myReader.GetInt32(0).ToString
myReader.Close()
myConnection.Close()

Cannot get the required value as keep getting the following error message:-

In order to evaluate an indexed property, the property must be qualified and
the arguments must be explicitly supplied by the user

Thanks
Kevin

[Göran Andersson]

Kevin wrote:

Have the following code to extract the ID of a member once a new member has
been inserted into the SQL 2005 Express database.

Dim myID As String = ""
Dim myConnString As String =
ConfigurationManager.ConnectionStrings("Connection String").ToString
Dim mySelectQuery As String = "SELECT IDENT_CURRENT('Members')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
Dim myReader As Data.SqlClient.SqlDataReader

myConnection.Open()
myReader = myCommand.ExecuteReader()
myID = myReader.GetInt32(0).ToString
myReader.Close()
myConnection.Close()

Cannot get the required value as keep getting the following error message:-

In order to evaluate an indexed property, the property must be qualified and
the arguments must be explicitly supplied by the user

Thanks
Kevin

The ident_current function returns an id created in any session. That
means that you get the last id created regardless if it was created in
the same session or not. What you want to use is scope_identity(), that
returns the last id created in the current scope.

You have to call the Read method of the datareader before you can get
any data from it. Or better yet, use ExecuteScalar instead.

If you still get an error message, you have to tell when the error
occurs and where the error message comes from.

[Kevin]

Thanks for your help.
Used the .Read() method and works fine.
Tried the ExecuteScalar and got millions of errors.

Used SELECT IDENT_CURRENT('Members') because then will get last ID for the
table where SCOPE_IDENTITY returns last ID of any table.
Assume because opening new connection for the datareader it is not in the
same scope and could get an update from another user??
ASP was easier that this! - Phew!!

Thanks again
Kevin

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uf**************@TK2MSFTNGP02.phx.gbl...

Kevin wrote:

Have the following code to extract the ID of a member once a new member
has been inserted into the SQL 2005 Express database.

Dim myID As String = ""
Dim myConnString As String =
ConfigurationManager.ConnectionStrings("Connection String").ToString
Dim mySelectQuery As String = "SELECT IDENT_CURRENT('Members')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery,
myConnection)
Dim myReader As Data.SqlClient.SqlDataReader

myConnection.Open()
myReader = myCommand.ExecuteReader()
myID = myReader.GetInt32(0).ToString
myReader.Close()
myConnection.Close()

Cannot get the required value as keep getting the following error
message:-

In order to evaluate an indexed property, the property must be qualified
and the arguments must be explicitly supplied by the user

Thanks
Kevin

The ident_current function returns an id created in any session. That
means that you get the last id created regardless if it was created in the
same session or not. What you want to use is scope_identity(), that
returns the last id created in the current scope.

You have to call the Read method of the datareader before you can get any
data from it. Or better yet, use ExecuteScalar instead.

If you still get an error message, you have to tell when the error occurs
and where the error message comes from.

[Göran Andersson]

Kevin wrote:

Thanks for your help.
Used the .Read() method and works fine.
Tried the ExecuteScalar and got millions of errors.
Then don't sweat it. Using a DataReader is just as good a solution. Only
it's a bit less code if you use ExecuteScalar.
Used SELECT IDENT_CURRENT('Members') because then will get last ID for the
table where SCOPE_IDENTITY returns last ID of any table.
Assume because opening new connection for the datareader it is not in the
same scope and could get an update from another user??
If you use ident_current you will get the wrong id sooner or later if
there are simultanious connections to the database. It works most of the
time, but not always. (And we all know that bugs that only occur
sometimes are the hardest ones to find.)

You should use the same connection to get the id that you used to add
the record, or you will not be in the same scope. The only truly safe
way to get the correct id is to use scope_identity in the same connection.

(Using @@identity will also work as long as you don't add records in
triggers, but why use it when scope_identity does it better.)
ASP was easier that this! - Phew!!
The database part is the same, it has never changed.

ASP might seem easier, but it's really not. There are so much more that
you have to know about ASP before you really can build a robust system.
With ASP.NET it's robust to start with, you just have to keep it robust,
you don't have to build the robustness from scratch.
Thanks again
Kevin

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uf**************@TK2MSFTNGP02.phx.gbl...

Kevin wrote:

Have the following code to extract the ID of a member once a new member
has been inserted into the SQL 2005 Express database.

Dim myID As String = ""
Dim myConnString As String =
ConfigurationManager.ConnectionStrings("Connection String").ToString
Dim mySelectQuery As String = "SELECT IDENT_CURRENT('Members')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery,
myConnection)
Dim myReader As Data.SqlClient.SqlDataReader

myConnection.Open()
myReader = myCommand.ExecuteReader()
myID = myReader.GetInt32(0).ToString
myReader.Close()
myConnection.Close()

Cannot get the required value as keep getting the following error
message:-

In order to evaluate an indexed property, the property must be qualified
and the arguments must be explicitly supplied by the user

Thanks
Kevin

The ident_current function returns an id created in any session. That
means that you get the last id created regardless if it was created in the
same session or not. What you want to use is scope_identity(), that
returns the last id created in the current scope.

You have to call the Read method of the datareader before you can get any
data from it. Or better yet, use ExecuteScalar instead.

If you still get an error message, you have to tell when the error occurs
and where the error message comes from.