help trying to pass in a parameter

[DC Gringo]

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

 

[William \(Bill\) Vaughn]

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