How to select data from database based on multi select list

ahmed salah

9y1.1kLearn iOS Programming

I work in c# windows form vs2015

Problem

How to select multiple item from database based on list box multiple select ?

Details

I set listbox 1 control property selection mode to Multi Simple

code below select only one item selected from list box and work without any problem but select only one item

but if i need to multi select for list box How to changes my code below

public DataTable ShowaSpecificSerial(string SerialNo)
{
SqlConnection con = new SqlConnection(connection);
con.Open();
string str = "select * from View_showdata where SerialNo=@SerialNo";
SqlCommand com = new SqlCommand();
com = new SqlCommand(str, con);
com.Parameters.AddWithValue("@SerialNo", SerialNo);
SqlDataAdapter oledbda = new SqlDataAdapter();
oledbda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "View_showdata");
con.Close();
DataTable dt = new DataTable();
dt = ds.Tables["View_showdata"];
return dt;
}public DataTable ShowaSpecificSerial(string SerialNo)
{
SqlConnection con = new SqlConnection(connection);
con.Open();
string str = "select * from View_showdata where SerialNo=@SerialNo";
SqlCommand com = new SqlCommand();
com = new SqlCommand(str, con);
com.Parameters.AddWithValue("@SerialNo", SerialNo);
SqlDataAdapter oledbda = new SqlDataAdapter();
oledbda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "View_showdata");
con.Close();
DataTable dt = new DataTable();
dt = ds.Tables["View_showdata"];
return dt;
}

under button i write following

private void button5_Click(object sender, EventArgs e)
{
string root = @"D:\Temp";
if (!Directory.Exists(root))
{
Directory.CreateDirectory(root);
}
Class1 CLS = new Class1();
DataTable dt = CLS.ShowaSpecificSerial(listBox3.SelectedValue.ToString());
for (int i = 0; i <= Convert.ToInt32(dt.Rows.Count) - 1; i++)
{
txt = "UserID" + dt.Rows[i][0] + "ProductNo" + dt.Rows[i][1] + "Firm Name" + dt.Rows[i][2] + "BtachNo" + dt.Rows[i][3] + "SerialNo" + dt.Rows[i][4];
dm.DM(txt, Color.FromName(comboBox1.SelectedItem.ToString()), Color.White).Save(root + "\\" + dt.Rows[i][4] + ".emf", System.Drawing.Imaging.ImageFormat.Emf);
}
}

Sample data to view View_showdata :

UserID Product Firm BatchNo SerialNo

Ali	Parasitemol	Farco	2099	000055
Ali	ColdFlue	Farco	2998	01112
Ali	ColdFlue	Farco	2998	08888866

Result

suppose i make multi select to list box to serial no to 000055 and 08888866

the result must done is create two files image datamatrix with name 000055 and 08888866 in E : /Temp

and when read it by scanner or mobile read data following to every serial selected

file name 08888866 when read it the data must have

Ali

ColdFlue

Farco

2998

08888866

so that How to change my code above to accept multi select from database also

according to list box it accept multi select but not from database

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Madhanmohan Devarajan9y ago
May be you can use foreach (var selectedItem in listBox3.SelectedItems) { selectedItem.value; } Please use this in your code and modify it according to your need. Don't copy paste without doing any change, it will not work.
ahmed salah9y ago
problem still exist in list item and listitemcollection see :
ahmed salah9y ago
list item and list item collection not defined in my code
so that please help me
Madhanmohan Devarajan9y ago
Do the following changes to your code.

1. No space between "ListItem Collection" => ListItemCollection
public DataTable ShowAllSerial(ListItemCollection SerialNos)

2. Please replace "selecteditem" with "sItem" (because sItem is in the ForEach)
like this
querystr.Append(" AND SerialNo='" + sItem.Value + "'");

querystr.Append("SerialNo='" + sItem.Value + "'");

3. whenever you found definitions not found, then you need to hover on the keyword. visual studio will show you which reference is missing.

Here is the updated code including the references.

using System.Data.SqlClient;
using System.Data.Common;
using System.Data;

Function:
public DataTable ShowAllSerial(ListItemCollection SerialNos)
{
SqlConnection con = new SqlConnection(dbConnection);
con.Open();
String querystr = "select * from View_showdata where ";
int i = 1;
foreach (ListItem sItem in SerialNos)
{
if (sItem.Selected)
{
if (i > 1)
{
querystr = querystr + " AND SerialNo='" + sItem.Value + "'";
}
else
{
querystr = querystr + "SerialNo='" + sItem.Value + "'";
}
}
i++;
}
SqlCommand com = new SqlCommand();
com = new SqlCommand(querystr, con);
//com.Parameters.AddWithValue("@SerialNo", SerialNo);
SqlDataAdapter oledbda = new SqlDataAdapter();
oledbda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "View_showdata");
con.Close();
DataTable dt = new DataTable();
dt = ds.Tables["View_showdata"];
return dt;
}

Note: please mark this answer as accepted, if it is helpful for you.
ahmed salah9y ago
thank you for reply
i make code as you tell me above but there are more things not have defintion
so that how to solve that please
name space used is
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
Madhanmohan Devarajan9y ago
Have modified your function as ShowAllSerial, hope it will solve your issue.

Calling function: DataTable dt = CLS.ShowAllSerial(listBox3.Items);

Function:
public DataTable ShowAllSerial(ListItemCollection SerialNos)
{
SqlConnection con = new SqlConnection(connection);
con.Open();
string querystr = "select * from View_showdata where ";
int i=1;
foreach(ListItem sItem in SerialNos)
{
if(sItem.Selected)
{
if(i > 1)
{
querystr.Append(" AND SerialNo='" + selecteditem.Value + "'");
}
else
{
querystr.Append("SerialNo='" + selecteditem.Value + "'");
}
}
i++;
}
SqlCommand com = new SqlCommand();
com = new SqlCommand(querystr, con);
//com.Parameters.AddWithValue("@SerialNo", SerialNo);
SqlDataAdapter oledbda = new SqlDataAdapter();
oledbda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "View_showdata");
con.Close();
DataTable dt = new DataTable();
dt = ds.Tables["View_showdata"];
return dt;
}

Note: please mark this answer as accepted, if it is helpful for you.
ahmed salah9y ago
thank you for reply
i cannot understand above
firstly i need to get more values selected in textbox separated by comma
how to do that please
Nilesh Jadav9y ago
Hey there !

Change your multiselect code according to this link :

http://stackoverflow.com/questions/28219259/how-to-set-multiple-selected-items-on-a-winforms-listbox