Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Query using Output

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-11 : 16:28:05
I am trying to set up a stored procedure that will insert a record into one table and then get the "ID" auto-increment value that the table assigns to that record to insert records into two other tables. This is what I have so far. But I keep getting an error that the @CompID isn't assigned so I guess it isn't setting that output variable. Any ideas what I am doing wrong?

USE [MT]
GO
/****** Object: StoredProcedure [dbo].[POToSparts] Script Date: 12/11/2012 14:22:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@CompID int Output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand)
SET @CompID = SCOPE_IDENTITY()
RETURN @CompID
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')
END

Thanks,
Stacy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 17:17:54
Remove the "Return @CompID" statement
...
SET @CompID = SCOPE_IDENTITY()
RETURN @CompID
INSERT INTO compequip
...
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 07:09:56
Still getting error POToSparts expects parameter '@CompID' Which was not supplied.

Thanks for your reply!
Stacy
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 07:28:11
While executing procedure,



DECLARE @CompID int;
Execute [dbo].[POToSparts] all param values, @CompID OUTPUT

--
Chandu
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 07:48:54
quote:
Originally posted by bandi

While executing procedure,



DECLARE @CompID int;
Execute [dbo].[POToSparts] all param values, @CompID OUTPUT

--
Chandu



I'm not sure what you mean here, I call this procedure from Visual Studio 2010 winforms app. Here is the code I am using to execute the procedure.
 Using connection As New SqlConnection(My.Settings.MTConnectionString)
' Create the command and set its properties.
Dim command As SqlCommand = New SqlCommand()
command.Connection = connection
command.CommandText = "POToSparts"
command.CommandType = CommandType.StoredProcedure

' Add the input parameter and set its properties.
Dim parameter As New SqlParameter()
parameter.ParameterName = "@Component"
parameter.SqlDbType = SqlDbType.VarChar
parameter.Direction = ParameterDirection.Input
parameter.Value = gridrow.Cells(3).Value

Dim parameter1 As New SqlParameter()
parameter1.ParameterName = "@QtyonHand"
parameter1.SqlDbType = SqlDbType.Decimal
parameter1.Direction = ParameterDirection.Input
parameter1.Value = zqty

Dim parameter2 As New SqlParameter()
parameter2.ParameterName = "@EquipmentID"
parameter2.SqlDbType = SqlDbType.Int
parameter2.Direction = ParameterDirection.Input
parameter2.Value = gridrow.Cells(0).Value

Dim parameter3 As New SqlParameter()
parameter3.ParameterName = "@VendorID"
parameter3.SqlDbType = SqlDbType.Int
parameter3.Direction = ParameterDirection.Input
parameter3.Value = ComboBox4.SelectedValue

Dim parameter4 As New SqlParameter()
parameter4.ParameterName = "@PartNum"
parameter4.SqlDbType = SqlDbType.VarChar
parameter4.Direction = ParameterDirection.Input
parameter4.Value = gridrow.Cells(2).Value

Dim parameter5 As New SqlParameter()
parameter5.ParameterName = "@Price"
parameter5.SqlDbType = SqlDbType.Decimal
parameter5.Direction = ParameterDirection.Input
parameter5.Value = gridrow.Cells(8).Value

Dim parameter6 As New SqlParameter()
parameter6.ParameterName = "@CompID"
parameter6.SqlDbType = SqlDbType.Int
parameter6.Direction = ParameterDirection.Output

' Add the parameter to the Parameters collection.
command.Parameters.Add(parameter)
command.Parameters.Add(parameter1)
command.Parameters.Add(parameter2)
command.Parameters.Add(parameter3)
command.Parameters.Add(parameter4)
command.Parameters.Add(parameter5)
command.Parameters.Add(parameter6)

' Open the connection and execute the reader.
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
connection.Close()


OK I added a 6th parameter for the ouput of the @CompID and now it will execute the first insert query but it doesn't do the next two queries and it give no error.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 08:24:12
Change your code to the following
....
ALTER PROC [dbo].[POToSparts]
(
@Component VARCHAR(150),
@QtyonHand NUMERIC,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VARCHAR(150),
@Price DECIMAL,
@CompID INT = NULL OUTPUT
)
AS
...
Alternatively, set a value to the parameter6 in your C# code.

As an aside, seems like you are not returning any record set from the stored proc, so better to use ExecuteNonQuery rather than ExecuteReader.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 08:27:36
Check this link
http://forums.asp.net/t/1251635.aspx

--
Chandu
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 10:26:18
quote:
Originally posted by sunitabeck

Change your code to the following
....
ALTER PROC [dbo].[POToSparts]
(
@Component VARCHAR(150),
@QtyonHand NUMERIC,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VARCHAR(150),
@Price DECIMAL,
@CompID INT = NULL OUTPUT
)
AS
...
Alternatively, set a value to the parameter6 in your C# code.

As an aside, seems like you are not returning any record set from the stored proc, so better to use ExecuteNonQuery rather than ExecuteReader.



Not sure how to set a value to the parameter6 in my vb.net code because that value is supposed to be assigned with the Scope_Identity(). Atleast that is what I am trying to do.

Changed my code like this for the ExecuteNonQuery. It still only runs the first insert query and then doesn't run the other two, which need that @CompID to do those insert queries.
Using connection As New SqlConnection(My.Settings.MTConnectionString)
'Dim command As SqlCommand = New SqlCommand()
Using command As New SqlCommand("POToSparts", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

connection.Open()
command.ExecuteNonQuery()
End Using
End Using
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 10:38:02
Do ALL of the things I am listing below:

1. Change the stored procedure template as I had indicated earlier - i.e., "@CompID INT = NULL OUTPUT"
2. Remove the RETURN @CompID in your stored procedure as I had indicated earlier in my posting at 12/11/2012 : 17:17:54
3. Immediately after the executenonquery, add the following statement

CompID = Cmd.Parameters("@CompID").Value
You may need to Dim CompId if you have not already or if you have Option Strict.


Now the CompId variable would have the value that you received from stored procedure via scope_identity(). If that does not work, post the EXACT error message that you are getting.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 10:55:04
quote:
Originally posted by sunitabeck

Do ALL of the things I am listing below:

1. Change the stored procedure template as I had indicated earlier - i.e., "@CompID INT = NULL OUTPUT"
2. Remove the RETURN @CompID in your stored procedure as I had indicated earlier in my posting at 12/11/2012 : 17:17:54
3. Immediately after the executenonquery, add the following statement

CompID = Cmd.Parameters("@CompID").Value
You may need to Dim CompId if you have not already or if you have Option Strict.


Now the CompId variable would have the value that you received from stored procedure via scope_identity(). If that does not work, post the EXACT error message that you are getting.


Have done everything you have suggested:
My Stored Procedure - WITH THE @CompID int = NULL Output ADDED and RETURN @CompID REMOVED!
USE [MT]
GO
/****** Object: StoredProcedure [dbo].[POToSparts] Script Date: 12/12/2012 06:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand)
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')
END

My VS2010 Code - WITH THE Dim CompID = command.Parameters("@CompID").Value ADDED!
 Using connection As New SqlConnection(My.Settings.MTConnectionString)
'Dim command As SqlCommand = New SqlCommand()
Using command As New SqlCommand("POToSparts", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

connection.Open()
command.ExecuteNonQuery()
Dim CompID = command.Parameters("@CompID").Value
End Using
End Using

And it still doesn't work! NO ERRORS - it just doesn't execute the last two INSERT queries in the Stored Procedure!
Stacy
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 11:30:40
Put a breakpoint immediately after "Dim CompID = command.Parameters("@CompID").Value" and inspect the value of CompId. If that has something (i.e., is not null), then in SSMS query "select * from compequip where ComponentID = 1234" assuming 1234 is the id you saw in debugger.

If none of that seems to give you any clue, run the stored procedure from SSMS and see if that works.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-12-12 : 11:37:14
I have run into this before. Make sure you are looking at the correct SQL Server instance.
I have yelled at my dev box while accidentally running my query on the SIT box








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 12:54:32
Also, does the table sparts have an identity column?
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 13:32:12
I tried putting the breaking point and the value of @CompID is 0. It is getting the Return Value not the @CompID value.

I ran the query straight from SSMS and it looks like this when it is run.
On the Top of the Screen
USE [MT]
GO

DECLARE @return_value int,
@CompID int

EXEC @return_value = [dbo].[POToSparts]
@Component = N'Testing!',
@QtyonHand = 1,
@EquipmentID = 1,
@VendorID = 1,
@PartNum = N'1',
@Price = 2.99,
@CompID = @CompID OUTPUT

SELECT @CompID as N'@CompID'

SELECT 'Return Value' = @return_value

GO

and then on the bottom of the screen it shows @CompID - 3127 (Which is correct) and ReturnValue - 0
I think it's returning this ReturnValue to VS rather than the @CompID.

And yes the table has an identity field.
And I only have one sql server instance.

Any Ideas?
Thanks for all your help!
Stacy

So then I tried adding back my other two insert queries, which are supposed to use that @CompID in the query!
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')

and I get the Following Error:
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@EquipmentID".
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@Component"
But I don't understand because I provide those parameters at the beginning of this procedure.
Oh Boy! HELP!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 14:18:02
When you removed the return statement, the inserts should have been part of the stored procedue. Which means, there should be rows with ComponentID = 3127 in the compequip table. Can you check if there is?

You are getting the sytax error because you have to run the entire query - all 3 inserts in one batch.

I don't think this would make any difference at all, but in the VB code, try changing ParameterDirection.Output to ParameterDirection.InputOutput
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-12 : 15:11:41
quote:
Originally posted by sunitabeck

When you removed the return statement, the inserts should have been part of the stored procedue. Which means, there should be rows with ComponentID = 3127 in the compequip table. Can you check if there is?

You are getting the sytax error because you have to run the entire query - all 3 inserts in one batch.

I don't think this would make any difference at all, but in the VB code, try changing ParameterDirection.Output to ParameterDirection.InputOutput



It doesn't ever put any values in the other two tables (for the other insert queries). That's my problem.
When I ran it out of SSMS with just the first query it worked fine and it seems to have the value in @CompID parameter.
It's when it goes to run those two other insert queries that I have the problem. When I add that CompID = Command.Parameters("@CompID").Value line in VS2010 it doesn't ever get the right value - it was getting the ReturnValue not the @CompID value.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-13 : 10:52:07
Ok I have it doing all three insert queries.

My problem now is trying to get Sql to return the @CompID to VS2010. Not sure if you guys are familiar with that or if you just do SQL.

Thanks,
Stacy
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 11:05:15
Did you mean to say that when you run the stored procedure with the "RETRUN @CompID" removed, it does all the right things - namely, inserts the data into the first table, gets the identity value, and then inserts into the second and third table?

If that is so, it is a short step from there to getting the data into .Net. What you suggested about the value for @CompID that you are seeing in .Net code being the stored procedure return value is not true. To get the return value, ParameterDirection would have to be ParameterDirection.ReturnValue.

Your code seems correct. The only other things that I can think of is if the call from .Net is throwing an exception and if it is being caught silently somewhere up in the call chain. Put a try/catch block around your .Net code that you posted and see if that generates any exceptions.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-13 : 14:00:03
quote:
Originally posted by sunitabeck

Did you mean to say that when you run the stored procedure with the "RETRUN @CompID" removed, it does all the right things - namely, inserts the data into the first table, gets the identity value, and then inserts into the second and third table?

If that is so, it is a short step from there to getting the data into .Net. What you suggested about the value for @CompID that you are seeing in .Net code being the stored procedure return value is not true. To get the return value, ParameterDirection would have to be ParameterDirection.ReturnValue.

Your code seems correct. The only other things that I can think of is if the call from .Net is throwing an exception and if it is being caught silently somewhere up in the call chain. Put a try/catch block around your .Net code that you posted and see if that generates any exceptions.



Yes I have all the insert queries working and if I execute the stored procedure in SSMS it shows the @CompID value and it is correct. Below is my VS2010 code which is supposed to be getting that value from SQL.
Try
Using connection As New SqlConnection(My.Settings.MTConnectionString)
Using command As New SqlCommand("POToSparts", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

connection.Open()
command.ExecuteNonQuery()
Dim zCompID As String = command.Parameters("@CompID").Value.ToString
MsgBox(zCompID)
End Using
End Using

Catch ex As Exception

End Try

After the ExecuteNonQuery() I am assigning that value from Sql to a variable called zCompID. When I run this it doesn't even bring up the MsgBox(zCompID) right now, but when it has the value was blank. I've changed this stuff so many times trying to get it to work. Been at it now for 3 days - starting to go a little nutty! Any ideas?
Appreciate all your help.
Stacy
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 14:16:05
quote:
When I run this it doesn't even bring up the MsgBox(zCompID)

That means that there is an exception somewhere in your VB code. Wrap that function in a try/catch block and see what the exception is. It could be from the stored procedure itself, or it could be while the call-stack is being created if there is an incomptible data passed to one of the parameters. Best way to find out is to put a try/catch block and look at the exception.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-13 : 14:20:42
quote:
Originally posted by sunitabeck

quote:
When I run this it doesn't even bring up the MsgBox(zCompID)

That means that there is an exception somewhere in your VB code. Wrap that function in a try/catch block and see what the exception is. It could be from the stored procedure itself, or it could be while the call-stack is being created if there is an incomptible data passed to one of the parameters. Best way to find out is to put a try/catch block and look at the exception.



I will try that. In the meantime I had tried doing the vb.net code differently using the execute_Scalar() like code below and my message box that I have popping up that returned @CompID from SQL is showing that the value is 0 and @CompID is 3157. It seems like it is returning the ReturnValue instead of my @CompID value. Does that make sense? How can that be? @CompID is in there as the output.

Try
Using connection As New SqlConnection(My.Settings.MTConnectionString)
Using command As New SqlCommand("POToSparts", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

connection.Open()
Dim zCompID As Integer
zCompID = command.ExecuteScalar()
MsgBox(zCompID)
End Using
End Using

Catch ex As Exception

End Try
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -