Reader produces no results at all

The following code seems to produce nothing.

No errors, no exceptions, no results as all.

Can some please tell me where I am goofing up? Thanks!!

If it makes a difference, I am using ASP.Net 2.0

The Connection is to a Access DB.

Dim strConnection As String = ConfigurationManager.ConnectionStrings("clocknoConnectionString").ToString()
            Dim conn As New SqlClient.SqlConnection(strConnection)
            Dim comm As New SqlClient.SqlCommand("SELECT [EmpNo], [Name] FROM [clockno] WHERE [EmpNo] = @EmpNo", conn)

            comm.Parameters.Add(New SqlClient.SqlParameter("@EmpNo", OleDb.OleDbType.Char, 10))
            comm.Parameters("@EmpNo").Value = txtEmpNo.Text

            conn.Open()

            Dim reader As SqlClient.SqlDataReader = comm.ExecuteReader()

            If reader.Read And reader.HasRows Then
                lblEmpNo.Text = reader("EmpNo")
                lblEmpName.Text = reader("Name")

            Else
                Response.Write("Clock # Not Found. Click back on your Browser")
            End If

Re: Reader produces no results at all

I did find a mistake in my code.

comm.Parameters.Add(New SqlClient.SqlParameter("@EmpNo", SqlDbType.Char, 10))

However it still produces no results!

Re: Reader produces no results at all

Here is the connection string for my Access DB.

<connectionStrings>
        <add name="clocknoConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Mode=Read;Data Source=E:\Web\rhi\databases\clockno.mdb"
            providerName="System.Data.OleDb"  />
    </connectionStrings>

Should I be using a different connection string now?

If so, why does this connection string work fine on the ASPX page when used by a SQLDataSource?

Re: Reader produces no results at all

No one has given any suggestions.

Have I posted this int the wrong forum category?

Is there a more appropriate category to post this question?

Re: Reader produces no results at all

Hi,

can you chage your code like instead of using data reader use data set. becuase u can find is it reader problem or data fetching problem.

Looser.

Re: Reader produces no results at all

Here is the code I used to try it as a datset.

No change in the output though. (which is nothing)

Dim adapter As New SqlClient.SqlDataAdapter
            Dim ds As New DataSet
            Dim tables As DataTable
            Dim i As Integer
            Dim sql As String

            Dim connetionString As String = ConfigurationManager.ConnectionStrings("clocknoConnectionString").ToString()
            Dim SqlConnection As New SqlClient.SqlConnection(connetionString)
            Dim SqlCommand As New SqlClient.SqlCommand("SELECT [EmpNo], [Name] FROM [clockno] WHERE [EmpNo] = @EmpNo", SqlConnection)

            SqlCommand.Parameters.Add(New SqlClient.SqlParameter("@EmpNo", SqlDbType.Text))
            SqlCommand.Parameters("@EmpNo").Value = txtEmpNo.Text

            SqlConnection.Open()
            adapter.Fill(ds, "SQL Temp Table")
            adapter.Dispose()
            SqlCommand.Dispose()
            SqlConnection.Close()


            For Each tables In ds.Tables
                Response.Write(tables.TableName)
            Next

Re: Reader produces no results at all

For Troubleshooting, I took out the Try - Catch .

I tried displaying the variables on screen to troubleshoot.

Response.Write(connetionString)

Works Fine

Response.Write(SqlConnection)

I now get a error.

Server Error in '/' Application.

Keyword not supported: 'provider'.

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.ArgumentException: Keyword not supported: 'provider'.

Line 78:         'Find Employee in database
Line 79:         '        Try
Line 80: Dim SqlConnection As New SqlClient.SqlConnection(connetionString)

Line 81:         Dim SqlCommand As New SqlClient.SqlCommand("SELECT [EmpNo], [Name] FROM [clockno] WHERE [EmpNo] = @EmpNo", SqlConnection)
Line 82:         Response.Write(SqlConnection)

Re: Reader produces no results at all

The more I think about this, it seems the provider connection strig is setup wrong to use sqlconnection with a Access db.

If that's the problem, the question still remains from post 3 above, "why does this connection string work fine on the ASPX page when used by a SQLDataSource?"

Re: Reader produces no results at all

Since I have made virtually no proggress since the orginal post, I am going to move the database over to a MySQL server I have access to.

I have to believe I will have better luck there.

Re: Reader produces no results at all

gene7135
Mode=Read;

Please remove this:)

I notice a very serious problemSince you are now using Access dbso please use OleDbConnection+OleDbCommand instead of SQL ones