locked
getting Data from my accdb's query view RRS feed

  • Question

  • User254942635 posted

    I am trying to put a pie chart in my asp page and I have created a query in my database that has the data I need.  I can not for the life of me figure out how to call that query.  I can access the tables no problems, but can't get the query.  I have been trying to google it but Access, query, view are way to common of words. 

    Wednesday, December 21, 2011 11:46 PM

Answers

  • User254942635 posted

    I have it working now.  What I ended up doing was removing all the data Access Controls.  I added one and that allowed me to use the Queries.  When I added the second one it would not.  I don't know why but it appeered to be pointing to a database that didn't exist.  I deleted that control and copied and Paisted the first control to create a second one and that allowed me to access the Queries in the database.  I still don't know whats going on, but I have moved passed the problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 30, 2011 10:57 AM

All replies

  • User-1199946673 posted

    and I have created a query in my database that has the data I need

    You mean you stored the query in Access, and named it for example query1? In that case, you can call the query just like an SQL stored procedure. In the CommandText, you set the name of the stored query, and then set the CommandType Property to Data.CommandType.StoredProcedure

    Using conn As New OleDbConnection("your_connectionstring")
      Using cmd As New OleDbCommand("query1", conn)
        cmd.CommandType = CommandType.StoredProcedure
        conn.Open()
        Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        While rdr.Read()
          'Do something
        End While
      End Using
    End Using

    Anothor option is to copy and paste the SQL string foirm Access, and use that string as the CommandText. Then the CommandType is Text (the default value, so you don't necessarily need to specify that.

    Using conn As New OleDbConnection("your_connectionstring")
      Using cmd As New OleDbCommand("SELECT ...............", conn)
        cmd.CommandType = CommandType.Text
        conn.Open()
        Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        While rdr.Read()
          'Do something
        End While
      End Using
    End Using
    Thursday, December 22, 2011 3:35 AM
  • User254942635 posted

    Below are the two controls I am trying to use, but neither one will allow me to get the data.  is this something I should be doing in C#?

    <asp:AccessDataSource ID="AccessDataSource2" runat="server"

     DataFile="~/App_Data/cbf.accdb"

     SelectCommand="SELECT DISTINCTROW customer.custsales, Count(*) AS [Count Of customer], qcustcnt.CountOfcustsales, [count of customer]/[countofcustsales] AS salespercent

                FROM customer, qcustcnt

                GROUP BY customer.custsales, qcustcnt.CountOfcustsales;">

     </asp:AccessDataSource>


    <telerik:RadChart ID="RadChart2" runat="server"

           DataSourceID="AccessDataSource2">

    </telerik:RadChart>

    The Microsoft Access database engine cannot find the input table or query
    'qcustcnt'. Make sure it exists and that its name is spelled correctly.

    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: The Microsoft Access database engine
    cannot find the input table or query 'qcustcnt'. Make sure it exists and that
    its name is spelled correctly.

    Source Error:

    An unhandled exception was generated during the execution of the
    current web request. Information regarding the origin and location of the
    exception can be identified using the exception stack trace below.

    Stack Trace:

    [OleDbException (0x80040e37): The Microsoft Access database engine cannot find the input table or query 'qcustcnt'. Make sure it exists and that its name is spelled correctly.]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1081372
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
       System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +116
       System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1618
       System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +76
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
       Telerik.Web.UI.RadChart.PerformSelect() +240
       Telerik.Web.UI.RadChart.DataBind() +127
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
       System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
       Telerik.Web.UI.RadDataBoundControl.OnPreRender(EventArgs e) +36
       Telerik.Web.UI.RadChart.OnPreRender(EventArgs e) +37
       System.Web.UI.Control.PreRenderRecursiveInternal() +103
       System.Web.UI.Control.PreRenderRecursiveInternal() +175
       System.Web.UI.Control.PreRenderRecursiveInternal() +175
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496
    

    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET
    Version:4.0.30319.237
    <!-- [OleDbException]: The Microsoft Access database engine cannot find the input table or query 'qcustcnt'. Make sure it exists and that its name is spelled correctly. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at Telerik.Web.UI.RadChart.PerformSelect() at Telerik.Web.UI.RadChart.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) at Telerik.Web.UI.RadDataBoundControl.OnPreRender(EventArgs e) at Telerik.Web.UI.RadChart.OnPreRender(EventArgs e) at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) [HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown. at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.default_aspx.ProcessRequest(HttpContext context) in c:\Users\Stephan\AppData\Local\Temp\Temporary ASP.NET Files\root\d1959ce9\e4106338\App_Web_fcavgzsf.0.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) -->

    Thursday, December 22, 2011 9:49 AM
  • User3866881 posted

    Hello Stevemiske:)

    Please copy the whole complete sql statement into the Access and have a try to check whether you've got the syntax error,or whether the tables exist or not。

    Best reguards!

    Friday, December 23, 2011 8:09 PM
  • User254942635 posted

    I think you missed the part where I said i copied them out of Access and pasted them into my code.  It seems like the VS2010 data access comp. can use the queries that are in an Access database.  I might have to do this with two calls, one to get my number of records and one to get the data I need to calculate against.

    Saturday, December 24, 2011 4:32 PM
  • User3866881 posted

    I think you missed the part where I said i copied them out of Access and pasted them into my code.

    Hello:)

    It's hans_v said instead of you,though I didn't see that……


    Anothor option is to copy and paste the SQL string foirm Access

    And now,I think

    I might have to do this with two calls, one to get my number of records and one to get the data I need to calculate against.

    Is there anything wrong with that?Welcome your feedback and show us your codes?

    Best reguards!

    Saturday, December 24, 2011 8:24 PM
  • User254942635 posted

    <asp:AccessDataSourceID="AccessDataSource2"runat="server"

    DataFile="~/App_Data/cbf.accdb"

    SelectCommand="SELECT DISTINCTROW customer.custsales, Count(*) AS [Count Of customer], qcustcnt.CountOfcustsales, [count of customer]/[countofcustsales] AS salespercent

                FROM customer, qcustcnt

                GROUP BY customer.custsales, qcustcnt.CountOfcustsales;">

    </asp:AccessDataSource>


    <telerik:RadChartID="RadChart2"runat="server"

           DataSourceID="AccessDataSource2">

    </telerik:RadChart>

    I should be able to make the above call and get everything I need in one call.  I don't understand why I can't call querys that reside in an access database from a web page.  Could it be a driver issue?  When I look at the database from VS2010 I can see the tables and the querys, I just can't do anything with the querys.

    Tuesday, December 27, 2011 12:15 PM
  • User3866881 posted

    So you mean that you can really find correct result in the Access Query window,but in the RadChart you cannot?

    Is it related to telerik control?Or maybe there's something you've missed for that?

    Have you checked in the forum of Telerik at:

    http://www.telerik.com/community/forums.aspx

    Tuesday, December 27, 2011 9:06 PM
  • User254942635 posted

    I get data in access, but not in the AccessDataSource I am beginning to think I am seeing two diffrent databases. 

    Tuesday, December 27, 2011 11:00 PM
  • User-1199946673 posted

    The AccessDataSource doesn't support .accdb (Access 2007) files, because it uses the Jet.OleDb.4.0 dirver. However, while not documented on MSDN, in .NET 4, it does support .accdb files. But when using these files, you must realize that the ACE.OleDb.12.0 driver needs to be installed on the server (it is not installed on any server by default).

    Although this not explains the errors you describe, can you try to transform your .accdb file into an .mdb file, and see if that works?

    Wednesday, December 28, 2011 3:58 AM
  • User1957418580 posted

    To be sure, can you make it simple and change the state ment to just "Select * from qcustcnt" - and maybe try the code Hans_v suggested in the earlier post, so that we're not having any bind issues.  Let us know if it works or if there is a new error with just code.  Also, I would run Process Monitor to make sure it's using the file you are expecting.

    Wednesday, December 28, 2011 6:51 AM
  • User254942635 posted

    I have it working now.  What I ended up doing was removing all the data Access Controls.  I added one and that allowed me to use the Queries.  When I added the second one it would not.  I don't know why but it appeered to be pointing to a database that didn't exist.  I deleted that control and copied and Paisted the first control to create a second one and that allowed me to access the Queries in the database.  I still don't know whats going on, but I have moved passed the problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 30, 2011 10:57 AM