search using one or more fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

enrico - 05 Mar 2008 03:12 GMT

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...

Reply to this Message

Cor Ligthert[MVP] - 05 Mar 2008 04:41 GMT

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

Reply to this Message

enrico - 07 Mar 2008 03:20 GMT

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?

Reply to this Message

Cor Ligthert[MVP] - 07 Mar 2008 05:14 GMT

Enrico,

> can you assist me on how to recode it so that i can look for a specific
> record?

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

Reply to this Message

search using one or more fields

Free Magazines