Problem with queries. Call for help.

J

johnmay1248

I am having a problem with queries running in this code When I use the
query "SELECT * FROM sample" the code runs and the data grid binds and shows
the contents of the sample table. If I change the query to filter the
results such as "SELECT * FROM sample WHERE (sales > 2000)" the code fails
on - objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection) with an unknown
error.


If I load the database up into access2000 and run the sql queries they run
as expected so I know they are valid queries of the data

I am a noob at asp.net data. Can anybody give me a hand?

The Code I am using is below followed by the error info with the stack
trace.

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

Imports System.Data.OleDb
Imports System.Data

Sub btnRunQuery_OnClick(ByVal Sender As Object, ByVal E As EventArgs)
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("NewDatabase.mdb") & ";")

strSQLQuery = "SELECT * FROM sample"

'strSQLQuery = "SELECT * FROM sample WHERE (sales > 2000)"

'strSQLQuery = "SELECT * FROM sample WHERE key_id=5"

'strSQLQuery = "SELECT * FROM sample WHERE last_name='Johnson'"

objCommand = New OleDbCommand(strSQLQuery, objConnection)

objConnection.Open()

objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

lblSQLCommandLabel.Text = strSQLQuery

EmployeeDataGrid.DataSource = objDataReader
EmployeeDataGrid.DataBind()
End Sub


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

Unknown
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: Unknown

Source Error:
Line 61: objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Stack Trace:

[OleDbException (0x80040e14): Unknown]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
Database101.WebForm1.btnRunQuery_OnClick(Object Sender, EventArgs E) in
c:\Database101\WebForm1.aspx.vb:61
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
 
S

sloan

What is the data type (int, decimal , string, text) of "sales" ?



johnmay1248 said:
I am having a problem with queries running in this code When I use the
query "SELECT * FROM sample" the code runs and the data grid binds and
shows
the contents of the sample table. If I change the query to filter the
results such as "SELECT * FROM sample WHERE (sales > 2000)" the code fails
on - objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection) with an unknown
error.


If I load the database up into access2000 and run the sql queries they run
as expected so I know they are valid queries of the data

I am a noob at asp.net data. Can anybody give me a hand?

The Code I am using is below followed by the error info with the stack
trace.

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

Imports System.Data.OleDb
Imports System.Data

Sub btnRunQuery_OnClick(ByVal Sender As Object, ByVal E As EventArgs)
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("NewDatabase.mdb") & ";")

strSQLQuery = "SELECT * FROM sample"

'strSQLQuery = "SELECT * FROM sample WHERE (sales > 2000)"

'strSQLQuery = "SELECT * FROM sample WHERE key_id=5"

'strSQLQuery = "SELECT * FROM sample WHERE last_name='Johnson'"

objCommand = New OleDbCommand(strSQLQuery, objConnection)

objConnection.Open()

objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

lblSQLCommandLabel.Text = strSQLQuery

EmployeeDataGrid.DataSource = objDataReader
EmployeeDataGrid.DataBind()
End Sub


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

Unknown
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: Unknown

Source Error:
Line 61: objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Stack Trace:

[OleDbException (0x80040e14): Unknown]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
Database101.WebForm1.btnRunQuery_OnClick(Object Sender, EventArgs E) in
c:\Database101\WebForm1.aspx.vb:61
System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Click to expand...
 
M

Manish

I tried the below code and it worked fine. could you please let me know the
Error from Event Viewer and also try to close the connection at the end.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String
objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("Nwind.mdb") & ";")
'strSQLQuery = "SELECT * FROM categories"
strSQLQuery = "SELECT * FROM categories WHERE (categoryID = 1)"
'strSQLQuery = "SELECT * FROM sample WHERE key_id=5"
'strSQLQuery = "SELECT * FROM sample WHERE last_name='Johnson'"
objCommand = New OleDbCommand(strSQLQuery, objConnection)
objConnection.Open()
objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
objCommand.CommandText = strSQLQuery
Me.GridView1.DataSource = objDataReader
Me.GridView1.DataBind()
End Sub

Regards,
Manish
www.ComponentOne.com

sloan said:
What is the data type (int, decimal , string, text) of "sales" ?



johnmay1248 said:
I am having a problem with queries running in this code When I use the
query "SELECT * FROM sample" the code runs and the data grid binds and
shows
the contents of the sample table. If I change the query to filter the
results such as "SELECT * FROM sample WHERE (sales > 2000)" the code fails
on - objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection) with an unknown
error.


If I load the database up into access2000 and run the sql queries they run
as expected so I know they are valid queries of the data

I am a noob at asp.net data. Can anybody give me a hand?

The Code I am using is below followed by the error info with the stack
trace.

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

Imports System.Data.OleDb
Imports System.Data

Sub btnRunQuery_OnClick(ByVal Sender As Object, ByVal E As EventArgs)
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("NewDatabase.mdb") & ";")

strSQLQuery = "SELECT * FROM sample"

'strSQLQuery = "SELECT * FROM sample WHERE (sales > 2000)"

'strSQLQuery = "SELECT * FROM sample WHERE key_id=5"

'strSQLQuery = "SELECT * FROM sample WHERE last_name='Johnson'"

objCommand = New OleDbCommand(strSQLQuery, objConnection)

objConnection.Open()

objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

lblSQLCommandLabel.Text = strSQLQuery

EmployeeDataGrid.DataSource = objDataReader
EmployeeDataGrid.DataBind()
End Sub


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

Unknown
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: Unknown

Source Error:
Line 61: objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Stack Trace:

[OleDbException (0x80040e14): Unknown]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
Database101.WebForm1.btnRunQuery_OnClick(Object Sender, EventArgs E) in
c:\Database101\WebForm1.aspx.vb:61
System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Click to expand...
Click to expand...
 
P

Peter Bromberg [C# MVP]

Try using a parameterized query instead of inline SQL statement. Its best
practices technique anyway.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com


johnmay1248 said:
I am having a problem with queries running in this code When I use the
query "SELECT * FROM sample" the code runs and the data grid binds and shows
the contents of the sample table. If I change the query to filter the
results such as "SELECT * FROM sample WHERE (sales > 2000)" the code fails
on - objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection) with an unknown
error.


If I load the database up into access2000 and run the sql queries they run
as expected so I know they are valid queries of the data

I am a noob at asp.net data. Can anybody give me a hand?

The Code I am using is below followed by the error info with the stack
trace.

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

Imports System.Data.OleDb
Imports System.Data

Sub btnRunQuery_OnClick(ByVal Sender As Object, ByVal E As EventArgs)
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("NewDatabase.mdb") & ";")

strSQLQuery = "SELECT * FROM sample"

'strSQLQuery = "SELECT * FROM sample WHERE (sales > 2000)"

'strSQLQuery = "SELECT * FROM sample WHERE key_id=5"

'strSQLQuery = "SELECT * FROM sample WHERE last_name='Johnson'"

objCommand = New OleDbCommand(strSQLQuery, objConnection)

objConnection.Open()

objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

lblSQLCommandLabel.Text = strSQLQuery

EmployeeDataGrid.DataSource = objDataReader
EmployeeDataGrid.DataBind()
End Sub


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

Unknown
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: Unknown

Source Error:
Line 61: objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Stack Trace:

[OleDbException (0x80040e14): Unknown]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
Database101.WebForm1.btnRunQuery_OnClick(Object Sender, EventArgs E) in
c:\Database101\WebForm1.aspx.vb:61
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running Stored Procedure in a Loop 7
not a member of 'ASP.default_aspx 6
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 3
Populate Dropdown from SQL Server 3
Database row counting -- HEEELLLPPPP!!! 7
No Data Exists? Weird field issue. 1
Need help with Datagrid 8
Help with FindControl method 10

Members online

Total: 340 (members: 2, guests: 338)
Robots: 425

Forum statistics

Threads
473,968
Messages
2,570,150
Members
46,697
Latest member
AugustNabo

Latest Threads

Top