Searching Access Database

  • Thread starter Thread starter Swifty_SySoP
  • Start date Start date
S

Swifty_SySoP

I have a VB.NET program that adds, edits, and deletes from an Access
database using OleDbConnection. I want to create a second VB.NET
program that can search the database for records by column ( column 1:
Teacher: column2: Problem: column 3: Completed By: ) by
pressing a Find button. I would like the found record results to be
put out to 3 lables: lblTeacher, lblProblem, and lblCompleted. I also
want a way to be able to search for the next record, such as in the
regedit's search function.
Currently, I have a drop-down List that selects teacher, problem,
and completed by. Then the user types what they want to find in a
text box and clicks a Find button. What I want it to do is if they
select teacher in the list, the program will search only in teacher
column. Same for problem (Search in problem column) and completed by
(search in completed by column). I would prefer that the program
would find an exact match. I want it to output to labels below the
button and also display a record position named lblRecordPosition. I
also have a second button that will search for the next exact matching
word and displays that record in the labels. Any ideas? I'm just
learning how to do this stuff and I am using this for a school I help
manage. Any help at all would be great. Thank you for your time. If
you need any more details or have a suggestion, please email me @
(e-mail address removed) . Thanks again! Oh, by the way, I am using
Visual Studio .NET 2003 Standard. Thanks!

- James
 
Rather, can anyone help me by giving me an idea for a search method?
All I need is a way to find the first exact match... I have coded so
far: (drpSelect is my drop down box)

btnFind_Click(byval sender...)

If drpSelect.text = "Teacher" then
(Now all i need is a method to search column "teacher" in my database)

ElseIf drpSelect.text = "Problem" then
(Now all i need is a method to search column "Problem" in my database)

ElseIf drpSelect.text = "Completed By" then
(Now all i need is a method to search column "Completed" in my
database)

End If

What would I also need to go to next hit? Would it be the same method
or a different one? If you can help I'd appreciate it!

-James

(e-mail address removed)
 
James

You can do something like the following


Dim sInput as String
Dim sSelectFormat as String Dim sField as String
Dim sSql as String

sSelectFormat = "Select {0:s} From Table Where {0:s} Like '%{1:s}%' Order By
{0:s}"
sField = drpSelect.SelectedItem.Text
sInput = TextBox1.Text

'This Sql statement will give you all of the matches that contain the text
the user inputs.
sSql = String.Format(sSelectFormat,sField,sInput)

'Then do the usual connecting to your data source and fill a dataset using a
sqldataadapter with the result of the select statement.
'Loop through the resulting dataset to get all of the matches.


'Note: Replace the word "Table" in the select statement with the actual
table name.

'Note: This code assumes the display on the dropdown list matches exactly to
the column names in the Database. If these are different then do something
like:

'If drpSelect.SelectedItem.Text = "Teacher" Then
' sField = "teacher"
'elseif drpSelect.SelectedItem.Text = "Problem" Then
' sField = "problem"
'elseif drpSelect.SelectedItem.Text = "Completed By" Then
' sField = "CompletedBy"



Let me know if this helps.
 
Oops I didn't see that you needed an exact match. Just change the sql to:
sSelectFormat = "Select {0:s} From Table Where {0:s}='{1:s}' Order By {0:s}"
 
Thanks for the help, but I run into a exception called:

An unhandled exception of type 'System.MissingMemberException'
occurred in microsoft.visualbasic.dll

Additional information: Public member 'Text' on type 'String' not
found.

Is this because I need to use sql server instead of jet 4.0 for my
database connection? I use SQL Statements on my DataSet and
DataAdapter

I then tried running option strict on and I recieve many syntax
errors:

C:\Documents and Settings\James\My Documents\Visual Studio
Projects\Swifty Search\Form1.vb(279): Option Strict On disallows
implicit conversions from 'Boolean' to 'String'.

C:\Documents and Settings\James\My Documents\Visual Studio
Projects\Swifty Search\Form1.vb(280): Option Strict On disallows late
binding.


any ideas?

James

(e-mail address removed)
 
You shouldn't be trying to access the "Text" property on a String. In the
sample code the only "Text" that is being called is on a
DropDownList.SelectedItem or on a TextBox. What line exactly was giving you
the error?
 
Back
Top