Extract Data from Access DB to Page ASP.NET

[stuartornum]

Hi,

I am really stuck on the most simplist of tasks you would think.

I have a MS Access database with users firstnames lastnames etc...

All I want to do is pass it a variable e.g. [firstname] and pull all information in the DB and display it.

Im using:
Visual Web Developer 2005
MS Access Database

    <script runat="server">
        Sub OnbtnLoginClicked(ByVal s As Object, ByVal e As EventArgs)
            
            Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("tblUsers.mdb") & ";"
            
            Dim mySQL As String = "SELECT * FROM tblUsers WHERE firstname= & txtFirstname"
            
            Dim MyConn As New OleDBConnection(strConn)
            Dim cmd As New OleDBCommand(mySQL, MyConn)
            MyConn.Open()
            
            Dim objReader As OleDbDataReader       
            objReader = cmd.ExecuteReader()
            
            While objReader.Read()
                
                Dim firstname As String = objReader("firstname")
                lblOutput.Text = firstname
            End While
            
            objReader.Close()
            MyConn.Close()
    
        End Sub
</script>

Also I am not sure which namespaces to use, I have trawled the MSDN website, but find it extremely difficult to find what im looking for.

(At the top of the .aspx page)

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace="System.Data.OleDB.OleDbDataReader" %>

Any help would be greatly appreciated.

Thanks

[stuartornum]

Anyone with an idea?

[mendhak]

Let's look at your query.

"SELECT * FROM tblUsers WHERE firstname= & txtFirstname"

Because the entire thing is in double quotes, this will be translated, for MS Access to be

SELECT * FROM tblUsers WHERE firstname = & txtFirstname

So MS Access is going to get confused. It is expecting a string with a name such as 'John', but instead you've passed it an ampersand and a crypting - what it assumes - column name.

"SELECT * FROM tblUsers WHERE firstname='" & txtFirstname &"';"

[stuartornum]

Hi Mendhak,

Thank you for the reply.

I have made the changes as you suggested,

Dim mySQL As String = "SELECT * FROM tblUsers WHERE firstname=" '& txtFirstname &'";"

now I have another error:

Syntax error (missing operator) in query expression 'firstname='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'firstname='.

Source Error: 

Line 26:             
Line 27:             Dim objReader As OleDbDataReader       
Line 28:             objReader = cmd.ExecuteReader()
Line 29:             
Line 30:             While objReader.Read()

Thanks again for your help.

[killer7k]

Dim mySQL As String = "SELECT * FROM tblUsers WHERE firstname=" '& txtFirstname &'";"

Dim mySQL As String = "SELECT * FROM tblUsers WHERE firstname="' & txtFirstname.text &'";"

[stuartornum]

Hi killer7k,

Sorry that didn't work either, I have also tried:

Dim input As String = txtFirstname.Text
Dim mySQL As String = "SELECT * FROM tblUsers WHERE firstname=" ' & input &'";"

Still no luck..

[mendhak]

Guys. Look at post #3 closely. Look at how I have created the string there. Look at the single quote and the double quote. If it still isn't obvious, just post back.

Hell, just copy and paste it.

[stuartornum]

mendhak,

I must say I am totally ashamed of myself. lol!

Thank you for your Help!!

All working hunky doory!