Problem with queries. Call for help.

Discussion in 'ASP .Net' started by johnmay1248, Jan 18, 2008.

  1. johnmay1248

    johnmay1248 Guest

    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()




    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:1.0.3705.6060; ASP.NET
    Version:1.0.3705.6060
     
    johnmay1248, Jan 18, 2008
    #1
    1. Advertising

  2. johnmay1248

    sloan Guest

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



    "johnmay1248" <> wrote in message
    news:z86kj.58737$...
    >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()
    >
    >
    >
    >
    > --------------------------------------------------------------------------------
    > Version Information: Microsoft .NET Framework Version:1.0.3705.6060;
    > ASP.NET
    > Version:1.0.3705.6060
    >
    >
     
    sloan, Jan 18, 2008
    #2
    1. Advertising

  3. johnmay1248

    Manish Guest

    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$...
    > >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()
    > >
    > >
    > >
    > >
    > > --------------------------------------------------------------------------------
    > > Version Information: Microsoft .NET Framework Version:1.0.3705.6060;
    > > ASP.NET
    > > Version:1.0.3705.6060
    > >
    > >

    >
    >
    >
     
    Manish, Jan 18, 2008
    #3
  4. 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.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()
    >
    >
    >
    >
    > --------------------------------------------------------------------------------
    > Version Information: Microsoft .NET Framework Version:1.0.3705.6060; ASP.NET
    > Version:1.0.3705.6060
    >
    >
    >
     
    Peter Bromberg [C# MVP], Jan 19, 2008
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick

    Help in solving queries.......

    Patrick, Jan 26, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    587
    John Vinson
    Jan 27, 2005
  2. Its_Me_SunnY

    Help Me in solving the queries...SUNNY

    Its_Me_SunnY, May 24, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    460
    Its_Me_SunnY
    May 24, 2005
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,549
    Crutcher
    Feb 26, 2006
  4. Biranchi Narayan Panda
    Replies:
    0
    Views:
    573
    Biranchi Narayan Panda
    Feb 21, 2010
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    372
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page