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.
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 ONGOSET QUOTED_IDENTIFIER OFFGOALTER PROC [dbo].[POToSparts](@Component Varchar(150),@QtyonHand numeric,@EquipmentID Integer,@VendorID Integer,@PartNum VarChar(150),@Price decimal,@CompID int Output)ASBEGINSET NOCOUNT ON;INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand)SET @CompID = SCOPE_IDENTITY()RETURN @CompIDINSERT 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... |
|
|
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 |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
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 |
|
|
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:543. 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. |
|
|
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:543. 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 ONGOSET QUOTED_IDENTIFIER OFFGOALTER PROC [dbo].[POToSparts](@Component Varchar(150),@QtyonHand numeric,@EquipmentID Integer,@VendorID Integer,@PartNum VarChar(150),@Price decimal,@CompID int = NULL Output)ASBEGINSET 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 |
|
|
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. |
|
|
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 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-12 : 12:54:32
|
Also, does the table sparts have an identity column? |
|
|
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 ScreenUSE [MT]GODECLARE @return_value int, @CompID intEXEC @return_value = [dbo].[POToSparts] @Component = N'Testing!', @QtyonHand = 1, @EquipmentID = 1, @VendorID = 1, @PartNum = N'1', @Price = 2.99, @CompID = @CompID OUTPUTSELECT @CompID as N'@CompID'SELECT 'Return Value' = @return_valueGO and then on the bottom of the screen it shows @CompID - 3127 (Which is correct) and ReturnValue - 0I 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!StacySo 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 16Must declare the scalar variable "@EquipmentID".Msg 137, Level 15, State 2, Line 17Must declare the scalar variable "@Component"But I don't understand because I provide those parameters at the beginning of this procedure.Oh Boy! HELP! |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|