Searching records

[imranshouket]

Hi,

I have built up a small employee info software. Have the tables in Access and linked them to VB2005, everything works fine. The only problem where I am stuck is, how to find a record by a given name or a number, lets say cheque number, from Access records in VB2005.

PLease any help would be appreciated.

THanks

 

[JuggaloBrotha]

you can do that with a SQL statement. After the user enters the information simply select the records where the name is similar to the search info or the check numbers match

you'd do this in the WHERE part

 

[imranshouket]

can I get an example of how should It be.

 

[Arg81]

use parameterised queries - check this link out
http://vbdotnetforums.com/showthread.php?p=56447

Basically you will create a query such as;

SELECT * FROM TABLE WHERE CustomerID = ?

and in your app, you set ? to your parameter (explained more in the attached link)

 

[davy_g]

When you have a textbox in which you type the text you want to search you can also use following statement (hope this works in Access, it does in SQL Server )

sql = "SELECT name FROM table WHERE name like '%" & Textbox1.Text & "%'"

 

[Arg81]

that's why I put the link in - Access and SQL do their parameters different, where SQL is @ParameterName, Access is ?

I don't think access uses % as it's wildcard either - I know someone else asked ages ago about this but I just couldn't find the thread anywhere

 

[davy_g]

that's why I put the link in - Access and SQL do their parameters different, where SQL is @ParameterName, Access is ?

I don't think access uses % as it's wildcard either - I know someone else asked ages ago about this but I just couldn't find the thread anywhere

Oh yeah that's right, I believe the wildcard in Access is * (just like Windows).
So if you replace my % by * it should work also or will my method never work with Access?

 

[Arg81]

I did a quick google search and think that's right. It should work if you use

sql = "SELECT name FROM table WHERE name like "*" & Textbox1.Text & "*"

instead.

 

[cjard]

Hi,

I have built up a small employee info software. Have the tables in Access and linked them to VB2005, everything works fine. The only problem where I am stuck is, how to find a record by a given name or a number, lets say cheque number, from Access records in VB2005.

PLease any help would be appreciated.

THanks

Read the DW2 link in my signature, section on "Creating a Form to Search Data"


Do not, ever, ever write an sql that looks like this:

"SELECT name FROM table WHERE name like '%" & Textbox1.Text & "%'"

For reasons why, see the PQ link in my signature

 

[Arg81]

haha I just realised what I'd been suggesting. I actually meant using parameterised queries, and then setting the paremter to "%" & Textbox1.Text & "%'"

edit - which I actually said in my first post. Sorry I confused the matter - I copied and pasted what needed to be changed from the poster above me, I didn't actually think to check to see whether the code / query was correct!

 

[cjard]

Confusing psot from Arg there.. I think what he means is:

Dim x as New OleDbCommand("SELECT name FROM table WHERE name LIKE ?")
x.Parameters.Add("name_parameter", OleDbType.VarChar)
x.Parameters("name_parameter").Value = String.Format("*{0}*", txtNameTextBox.Text)

But DONT EVEN DO THIS

YOU HAVE .NET 2

There is a better way to put your queries into code! Read DW2 link!

 

[Arg81]

no, I meant create the SQL query as normal on the dataTable, like;

SELECT * FROM Users WHERE Surname LIKE @Surname


and then on the form, code it so

me.UsersTableAdapter.FillBySurname(me.dataSet1.Users, "%" & Textbox1.Text & "%")


Maybe I should of just wrote that to start with

 

[cjard]

"%" & Textbox1.Text & "%"

Maybe it's IMHO only, but I reckon String.Format("%{0}%", textbox.Text) looks neater..

Maybe I should of just wrote that to start with

Should have, dear boy.. Should have