Public/Global Variable recommendation for URL Param and SQL

[Rob Wire]

Please let me know the preferred way to do the following.
Accept a Variable in the calling page's URL of an ID. Set
that variable to be global scope of the class so that it
can be used throughout the functions from Page Load.
Would like to send the ID variable as a SQL parameter to
stored procedures for Select, Update, and Insert.

What I have done so far to get around it is declare it in
the SQL Select statements of the Web form code, then
create a new parameter for the stored procedure, then edit
the value to have the variable. However, Visual Studio
keeps overwriting this code with the GUI tools, just
looking at the properties.

Please advise. Thank you, Rob
----
Public Class WebForm1
Inherits System.Web.UI.P age

'RW - Global Employee ID to be used for URL EmployeeID
Parameter.
Public EmployeeID

#Region " Web Form Designer Generated Code "
....
'
'SqlSelectComma nd1
'
'RW - Attempt to globally define EmployeeID from
URL paramater for stored procedure
If Not (Request.Params ("EmployeeID ") Is Nothing)
Then
EmployeeID = Int32.Parse(Req uest.Params
("EmployeeID "))
Else
EmployeeID = "302"
End If
'/RW
Me.SqlSelectCom mand1.CommandTe xt
= "[spGetEmpAttach]"
Me.SqlSelectCom mand1.CommandTy pe =
System.Data.Com mandType.Stored Procedure
Me.SqlSelectCom mand1.Connectio n = Me.SqlConnectio n1
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@RETURN_V ALUE",
System.Data.Sql DbType.Int, 4,
System.Data.Par ameterDirection .ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.Dat aRowVersion.Cur rent, Nothing))
'RW
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@Employee ID",
System.Data.Sql DbType.Int, 4,
System.Data.Par ameterDirection .Input, False, CType(0,
Byte), CType(0, Byte), "",
System.Data.Dat aRowVersion.Cur rent, EmployeeID))
'/RW

[Rick Rainey[MSFT]]

>I've started trying to define the following, but get

a "Specified cast is not valid." error on the SQL
parameter. Am I on the right track? Thank you in
advance, Rob.
---
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArg s) Handles MyBase.Load
'RW - Session state for insert function, then
fills the dataset with stored procedure
GetURL()
If IsPostBack Then
DsAttachmentDtl s1 = CType(Session
("SessionAttac hmentDtls"), dsAttachmentDtl s)
Else
SqlDataAdapter1 .Fill(DsAttachm entDtls1)
Session("Sessio nAttachmentDtls ") =
DsAttachmentDt ls1
DataGrid1.DataB ind()
End If
End Sub
'RW - Global Employee ID to be used for URL EmployeeID
Parameter.
Public EmployeeID
Public Sub GetURL()
'RW - Attempt to globally define EmployeeID from
URL paramater for stored procedure
If Not (Request.Params ("EmployeeID ") Is Nothing)
Then
EmployeeID = Request.Params( "EmployeeID ")
Else
EmployeeID = "302"
End If
'/RW
Me.SqlSelectCom mand1.Parameter s.Item
("@EmployeeID" ) = CType(EmployeeI D,
System.Data.Sq lClient.SqlPara meter)
End Sub

-----Original Message-----
Please let me know the preferred way to do thefollowing.

Accept a Variable in the calling page's URL of an ID.

Set

that variable to be global scope of the class so that it
can be used throughout the functions from Page Load.
Would like to send the ID variable as a SQL parameter to
stored procedures for Select, Update, and Insert.

What I have done so far to get around it is declare it in
the SQL Select statements of the Web form code, then
create a new parameter for the stored procedure, then

edit

the value to have the variable. However, Visual Studio
keeps overwriting this code with the GUI tools, just
looking at the properties.

Please advise. Thank you, Rob
----
Public Class WebForm1
Inherits System.Web.UI.P age

'RW - Global Employee ID to be used for URL

EmployeeID

Parameter.
Public EmployeeID

#Region " Web Form Designer Generated Code "
....
'
'SqlSelectComma nd1
'
'RW - Attempt to globally define EmployeeID from
URL paramater for stored procedure
If Not (Request.Params ("EmployeeID ") Is Nothing)
Then
EmployeeID = Int32.Parse(Req uest.Params
("EmployeeID" ))
Else
EmployeeID = "302"
End If
'/RW
Me.SqlSelectCom mand1.CommandTe xt
= "[spGetEmpAttach]"
Me.SqlSelectCom mand1.CommandTy pe =
System.Data.C ommandType.Stor edProcedure
Me.SqlSelectCom mand1.Connectio n =

Me.SqlConnecti on1

Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.S qlClient.SqlPar ameter("@RETURN _VALUE",
System.Data.S qlDbType.Int, 4,
System.Data.P arameterDirecti on.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.D ataRowVersion.C urrent, Nothing))
'RW
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.S qlClient.SqlPar ameter("@Employ eeID",
System.Data.S qlDbType.Int, 4,
System.Data.P arameterDirecti on.Input, False, CType(0,
Byte), CType(0, Byte), "",
System.Data.D ataRowVersion.C urrent, EmployeeID))
'/RW
.

Hi Rob,

Regarding the casting error; I assume you're getting the error on this line:
Me.SqlSelectCom mand1.Parameter s.Item("@Employ eeID") = CType(EmployeeI D, System.Data.Sql Client.SqlParam eter)
In your CType function call, you need to specify a particular SqlDBType. Assuming EmployeeID is going to be an integer, then you will need to change you
code as follows:
Me.SqlSelectCom mand1.Parameter s.Item("@Employ eeID") = CType(EmployeeI D, System.Data.Sql Client.SqlParam eter.SqlDbType. Int)

Thanks,
Rick[MSFT]
--

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.