Retrieve Unique ID for a new Record
I cannot figure out what is going wrong here.
I get the error.
PK_RequestID is not a parameter for procedure sp_InsertRequestID.
Here is my SP
Code:
ALTER PROCEDURE sp_InsertRequestID
(
@User varchar(50),
@Request varchar(50),
@PK_RequestID int OUTPUT
)
AS
Insert INTO TBL_Requests
(LoginID, Request)
Values
(@User, @Request)
Select @PK_RequestID=@@Identity
Return
Here is my Code to call it
Code:
Dim intReturnID
Me.SqlConnection1.Open()
Me.SqlCommand1.Parameters.Item("@Request").Value = Me.TextBox1.Text
Me.SqlCommand1.Parameters.Item("@User").Value = Session("MyID")
Me.SqlCommand1.ExecuteNonQuery()
intReturnID = SqlCommand1.Parameters("@PK_RequestID").Value
Me.SqlConnection1.Close()
And here is my declarations
Code:
Me.SqlCommand1.CommandText = "[sp_InsertRequestID]"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("PK_RequestID", SqlDbType.Int, ParameterDirection.Output))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Request", System.Data.SqlDbType.Text, 16, "LoginID"))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@User", System.Data.SqlDbType.VarChar, 50, "UserName"))
Re: Retrieve Unique ID for a new Record
VB Code:
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("PK_RequestID", SqlDbType.Int, ParameterDirection.Output))
u left out an @, would that matter? :ehh:
Re: Retrieve Unique ID for a new Record
If it's not a parameter for the SP, why are you using it?
Re: Retrieve Unique ID for a new Record
It is or at least should be a parameter for the SP. I tried it again with the @ and still get the similar error
Procedure 'sp_InsertRequestID' expects parameter '@PK_RequestID', which was not supplied
It should be the return value therefore an outpout parameter.. I think
Re: Retrieve Unique ID for a new Record
I retrieved the @@Identity a little differently when I needed the new uniqueID.
Instead of trying to retrieve it back as a parameter I simply wrote:
SELECT @@Identity
Just make sure that where you execute the the command you are receiving the ID:
intID = comSQLProc.ExecuteScalar
You could try that.
Re: Retrieve Unique ID for a new Record
Nice one token it worked can you believe ive been stuck on that all day, my original way was the one given in all the txt books and iv had it working in windows forms but not in asp. Any way your way is working fine for me thanks.
Re: Retrieve Unique ID for a new Record
Hey no problem. I , and I'm sure many of the others here, understand the frustrations of being stuck on one small thing all day.
Just glad I could help.