ASP.NET

Populating Session Var in STORED PROC or POSTBACK - HELP please!! RSS

33 replies

Last post Aug 05, 2009 05:17 PM by march11

‹ Previous Thread|Next Thread ›

- Reply
march12

Member

43 Points

30 Posts

Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 03, 2009 04:33 PM|LINK

Having big problesm with this. I am familiar with working with Session Variables at least in general but,

How can I populate a Session Variable with the KeyID (PK) of the record that gets inserted with the code below??? I load the accountcreated.aspx page after successful completion of the input fields, but I need the keyId of the record that was just created for use on the new page. How can I populate the PK in the Stored Pocedure or upon Postback????????

        Dim schoolsDataSource As New SqlDataSource

        schoolsDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringPSR").ToString()

        schoolsDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

        schoolsDataSource.InsertCommand = "insertSchools"

        schoolsDataSource.InsertParameters.Add("schoolName", schoolNameTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolAddress1", schoolAddress1TextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolAddress2", schoolAddress2TextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolCity", schoolCityTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolState", schoolStateTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolZip", schoolZipTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolContact1Fname", schoolContact1FnameTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolContact1Lname", schoolContact1LnameTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolContact1Title", schoolContact1TitleTextBox.Text)

        schoolsDataSource.InsertParameters.Add("schoolIPAddress", Request.UserHostAddress.ToString())



        Dim rowsAffected As Integer = 0

       rowsAffected = schoolsDataSource.Insert()

        Try

        Catch ex As Exception

            '/ Post to windows error log, or send an email if this line fires.

            Server.Transfer("problem_uploadpage.aspx")

        Finally

            schoolsDataSource = Nothing

        End Try

        If rowsAffected <> 1 Then

            Server.Transfer("problem_dbuploadpage.aspx")

        Else

            Server.Transfer("accountcreated.aspx")

        End If

Thanks for your help everyone

Populating Session Var in STORED PROC or POSTBACK
- Reply
suthish nair

All-Star

15176 Points

3304 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 03, 2009 04:57 PM|LINK
"ScopeIdentity"... bing or google it for examples to retrive last insertd rowid.

Mark as Answer, on the posts replys that helped you !!!.

My Blog
- Reply
march11

Contributor

3017 Points

1366 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 01:02 PM|LINK

Could anyone offer some support in coding this? I think I have evrything correct, but my return value to newID is 2. No matter how I state the type. See below please.....

parentsDataSource.InsertParameters.Add("parent2Email", parent2EmailTextBox.Text.Trim())

parentsDataSource.InsertParameters.Add("parent2Zip", parent2ZipTextBox.Text.Trim())

parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int)

newID = ParameterDirection.Output

AND MY STORED PROCEDURE IS HERE....

                @parent2Zip nchar(10),

                @parent2Email varchar(50),

                @newID int output



AS

      INSERT INTO [parents] ([parent2Zip], [parent2Email])

      VALUES (@parent2Zip, @parent2Email);

      SELECT SCOPE_IDENTITY()

      RETURN

I just need to return the Primary Key after the insert and this just doesn't seem to work correctly!!

thanks,
- Reply
paindaasp

Star

12090 Points

2034 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 02:40 PM|LINK

In SP change:

SELECT SCOPE_IDENTITY()

To:

SET @newID = SCOPE_IDENTITY()

In Code change:

newID = ParameterDirection.Output

To:

newID.Direction = ParameterDirection.Output

Execute SP and to pull newID use

newID.Value

march11

Contributor

3017 Points

1366 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 03:10 PM|LINK

Dim newID As Object

newID = ""

parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int)

newID.Direction = ParameterDirection.Output

Session("parentID") = newID.value

Resulted in this error.

Public member 'Direction' on type 'String' not found.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMemberException: Public member 'Direction' on type 'String' not found.

Source Error:

Line 53:         parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int)

Line 54:
Line 55:         newID.Direction = ParameterDirection.Output
Line 56:
Line 57:         Session("parentID") = newID.value

Edited by march11 on Aug 04, 2009 05:36 PM

- Reply
march11

Contributor

3017 Points

1366 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 03:18 PM|LINK
- Edited by march11 on Aug 04, 2009 03:34 PM

march11

Contributor

3017 Points

1366 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 03:22 PM|LINK

Tweaked the code to this and got the error below....

Dim newID As Object

newID = ""

parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int).ToString()

newID = ParameterDirection.Output

Session("parentID") = newID.value

Server Error in '/' Application.

Public member 'value' on type 'ParameterDirection' not found.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMemberException: Public member 'value' on type 'ParameterDirection' not found.

Source Error:

Line 55:         newID = ParameterDirection.Output

Line 56:
Line 57:         Session("parentID") = newID.value
Line 58:
Line 59:         'parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int)

Edited by march11 on Aug 04, 2009 05:36 PM

- Reply
paindaasp

Star

12090 Points

2034 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 04:04 PM|LINK

The newID should be defined as a SqlParameter, as in:

Dim newID As SqlParameter = command.Parameters.Add("@newID", SqlDbType.Int)

newID.Direction = ParameterDirection.Output
- Reply
march11

Contributor

3017 Points

1366 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 04:56 PM|LINK

This line of code generates an error over SqlParameter stating "Type SqlParameter is not defined."

Dim newID As SqlParameter = command.Parameters.Add("@newID", SqlDbType.Int)
- Reply
NihirPorecha

Contributor

3400 Points

616 Posts

Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!

Aug 04, 2009 05:08 PM|LINK

Try this :

Dim newID As new SqlParameter

newID.ParameterName = "@newID"

newID.SqlDBType =SqlDbType.Int

newID.Direction = ParameterDirection.OutputSqlDbType.Int

command.Parameters.Add(newID)

command.ExecuteNonQuery()

newID.Value will give you the identity value.

Please Mark as answer if it helps you.

My blog

Prev Next

1 2 3 4 Last

‹ Previous Thread|Next Thread ›

Populating Session Var in STORED PROC or POSTBACK - HELP please!! RSS

33 replies

march12