SCOPE_IDENTITY() returning variable type(6) not actual value

[Jonn1s]

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

[brucevde]

The statement that creates the NewId parameter is setting the Parameter's Value equal to ParameterDirection.ReturnValue, which is 6. You are using the wrong overload of the constructor for setting the ParameterDirection.

However in this case you cannot use a Return Value, use an Output parameter instead

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) Set @NewId = SCOPE_IDENTITY() "

        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", SqlDbType.Int)).Direction = ParameterDirection.Output

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

[Jonn1s]

cool,

thanks for explaining. i managed to figure it out using 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, Year, Period, Date, EQval, SCval) " & _
           "VALUES(@par1, @par2, @par3, @par4, @par5, @par6, @par7) SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]"


        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 = 2                                'EQval
        Dim val7 = 34                                '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("SCOPE_IDENTITY", ParameterDirection.ReturnValue))

        myCon.Open()
        'myCom.ExecuteNonQuery()
        Dim i As Integer = myCom.ExecuteScalar()
        Me.TextBox1.Text = i
        MsgBox("added")
        myCon.Close()

[brucevde]

Delete the statement that creates the Scope_Identity parameter. It serves no purpose and just adds unneccessary overhead.