locked
One connection and 3 Queries walk into a bar..... RRS feed

  • Question

  • User-1988569308 posted

    Hey guys,

    I have started to develop a website using ASP.NET and VB.NET to build a Website. Let me give you a snippet of code below first:

     

    adoCon = Server.CreateObject("ADODB.Connection")
            adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=SCA.mdb;Persist Security Info=True")
            rsTable = Server.CreateObject("ADODB.Recordset")
    
            strSQL = "SELECT SUM([Calls Ans]) AS MTDCallsAns FROM DATA WHERE (USER = '" & lbAgentUser.Text & "') AND ([Date] BETWEEN #" & tbStartDate.Text & "# AND #" & tbStopDate.Text & "#)"
            rsTable.Open(strSQL, adoCon)
    lbMTDCallsAns.Text = rsTable.Fields.Item("MTDCallsAns").Value
                MTDCallsAnsVB = rsTable.Fields.Item("MTDCallsAns").Value
                rsTable.Close()
    
    

    The above code pretty much creates a new connection and connects to my database. I have no issues with this code. I am able to do several queries against my access database with no problems. The one thing I want to improve is, each time I want to do a new query, I have to close the last one and open a new  Recordset. I am wanting to complete all of the needed SQL queries in one connection. If this is NOT possible, what would be the most efficient way to query my access database? When this specific page loads, I am currently doing 15 SELECT queries (15 open and closes). This there a better way to do this?

    Thanks for the help!!

    Tuesday, July 12, 2011 9:45 PM

Answers

  • User-1199946673 posted

    I have started to develop a website using ASP.NET and VB.NET

    But you're using old VB (ADO) code....

    I have no issues with this code. I am able to do several queries against my access database with no problems.

    Perhaps, because sooner or later you will be in trouble, because you obviously never heard of SQL injections!

    Start using parameterized queries and ADO.NET

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    I am currently doing 15 SELECT queries (15 open and closes). This there a better way to do this?

    First of all, you don't need to close and reopen the database connection between executing the queries. And it all depands which information you need on the page, you might be able to do multiple (or all) queries into 1 using joins and/or subquery's, but without the queries it is impossble to tell....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2011 5:29 AM

All replies

  • User-401610792 posted

    Hey,

    You don't really need to keep opening/closing connections, just call for different queries, ie:

    strSQL = "SELECT SUM([Calls Ans]) AS MTDCallsAns FROM DATA WHERE (USER = '" & lbAgentUser.Text & "') AND ([Date] BETWEEN #" & tbStartDate.Text & "# AND #" & tbStopDate.Text & "#)"
            rsTable
    .Open(strSQL, adoCon)
    lbMTDCallsAns
    .Text = rsTable.Fields.Item("MTDCallsAns").Value
               
    MTDCallsAnsVB = rsTable.Fields.Item("MTDCallsAns").Value

    strSQL2 = "SELECT * from whetever"

            rsTable.Open(strSQL2, adoCon)
    strSQL3 = "SELECT * from whetever2"

            rsTable.Open(strSQL2, adoCon)

    Then when you are done close the connection.

    Hope that helps,

    Cheers,

    Alex


    Tuesday, July 12, 2011 9:54 PM
  • User-1988569308 posted

    Thanks for the quick reply! When I tried doing new queries without closing the connection I got errors talking about how it cannot open a file already open.

    Tuesday, July 12, 2011 10:00 PM
  • User-401610792 posted

    Ah,

    sorry,

    that trick works with mssql. Try this:

    http://www.codingforums.com/archive/index.php/t-176159.html

    Cheers,

    Alexi

    Tuesday, July 12, 2011 10:10 PM
  • User-1988569308 posted

    Basically, the way I am doing it now is perfectly fine? That is what I am gathering from that link you gave me. How will my database hande 15-20 users running this specific page at once?

    Tuesday, July 12, 2011 10:19 PM
  • User-401610792 posted

    Not really, you can run queries one after another with a semi-colon in-between then navigate through the results;

    second, have you tried to join you tables?

    Tuesday, July 12, 2011 10:37 PM
  • User-1988569308 posted

    Joining the queries with a ; (semicolon) only works with SQL as well :( I do not know what you mean by "joining the tables"

    Tuesday, July 12, 2011 10:39 PM
  • User-401610792 posted

    Like INNER JOIN, etc?

    Tuesday, July 12, 2011 10:45 PM
  • User-1988569308 posted

    Would it be possible to just do a "SELECT * FROM DATA AS varData" and store that into memory and then pull  data from the VarData?

    Tuesday, July 12, 2011 10:56 PM
  • User-1988569308 posted

    Let me rephrase this: What is the best way to do 15 - 30 queries in one VB.NET procedure?

    Tuesday, July 12, 2011 11:13 PM
  • User-1199946673 posted

    I have started to develop a website using ASP.NET and VB.NET

    But you're using old VB (ADO) code....

    I have no issues with this code. I am able to do several queries against my access database with no problems.

    Perhaps, because sooner or later you will be in trouble, because you obviously never heard of SQL injections!

    Start using parameterized queries and ADO.NET

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    I am currently doing 15 SELECT queries (15 open and closes). This there a better way to do this?

    First of all, you don't need to close and reopen the database connection between executing the queries. And it all depands which information you need on the page, you might be able to do multiple (or all) queries into 1 using joins and/or subquery's, but without the queries it is impossble to tell....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2011 5:29 AM
  • User-1199946673 posted

    Would it be possible to just do a "SELECT * FROM DATA AS varData" and store that into memory

    In ASP.NET, there are many options to store data. It all depands what you want to do exactly to determine the best place....

    Wednesday, July 13, 2011 5:31 AM
  • User3866881 posted

    Hello ChrisKader:)

    ADO, compared with ADO.NET, seems to be a little overdue. I think you should choose the latest and new technology in NET to relaese the power of that.

    In fact, OleDbConnection and its series of classes having relations with that are helpful to simplify accessing Access DB. You can create a SqlCommand and use SqlDataReader to read out the data, if you want to change the sql select statement, just close the DataReader and reset the "CommandText" for the SqlCommand and call "ExecuteReader()" again to get the result, and then use DataReader to read things out.

    Thx

    Thursday, July 14, 2011 9:08 PM