[RESOLVED] [2005] Ms

Pls help. Am tryin to retrieve data from a database using a query.
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
'The OleDbDataReader is a forward-only data reader used for speed and efficiency.
Dim SQLReader As OleDbDataReader
'Dim Searchstr As String = ""
Dim Searchstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\NRaqa\Desktop\TestD.mdb"
Dim conn As OleDbConnection

conn = New OleDbConnection(Searchstr)

Dim query As String = "Select NAME FROM KonkeQuery where NAME like '%storename.text%'"

Dim command As New OleDbCommand(query, conn)

If storename.Text = "" Then
MsgBox("Please Enter Strore name", MsgBoxStyle.Exclamation)
Exit Sub
Else
conn.Open()
SQLReader = command.ExecuteReader()


If SQLReader.HasRows Then
While SQLReader.Read

storename.Text = SQLReader.Item(query) <-------------this lne is bein skipped even if the user enters part of the string dat is d
End While
Else
MsgBox("There were no matches found, please try again", MsgBoxStyle.Information)
End If

conn.Close()
End If


End Sub


Thanks in advance

Regards
Noks

I'm not 100% certain...but i think ur problem might be to do with your select statement because it looks like you are searching for the text 'storename.text' not what that contains. Have you tried changing that part of the statement to something you know will return a result? If you have then what did you try, what did it return and what should it have returned?

Missing quotes....should be

VB Code:

Dim query As String = "Select NAME FROM KonkeQuery where NAME like '%" & storename.text & "%'"

Try changing:

VB Code:

storename.Text = SQLReader.Item(query)

to

VB Code:

storename.Text = SQLReader.Item("name")

because then it will return the value in that named column. If the column is called something else then change whats inside the quotes. Or you could use the column number if you know what it is going to be.
Also another thing i have noticed is that if there is going to be more than one result got back then it will only display the last result received in the textbox (assuming storename is a textbox), if you want to display all returned results then you will need to either say

VB Code:

storename.Text += SQLReader.Item("name") & controlchars.newline

Hope this helps
:thumb:

when in doubt do msgbox(query) and see then you can see what the sql server is getting and try it in the query manager.

Also a try catch block might be helpful

Try
storename.Text = SQLReader.Item(query)
Catch ex As Exception
'this will give you lots of good info!
MsgBox(ex.ToString)
End Try

Thanks guys... u've been a huge help.. now i get the results i want...Thanks 4 a gr8 welcome and not ignorin ma post

Regards Noks