Database record problems...no clue!

[dakota97]

Hi all,

 

I've got a problem with pulling records from my Access DB, and I can't figure it out. I've checked my field names, table structure, and code, but still can't get it to work. What I have is a form with a text box, 6 radio buttons, a list box, and 2 command buttons. The user types data into the text box, selects a radio button, and clicks a search button. The program then searches the database fields for a match, and populates the listbox with the matching record information. If there's no match, then it notifes the user.

 

The only thing is, whenever I run through the process, it says that there are no matching records, even though I type the data exactly as it's listed in the DB. Here's my code:

 

 

 

Public strSearch As String

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' Place cursor in text box

txtSearch.Select()

 

End Sub

 

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

 

' Check to make sure the user selected a search criteria

If radSearch1.Checked And radSearch2.Checked And radSearch3.Checked And radSearch4.Checked And radSearch5.Checked And radSearch6.Checked = False Then

MessageBox.Show("Please select a criteria to search by", "Error")

Else

 

' Clear the contents of the list box, and then find matching records in the database

lstSearch.Clear()

MyConnection.Open()

Dim MyCommand As New OleDbCommand("SELECT * FROM Drawings WHERE '" & strSearch & "' = '" & txtSearch.Text & "'", MyConnection)

Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()

 

' If there are matching records, add all matches to the listbox

If MyReader.Read Then

While MyReader.Read

lstSearch.Items.Add(MyReader("Drawing #"))

 

MyConnection.Close()

MyReader.Close()

MyCommand.Dispose()

End While

 

' If no matching records are found, notify the user

Else

MyConnection.Close()

MyReader.Close()

MyCommand.Dispose()

MessageBox.Show("There are no matching records for the criteria entered", "Error")

txtSearch.Select()

 

End If

End If

 

End Sub

 

 

Any ideas on what I'm missing? Any help is appreciated.

 

Thanks in advance,

 

Chris

[dakota97]

BTW. The code taht sets the value of strSearch is as follows:

 

 

Private Sub radSearch1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch1.CheckedChanged

strSearch = radSearch1.Text ' Sets the value of Search to the radiobutton text value

End Sub

 

Private Sub radSearch2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch2.CheckedChanged

strSearch = radSearch2.Text ' Sets the value of Search to the radiobutton text value

End Sub

 

Private Sub radSearch3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch3.CheckedChanged

strSearch = radSearch3.Text ' Sets the value of Search to the radiobutton text value

End Sub

 

Private Sub radSearch4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch4.CheckedChanged

strSearch = radSearch4.Text ' Sets the value of Search to the radiobutton text value

End Sub

 

Private Sub radSearch5_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch5.CheckedChanged

strSearch = radSearch5.Text ' Sets the value of Search to the radiobutton text value

End Sub

 

Private Sub radSearch6_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch6.CheckedChanged

strSearch = radSearch6.Text ' Sets the value of Search to the radiobutton text value

End Sub

[techmanbd]

this is what your query should look like

"SELECT * FROM Drawings WHERE '" & strSearch & "' LIKE %'" & txtSearch.Text & "%'"

[dakota97]

techman,

 

When I copied your code into mine I received an error in the execution of the reader. I took out the % symbol, and got the same thing as before with no records. Any other suggestions?

[PlausiblyDamp]

I think techmanbd meant to say

"SELECT * FROM Drawings WHERE '" & strSearch & " LIKE '%" & txtSearch.Text & "%'"

 

notice the ' has moved near the LIKE keyword.

 

Also this is a very inefficient way of searching tables.

I

Going back to your original post - are you sure you are putting in exactly the same text as is in the DB? Also are you sure you are querying the correct field (made that mistake myself before now)

[dakota97]

All I'm really looking to do is create a program that allows us to search a database for specific information, and display the matching record in a litbox. I work for a sign shop creating vinyl graphics signs. Our art department has managed to save all of the drawings by naming the file something related to the customer, instead of by drawing number (which every drawing has). They've also saved them in 5 or 6 different folders across 2 computers. This makes it very difficult to find a specific drawing, especially since we have drawings dating pre-1998. Our ultimate goals are as follows:

 

1. Rename all of the drawing files by drawing number, and store them in 1 folder

 

2. Add information about each drawing to the DB (e.g. Customer, Date, Number, Logos used, etc.)

 

3. Be able to search the DB based on a text box and combo box (which has the DB fields)

 

4.) Loop through the DB and add each item found to a listbox, sorted by date (youngest to oldest)

 

 

BTW...I tried the chaged code, and still get the MyReader error....any other suggestions?

 

Thanks in advance,

 

Chris

[jfackler]

This Statment:

While MyReader.Read
lstSearch.Items.Add(MyReader("Drawing #"))

MyConnection.Close()
MyReader.Close()
MyCommand.Dispose()
End While

will only read the first line in your data. Since you close the DataReader and the connection after each line read, it will of necessity, reopen and start at the beginning again. Move the close and disposes out of your loop. I'm suprised you dont get get an overload error.

 

Are you getting a true error or is your If statement returning the messagebox informing you of no records?

 

Jon

Edited March 5, 2004 by jfackler

[dakota97]

I'm getting the message box stating there are no matches. The only thing is I'm typing the search exactly how it's listed in the DB. This doesn't make any sense to me because I've worked with searching a DB before and never ran into this.

 

I moved the close statements outside of the loop now. Slight oversight on my part that probably would've baffled me later...lol

 

Any suggestions on why this is happening?

[jfackler]

Try this:

If MyReader.Read Then
Debug.WriteLine("Returns: " & MyReader.GetString(0))

and see what you get.

[dakota97]

I get this error

 

 

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

 

Additional information: No data exists for the row/column.

[turonah]

If I remember right, I've had this problem before ... First off, instead of

"SELECT * FROM ..." try "SELECT TableName.* FROM ..."

If that doesn't help, try putting brackets ( '(' and ')' ) around your WHERE criteria ...

 

Hope this helps ...