Quick Question - 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

General SQL Server Forums

New to SQL Server Programming

Quick Question

Author

Topic

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 09:59:49

I asked a question over the weekend and got a answer very quick. I'm new to stored procedures and have to do a insert into two tables. I need to insert into the first table (Journal_log). This table has a colunm ID that is Identity is set to yes. After inserting that record I need to retrieve the ID from the Journal_log that I just inserted and insert it into the second table (District_Journal) with data for a new record into the second. Can this be done in one stored procedure or do I have to have two?

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-22 : 10:12:38

It can be done in one SP.

INSERT INTO Table1 (a, b, c, d) VALUES (@Pa, @Pb, ...)

SET @ID = SCOPE_IDENTITY()
-- or SET @ID = @@IDENTITY

INSERT INTO Table2 (d, e, T1ID) VALUES (@Pd, @Pe, @ID)

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 14:15:19

I'm having trouble return the ID to my web page. It keeps asking for the input parameter for @@Journal_ID. I included the stored procedure below.

ALTER PROCEDURE dbo.Journal_ID_Journal_Dist

( @Journal_Area char (10),
@Journal_Title char (10),
@Analyst_name varchar (50),
@Journal_time datetime ,
@district char(10),
@Dist_personal varchar(50),
@Dist_name varchar(50),
@DistNarr nvarchar(300),
@@Journal_ID int output

)

AS
Insert Journal_logs(
Journal_area,
Journal_Title,
Analyst_name,
Journal_time
)
Values
(
@Journal_Area,
@Journal_title,
@Analyst_name,
@Journal_time)

declare @ID int

set @ID = SCOPE_IDENTITY()

Insert District_Journal(
Journal_ID,
District,
District_personal,
Dist_name,
Narrative
)
Values
(
@ID,
@District,
@Dist_personal,
@Dist_name,
@DistNarr)

set @@Journal_ID = @ID
return

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 15:07:56

I forgot to state that this is the error the browser is displaying:

Procedure 'Journal_ID_Journal_Dist' expects parameter '@@Journal_ID', which was not supplied.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-22 : 15:16:17

We'll need to see your application code, the part where you are executing the stored procedure plus the variables. You haven't setup the option in your application code to accept an output parameter.

Tara Kizer
aka tduggan

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 15:52:25

I changed one thing in the stored procedure: @@Journal_ID int = null output

Here is the vb.net code:
SqlCommand1.Parameters.Clear()
plDistrict.Visible = False
If tbJournal_id.Text = "" Then
Dim Journal_Area As New SqlParameter("@Journal_Area", SqlDbType.Char, 10)
Journal_Area.Value = tbArea.Text
SqlCommand1.Parameters.Add(Journal_Area)

Dim Journal_Title As New SqlParameter("@Journal_Title", SqlDbType.Char, 10)
Journal_Title.Value = tbtitle.Text
SqlCommand1.Parameters.Add(Journal_Title)

Dim Analyst_name As New SqlParameter("@Analyst_name", SqlDbType.VarChar, 50)
Analyst_name.Value = tbAnalystName.Text
SqlCommand1.Parameters.Add(Analyst_name)

Dim Journal_time As New SqlParameter("@Journal_time", SqlDbType.DateTime)
Journal_time.Value = System.DateTime.Now.ToString
SqlCommand1.Parameters.Add(Journal_time)

End If

Dim District As New SqlParameter("@District", SqlDbType.VarChar, 10)
District.Value = ddlDistrict1.SelectedValue
SqlCommand1.Parameters.Add(District)

Dim Dist_personal As New SqlParameter("@Dist_personal", SqlDbType.VarChar, 50)
Dist_personal.Value = ddlPersonal1.SelectedValue
SqlCommand1.Parameters.Add(Dist_personal)

Dim Dist_name As New SqlParameter("@Dist_name", SqlDbType.VarChar, 50)
Dist_name.Value = tbDist_Name.Text
SqlCommand1.Parameters.Add(Dist_name)

Dim DistNarr As New SqlParameter("@DistNarr", SqlDbType.NVarChar, 300)
DistNarr.Value = tbDistNarr.Text
SqlCommand1.Parameters.Add(DistNarr)

'Dim JID As New SqlParameter("@@Journal_ID", SqlDbType.Int, 4, ParameterDirection.Output)

SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
ID = SqlCommand1.Parameters("@@Journal_ID").Value
tbJournal_id.Text = ID
SqlConnection1.Close()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-22 : 15:59:30

Here's how I've done it:
SqlCommand1.Parameters("@Journal_ID").Direction = ParameterDirection.Output
SqlCommand1.ExecuteNonQuery()
ID = SqlCommand1.Parameters("@Journal_ID").Value

Oh and use @Journal_ID instead of @@Journal_ID in your stored procedure. @@ refers to global variables that SQL Server provides, whereas @ refers to local variables that we provide.

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-22 : 17:00:12

quote:
Originally posted by tkizer

Here's how I've done it:
SqlCommand1.Parameters("@Journal_ID").Direction = ParameterDirection.Output
SqlCommand1.ExecuteNonQuery()
ID = SqlCommand1.Parameters("@Journal_ID").Value

Oh and use @Journal_ID instead of @@Journal_ID in your stored procedure. @@ refers to global variables that SQL Server provides, whereas @ refers to local variables that we provide.

Tara Kizer
aka tduggan

You know application code?

eeeewwwwwwwwwwwwww

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 17:52:32

I did what you posted and regenerated the collection, I get the error:

An SqlParameter with ParameterName '@Journal_ID' is not contained by this SqlParameterCollection

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-22 : 18:06:03

Did you update the stored procedure to use @Journal_ID instead of @@Journal_ID?

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-22 : 18:07:35

quote:
Originally posted by X002548

quote:
Originally posted by tkizer

Here's how I've done it:
SqlCommand1.Parameters("@Journal_ID").Direction = ParameterDirection.Output
SqlCommand1.ExecuteNonQuery()
ID = SqlCommand1.Parameters("@Journal_ID").Value

Oh and use @Journal_ID instead of @@Journal_ID in your stored procedure. @@ refers to global variables that SQL Server provides, whereas @ refers to local variables that we provide.

Tara Kizer
aka tduggan

You know application code?

eeeewwwwwwwwwwwwww

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Yes, well VB.NET at least.

Tara Kizer
aka tduggan

zoom14151
Starting Member

34 Posts

Posted - 2006-02-22 : 21:39:48

Yes, I updated the stored procedure

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-22 : 22:31:52

quote:
Originally posted by zoom14151

I changed one thing in the stored procedure: @@Journal_ID int = null output

Here is the vb.net code:
SqlCommand1.Parameters.Clear()
plDistrict.Visible = False
If tbJournal_id.Text = "" Then
Dim Journal_Area As New SqlParameter("@Journal_Area", SqlDbType.Char, 10)
Journal_Area.Value = tbArea.Text
SqlCommand1.Parameters.Add(Journal_Area)

Dim Journal_Title As New SqlParameter("@Journal_Title", SqlDbType.Char, 10)
Journal_Title.Value = tbtitle.Text
SqlCommand1.Parameters.Add(Journal_Title)

Dim Analyst_name As New SqlParameter("@Analyst_name", SqlDbType.VarChar, 50)
Analyst_name.Value = tbAnalystName.Text
SqlCommand1.Parameters.Add(Analyst_name)

Dim Journal_time As New SqlParameter("@Journal_time", SqlDbType.DateTime)
Journal_time.Value = System.DateTime.Now.ToString
SqlCommand1.Parameters.Add(Journal_time)

End If

Dim District As New SqlParameter("@District", SqlDbType.VarChar, 10)
District.Value = ddlDistrict1.SelectedValue
SqlCommand1.Parameters.Add(District)

Dim Dist_personal As New SqlParameter("@Dist_personal", SqlDbType.VarChar, 50)
Dist_personal.Value = ddlPersonal1.SelectedValue
SqlCommand1.Parameters.Add(Dist_personal)

Dim Dist_name As New SqlParameter("@Dist_name", SqlDbType.VarChar, 50)
Dist_name.Value = tbDist_Name.Text
SqlCommand1.Parameters.Add(Dist_name)

Dim DistNarr As New SqlParameter("@DistNarr", SqlDbType.NVarChar, 300)
DistNarr.Value = tbDistNarr.Text
SqlCommand1.Parameters.Add(DistNarr)

Dim JID As New SqlParameter("@Journal_ID", SqlDbType.Int, 4, ParameterDirection.Output)
SqlCommand1.Parameters.Add(JID)

SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
tbJournal_id.Text = JID.Value.ToString()
SqlConnection1.Close()

IN addition to changing the "@@" to "@" everywhere, you never did add that output parameter to your command's parameters() collection. See the changes in bold. Also, since you have declared "JID" already, no need to access the parameters collection after executing to get the output value when you already have the reference to it.

FYI -- there are much shorter ways to add parameters to a command; the parameters() object has overloaded "Add" methods that allow you to declare, add, and set parameters all at once w/o the need for additional variables and declarations. Much shorter code!

Final piece of advice: turn OPTION STRICT ON and be sure to always explicitly cast your datatypes.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-22 : 22:36:46

uncomment the line where you declare the output parameter
and verify if parameter names are the same with sp

HTH

--------------------
keeping it simple...

zoom14151
Starting Member

34 Posts

Posted - 2006-02-23 : 09:50:02

Now this is the error I'm getting:

Object reference not set to an instance of an object

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 09:58:53

quote:
Originally posted by zoom14151

Now this is the error I'm getting:

Object reference not set to an instance of an object

Do you feel that provides us with enough information to be able to help you?

zoom14151
Starting Member

34 Posts

Posted - 2006-02-23 : 10:01:27

This is the line that error is at:

tbJournal_id.Text = Journal_ID.Value.ToString()
If I jump over this line there is no error.

zoom14151
Starting Member

34 Posts

Posted - 2006-02-23 : 10:02:51

I forgot to tell you that I changed JID to Journal_ID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 10:12:43

I know this might sound silly, but maybe if you should us all of the releveant code it might be a little eaiser to help you .... ?

zoom14151
Starting Member

34 Posts

Posted - 2006-02-23 : 10:20:55

Ok here the code:

(Stored Procedure)
ALTER PROCEDURE dbo.Journal_ID_Journal_Dist

( @Journal_area char (10),
@Journal_title char (10),
@Analyst_name varchar (50),
@Journal_time varchar (30) ,
@district char(10),
@District_personal varchar(50),
@Dist_name varchar(50),
@Narrative nvarchar(300),
@Journal_ID int = null output

)

AS
Insert Journal_logs(
Journal_area,
Journal_title,
Analyst_name,
Journal_time
)
Values
(
@Journal_area,
@Journal_title,
@Analyst_name,
@Journal_time)

set @Journal_ID = SCOPE_IDENTITY()

Insert District_Journal(
Journal_ID,
District,
District_personal,
Dist_name,
Narrative
)
Values
(
@Journal_ID,
@District,
@District_personal,
@Dist_name,
@Narrative
)

return

Here is the Button click code:

Private Sub btDisAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btDisAdd.Click
SqlCommand1.Parameters.Clear()
plDistrict.Visible = False
If tbJournal_id.Text = "" Then
Dim Journal_area As New SqlParameter("@Journal_area", SqlDbType.Char, 10)
Journal_area.Value = tbArea.Text
SqlCommand1.Parameters.Add(Journal_area)

Dim Journal_title As New SqlParameter("@Journal_title", SqlDbType.Char, 10)
Journal_title.Value = tbtitle.Text
SqlCommand1.Parameters.Add(Journal_title)

Dim Analyst_name As New SqlParameter("@Analyst_name", SqlDbType.VarChar, 50)
Analyst_name.Value = tbAnalystName.Text
SqlCommand1.Parameters.Add(Analyst_name)

Dim Journal_time As New SqlParameter("@Journal_time", SqlDbType.VarChar, 30)
Journal_time.Value = System.DateTime.Now.ToString
SqlCommand1.Parameters.Add(Journal_time)

End If

Dim District As New SqlParameter("@District", SqlDbType.VarChar, 10)
District.Value = ddlDistrict1.SelectedValue
SqlCommand1.Parameters.Add(District)

Dim District_personal As New SqlParameter("@District_personal", SqlDbType.VarChar, 50)
District_personal.Value = ddlPersonal1.SelectedValue
SqlCommand1.Parameters.Add(District_personal)

Dim Dist_name As New SqlParameter("@Dist_name", SqlDbType.VarChar, 50)
Dist_name.Value = tbDist_Name.Text
SqlCommand1.Parameters.Add(Dist_name)

Dim Narrative As New SqlParameter("@Narrative", SqlDbType.NVarChar, 300)
Narrative.Value = tbDistNarr.Text
SqlCommand1.Parameters.Add(Narrative)

Dim Journal_ID As New SqlParameter("@Journal_ID", SqlDbType.Int, 4, ParameterDirection.Output)
SqlCommand1.Parameters.Add(Journal_ID)

SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
tbJournal_id.Text = Journal_ID.Value.ToString()
SqlConnection1.Close()
plControl.Visible = True
End Sub

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 10:45:18

hmmmm -- I have a guess, but I am not sure ... When you add a parameter object to the collection, I believe you get a new reference returned. THAT might be the one you need to use.

So, try modifying your code like this:

Dim Journal_ID As New SqlParameter("@Journal_ID", SqlDbType.Int, 4, ParameterDirection.Output)
Journal_ID = SqlCommand1.Parameters.Add(Journal_ID)

that should do the trick. If not, then I will write up a new snippet of code for you, fully tested.

Seems weird, though . the code you have should work ...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources