Problem with queries. Call for help.

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.ExecuteCommandTextErrorHandli ng(Int32 hr)
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleRe sult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavio r behavior,
Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior
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.IPostBackEven tHandler.RaisePostBackEvent(String
eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()




------------------------------------------------------------ --------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.6060; ASP.NET
Version:1.0.3705.6060

Re: Problem with queries. Call for help.

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



"johnmay1248" wrote in message
news:z86kj.58737$N67.27837@bignews5.bellsouth.net...
>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.ExecuteCommandTextErrorHandli ng(Int32 hr)
>
> System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleRe sult(tagDBPARAMS
> dbParams, Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavio r behavior,
> Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior
> 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.IPostBackEven tHandler.RaisePostBackEvent(String
> eventArgument)
> System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
> sourceControl, String eventArgument)
> System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
> System.Web.UI.Page.ProcessRequestMain()
>
>
>
>
> ------------------------------------------------------------ --------------------
> Version Information: Microsoft .NET Framework Version:1.0.3705.6060;
> ASP.NET
> Version:1.0.3705.6060
>
>

Re: Problem with queries. Call for help.

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" wrote:

> What is the data type (int, decimal , string, text) of "sales" ?
>
>
>
> "johnmay1248" wrote in message
> news:z86kj.58737$N67.27837@bignews5.bellsouth.net...
> >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.ExecuteCommandTextErrorHandli ng(Int32 hr)
> >
> > System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleRe sult(tagDBPARAMS
> > dbParams, Object& executeResult)
> > System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
> > System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavio r behavior,
> > Object& executeResult)
> > System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior
> > 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.IPostBackEven tHandler.RaisePostBackEvent(String
> > eventArgument)
> > System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
> > sourceControl, String eventArgument)
> > System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
> > System.Web.UI.Page.ProcessRequestMain()
> >
> >
> >
> >
> > ------------------------------------------------------------ --------------------
> > Version Information: Microsoft .NET Framework Version:1.0.3705.6060;
> > ASP.NET
> > Version:1.0.3705.6060
> >
> >
>
>
>

RE: Problem with queries. Call for help.

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" wrote:

> 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.ExecuteCommandTextErrorHandli ng(Int32 hr)
> System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleRe sult(tagDBPARAMS
> dbParams, Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavio r behavior,
> Object& executeResult)
> System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Command Behavior
> 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.IPostBackEven tHandler.RaisePostBackEvent(String
> eventArgument)
> System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
> sourceControl, String eventArgument)
> System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
> System.Web.UI.Page.ProcessRequestMain()
>
>
>
>
> ------------------------------------------------------------ --------------------
> Version Information: Microsoft .NET Framework Version:1.0.3705.6060; ASP.NET
> Version:1.0.3705.6060
>
>
>