SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Query using Output
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/11/2012 :  16:28:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

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

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/12/2012 :  07:09:56  Show Profile  Reply with Quote
Still getting error POToSparts expects parameter '@CompID' Which was not supplied.

Thanks for your reply!
Stacy
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/12/2012 :  07:28:11  Show Profile  Reply with Quote
While executing procedure,



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

--
Chandu
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/12/2012 :  07:48:54  Show Profile  Reply with Quote
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.

Edited by - StacyOW on 12/12/2012 08:13:54
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  08:24:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

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

--
Chandu
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/12/2012 :  10:26:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  10:38:02  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 12/12/2012 10:39:00
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/12/2012 :  10:55:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  11:30:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 12/12/2012 :  11:37:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  12:54:32  Show Profile  Reply with Quote
Also, does the table sparts have an identity column?
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/12/2012 :  13:32:12  Show Profile  Reply with Quote
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!

Edited by - StacyOW on 12/12/2012 13:59:47
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/12/2012 :  14:18:02  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/12/2012 :  15:11:41  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/13/2012 :  10:52:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  11:05:15  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/13/2012 :  14:00:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  14:16:05  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/13/2012 :  14:20:42  Show Profile  Reply with Quote
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

Edited by - StacyOW on 12/13/2012 14:21:31
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000