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.QuerySt ring("archiveTy pe"),
it doesn't work. What am I doing wrong?

'SqlSelectComma nd1
'
Me.SqlSelectCom mand1.CommandTe xt = "[myProcedure]"
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))
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@archiveT ype",
System.Data.Sql DbType.VarChar, 5, System.Data.Par ameterDirection .Input,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.Dat aRowVersion.Cur rent, "all"))

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

Here's the procedure:

CREATE PROCEDURE myProcedure
@archiveType varchar(5)
as
SELECT col1
FROM table1
WHERE col1 = @archiveType



--
_____
DC G

[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.SqlSelectCom mand1
.CommandText = "[myProcedure]"
.Connection = Me.SqlConnectio n1
.Parameters.Add ("@RETURN_VALUE ").Direction=Sy stem.Data.Param eterDirection.R eturnValue
.Parameters.Add ("@archiveType" ,System.Data.Sq lDbType.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" <dc******@visio ntechnology.net > wrote in message
news:uL******** ******@TK2MSFTN GP14.phx.gbl...

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.QuerySt ring("archiveTy pe"),
it doesn't work. What am I doing wrong?

'SqlSelectComma nd1
'
Me.SqlSelectCom mand1.CommandTe xt = "[myProcedure]"
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))
Me.SqlSelectCom mand1.Parameter s.Add(New
System.Data.Sql Client.SqlParam eter("@archiveT ype",
System.Data.Sql DbType.VarChar, 5, System.Data.Par ameterDirection .Input,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.Dat aRowVersion.Cur rent, "all"))

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

Here's the procedure:

CREATE PROCEDURE myProcedure
@archiveType varchar(5)
as
SELECT col1
FROM table1
WHERE col1 = @archiveType



--
_____
DC G