Click here to Skip to main content
15,030,954 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have relational tables and here is i want to do.

1. insert data into table
2. Get the last inserted row id
3. Insert the last inserted row id into the relational tables

Error :
'LAST_INSERT_ID' is not recognized built-in function name

Here is my code
 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    Using cmd As New SqlClient.SqlCommand("INSERT INTO tblcustomer(FirstName,LastName,Image)VALUES('" & txtName.Text & "' ,'" & txtSurname.Text & "', @customerPic )", cn)

    End Using

    Using cmd As New SqlClient.SqlCommand("INSERT INTO tblProduct(ProductName,Quantity,CustID)VALUES('" & txtProductName.Text & "' ,'" & txtQuantity.Text & "',LAST_INSERT_ID())", cn)

    cmd.Parameters.Add(New SqlClient.SqlParameter("@customerPic", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)
    i = cmd.ExecuteNonQuery
    If (i > 0) Then
        MsgBox("Save " & i & "Record Successfully")
    End If

    End Using

End Sub
Updated 7-Dec-15 16:27pm
PIEBALDconsult 7-Dec-15 22:30pm
And please use parameters for all the values, not just the image.
aarif moh shaikh 7-Dec-15 23:49pm
why you are not using trigger for this??

thats because LAST_INSERT_ID() isnt a SQL Server function - its a MySQL function and MySQL != MSSQL

- just as the error message says - sad but true - here's a link where some alternatives are discussed[^]
You will need SCOPE_IDENTITY (Transact-SQL)[^].
Check up this article for some idea too Ask Your Database for that Unique ID[^]
 Using cmd As New SqlClient.SqlCommand("INSERT INTO tblcustomer(FirstName,LastName,Image)VALUES(@fName,@lName, @customerPic )", cn)
End Using
Using cmd As New SqlClient.SqlCommand("INSERT INTO tblProduct(ProductName,Quantity,CustID)VALUES(@ProdName,@Quantity, @custID )", cn)
End Using
cmd.Parameters.Add("@fname", SqlDbType.VarChar, 100).Value = txtName.Text
cmd.Parameters.Add("@lName", SqlDbType.VarChar, 100).Value = txtSurname.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@customerPic", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)
cmd.Parameters.Add("@Prodname", SqlDbType.VarChar, 100).Value = txtProductName.Text
cmd.Parameters.Add("@Quantity", SqlDbType.VarChar, 100).Value = txtQuantity.Text
cmd.Parameters.Add("@custID", SqlDbType.VarChar, 100).Value = ("Select @@IDENTITY")
i = cmd.ExecuteScalar
If (i > 0) Then
    MsgBox("Save " & i & "Record Successfully")
End If

This is what i did, it says successfully save record but no save data in my sqlserver. can someone help me to fix this? thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900