SQL query... is there a better way?

  • Thread starter Thread starter Scott Reynolds
  • Start date Start date
S

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
 
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
 
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
 
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
 
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
 
Back
Top