SQL query... is there a better way?

[Scott Reynolds]

Hi,

I am using following code to read and generate SQL query based on values in
storedarray. But I am not sure if it is the best way... all suggestions are
welcome!

Thank you!
Scott


Dim SqlQuery As String
Dim SqlCity As String

If Not ItemsArray.Count = 0 Then
For i As Integer = 0 To ItemsArray.Count - 1
If i = 0 Then
SqlCity = "(City =" + ItemsArray(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + ItemsArray(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

 

[Sahil Malik]

... hmm .. that's a toughie...

An obvious suggestion is to use StringBuilder.

Other than that, what is the source of your ItemsArray?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Scott Reynolds]

Hi Sahil,

I am devaloping an Asp.Net page, where user can select multiple values in
CheckBoxList web control, all selected values are stored in ArrayList named
ItemsArray...

Carry

 

[Chris Taylor]

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

 

[Scott Reynolds]

Hello Chris!

Thank you, that's what I was looking for...

Scott

 

[Scott Reynolds]

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]

Hi,

Sorry for the delay. The most likely reason for the exception is that one of
the values in your list is null (Nothing in VB.NET), if that is the case you
should pass DBNull.Value as the parameter value.

Try the following when adding the parameter.
oCmd.Parameters.Add(New SqlParameter(paramName, IIf(IsNothing(value),
DBNull.Value, value)))

Hope this helps