Public/Global Variable recommendation for URL Param and SQL

Discussion in 'ASP .Net' started by Rob Wire, Jul 29, 2003.

  1. Rob Wire

    Rob Wire Guest

    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.Page

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

    #Region " Web Form Designer Generated Code "
    ....
    '
    'SqlSelectCommand1
    '
    'RW - Attempt to globally define EmployeeID from
    URL paramater for stored procedure
    If Not (Request.Params("EmployeeID") Is Nothing)
    Then
    EmployeeID = Int32.Parse(Request.Params
    ("EmployeeID"))
    Else
    EmployeeID = "302"
    End If
    '/RW
    Me.SqlSelectCommand1.CommandText
    = "[spGetEmpAttach]"
    Me.SqlSelectCommand1.CommandType =
    System.Data.CommandType.StoredProcedure
    Me.SqlSelectCommand1.Connection = Me.SqlConnection1
    Me.SqlSelectCommand1.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    System.Data.SqlDbType.Int, 4,
    System.Data.ParameterDirection.ReturnValue, False, CType
    (0, Byte), CType(0, Byte), "",
    System.Data.DataRowVersion.Current, Nothing))
    'RW
    Me.SqlSelectCommand1.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@EmployeeID",
    System.Data.SqlDbType.Int, 4,
    System.Data.ParameterDirection.Input, False, CType(0,
    Byte), CType(0, Byte), "",
    System.Data.DataRowVersion.Current, EmployeeID))
    '/RW
    Rob Wire, Jul 29, 2003
    #1
    1. Advertising

  2. >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.EventArgs) Handles MyBase.Load
    > 'RW - Session state for insert function, then
    >fills the dataset with stored procedure
    > GetURL()
    > If IsPostBack Then
    > DsAttachmentDtls1 = CType(Session
    >("SessionAttachmentDtls"), dsAttachmentDtls)
    > Else
    > SqlDataAdapter1.Fill(DsAttachmentDtls1)
    > Session("SessionAttachmentDtls") =
    >DsAttachmentDtls1
    > DataGrid1.DataBind()
    > 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.SqlSelectCommand1.Parameters.Item
    >("@EmployeeID") = CType(EmployeeID,
    >System.Data.SqlClient.SqlParameter)
    > End Sub
    >
    >>-----Original Message-----
    >>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.Page
    >>
    >> 'RW - Global Employee ID to be used for URL

    >EmployeeID
    >>Parameter.
    >> Public EmployeeID
    >>
    >>#Region " Web Form Designer Generated Code "
    >>....
    >> '
    >> 'SqlSelectCommand1
    >> '
    >> 'RW - Attempt to globally define EmployeeID from
    >>URL paramater for stored procedure
    >> If Not (Request.Params("EmployeeID") Is Nothing)
    >>Then
    >> EmployeeID = Int32.Parse(Request.Params
    >>("EmployeeID"))
    >> Else
    >> EmployeeID = "302"
    >> End If
    >> '/RW
    >> Me.SqlSelectCommand1.CommandText
    >>= "[spGetEmpAttach]"
    >> Me.SqlSelectCommand1.CommandType =
    >>System.Data.CommandType.StoredProcedure
    >> Me.SqlSelectCommand1.Connection =

    >Me.SqlConnection1
    >> Me.SqlSelectCommand1.Parameters.Add(New
    >>System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    >>System.Data.SqlDbType.Int, 4,
    >>System.Data.ParameterDirection.ReturnValue, False, CType
    >>(0, Byte), CType(0, Byte), "",
    >>System.Data.DataRowVersion.Current, Nothing))
    >> 'RW
    >> Me.SqlSelectCommand1.Parameters.Add(New
    >>System.Data.SqlClient.SqlParameter("@EmployeeID",
    >>System.Data.SqlDbType.Int, 4,
    >>System.Data.ParameterDirection.Input, False, CType(0,
    >>Byte), CType(0, Byte), "",
    >>System.Data.DataRowVersion.Current, EmployeeID))
    >> '/RW
    >>
    >>
    >>.
    >>

    >


    Hi Rob,

    Regarding the casting error; I assume you're getting the error on this line:

    > Me.SqlSelectCommand1.Parameters.Item("@EmployeeID") = CType(EmployeeID, System.Data.SqlClient.SqlParameter)


    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.SqlSelectCommand1.Parameters.Item("@EmployeeID") = CType(EmployeeID, System.Data.SqlClient.SqlParameter.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.
    Rick Rainey[MSFT], Jul 29, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Charles A. Lackman
    Replies:
    1
    Views:
    1,340
    smith
    Dec 8, 2004
  2. SpamProof
    Replies:
    0
    Views:
    546
    SpamProof
    Oct 21, 2003
  3. Geathaa
    Replies:
    2
    Views:
    690
    Geathaa
    Jul 30, 2003
  4. ectoplasm
    Replies:
    12
    Views:
    635
    Zorro
    Jul 28, 2005
  5. Dave
    Replies:
    5
    Views:
    650
    John Bokma
    Apr 26, 2011
Loading...

Share This Page