Hello everyone!
I'm new to this site and vb ,find it very useful.
I've got a problem here:
I have a database : DB with a table contains 10 columns. I have created a form : form1, with 3 comboboxes on it : 1,2,3
onev.column------------combobox1
bvnev.column-----------combobox2
tnev.column-------------conbobox3
I want to have the search on this three criteria in any combinations, and show a datagridview with all 10 columns regarding to the search.
How do i fill my comboboxes to fit the search, how do i make multiple search on the 3 criteria, and how do i show it in datagridview ?
I'm using vs 2008 and sql 3.5 ce
Any of your help appriciated !
Excuse my english it is not my mothertongue.
Loading
Kirtan PatelPosted Nov 16, 2009, 10:01 AM
Here I coded your Solution
please check "Do you like this Answer" check box " :)
Download is Solution Files Coded For you
Code can Search in Any Criteria weather you select one combo box or multiple :)
Imports System.Data.SqlServerCe
Public Class Form1
Dim dv As DataView
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Filling Combo boxes With Values
Dim con As SqlCeConnection = New SqlCeConnection("Data Source=|DataDirectory|\bin\Debug\multicriteria.sdf")
con.Open()
Dim comm As SqlCeCommand = New SqlCeCommand("select onev from mc ", con)
' Fill combobox 1
Dim reader As SqlCeDataReader = comm.ExecuteReader()
While (reader.Read())
ComboBox1.Items.Add(reader(0).ToString())
End While
'Fill COmbobox 2
comm = New SqlCeCommand("select bvnev from mc", con)
reader = comm.ExecuteReader()
While (reader.Read())
ComboBox2.Items.Add(reader(0).ToString())
End While
' Fill ComboBox 3 with Values From Database
comm = New SqlCeCommand("select tnev from mc", con)
reader = comm.ExecuteReader()
While (reader.Read())
ComboBox3.Items.Add(reader(0).ToString())
End While
'Close the connection
con.Close()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
DataGridView1.DataSource = Nothing
Dim con As SqlCeConnection = New SqlCeConnection("Data Source=|DataDirectory|\bin\Debug\multicriteria.sdf")
con.Open()
Dim comm As SqlCeCommand = New SqlCeCommand("select * from mc where (@onev is null or onev=@onev) and (@bvnev is null or bvnev=@bvnev) and (@tnev is null or tnev=@tnev)", con)
comm.Parameters.AddWithValue("@onev", ReturnAccrd(ComboBox1))
comm.Parameters.AddWithValue("@bvnev", ReturnAccrd(ComboBox2))
comm.Parameters.AddWithValue("@tnev", ReturnAccrd(ComboBox3))
Dim ds As DataSet = New DataSet()
Dim da As SqlCeDataAdapter = New SqlCeDataAdapter(comm)
da.Fill(ds, "vtable")
dv = ds.Tables(0).DefaultView
DataGridView1.DataSource = dv
End Sub
Public Function ReturnAccrd(ByVal c As ComboBox)
If c.SelectedIndex = -1 Then
Return DBNull.Value
Else
Return c.SelectedItem.ToString()
End If
End Function
End Class
pati tamasPosted Jan 4, 2010, 4:06 AM
It's me again...
I've tried your code but it throws me an exeption at dataadapter. fill methode :SqlCe Exeption was unhandled: the specified argument value for the function is not valid..Argument#=1, Name of function (if known) = isnull
I really would appreciate your help
I've sold it!
in the select comm i used coalesce and check comboboxes text in if .. then if is ="" or not , and it works correctly now!
thanks for help
pati tamasPosted Nov 15, 2009, 9:07 AM
Thanks very much
Kirtan PatelPosted Nov 15, 2009, 7:50 AM
Just Upload Your Project Files (Zip) with database to
http://mediafire.com
and Send me the Download link in mail [email protected]
I will provide you the code how to accomplish the that combobox task :)
Thank you :)
pati tamasPosted Nov 15, 2009, 5:30 AM
pati tamasPosted Nov 14, 2009, 10:05 AM
i have tried the code you have provided but it throws an error:
"sql exeption was unhandled" at adapter.fill(dataset)
and say "variable criteria 1,2,3 and fulcriteria are used before they have been assigned a value.A null reference exeption could result at runtime"
What shall i do ?
an d also could you pls tell me how to set the comboboxes ?
Pls it is urgent...
pati tamasPosted Nov 14, 2009, 6:11 AM
Nilanka DharmadasaPosted Nov 14, 2009, 6:03 AM
pati tamasPosted Nov 14, 2009, 5:46 AM
there is only one more question: shall i bind the combobox to the desired table thru DB binding source select the items distinct or fill it manually ?
thanks in advance...
Nilanka DharmadasaPosted Nov 14, 2009, 5:06 AM
First you have to connect to database.
Then you have to create your SQL statement.
Then you have to set the get data from database and assign it to datagridview.
How to create your connection
---------------------------------------
Assuming you have a SQL Server DB,
Dim con As New SqlConnection
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
Create the search criteria
--------------------------------
Dim mycommandtesxt As String
mycommandtesxt = "SELECT * FROM [tablename]"
Dim criteria1 As String
Dim criteria2 As String
Dim criteria3 As String
Dim fulcriteria As String
If (ComboBox1.SelectedIndex > -1) Then
criteria1 = String.Format("onev = '{0}'", ComboBox1.SelectedValue.ToString())
End If
If (ComboBox2.SelectedIndex > -1) Then
criteria2 = String.Format("bvnev = '{0}'", ComboBox2.SelectedValue.ToString())
End If
If (ComboBox3.SelectedIndex > -1) Then
criteria3 = String.Format("tnev = '{0}'", ComboBox3.SelectedValue.ToString())
End If
If Not (String.IsNullOrEmpty(criteria1)) Then
fulcriteria = criteria1
End If
If Not (String.IsNullOrEmpty(criteria2)) Then
If Not (String.IsNullOrEmpty(fulcriteria)) Then
fulcriteria = String.Format("{0} and {1}", fulcriteria, criteria2)
Else
fulcriteria = criteria2
End If
End If
If Not (String.IsNullOrEmpty(criteria3)) Then
If Not (String.IsNullOrEmpty(fulcriteria)) Then
fulcriteria = String.Format("{0} and {1}", fulcriteria, criteria3)
Else
fulcriteria = criteria3
End If
End If
If Not (String.IsNullOrEmpty(fulcriteria)) Then
mycommandtesxt = String.Format("{0} where {1}", mycommandtesxt , fulcriteria)
End If
Read data from DB abd get the dataset
--------------------------------------------------
Now you have the SQL commande with search criteria.So use it to get data from DB.
Dim dataset As New DataSet()
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand( _
mycommandtesxt , con)
adapter.Fill(dataSet)
Set the data set to datagridview
-----------------------------------------
Now you have the dataset. You can set it to datagridview.
DataGridView1.DataSource = dataset
Hope this answer will help you. If you find this useful, please do not forget to mark this as accepted.