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
I will learn ASP.NET if it kills me!
You can teach an old dog new tricks, but it is very hard work!
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
I will learn ASP.NET if it kills me!
You can teach an old dog new tricks, but it is very hard work!
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)
I will learn ASP.NET if it kills me!
You can teach an old dog new tricks, but it is very hard work!
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?"
I will learn ASP.NET if it kills me!
You can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Reader produces no results at all
Jun 12, 2012 04:10 PM|LINK
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 IfYou can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 12, 2012 05:41 PM|LINK
I did find a mistake in my code.
comm.Parameters.Add(New SqlClient.SqlParameter("@EmpNo", SqlDbType.Char, 10))However it still produces no results!
You can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 12, 2012 08:17 PM|LINK
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?
You can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 13, 2012 07:09 PM|LINK
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?
You can teach an old dog new tricks, but it is very hard work!
Vardhanmm
Member
2 Points
6 Posts
Re: Reader produces no results at all
Jun 13, 2012 07:21 PM|LINK
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.
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 13, 2012 08:58 PM|LINK
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) NextYou can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 14, 2012 12:21 PM|LINK
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)You can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 14, 2012 12:32 PM|LINK
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?"
You can teach an old dog new tricks, but it is very hard work!
gene7135
Member
9 Points
64 Posts
Re: Reader produces no results at all
Jun 15, 2012 08:42 PM|LINK
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.
You can teach an old dog new tricks, but it is very hard work!
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Reader produces no results at all
Jun 18, 2012 02:00 AM|LINK
Please remove this:)
I notice a very serious problem——Since you are now using Access db，so please use OleDbConnection+OleDbCommand instead of SQL ones……