Access Database Help

[tempoman]

Could someone please look over this code and see what I am doing wrong? I can successfully connect to the database however if I true to Select the record that matches the Surname a user enters from the database it returns an error:
----------------------------
"An unhandled exception of type 'System.InvalidOperationException' occurred in microsoft.data.odbc.dll

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

Here is my code:

----------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As OdbcConnection
cn = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=c:\Mouse Tails\Database\prototype1.mdb;UID=;PWD=&quot

cn.Open()
Dim mystring As String = "Select * from Owner where OwnerSname Like 'UserName.Text'"
Dim cmd As OdbcCommand = New OdbcCommand(mystring, cn)
Dim myReader As OdbcDataReader
Dim Fname As Object
myReader = cmd.ExecuteReader()

If myReader(&quot;OwnerFname&quot <> 0 Then
Password.Text = myReader(&quot;OwnerFname&quot
End If


cn.Close()
End Sub

-----------------------------------------------------

 

[chiph]

Your code:

Dim mystring As String = "Select * from Owner where OwnerSname Like 'UserName.Text'"

Results in UserName.Text coming through as literal text, and not the contents of the textbox like you expect. I'm used to C#, not VB.NET, so my code might look a little weird, but try this:

Dim MyString As StringBuilder = ""
MyString.Append("SELECT * FROM Owner WHERE OwnerSname LIKE '")
MyString.Append(UserName.Text)
MyString.Append("'")

Dim cmd As OdbcCommand = New OdbcCommand(MyString.ToString(), cn)

Something else you might want to do is surround this code in a try..catch..finally block, so that if the ODBC layer raises an exception you'll be able to catch it. You'll need to make some code changes regarding variable declaration for that, though.

Chip H.