Return @@identity using stored procedure...

[lidds]

I want to return the database row number that my previous insert statement was insert into using a stored procedure, however I am unable to get the valve back into my vb.net code

My storedprocedure looks like this:

CREATE PROCEDURE [dbo].[spInsertImgObjects] @commID as varchar(10),@shape as char(10),@X1 as int,@Y1 as int,@X2 as int,@Y2 as int,@X3 as int,
@Y3 as int,@objZone as char(1),@lineColour as int,@lineWidth as int,@lineStyle as varchar(50),@fillColour as int,@text as varchar(500),@fontName as varchar(50),
@fontStyle as int,@fontSize as int,@allignment as nvarchar(10),@justification as nvarchar(10),@foreColour as int,@ptIndex as int,@Ltxtwid as int,@Ltxtht as int,
@userID as varchar(200),@projName as varChar(100)
AS
INSERT INTO imageObjectsTbl VALUES (@commID,@shape,@X1,@Y1,@X2,@Y2,@X3,@Y3,@objZone,@lineColour,@lineWidth,@lineStyle,@fillColour,@text,@fontName,
@fontStyle,@fontSize,@allignment,@justification,@foreColour,@ptIndex,@Ltxtwid,@Ltxtht,@userID,@projName)

SELECT @@IDENTITY
GO

and my vb.net code for the stored procedure looks like this:

Dim myInsertObjects As New OleDb.OleDbCommand("spInsertImgObjects")
                    myInsertObjects.CommandType = CommandType.StoredProcedure
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@commID", OleDb.OleDbType.VarChar)).Value = newCommID
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@shape", OleDb.OleDbType.Char)).Value = ob.Shape
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@X1", OleDb.OleDbType.Integer)).Value = ob.StartX
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@Y1", OleDb.OleDbType.Integer)).Value = ob.StartY
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@X2", OleDb.OleDbType.Integer)).Value = ob.EndX
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@Y2", OleDb.OleDbType.Integer)).Value = ob.EndY
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@X3", OleDb.OleDbType.Integer)).Value = ob.Ph.X
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@Y3", OleDb.OleDbType.Integer)).Value = ob.Ph.Y
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@objZone", OleDb.OleDbType.Char)).Value = objZone
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@lineColour", OleDb.OleDbType.Integer)).Value = ob.LineColor.ToArgb
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@lineWidth", OleDb.OleDbType.Integer)).Value = ob.LineWeight
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@lineStyle", OleDb.OleDbType.VarChar)).Value = ob.LineStyle
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@fillColour", OleDb.OleDbType.Integer)).Value = ob.FillColor.ToArgb
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@text", OleDb.OleDbType.VarChar)).Value = text
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@fontName", OleDb.OleDbType.VarChar)).Value = ob.FontName
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@fontStyle", OleDb.OleDbType.Integer)).Value = ob.FontStyle
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@fontSize", OleDb.OleDbType.Integer)).Value = ob.FontSize
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@allignment", OleDb.OleDbType.VarChar)).Value = ob.Allignment
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@justification", OleDb.OleDbType.VarChar)).Value = ob.Justification
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@foreColour", OleDb.OleDbType.Integer)).Value = ob.ForeColour.ToArgb
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@ptIndex", OleDb.OleDbType.Integer)).Value = ob.PtIndx
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@Ltxtwid", OleDb.OleDbType.Integer)).Value = ob.WrctWid
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@Ltxttht", OleDb.OleDbType.Integer)).Value = ob.WrctHT
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@userID", OleDb.OleDbType.Integer)).Value = MISData.Instance.UserID
                    myInsertObjects.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = MISData.Instance.ProjectName


                        Dim myReader1 As OleDb.OleDbDataReader = Nothing
                        myReader1 = myDB.RunMyDataQuery(myInsertObjects)

                        Dim objID As String = Nothing
                        Do While myReader1.Read
                            objID = (myReader1.Item("@@IDENTITY"))
                        Loop

                        msgbox(objID.ToString)

Could anyone please help me in letting me know where I am going wrong.

Thanks in advance

Simon

 

[cjard]

mmmhhh.. I'm not as hot on sql server as I am on oracle SPs but in oracle, procedures dont return values (functions do) and we explicitly declare the type that will be returned either by making some parameters for it, or a return value (if we are writing a function)

Then in the vb side, we make parameters that we set to have a direction of output..


THough, its all very much harder work than just telling the designer to create you a stored procedure.. Have you tried that? Just reconfigure the InsertCommand of the relevant tableadapter to be a Sproc and follow the prompts