none
SCOPE_IDENTITY Error RRS feed

  • Question

  • Hi everyone,

    Please see the below code,

    I'm adding a line in a table and trying to return the primary key witch is autoincremented.

    At the moment the SCOPE_IDENTITY is returning "6" everytime! and i knwo for a fact it should be returning a number high than that. what am i doing wrong?

    It is also doing the same if i use @@IDENTITY instead of the scope_identity!!

    why is it only returning 6?

    Code Snippet

     Dim SQLtext, a As String        myCon.Close()


            a = Me.PurchaseDetailsTableAdapter.Connection.ConnectionString.Clone()
            Dim myCon As New SqlClient.SqlConnection(a)

            SQLtext = "INSERT INTO Purchases (CNum, CustomerID, Year, Period, Date, EQval, SCval) " & _
               "VALUES(@par1, @par2, @par3, @par4, @par5, @par6, @par7) SELECT SCOPE_IDENTITY() AS NewID"


            Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)
           
            Dim val1 = "C1239"                          'ConNum

            Dim val2 = 1                                'CustomerID
            Dim val3 = Now.Year                         'Year
            Dim val4 = ((Now.Month + 8) Mod 12) + 1     'Period
            Dim val5 = Now.Date                         'Date
            Dim val6 = 0                                'EQval
            Dim val7 = 0                                'SCval

            myCom.Parameters.Add(New SqlClient.SqlParameter("@par1", SqlDbType.VarChar)).Value = val1
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par2", SqlDbType.Int)).Value = val2
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par3", SqlDbType.VarChar)).Value = val3
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par4", SqlDbType.Int)).Value = val4
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par5", SqlDbType.DateTime)).Value = val5
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par6", SqlDbType.Money)).Value = val6
            myCom.Parameters.Add(New SqlClient.SqlParameter("@par7", SqlDbType.Money)).Value = val7
            myCom.Parameters.Add(New SqlClient.SqlParameter("NewID", ParameterDirection.ReturnValue))

            myCon.Open()
            myCom.ExecuteNonQuery()
            Dim i = myCom.Parameters("NewID").Value
            MsgBox(i)
            Me.TextBox1.Text = i
            MsgBox("added")

    Any Ideas????

    Thanks Very Much!

    Jon

    Sunday, July 29, 2007 12:37 PM

All replies

  • Hi, i think i figure out why it is return "6" because that is the number the variable type FLOAT? so why is it returning the variable type of the SCOPE_IDENTITY() and not the actual value?

    Please help its driving me mad!!!

    Jon
    Sunday, July 29, 2007 1:36 PM
  • hi,

    i'm having troube with the SCOPE_IDENTITY of a Client-side SQL INSERT statement, it is returning '6' everytime which i think has something to do with the variable type 6 for Floating, not the actual value?

    I'm really confused and can't seem to get it to return the actual value!

    Please see the code below.

     Dim SQLtext, a As String       

    a = Me.PurchaseDetailsTableAdapter.Connection.ConnectionString.Clone()
    Dim myCon As New SqlClient.SqlConnection(a)

    SQLtext = "INSERT INTO Purchases (CNum, CustomerID) " & _
    "VALUES(@par1, @par2) SELECT SCOPE_IDENTITY() AS NewID"

    Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)

    Dim val1 = "C1239" 'ConNum
    Dim val2 = 1 'CustomerID

    myCom.Parameters.Add(New SqlClient.SqlParameter("@par1", SqlDbType.VarChar)).Value = val1
    myCom.Parameters.Add(New SqlClient.SqlParameter("@par2", SqlDbType.Int)).Value = val2
    myCom.Parameters.Add(New SqlClient.SqlParameter("NewID", ParameterDirection.ReturnValue))

    myCon.Open()
    myCom.ExecuteNonQuery()
    Dim i = myCom.Parameters("NewID").Value
    Me.TextBox1.Text = i
    MsgBox("added")
    myCon.Close()

    it is returning i = 6 (everytime) which is the value of the variable type Floating. How can i get it to return the actual value?

    Thanks

    Jon
    Sunday, July 29, 2007 4:33 PM
  • Somewhere befor the Mycon.Close(), create and execute another SQL statement:

    "SELECT SCOPE_IDENTITY()"

    the recordset will contain the ID of the last inserted record.

     

    The way you are using it, I'm surprised you're not getting a SQL syntax error ...

    Monday, July 30, 2007 5:07 PM
  • You have the syntax wrong when declaring your parameters.  Look at the different overloaded options for creating parameters (in the help file)...  The value 6 comes from the fact that you are using "ParameterDirection.ReturnValue" in the spot where you are either supposed to specify the datatype, or the value, of the parameter.  Because "ParameterDirection.ReturnValue" doesn't jive with a valid datatype (SqlDbType), it is choosing to treat that as the value of that parameter.  If you execute the following code:

     

    Code Snippet
    Int32 intValue = ((Int32)ParameterDirection.ReturnValue);

     

     

    ...you will find the value of "intValue" is "6".  So your code is technically setting the value of your parameter, not the direction (for just that one, I think the others are okay).

     

    If database you are working on is SQL Server, then the other thing I have to say is that I don't think you are using the "ParameterDirection.ReturnValue" correctly.  In your code, I don't think "SCOPE_IDENTITY()" will come back as a return value.  It will instead come back as a recordset (as mentioned by OmegaDan).  You might want to refer to the SQL help files for better information regarding return values of SQL statements.  With the SQL work I've done, return values don't usually come into play unless you are using stored procedures, but then again I've never executed an adhoc SQL statement and checked if it returned a return value.

     

    Anyway, follow OmegaDan's advice and add the extra SELECT.  If you still have problems, come back and post your changes and the results.  Thanks.

     

     

    Tuesday, July 31, 2007 3:22 AM
  • Hi,

     

    Write your insert statement as follows:

    ==============================================

    INSERT INTO tblRegOrder (txtProductID, intCustomerID, intQty)
    VALUES     (@txtProductID, @intCustomerID, @intQty);

    SELECT  SCOPE_IDENTITY()

    ==============================================

    then use executescalar instead of executenonquery.

     

    executescalar return object that can be converted by int.parse(xxx.executescalar().tostring())

     

    Hope this help you,

    Amir

     

    • Proposed as answer by Cyborgx372 Tuesday, October 25, 2011 3:12 PM
    Tuesday, July 31, 2007 7:56 AM
  •  

    thanks to everyone, the executescalar value worked!

     

    Thanks!

    Tuesday, July 31, 2007 11:17 AM