i have problem to get new inserted columnID using sql

Question

hi I have an identity column defined as int in sql . I use SCPOE_IDENTITY () to get the new inserted column id.

this is sample of my code:

     Dim sql As String = "insert into infoHotel (nameHotel, knownAs1, knownAs2, knownAs3, knownAs4, streetAddress) values (N" & _
                                       FormatSqlParam(hotel) & ",N" & _
                                       FormatSqlParam(KnownAs(0)) & ",N" & _
                                       FormatSqlParam(KnownAs(1)) & ",N" & _
                                       FormatSqlParam(KnownAs(2)) & ",N" & _
                                       FormatSqlParam(KnownAs(3)) & ",N" & _
                                       FormatSqlParam(StreetAddress) & _
                                       "SELECT CAST(scope_identity() AS int)")"

    Dim objCommand1 As New SqlCommand(sql, conn)

    Dim infoID As Integer = objCommand1.ExecuteScalar()

my problem here is, i cant get the infoID's value..is my code wrong?..some help plzz.. im using vb.net n sql

Answer 1

I think you need to commit your changes to the database before you can query it...

Answer 2

I think you are missing a bracket and the end of your SQL statement.

It should read

FormatSqlParam(StreetAddress) & ")" & _