Scope_identity Error

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:

---

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")
        myCon.Close()

---

Any Ideas????

i think i've figured 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!!!

Thanks Very Much!

Jon

You are not identifying NewID as a parameter in your query. If you want to get the value via a parameter you are going to have to change the end of your batch so that it says this:

VB Code:

"SELECT @newID = SCOPE_IDENTITY()"

The other way that you could do it... and my recommendation would be to use the ExecuteScalar method instead to get that identification number

VB Code:

Dim i = myCom.ExecuteScalar()
MsgBox(i)
Me.TextBox1.Text = i

brilliant thanks