ASP.NET 2.0 Return Values

Discussion in 'ASP .Net' started by Kevin, Apr 29, 2006.

  1. Kevin

    Kevin Guest

    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("ConnectionString").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
     
    Kevin, Apr 29, 2006
    #1
    1. Advertising

  2. 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("ConnectionString").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.
     
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Apr 29, 2006
    #2
    1. Advertising

  3. Kevin

    Kevin Guest

    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" <> wrote in message
    news:...
    > 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("ConnectionString").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, Apr 29, 2006
    #3
  4. 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" <> wrote in message
    > news:...
    >> 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("ConnectionString").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.

    >
    >
     
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Apr 30, 2006
    #4
    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. Brian Henry
    Replies:
    1
    Views:
    404
    bruce barker
    Dec 12, 2003
  2. =?Utf-8?B?cGhpbA==?=

    JavaScript return values accessible to ASP.NET

    =?Utf-8?B?cGhpbA==?=, Apr 8, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    9,752
    =?Utf-8?B?cGhpbA==?=
    Apr 8, 2005
  3. Greenhorn
    Replies:
    15
    Views:
    849
    Keith Thompson
    Mar 6, 2005
  4. Chris Rebert
    Replies:
    1
    Views:
    696
    Bobby
    May 28, 2009
  5. DonaldN
    Replies:
    1
    Views:
    566
    Josh Twist
    Feb 17, 2006
Loading...

Share This Page