search using one or more fields

  • Thread starter Thread starter enrico
  • Start date Start date
E

enrico

i'm new to this language. i'm creating a system using vb.net as front end and
mysql as my back end.
can anyone give me a code sample on how to search a particular client in my
system using one field OR two fields OR three Or either of the six. i'm using
six fields by the way for searching: date, lastname, barangay, nature of
service and accounts collectible. please help...
 
Enrico,

Do that in your SQL procedure in this way

@firstname nvarchar = null,
@lastname nvarchar = null
--etc

Select * from b where
@firstname is null or @ firstname = firstname
@lastname is null or @lastname = lastname

In the parameters you set only those where you searching for

(In fact this in not VB.Net however SQL transact)

-Cor
 
my search button already worked but there's one minor problem. it shows
all records and not what i specified to look for. this is my code anyway:


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim myquery As New MyClasslibrary.MyHelper
Form3.connect(myquery)

Dim myd As DataTable

With myquery
Dim id As String = ""

myd = .runQuery("SELECT Date, Lastname, Firstname,
ts.Barangay," _
& "`Sitio/Purok`, tc.Barangay, NatureofService," _
& " Free, NoOfHectares, NoOfLiters, TotalAmount," _
& " AmountPaid, AmountRemaining, Remarks FROM tbltrcclient
tc inner join tbltrcservice ts on tc.ClientID = ts.ClientID " _
& " where Date LIKE '%" + DateTimePicker1.Value + "%' OR
Lastname LIKE '%" + TextBox1.Text + "%' " _
& " OR ts.Barangay LIKE '%" + ComboBox1.Text + "%' OR
NatureofService LIKE '%" + ComboBox3.Text + "%' ")

End With

DataGridView1.DataSource = myd
DataGridView1.Refresh()
DataGridView1.Columns(3).HeaderText = "Address"
DataGridView1.Columns(5).HeaderText = "Barangay"
End Sub

can you assist me on how to recode it so that i can look for a specific
record?
 
Enrico,
can you assist me on how to recode it so that i can look for a specific
record?
Click to expand...
Sorry, no I cannot in this way, because this is completely based on your
environment. If you want to use this kind of concatinated sql string, then
simple put it in the Querry Analyzer of SQL server and simple try it.

If you want to use parameters, then start with a simple sample to try

By instance for SQL Server

dim myTable as new DataTable
dim mySqlConnection as new SqlConnection("ConnectionString")
dim SqlAdapter1 as New SqlAdapter(Select * from b where c = @b,
mySqlConnection)

SqlAdapter1.parameters.Add("@b",myval)

SqlAdapter1.Fill(myTable)

All typed here so watch typos or other failures.

Cor
 
Back
Top