[mpe-IT]

   Dim addSupplier As String
        addSupplier = "insert into Suppliers (CompanyName, ContactPerson, Street, City, State, ContactNumber) values (" & _
                 WrapQuote(tbCompanyName.Text) & COMMA & _
                 WrapQuote(tbContactPerson.Text) & COMMA & _
                 WrapQuote(tbStreet.Text) & COMMA & _
                 WrapQuote(tbCity.Text) & COMMA & _
                 WrapQuote(tbState.Text) & COMMA & _
                 WrapQuote(tbContactNumber.Text) & ")"

        cmdUser = New SqlCommand(addSupplier, conn)

        Dim ID As Integer
        conn.Open()
        cmdUser.ExecuteNonQuery()
        cmdUser.CommandText = "Select Scope_Identity() Statement"
        ID = cmdUser.ExecuteScalar



        Dim addLab As String
        addLab = "insert into Laboratory (LaboratoryID,LaboratoryName,Location) values (" & _
        WrapQuote(ID) & COMMA & _
        WrapQuote(cmbLab.SelectedItem) & COMMA & _
        WrapQuote(tbLocation.Text) & ")"
        cmdUser = New SqlCommand(addLab, conn)
        cmdUser.ExecuteNonQuery()

        cmdUser.CommandText = "Select Scope_Identity() Statement"
        ID = cmdUser.ExecuteScalar
        Label1.Text = ID
        MsgBox("saved")
        conn.close()

this code is located on my save button. i get this "Conversion from type 'DBNull' to type 'Integer' is not valid" error on "ID = cmdUser.ExecuteScalar at the 2nd block of codes('addlab') it has a value of 1 which is correct as i wanted to get the ID from supplier and then pass it to the laboratory. what may be the cause of the error? help

[CharlieMay]

Does your table contain an Identity column? Did you specifically set (Is Identity) to Yes in the column properties for the ID field?

[Ionut]

MSDN says:
Scope_Identity

Quote

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

In your case scope_identity returns null because it is the only statement in its batch(its scope).

But, Microsoft offers an alternative for your situation: IDENT_CURRENT which takes a table name as a parameter and returns the last identity value inserted.
Here may appear some problems: if user A makes an insert in the table and user B clicks almost in the same time with user A to make the same insert, until you execute IDENT_CURRENT, user's B statement will execute and you will end up with the second ID. This is why I recommand to make the insert through a stored procedure(where you can execute scope_identity enclosed in a transaction) and return the value thorugh an output parameter. This way you get safer from concurrent inserts into the same table.

This post has been edited by Ionut: 03 February 2012 - 07:01 AM