Combo box list

[coily]

Hi,

Could some explain what is my best solution?

I have a comboboxlist that is populated via sql select, the list could contain anything from 1 to 30 items and all items can be selected at once.

Once the user clicks button I iterate through the list of selected items items and retrive the text field of each.

My problem is that I then need to query the database with something like

select * where field = selecteditem OR nextselecteditem

select statment needs to run for each of the items selected in the checkboxlist which is of course ua user defined amount.

I have given this much thought but can't work out how to achieve this, be assured i have searched but have been unable to find anything that I recognise as being helpful.

Any advice would be great thanks

 

[chrissie1]

something like this

dim strselect as string
dim strand as string

strselect = "select * from "
strand = ""
for inttemp as integer = 1 to cbolist.selecteditems.count
  strselect &= strand & "field = '" & cbolist.item(inttemp) & "'"
  strand = " and " 
next

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[SqueakinSweep]

if you're using SQL Server you can use the In Clause

select * from MyTable where code in ('A' ,'B', 'C')

Sweep
...if it works dont mess with it

 

[coily]

Thanks for the posts guys but AGHHHHHH

Its taken a hour to get the querystring right when I do I put the whole thing in a function and build the sql.

then I get an error saying

System.Data.SqlClient.SqlException: Invalid column name

the datareader does not seem to like the value of the comboboxlist item as a column name(same result with dataset). The code is as follows

Function find() As System.Data.IDataReader

Dim i as integer = 0
    dim Counter as integer = 1
    dim stringbuilder As StringBuilder = New StringBuilder("SELECT [zoo_contacts].* FROM [zoo_contacts] WHERE ([zoo_contacts].[fname] = ")
    For i = 0 to CheckBoxList1.Items.Count -1
    If CheckBoxList1.Items(i).Selected then
    if Counter = 1 then

    stringbuilder.Append(CheckBoxList1.Items(i).text & ")")
    else if counter > 1 then
    stringbuilder.Append(" Or ([zoo_contacts].[fname] = " & CheckBoxList1.Items(i).text & ")")

    end if
    counter += 1
    end if
    next
    if counter > 2 then
    stringbuilder.Append(")")
    stringbuilder.insert(51,"(")
    end if
    dim queryString as string = stringbuilder.ToString
itemlabel.text = queryString
        Dim connectionString As String = "server='*'; user id='*'; password='*'; database='*'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)


        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection



        Dim myReader As System.Data.IDataReader
dbConnection.Open()
myReader = dbCommand.ExecuteReader()
myReader.Read()
myReader.Close()
dbConnection.Close()
  End Function

Any idea as to where I am going wrong?

 

[chrissie1]

Firstly
why do you close your connection and datareader in the function while you want to return it. And you are not returning your datareader.

Secondly
why do you no use sqldatareader but Idatareader same goes for the connection and the command?

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[coily]

chrissie1,

Firstly
why do you close your connection and datareader in the function while you want to return it. And you are not returning your datareader.

I tried both ways but get same error

Secondly
why do you no use sqldatareader but Idatareader same goes for the connection and the command?

Again I tried both but with same result.

In the past I have used dbparams for sql ie

Dim dbParam_fname As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_fname.ParameterName = "@fname"
        dbParam_fname.Value = fname
        dbParam_fname.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_fname)

I would then pass the value of each param in the function call.
Only if I use this method with dataset or datareader i would need to create 1 param for each possible selected item. That seems like a lot of redundant code yeah?

Would it be possible to run the sql for one item in a loop
adding the row selected to dataset. If so would this be workable solution?

Thanks for your help

 

[SqueakinSweep]

Here's a possible solution based on the IN clause. It does use dynamic sql, inside a Stored proc however.

CREATE PROCEDURE [dbo].[spSample] 

@Contract_Id INT,
@sDrawings VARCHAR(200) = ''

AS

DECLARE @sWhere VARCHAR(1000)
SET @sWhere = '  WHERE p.contract_id = ' + CONVERT(VARCHAR,@Contract_Id)
IF @sDrawings <> '' SET @sWhere = @sWhere + ' AND d.draw_id in (' + @sDrawings + ')'

DECLARE @sSql VARCHAR(3000)
SET @sSql = 'select p.pieces_id,p.mark,p.descr,
from piece_instance i
inner join pieces p on i.pieces_id = p.pieces_id
+@sWhere

EXEC (@sSql)
GO

All you then have to do is to build a comma delimited list of your selected items, and pass it into the stored procedure. In my case example the string is a series of integer values. The good thing is you can test it out to your hearts content in Query Analyser and know when calling it in .NET, that its just a case of passing the parameters.



Sweep
...if it works dont mess with it

 

[coily]

Stored Procs man, thats like chapters away!

Does your example work if passed any number of params?

If so it's certainly something I should read up on.

Thanks for the pointer

 

[SqueakinSweep]

Take time out to look at Stored Procs.

They will save you hours in development time. They are far simpler than building dynamic strings in .Net, which is a painstaking excercise

And yes the example could be amended to accept more parameters, including other In clauses.

Once built SP's can be tested through Query Analyser, and it makes the .Net code behind so simple and more readable. Simple amendments can often be made then at the data layer and not the code layer.






Sweep
...if it works dont mess with it

 

[coily]

I am so dumb!

Thanks for all your help guys.

I found the problem, it was with the sql query

stringbuilder.Append(CheckBoxList1.Items(i).text & ")")
[code]

Its missing the single quotes needed for strings i changed it to 
[code]stringbuilder.Append("'" & CheckBoxList1.Items(i).text & "')")
[code]

This works fine for one selection or 100 selections. Bingo

"SqueakinSweep" I will look at stored procs, Thanks again