[RESOLVED] Display @id=scope_identity()"

[stephena]

            Dim SQLData As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FixedLineProvisioningConnectionString").ToString())
            Dim cmdSelect As New System.Data.SqlClient.SqlCommand("INSERT INTO tblTotalComms_custinfo SET nt_username=@nt_username; SELECT @ID=SCOPE_IDENTITY()", SQLData)
            cmdSelect.Parameters.AddWithValue("@nt_username", Page.User.Identity.Name)
            cmdSelect.Parameters.Add("@ID", System.Data.SqlDbType.Int, 4, "ID").Direction = Data.ParameterDirection.Output
            SQLData.Open()

            SQLData.Close()
            w.HeaderText = "Order Number " & Session("OrderNo")

I want to return the value @ID from the insert statement. Does anybody know how i would achieve this?

[motil]

;select scope_identity()

example:

     Dim query As String = "INSERT INTO  tbl_items (item) Values (?);Select scope_identity()"

          Dim ID As Integer
        Dim connect As String = "sql connection string"
        Using conn As New SqlConnection(connect)
            Using cmd As New SqlCommand(query, conn)
                cmd.Parameters.AddWithValue("", YOURVALUE)
                conn.Open()
                ID = cmd.ExecuteScalar()
            End Using
        End Using

i didn't test it but it should work, if not post back

[stephena]

"Incorrect syntax near the keyword 'SET'."

Pointing at line... ID = cmd.ExecuteScalar()

[motil]

can you post your code ?

[stephena]

            Dim query As String = "INSERT INTO tblTotalComms_custinfo SET nt_username=@nt_username; SELECT SCOPE_IDENTITY()"
            Dim ID As Integer
            Dim connect As String = ConfigurationManager.ConnectionStrings("FixedLineProvisioningConnectionString").ToString()
            Using conn As New System.Data.SqlClient.SqlConnection(connect)
                Using cmd As New System.Data.SqlClient.SqlCommand(query, conn)
                    cmd.Parameters.AddWithValue("", ID)
                    conn.Open()
                    ID = cmd.ExecuteScalar()
                End Using
            End Using

[motil]

 cmd.Parameters.AddWithValue("@nt_username", UserNameVar)

replace the UserNameVar with the DIM that contains the user name.

[motil]

one sec..
you have the whole statement wrong

your new Statement:

    Dim query As String = "INSERT INTO tblTotalComms_custinfo (nt_username) VALUES(@nt_username); SELECT SCOPE_IDENTITY()"

[stephena]

Brilliant thank you!