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
VB
 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    cn.Open()

    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
    cn.Close()

End Sub
Posted
Updated 7-Dec-15 16:27pm
v2
Comments
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

https://www.sitepoint.com/community/t/convert-mysql-last-insert-id-to-mssql-2005/36998/4[^]
   
You will need SCOPE_IDENTITY (Transact-SQL)[^].
Check up this article for some idea too Ask Your Database for that Unique ID[^]
   
VB
 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