Ayhan Yerli (TR-NL)

 

Hey friends,

I have a question but I don't know how to explain, I will try :)

I use a SQL 2000 database and I have a table called "tblObject" .  I want to get  some records from this table according to a listboxes checked items. There might be only one Item checked or several items. Therefore I don't know how many parameter I have to send to the query.

this is the way I do this job currently :

' Creates a WHERE statement string.
' To add at the end of Select command
Private Function getCriteria(ByVal objectIds() As Integer) As String
      Dim criteriaString As New StringBuilder
      For Counter As Integer = 0 To objectIds.Length - 1
          If Counter = 0 Then
               criteriaString.Append("WHERE ObjectId = " & objectIds(Counter).ToString())
          Else
               criteriaString.Append(" OR ObjectId = " & objectIds(Counter).ToString)
          End If
     Next
     Return criteriaString.ToString
End Function

 

Private Function GetTheObjectInformation(ByVal ObjectIds() As Integer) As DataTable
     Dim conn As New SqlClient.SqlConnection("Connection String bla bla")
     Dim dtObjects As New DataTable
     Dim cmd As New SqlClient.SqlCommand
     Dim da As New SqlClient.SqlDataAdapter
     Dim Commandstring As New StringBuilder("SELECT * FROM tblObject " & getCriteria(ObjectIds))
     ' Command String will look like "SELECT * FROM tblobject WHERE ObjectId = 12 OR ObjectId = 15"
     cmd.CommandText = Commandstring.ToString
     cmd.Connection = conn
     da.SelectCommand = cmd
     da.Fill(dtObjects)
     Return dtObjects
End Function

 

 Normally I prefer to use Stored Procedures rather than using   commandType.Text ,  but I don't  think that, it is  possible to use stored procedure in this case.  Some how  i don't feel very comfortable with the way i do it.

Is there a better (or more professional ) way to do this job  

Thank you all.

by the way forgive my poor English..

 

 

 

 

 

 

 




Re: .NET Framework Data Access and Storage sending unknown amount of parameter to a select query.

cverdon

Hi,

Look at this article:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Charles





Re: .NET Framework Data Access and Storage sending unknown amount of parameter to a select query.

Ayhan Yerli

Thank you very Cverdon,
There are very usefull tecnics on the link you sent me.

I don't think it is what I am loking for exactly. I have to create Comma seperated string instead of creating a where statement. I admit that it is a little better than the way a do it

still open to another suggestions






Re: .NET Framework Data Access and Storage sending unknown amount of parameter to a select query.

kbradl1

You really only have two choices:
Building stored procedures like the link cverdon provides or building your own SQL statement. Either one is fine.
The only other thing I can offer is a recommendation: to improve performance (reduce the size of the SQL statement) I would use the IN statement instead of building a huge WHERE statement.

'return string like 1,2,3,4,5,8, this could also be passed as a parameter to a SP

Private Function getCriteria(ByVal objectIds() As Integer) As String
Dim criteriaString As New StringBuilder
For Counter As Integer = 0 To objectIds.Length - 1
If Counter = 0 Then
criteriaString.Append(objectIds(Counter).ToString())
Else
criteriaString.Append("," & objectIds(Counter).ToString())
End If
Next
Return criteriaString.ToString
End Function

Private Function GetTheObjectInformation(ByVal ObjectIds() As Integer) As DataTable
Dim conn As New SqlClient.SqlConnection("Connection String bla bla")
Dim dtObjects As New DataTable
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim Commandstring As New StringBuilder("SELECT * FROM tblObject WHERE ObjectId IN (" & getCriteria(ObjectIds) ")")
' Command String will look like "SELECT * FROM tblobject WHERE ObjectId IN (12,15,17)"
cmd.CommandText = Commandstring.ToString
cmd.Connection = conn
da.SelectCommand = cmd
da.Fill(dtObjects)
Return dtObjects
End Function






Re: .NET Framework Data Access and Storage sending unknown amount of parameter to a select query.

Paul P Clement IV


You may want to try Googling the newsgroups for ideas as well. There are a number of ways to handle this but none of them optimum.

http://tinyurl.com/yfzkug






Re: .NET Framework Data Access and Storage sending unknown amount of parameter to a select query.

VMazur

I believe this is what you need

http://support.microsoft.com/kb/555266/en-us