help trying to pass in a parameter

Discussion in 'ASP .Net' started by DC Gringo, Sep 7, 2004.

  1. DC Gringo

    DC Gringo Guest

    I am trying to pass an input parater to SQL Server. The parameter is a URL
    querystring variable. I can't seem to get it to pass a variable, only a
    literal value...

    Help!

    In the following example, the @archiveType parameter accepts the "all"
    sample value. If I replace "all" with Request.QueryString("archiveType"),
    it doesn't work. What am I doing wrong?

    'SqlSelectCommand1
    '
    Me.SqlSelectCommand1.CommandText = "[myProcedure]"
    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))
    Me.SqlSelectCommand1.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@archiveType",
    System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,
    False, CType(0, Byte), CType(0, Byte), "",
    System.Data.DataRowVersion.Current, "all"))

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

    Here's the procedure:

    CREATE PROCEDURE myProcedure


    @archiveType varchar(5)


    as


    SELECT col1
    FROM table1
    WHERE col1 = @archiveType







    --
    _____
    DC G
     
    DC Gringo, Sep 7, 2004
    #1
    1. Advertising

  2. When you refer to a variable like this you need to get the compiler to
    recognize it.
    If ArchiveType is declared as a string, this should work. If it's a TextBox
    control you'll need to refer to the Text property. I'll be discussing these
    issues in my workshop in Orlando this weekend and in my session in Chicago
    in late October. I'm also doing sessions in Las Vegas in early November.

    With Me.SqlSelectCommand1
    .CommandText = "[myProcedure]"
    .Connection = Me.SqlConnection1
    .Parameters.Add("@RETURN_VALUE").Direction=System.Data.ParameterDirection.ReturnValue
    .Parameters.Add("@archiveType",System.Data.SqlDbType.VarChar, 5).Value =
    ArchiveType
    End With

    hth
    --
    ____________________________________
    William (Bill) Vaughn
    Author, Mentor, Consultant
    Microsoft MVP
    www.betav.com
    Please reply only to the newsgroup so that others can benefit.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    __________________________________

    "DC Gringo" <> wrote in message
    news:...
    >I am trying to pass an input parater to SQL Server. The parameter is a URL
    > querystring variable. I can't seem to get it to pass a variable, only a
    > literal value...
    >
    > Help!
    >
    > In the following example, the @archiveType parameter accepts the "all"
    > sample value. If I replace "all" with Request.QueryString("archiveType"),
    > it doesn't work. What am I doing wrong?
    >
    > 'SqlSelectCommand1
    > '
    > Me.SqlSelectCommand1.CommandText = "[myProcedure]"
    > 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))
    > Me.SqlSelectCommand1.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("@archiveType",
    > System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,
    > False, CType(0, Byte), CType(0, Byte), "",
    > System.Data.DataRowVersion.Current, "all"))
    >
    > ---------------------------------
    >
    > Here's the procedure:
    >
    > CREATE PROCEDURE myProcedure
    >
    >
    > @archiveType varchar(5)
    >
    >
    > as
    >
    >
    > SELECT col1
    > FROM table1
    > WHERE col1 = @archiveType
    >
    >
    >
    >
    >
    >
    >
    > --
    > _____
    > DC G
    >
    >
    >
     
    William \(Bill\) Vaughn, Sep 7, 2004
    #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. DC Gringo
    Replies:
    1
    Views:
    457
    Cor Ligthert
    Sep 6, 2004
  2. Phil Certain
    Replies:
    12
    Views:
    859
    Phil Certain
    Feb 1, 2005
  3. Bart Nessux

    still trying to pass strings to the OS

    Bart Nessux, Feb 23, 2004, in forum: Python
    Replies:
    5
    Views:
    342
    Bart Nessux
    Feb 25, 2004
  4. deanfamily
    Replies:
    4
    Views:
    430
    puzzlecracker
    Oct 24, 2005
  5. AzamSharp
    Replies:
    2
    Views:
    203
Loading...

Share This Page