Use SCOPE ??? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Use SCOPE ???

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 13:10:06

I have 2 tables:
a_Users_Portfolios (PortfolioID, UserID, Portfolio_Name)
a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbols)

My Query has the variables @UserID, @Portfolio_Name, @Symbol)

In this query, I'm using @UserID and @Portfolio_Name to return the correct PortfolioID.

I then want to use this PortfolioID to insert both it's value and the @Symbol value into the second table, ie. a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)

There will only be one record inserted at a time, so the inserted data will look something like this:

PortfolioID........Portfolio_Symbol

12 ................C

Do I somehow use SCOPE to get the PortfolioID so that it can be used in the INSERT that will be used to put the @PortfolioID and the @Symbol variables into the a_Users_Portfolios_Symbol table?
==================================================================
SELECT a_Users_Portfolios.PortfolioID, a_Users_Portfolios_Symbol.Portfolio_Symbol
FROM a_Users_Portfolios INNER JOIN
a_Users_Portfolios_Symbol ON a_Users_Portfolios.PortfolioID = a_Users_Portfolios_Symbol.PortfolioID
WHERE (a_Users_Portfolios.UserID = @UserID) AND (a_Users_Portfolios.Portfolio_Name = @Portfolio_Name)
ORDER BY a_Users_Portfolios_Symbol.Portfolio_Symbol

----------------

--SCOPE ????

--SELECT @PortfolioID, @Symbol

--INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol) ????

Thanks for the help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 13:17:32

Are you referring to SCOPE_IDENTITY? If so, you use that to capture the identity value of the previous insert. Where is the insert into a_Users_Portfolios occurring?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 13:20:35

Hi Tara:

No, the insert has already happened separately in another procedure at a different time.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 13:23:06

How about a trigger?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 13:24:38

SCOPE_IDENTITY can't be used then.

So, you can use a trigger as Brett suggested, or:

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)
SELECT @PortfolioID, @Symbol

Since you've already got the values in variables.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 13:36:17

I'm doing something wrong here because when I run this procedure, I get the error that the PortfolioID variable was not supplied....

CREATE PROCEDURE _Charts_Basic_Add_Symbol_To_Portfolio (@UserID as int, @Portfolio_Name as varchar (50), @Symbol varchar(10), @PortfolioID int) AS

SELECT a_Users_Portfolios.PortfolioID, a_Users_Portfolios_Symbol.Portfolio_Symbol

FROM a_Users_Portfolios INNER JOIN
a_Users_Portfolios_Symbol ON a_Users_Portfolios.PortfolioID = a_Users_Portfolios_Symbol.PortfolioID

WHERE (a_Users_Portfolios.UserID = @UserID) AND (a_Users_Portfolios.Portfolio_Name = @Portfolio_Name)

ORDER BY a_Users_Portfolios_Symbol.Portfolio_Symbol

--
INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)
SELECT @PortfolioID, @Symbol

GO

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 13:39:30

OK, Where's the EXECUTE statement for the sproc?

Did you leave it NULL?

Cut and paste the exact error

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 13:39:50

I think this is what you want:



CREATE PROCEDURE _Charts_Basic_Add_Symbol_To_Portfolio 
(@UserID as int, @Portfolio_Name as varchar (50)) 
AS

DECLARE @Symbol varchar(10)
DECLARE @PortfolioID int

SELECT @PortfolioID = a_Users_Portfolios.PortfolioID, @Symbol = a_Users_Portfolios_Symbol.Portfolio_Symbol
FROM a_Users_Portfolios 
INNER JOIN a_Users_Portfolios_Symbol 
ON a_Users_Portfolios.PortfolioID = a_Users_Portfolios_Symbol.PortfolioID
WHERE (a_Users_Portfolios.UserID = @UserID) AND (a_Users_Portfolios.Portfolio_Name = @Portfolio_Name)
ORDER BY a_Users_Portfolios_Symbol.Portfolio_Symbol

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)
SELECT @PortfolioID, @Symbol

RETURN 1

GO

You had @Symbol and @PortfolioID as input parameters. But I don't think that's what you want. You want to pull the data from the SELECT query into those variables. So you use DECLARE for non-input parameters. Then in the SELECT, you put the columns into the variables.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 15:18:58

Here's the current error:
Cannot insert the value NULL into column 'PortfolioID', table 'DotNetNuke.dbo.a_Users_Portfolios_Symbol'; column does not allow nulls. INSERT fails.
========================================================================
As I was looking at this, I decided to simplify it a little below. (Eventually I'd like to test for pre-existing symbols in table 2 (a_Users_Portfolios_Symbol) so I was returning the list of exisitng symbols found in (a_Users_Portfolios_Symbol Portfolio_Symbol). I have taken the pre-existing symbols out and I'm now just returning the PortfolioID--as that is a value that I need in order to write the new symbol (assigned to the @Symbol variable) to: a_Users_Portfolios_Symbol Portfolio_Symbol.

I don't think that I made it clear that there are already symbols in the table in the column Portfolio_Symbol, but the new symbol that will be written to that same column is contained in the variable @Symbol.

Sorry for the confusion.

============================================================================

CREATE PROCEDURE _Charts_Basic_Add_Symbol_To_Portfolio
(@UserID as int, @Portfolio_Name as varchar (50), @Symbol varchar(10))
AS

--DECLARE @Symbol varchar(10)
DECLARE @PortfolioID int

SELECT DISTINCT @PortfolioID = a_Users_Portfolios.PortfolioID--, @Symbol = a_Users_Portfolios_Symbol.Portfolio_Symbol

FROM a_Users_Portfolios

INNER JOIN a_Users_Portfolios_Symbol
ON a_Users_Portfolios.PortfolioID = a_Users_Portfolios_Symbol.PortfolioID

WHERE (a_Users_Portfolios.UserID = @UserID) AND (a_Users_Portfolios.Portfolio_Name = @Portfolio_Name)

--ORDER BY a_Users_Portfolios_Symbol.Portfolio_Symbol

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)
SELECT @PortfolioID, @Symbol

RETURN 1
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 15:44:08

You've got @Symbol commented out in the code. But you are using it in the INSERT. That's why you are getting that error. Do you just want to UPDATE it?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 16:46:58

When I use the code that you supplied in your 13:39:50 post, I received this error:
Procedure or function _Charts_Basic_Add_Symbol_To_Portfolio has too many arguments specified.

I don't think that I want update, do I? I'm adding a new record to a table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 16:54:14

Ok, we need to take a step back and figure out what is going on. What will the user be inputting into the stored procedure? Those inputs are defined before the AS. Any other variables that you need are put after the AS in DECLAREs.

What is the command that you are using to execute the stored procedure?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 17:34:46

This is ASP.NET; I have 3 user submitted variables (@UserID, @Symbol, @Portfolio_Name)

private void Button_Submit_New_Symbol_Click(object sender, System.EventArgs e)
{
//Add Stock Symbol to selected Portfolio List for this user-------------------------------------
int UserID ;

UserID = int.Parse( Context.User.Identity.Name );
Label_UserID.Text = UserID.ToString();

SqlConnection myConnection = new SqlConnection("server=AMD;database=DotNetNuke;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("_Charts_Basic_Add_Symbol_To_Portfolio", myConnection);

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@UserID"].Value = UserID;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Symbol", SqlDbType.VarChar, 10));
myCommand.SelectCommand.Parameters["@Symbol"].Value = DropDownList_Select_Company.SelectedValue;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Portfolio_Name", SqlDbType.VarChar, 30));
myCommand.SelectCommand.Parameters["@Portfolio_Name"].Value = DropDownList_My_Portfolios_To_Add_To.SelectedValue;

DataSet ds = new DataSet();
myCommand.Fill(ds, "Add_Symbol");

Datagrid_Price_Quote.DataSource=ds.Tables["Add_Symbol"].DefaultView;
Datagrid_Price_Quote.DataBind();
}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 17:40:06

Run this in Query Analyzer:

CREATE PROCEDURE _Charts_Basic_Add_Symbol_To_Portfolio
(@UserID as int, @Portfolio_Name as varchar (50), @Symbol varchar(10))
AS

DECLARE @PortfolioID int

SELECT DISTINCT @PortfolioID = a_Users_Portfolios.PortfolioID
FROM a_Users_Portfolios
INNER JOIN a_Users_Portfolios_Symbol
ON a_Users_Portfolios.PortfolioID = a_Users_Portfolios_Symbol.PortfolioID
WHERE (a_Users_Portfolios.UserID = @UserID) AND (a_Users_Portfolios.Portfolio_Name = @Portfolio_Name)

PRINT @PortfolioID

RETURN 1
GO

Then let's execute the stored proc inside Query Analyzer instead of through your app.

EXEC _Charts_Basic_Add_Symbol_To_Portfolio @UserID=1, @Portfolio_Name='EBAY', @Symbol='EBAY'

Change the three inputs so that they make sense in your environment. What do you get? It should return the value of @PortfolioID to the screen.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 17:53:26

Yes, it returns the correct value for @PortfolioID in query analyzer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 17:55:31

Then now do the same thing but instead of the PRINT statement, put:

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol)
SELECT @PortfolioID, @Symbol

Then execute the stored proc in QA the same way. Does the row get inserted? If so, the problem is not the stored procedure.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 18:03:19

Yes, the value gets inserted correctly in the table when using query analyzer.

I'm still getting the following error from the web page, hmmmmmm??:
Cannot insert the value NULL into column 'PortfolioID', table 'DotNetNuke.dbo.a_Users_Portfolios_Symbol'; column does not allow nulls. INSERT fails.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 18:07:57

The input parameters are incorrect then. You need to find out what you are passing to the stored procedure. What you are passing is causing the SELECT to return 0 rows causing @PortfolioID to have NULL. You can run a trace in SQL Profiler to determine what you are passing or you can put some debug code in your app that writes out the parameters.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-05-25 : 18:10:06

Tara:

Thanks for the help with the sql.

I'll look elswhere for this problem as it is evidently something in the .NET code.

Paul

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-25 : 20:13:36

Run this in debug and give us the final statement being passed from you code to the database.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources