Solved: Create loop for insert query

[better]

I managed to create a looping insert in my ASP application by using code something like this:

WHILE Counter <> 0
con.execute
Counter = Counter - 1
Wend

I want to simply pass the counter value to a stored insert query along with the values to be inserted. How do I create a loop within a qeuery in access? I've tried using WHILE @Counter <> 0, but it expects select, insert, update, or delete. Should this be done in a query, or does this need a macro, module, or something like that?

[Norie]

As far as I know this would need to be done in code.

What are you actually trying to do?

[better]

Okay, let me give you the full workflow...

User submits values for (Location) and (NumDoors) in a form on my ASP application.
ASP app passes (JobName, Location, NumDoors, Door, and DefaultID) to the database.

Database needs to insert (JobName, Location, Door, DefaultID) into table {Doors}, (NumDoors) times. I am intentionally creating multiple duplicate records at this stage.

For clarification, this is a proposal development application, that will in later stages be used for job costing and project scheduling. At this stage, I only want the salesperson to specify how many doors at each location. In the next stage, the salesperson or project manager will specify what type (DefaultID) each door will be, and may then modify the "default" values for an individual door, which will need to create a new entry in {DoorDefaults} as mentioned in my other thread. The objective of the table structure and relationships in my other thread is to allow the PM or salesperson to modify the 20 someodd door values by simply selecting a value-set (defaultID) from a menubox.

Ultimately, the application will pull the information from {Doors, DoorDefaults (and eventually other)} tables to compile an equipment list and project schedule.

Hopefully you're less confused about what I'm trying to do than I am now!

Thanks!

Bart

[Norie]

Bart

Any chance of some sample data?

[XLGibbs]

You need to cycle through the recordset?


Dim objRS as Recordset, dim strCount as Long, dim x as long
Set objRS = New Recordeset



strSQL = "Select * From [ASPTable]"

objRS.Open strSQL


strSQL2 = "Insert Into [DestTable] ([JobName],[Location],[Door],[DefaultID]) " _
"Select [JobName], [Location], [Door], [DefaultID]" _
"From [ASP] Table] where [DefaultID] =" & objRS![DefaultID]

strCount = 99999 '<=====Intentionally large number
Do While Not objRS.EOF

If strCount <> objRS![NumDoors] Then strCount = objRS![NumDoors]
For x = 1 to strCount
DoCmd.RunSQL strSQL2
Next x
End if

Loop




Something like that would work...

[better]

Here's some sample data...

Bart

[better]

XLGibbs,

Ok, I think I understand at least some of that. Where would I put it, in a query, a module? I know how to call a query from asp, but I don't know anything about modules, other than that you can put vba in them.

Bart

[XLGibbs]

YOu are doing this in Access right? How does the ASP data get to you? If it ends up in a table in Access....I can help you incorporate something like that loop into your project.

I don't have a chance right to now to look at the data sample...

Please clarify..

1.How you obtain the data mentioned in your initial post and in what format it comes in...
2. Is there an existing Access database that contains the other information, such as the proposed desination table requiring the insert rows?

[better]

Yes, I am doing this in Access, and I do have a live database. The sample data above is an export of the two tables I'm working with right now, and the query is below. Here's how I'm getting data into the database:

I have a query called "example" with the following SQL:

INSERT INTO doors ( JobName, Location, Door, DefaultID )
VALUES ([@JobName], [@Location], [@DoorName], [@DefaultID]);

and I have an ASP page with the following code, being fed values from the form on the previous page by the POST method:

<%
Dim VarJobName
Dim VarLocation
Dim VarDoorName
Dim VarDefaultID
Dim VarCommand
Dim VarCounter
VarJobName = Request.Form("JobName")
VarLocation = Request.Form("Location")
VarDoorName = "Unnamed " & Request.Form("Location") & " Door"
VarDefaultID = Request.Form("DefaultID")
VarCommand = "example '" & VarJobName & "', '" & VarLocation & "', '" & VarDoorName & "', " & VarDefaultID
VarCounter = Request.Form("DoorNo")
%>
<%
Dim connStr
connStr = MM_ProjectDb_STRING
Dim con
Set con = Server.CreateObject("ADODB.Connection")
con.Open connStr
%>
<% While ((VarCounter <> 0)) %>
<%
con.Execute VarCommand
%>
<%
VarCounter = VarCounter-1
Wend
%>

My ASP code is currently creating a loop, by means of the <% While ((VarCounter <> 0)) %> statement, around the <%
con.Execute VarCommand
%>, which executes the stored query and passes the required variables.

The following form values:

Request.Form("JobName") = BEtter Audio
Request.Form("Location") = Lobby
Request.Form("DoorNo") = 3
Request.Form("DefaultID") = 1

will insert 3 records with column values of:
(BEtter Audio, Lobby, Unnamed Lobby Door, 1)

This all works well enough with a webserver under no load and directly on the network, but I'm concerned that it will lag things down in a real deployment, so I want to perform the looping in the database, so the data only need traverse the network once. We'll have salespeople and Project Managers using this application over PDA's and such, so I want to keep it from being too bandwitdh intensive.

Since I'm using VB in my ASP application, I'm guessing the same basic structure will work inside the database, and that I basically just need to create and execute from the ASP app, a stored query in the database that calls a procedure with a WHILE statement that encapsulates a call to the actual INSERT query, or something like that.

As you can tell, I'm still pretty new at this, so please excuse my ignorance. I really appreciate the help!

http://vbaexpress.com/forum/images/smilies/notworthy.gif Bart

[XLGibbs]

Well, I am not familiar to much with interaction with the ASP...so I think you are on the right track, if the existing method works.

It would make more sense to simply bring in a temporary recordset from the ASP one time, then do the necessary updates in Access rather than deal with the While loop...

[better]

Okay, I'm trying to get my head around this. Help me out...


You need to cycle through the recordset?


Dim objRS as Recordset, dim strCount as Long, dim x as long
Set objRS = New Recordeset
strSQL = "Select * From [ASPTable]"
objRS.Open strSQL



I'm presuming that I've already created a query that creates the [ASPTable] with the variables (to include NumDoors, which will not be inserted into the destination table) I pass when I call it from my ASP page. So this first section of your code (in a module I call from that same query?) performs a SELECT * FROM ASPTable query, and creates objRS with the results?





strSQL2 = "Insert Into [DestTable] ([JobName],[Location],[Door],[DefaultID]) " _
"Select [JobName], [Location], [Door], [DefaultID]" _
"From [ASP] Table] where [DefaultID] =" & objRS![DefaultID]

This is where I'm getting confused.
You're creating a variable which when executed will select the values to be inserted into the destination table from the ASPTable and insert it into the destination table. That much I understand.
The filtering I don't understand. Since I will only be inserting one record into ASPtable which this code needs to insert [NumDoors] times into the destination table, is any filtering even necessary?
And why don't strSQL and strSQL2 need to be Dim'd?





strCount = 99999 '<=====Intentionally large number
Do While Not objRS.EOF

If strCount <> objRS![NumDoors] Then strCount = objRS![NumDoors]
For x = 1 to strCount
DoCmd.RunSQL strSQL2
Next x
End if

Loop



And I'm a bit confused about this part as well. You're setting strCount with a value, (inentionally high for reasons I don't know yet). The Do While Not objRS.EOF I don't understand. Is this saying "repeat as long as there are more records in the recordset"?

Now I'm working with the assumption that the ASP table is a temporary table, and will only ever contain one record at a time, that being the record being inserted into the destination table [NumDoors] times. If that's the case, then would my code need to look more like this?





Dim objRS As Recordset, Dim strCount As Long, Dim x As Long
Set objRS = New Recordeset

strSQL = "Select * From [ASPTable]"
objRS.Open strSQL


strSQL2 = "Insert Into [DestTable] ([JobName],[Location],[Door],[DefaultID]) " _
"Select [JobName], [Location], [Door], [DefaultID]" _
"From [ASP] Table]

strCount = 99999 '<=====Intentionally large number
Do

If strCount <> objRS![NumDoors] Then strCount = objRS![NumDoors]
For x = 1 To strCount
DoCmd.RunSQL strSQL2
Next x
End If

Loop

strSQL3 = "Delete * From [ASP] Table]
DoCmd.RunSQL strSQL3

I'm not familiar with the syntax of DELETE yet, so that may be wrong.

[XLGibbs]

Looks about right to me. The filter in my post was more for example, and assumed you may have a complete recordset to work ith not just one record...

Your rewrite looks syntax correct...including the Delete statement...


Creating the recordset using the SQL statements keeps things under control...looks like you are on the right track...

the variables do need to be DIM'd...my sample was more for syntax than for plug n play...but I should have included those statements. Likewise the Set object = nothing statements once done are needed for the objects (not the strings)

[better]

Looks about right to me. The filter in my post was more for example, and assumed you may have a complete recordset to work ith not just one record...

Your rewrite looks syntax correct...including the Delete statement...


Creating the recordset using the SQL statements keeps things under control...looks like you are on the right track...

the variables do need to be DIM'd...my sample was more for syntax than for plug n play...but I should have included those statements. Likewise the Set object = nothing statements once done are needed for the objects (not the strings)

Ok, great, so maybe I'm not as incompetent as this had been making me feel! I do have one remaining item that I need clarification on though. I'm guessing this goes in a module, and now that I know how to deal with passing variables into a module (inserting into ASPTable first), I just need to know how to call the module from the query that's inserting the passed variables into the ASPTable, and I think I'll be in business.

Thanks alot!

Bart

[XLGibbs]

Yes, standard module...

Call the routine by this syntax...change the names obviously..


Sub code1()
'''code
Call Code2

End Sub

Sub Code2
''code
End Sub


When code2 is done, it goes back to code 1...

If you need to pass a variable to Code2


Sub Code1()
'code
Call Code2 variable
End Sub


Sub Code2 (byVal variable as type of variable) 'i.e string, long, etc..

'code

End Sub

[Bob Phillips]

Ok, great, so maybe I'm not as incompetent as this had been making me feel! I do have one remaining item that I need clarification on though. I'm guessing this goes in a module, and now that I know how to deal with passing variables into a module (inserting into ASPTable first), I just need to know how to call the module from the query that's inserting the passed variables into the ASPTable, and I think I'll be in business.

Why do you not just use VBScrip direct in your ASP and communicate with the Access database using ADO? Or maybe even build in classic VB or VB.Net? Why use Access at all other than as a database?

[better]

Not sure, xld. Keep in mind I'm very inexperienced with database integration and vb. I currently have my asp application doing everything, but I am under the impression that it will be more efficient to just pass the data to access once and let the database do the processingbased on what I read here (http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1). It's entirely possible that I'm way off in left field somewhere.

Bart

[Bob Phillips]

Not sure, xld. Keep in mind I'm very inexperienced with database integration and vb. I currently have my asp application doing everything, but I am under the impression that it will be more efficient to just pass the data to access once and let the database do the processingbased on what I read here (http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1). It's entirely possible that I'm way off in left field somewhere.

Bart

Bart,

No I would agree that it is better to let the database do the processing, but you do that by specifying within the SQL statement. There is absolutely no need to use Access, or Excel, or any other VBA container unless you need it to do something that the ASP cannot do, or that container does better.

The code that you write in VBA could as easily be written as inline code within the ASP script, remember that ASP can support VBScript (or even JavaScript should you so wish). If you are looking for some extra security, then you should use VB not VBA within an application container.

Are you interested in pursuing this, in which case I will get some links for you to read up on, or do you just want to let it drop?

Regards

[better]

XLD,

Yes, I would be interested in doing that, I just don't know how. In case you're not fully aware of what I'm trying to do, let me recap briefly. I need to insert variables A, B, C, and D into the desitnation table, (variable E) times. I currently have code on my ASP page that is doing this, see post #9.

If I understand you correctly, there's an argument I can include in my SQL statement that will repeat the INSERT a specified number of times. That is precisely what I've been looking for, but unable to find, which is why I ended up here!

Thanks!
Bart

[XLGibbs]

Bart,

I think what he is saying is, Access is merely a container for which the code is being run to do what is necessary. The code ASP script, or a VB (not VBA) that does not require Access as a container for the process may be an alternative. Within Access, you are executing the SQL statement necessary, not that there is another sQL statement.

Since you are using Access to "house" the SQL statements, he is offering alternatives that are out there to potentially skip the process being in Access, but I am not sure you have those resources at your disposal (such as Visual Studio or another script writing software tool that allows programming in VB, Java or other non VBA language...

[better]

Could be, Gibbs, it's all greek to me at the moment.

If I had hair, I'd be pulling it out right now. I'm still not quite clear on how I'd accomplish it inside Access. I tried putting the vba code we've been working on into a module as a sub procedure, and couldn't get that to work, debug says the connection isn't valid.

And even if I could get it to run locally, I still don't understand how I would execute the procedure from my ASP application. I can execute a stored query from ASP, but how do I call a procedure from a query?
:banghead:

Here's the code I'm using in my procedure:


Public Sub LoopInsert()
Dim objRS As Recordset
Dim strCount As Long
Dim x As Long
Set objRS = New Recordset

strSQL = "Select * From [ASPTable]"
objRS.Open strSQL '<==== debug hangs here


strSQL2 = "Insert Into ACS_Doors ([JobName],[Location],[Door],[DefaultID]) Select [JobName], [Location], [Door], [DefaultID] From [ASPTable]"

strCount = 99999 '<=====Intentionally large number
Do

strCount = objRS![NumDoors]
For x = 1 To strCount
DoCmd.RunSQL strSQL2
Next x

Loop

strSQL3 = "Delete * From [ASPTable] "
DoCmd.RunSQL strSQL3
End Sub


That's all I can take for tonight!

Bart

[XLGibbs]

I though this WAS in Access. But you still have to open a connection to the database as follows...change the ObjRs.Open strSql as below..


Public Sub LoopInsert()
Dim objRS As Recordset
Dim strCount As Long
Dim x As Long

Dim strConnect as string

Set objRS = New Recordset


StrConnect = ?Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\UDLSample.mdb;Persist Security Info=False?

strSQL = "Select * From [ASPTable]"

ObjRS.Open strSQL ,strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText


strSQL2 = "Insert Into ACS_Doors ([JobName],[Location],[Door],[DefaultID]) Select [JobName], [Location], [Door], [DefaultID] From [ASPTable]"

strCount = 99999 '<=====Intentionally large number
Do

strCount = objRS![NumDoors]
For x = 1 To strCount
DoCmd.RunSQL strSQL2
Next x

Loop

strSQL3 = "Delete * From [ASPTable] "
DoCmd.RunSQL strSQL3
End Sub



Change the C:\UDLSample.mdb to the path of your database...

[Bob Phillips]

Since you are using Access to "house" the SQL statements, he is offering alternatives that are out there to potentially skip the process being in Access, but I am not sure you have those resources at your disposal (such as Visual Studio or another script writing software tool that allows programming in VB, Java or other non VBA language...

Access does not "house" the SQL statements, it "houses" the VBA that is used to pass the SQL string to the database. Access is really two products, the db back-end, and a forms front-end. The back-end understands SQL, but you don't need to run Access to use that aspect, you can do that from anywhere (as we all know with Excel).

If he has some tool to write ASP, then he has a tool to write VBScript. For goodness sake, it only needs a text editor, he isn't writing complex apps that would benefit from tools like Visual Studio.

[better]

I couldn't seem to get any of these solutions to work for me. I did, in my research, come across the syntax for the DoCmd.RunMacro action, which has a repeat count argument. If I could figure out how to execute a macro from my ASP application using VB script, that would be ideal, as I define the repeat count argument as Request.Form("NumDoors") easily enough. Does anyone know how to go about this?

XLD, I am using Macromedia Dreamweaver 8 for my ASP/VB Script development. My problem isn't so much lack of access to tools, it's lack of knowledge!

[better]

I finally got it!

Created a macros named "InsertDoors" with RunSQL "INSERT INTO ACS_Doors ( JobName, Location, DoorName, DefaultID, DefaultLabel ) SELECT ASPTable.JobName, ASPTable.Location, ASPTable.Door, ASPTable.DefaultID, ASPTable.DefaultLabel
FROM ASPTable;" and "DeleteASP" with RunSQL "DELETE ASPTable.* From [ASPTable];

and executed it from my ASP page with this code:


<%
Dim DbPath
Dim objAccess
Dim NumDoors
Dim macroName
Dim macro2Name
%>
<% Set Session ("objAccess") = Server.CreateObject("Access.Application")
DbPath = "/dbfolder/dbname.mdb"
Session("objAccess.Visible") = True
Session("objAccess").OpenCurrentDatabase Server.MapPath(DbPath)
macroName = "InsertDoors"
macro2Name = "DeleteASP"
NumDoors = Request.Form("NumDoors")
Session("objAccess").DoCmd.RunMacro macroName, NumDoors
Session("objAccess").DoCmd.RunMacro macro2Name

Session("objAccess").CloseCurrentDatabase
%>
<% Set Session("objAccess") = Nothing%>
So Gibbs, thanks so much for the idea of using the temp table approach, it's working beautifully!
http://vbaexpress.com/forum/images/smilies/beerchug.gif


Bart

[XLGibbs]

Great job!

Happy to have,err, helped?

You get all the credit on this one..nice job.

Pete

PS. If this is a done issue, just mark the thread solved for us, thanks!