Re: SQL query... is there a better way?

From: Scott Reynolds (scott_at_it-xenius.co.uk)
Date: 01/20/05 

Date: Fri, 21 Jan 2005 01:06:32 +0200

Hi Chris!

Your code works well, but when I am usin parameterized query, it gives me
followin error: "No value given for one or more required parameters."

Could you please take a look at code samples below... Do you have any idea
what may cause this error?

Dim paramList As New StringBuilder
Dim param As Integer = 0
Dim paramName As String

' WORKING CODE
For Each value As Object In values
    paramName = value.ToString
    If paramId > 0 Then paramList.Append(",")
    paramList.Append(paramValue)
    paramId += 1
Next

oCmd.CommandText = String.Format("select * from Buildings where
SubwayStationId IN ({0})", paramList.ToString())

' NOT WOEKING CODE
For Each value As Object In values
    paramName = String.Format("@Param{0}", param)
    If param > 0 Then paramList.Append(",")
    paramList.Append(paramName)
    oCmd.Parameters.Add(New SqlParameter(paramName, value))
    param += 1
Next

oCmd.CommandText = String.Format("select * from MyTable where City IN
({0})", paramList.ToString())

Thanks!
Scott

"Chris Taylor" <chris_taylor_za@hotmail.com> wrote in message
news:ezoNTua$EHA.1260@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> You could build a comma separated list of the values and use the IN
> statement as an alternative to the OR not realy significant.
>
> Select * FROM MyTable WHERE City IN ('City1', 'City2')
>
> Personally I would use a parameterized query, the code might look
> something
> like this (untested)
>
> Dim paramList As New StringBuilder
> Dim param As Integer = 0
> Dim paramName As String
> For Each value As Object In values
> paramName = String.Format("@Param{0}", param)
> If param > 0 Then paramList.Append(",")
> paramList.Append(paramName)
> oCmd.Parameters.Add(New SqlParameter(paramName, value))
> param += 1
> Next
> oCmd.CommandText = String.Format("select * from MyTable where City IN
> ({0})", paramList.ToString())
>
> Hope this helps
>
> --

Relevant Pages
Loading