How to execute multiple SQL queries in a loop cycle

Question

i'm stuck on how to do the task mentioned on the title, you see, a couple of days, maybe weeks i asked how to create some sort of search method to include an entire month, well it's something related to that task that i'm stuck in again -.-'

i came across with the searching method but i can't figure out how to ask the database to bring all the rows matching the search, for example, let's say i enter a search for the current month of november of the current year 2019, and i inserted 5 rows, i need the search to return these five rows inserted this month of the current year.

this is what i'm currently trying with no result

the variables mentioned below: 'num_month' and 'num_year' are inserted by search comboboxes in another page, the 'cn_body' thing is the connection string page which is stored somewhere else

'CREATION OF COUNTER FOR MONTHS OF 30 DAYS
    '------------------------------------------------------------------------------------
Set rs_Results = Server.CreateObject("ADODB.Recordset") -(creation of recordset for opening sql query)

    If month_num = 4 Or month_num = 6 month_num = 9 Or month_num = 11 Then -(checks the specific months with 30 days)

    For day_counter=1 To 30 -(creates a counter for days from 1 to 30 since is the case for months 

of 30 days only)

        search_date = cDate(day_counter &"-"& month_num &"-"& year_num) -(inserts the day counter along with the
 month and year counter separating them by "-" making it a valid date after the conversion)

        converted_date= Clng(search_date) -(converts the date of the variable 'search_date' to numbers)
    strSQL_CIP_Date="SELECT * FROM data_storage WHERE creation_date=" & converted_date 

    'cn_body.execute (strSQL_CIP_Date) -(when using this method on the portion of page that shows the results it throws 
an error which is: 'ADODB.Recordset error '800a0e78' Operation not allowed if the object is closed.', in this case the 
query is executed the times it should, but the results aren't shown because of the mentioned error)

        rs_Results.open strSQL_CIP_Date,cn_body,1,1 

(cn_body is the string connection which is stored into another page, 
    what i'm doing here is opening the sql query using the recordset which is the method i used for other queries without bigger issues,
    but for some reason here it is not working, only runs 2 times then it appears this error: 
    'ADODB.Recordset error '800a0e79'

'Operation not allowed if the object is open')

        response.write day_counter & " " & strSQL_CIP_Date & "<br><br><br><br>" 
-(prints the query with the converted date to ask to the database)
    Next
End If

i had also tried the looping method with, do, do while, for, if, do while not, loop until, while loop and many more, and had the same result.

so there you have it, i don't know the causes of any of the errors that i mentioned on the code, any kind of help would be great, ask whatever you feel the need to, thanks in advance

hi @Lankymart currently i'm trying to execute the query in a For cycle, what i need is to execute the query for every day of the month and retrieve which ones exist in the database — RUCK FULES
– RUCK FULES, Commented Nov 29, 2019 at 15:29
Does this answer your question? How to return month and year only except days from date? — user692942
– user692942, Commented Dec 4, 2019 at 1:54

Étienne Laneville · Accepted Answer · 2019-11-30 17:28:14Z

1

You can simply query for all records BETWEEN two dates instead of running a query for each date in a loop:

strSQL_CIP_Date = "SELECT * FROM data_storage WHERE creation_date BETWEEN '" & strStartDate & "' AND '" & strEndDate & "'"

Depending on your database provider, you might need to use # instead of ' as your date delimiter in your query:

strSQL_CIP_Date = "SELECT * FROM data_storage WHERE creation_date BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

You can also use just the month in your query:

strSQL_CIP_Date = "SELECT * FROM data_storage WHERE MONTH(creation_date) = 11"

edited Nov 30, 2019 at 17:28

answered Nov 30, 2019 at 17:21

Étienne Laneville

5,0815 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user692942 Over a year ago

Recommending an approach which involves no protection such as using a parameterised query etc shouldn’t be encouraged. Both answers fall short on this front.

RUCK FULES Over a year ago

thank you @Lankymart your first example made the work, it was way easier than i thought it was, thank you very much!

Stephen R Over a year ago

@black-soul That's essentially the same answer you got in your earlier question. Not sure how you got here from there in the intervening weeks! stackoverflow.com/a/58881543/339440

Daniel Nordh · Accepted Answer · 2019-12-06 12:42:02Z

1

From what I gather, since you're converting the dates to a number, the dates in the database aren't in a date-format? If your database connection is already open, this is how I would set it up.

Edited function for parameterized query. Plus added a function to make parameterized calls much easier.

Set Cn = Server.CreateObject("ADODB.Connection")
Cn.Open CONNECTION_INFO

function dbPara(sql, params)
    dim cmd
    set cmd = Server.CreateObject("ADODB.Command")
    with cmd
        .CommandText = sql
        set .ActiveConnection = cn
    end with

    if NOT isEmpty(params) then
        set rs = cmd.execute(, params)
    else
        set rs = cmd.execute()
    end if

    set dbPara = rs
end function

strSQL_CIP_Date = "SELECT * FROM data_storage WHERE creation_date BETWEEN ? AND ?"
set strRS = dbpara(strSQL_CIP_Date,array(converted_date, converted_date+29))
do until strRS.eof
   'insert code for each entry here
   strRS.movenext
loop

edited Dec 6, 2019 at 12:42

answered Dec 2, 2019 at 8:34

Daniel Nordh

3903 silver badges15 bronze badges

7 Comments

user692942 Over a year ago

Recommending an approach which involves no protection such as using a parameterised query etc shouldn’t be encouraged. Both answers fall short on this front.

Daniel Nordh Over a year ago

@lankymart You are absolutely correct and people need to use these answers at the own discretion. I merely wanted to help point in the right direction. Feel free to add an answer that you feel is more appropriate. Would help more than saying what's wrong with the current answers. :) Seeing how questions should be answered is great advice for both the OP and for people like me.

user692942 Over a year ago

There is already plenty of examples of using parameterised queries in ADODB already on Stack Overflow.

Daniel Nordh Over a year ago

@Lankymart Does this look better? Let me know if you see any problems with it. I added in a function for parameterized queries to make them easier to use.

user692942 Over a year ago

You know you don't have to create an ADODB.Connection if you pass ActivieConnection a valid connection string it will instantiate and open the connection for you and dispose of it when the command is released. So .ActiveConnection = CONNECTION_INFO would also work.

|

Collectives™ on Stack Overflow

How to execute multiple SQL queries in a loop cycle

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related