Split function containing INSERT

[lazydays]

Hi,

I have taken over a website which has a function that inserts data to a table using the recordset.

I need to split the insert query into 2 as we want to split the table being inserted into.

This is fine - I can write the queries required, but I am used to PHP and am unsure of how to run a 2nd query within the recordset.
Or do I have to set up a new function and call both functions?

Hope this makes sense!

Thanks in advance.

 

[DotNetGnat]

you need to call the function twice passing a different query each time.

-DNG

 

[lazydays]

Thanks DNG - how do I parse a different query from within the function?

 

[DotNetGnat]

I thought you said you can split the recordset into two queries and then i though your function is written such a way that it takes recordset as the input parameter and displays the table.

is that right? if so what problem do you have? can you provide some code or sample on what you are actually trying to do?

-DNG

 

[lazydays]

Sorry but I don't think I explained it very well.

I have a function that calls and executes a query.
This query is an insert query.

What I meant was that I know how to split the sql query into 2 parts, but I don't know how to make the function call both queries.

Unfortunately I cannot post the code as it is not in front of me at the moment.

I hope that explains it a bit better.

Thanks for your help so far!

 

[DotNetGnat]

when you want a function( that takes only a single input parameter) to call two queries...you need to call the function twice with different input query parameters.

-DNG

 

[Sheco]

How about combining 2 statements with a semicolon this:
[tt]
sSQL = "UPDATE FirstTable SET foo = 'bar'"
sSQL = sSQL & " ; "
sSQL = sSQL & "Update SecondTable SET hoppy = 'frood'"

adoCN.Execute sSQL
[/tt]

 

[DotNetGnat]

Sheco, that might then create a single table output for two queries...

am i right or did i miss something?

-DNG

 

[lazydays]

DNG you are right - I need to update 2 seperate tables

 

[Tarwn]

Ok, executing two INSERTs or UPDATEs on two completely differant tables in the same SQL statement (Such as Sheco displayed) will still update the two tables you wanted it to. Provided you do a
ConnectionObject.Execute sqlQuery
then it should not matter that internally it may build two empty recordsets (just like it builds one empty recordset each time you execute a single INSERT or UPDATE).

--

I think what DNG was trying to point out to begin with is that you could alter your original function to accept a SQL query as a function argument and then execute that query, thus allowing you to reuse the function for two differant queries. Somehting like:

Function DoQuery(strQuery)
   'ADO stuff here to execute strQuery
End Function

DoQuery "UPDATE Table1 SET blah=blah WHERE blah=blah"
DoQuery "UPDATE Table2 SET blah=blah WHERE blah=blah"

The only downside to this method is if you are creating and opening your connection inside that function. By re-using it in this manner you would be opening and closing the connection twice, which is a little wasteful of resources.

---

The fact that you mentioned inserting using a recordset in your first post worries me. We really do need to see, at a minimum, the code for your current function. i'm concerned we might be describing a solution based on some assumptions that aren;t quite true.

-T

 

[lazydays]

This is the relevant part of the function I am working with.

I need to spilt the Insert query into 2 and run them both.


function quoteSaveQuote2DB()
Set rs2use = Server.CreateObject("ADODB.Recordset")
rs2use.ActiveConnection = MM_PrimaryLocal_STRING

rs2use.LockType = 3
rs2use.CursorLocation = 3' adUseClient
rs2use.CursorType = 3 'adOpenStatic
PKArray = Session("APPLICANTS_PK")

' First lets check to see if we need to insert or update the quote
If SESSION("QUOTE_PK") = "" Then
' Create a new quote
'-------------------------
'Let's generate a quote number.
' QuoteID = replace(replace(replace(strreverse(time())," ",""),":",""),"/","")
' QuoteID = "Q/"&PKArray(0)&"/"&Session("AFF_PK")&"/"&Session("AFFPROD_PK")&"/"&QuoteID

QuoteID = db_getSingleValue("execute pt_generate_quote_id "&Session("AFF_PK")&","&PKArray(0)&","&Session("AFFPROD_PK"),"ERROR")
Session("QuoteID") = QuoteID
'-------------------------

rs2use.source = "insert INTO pt_quotes "&_
"(quote_type,quote_ID,quote_affprod_pk,quote_prod_pk,quote_mod_pk,
quote_from_pk,quote_to_pk,quote_aff_pk,quote_subaff_pk,quote_camp_pk"&_
",quote_cust_pk,quote_date_from,quote_date_to,quote_applicant_number,
quote_renewal_pol_pk,quote_Currency,quote_GrossInsurancePremium,
quote_Rounding,quote_GrossSalesPrice"&_

",cid,uid,cd,dlc)"&_
"values("&_
db_getSingleValue("SELECT prod_type FROM pt_products WHERE prod_pk="&Session("PROD_PK"),0)&_
","&tools_prepare_input(QuoteID)&_
","&tools_coalesce(Session("AFFPROD_PK"),0)&_
","&tools_coalesce(Session("PROD_PK"),0)&_
","&tools_coalesce(Session("MOD_PK"),0)&_
","&tools_coalesce(Session("FROM_PK"),0)&_
","&tools_coalesce(Session("TO_PK"),0)&_
","&tools_coalesce(Session("AFF_PK"),0)&_
","&tools_coalesce(Session("SUBAFF_PK"),"null")&_
","&tools_coalesce(Session("CAMP_PK"),"null")&_
","&tools_coalesce(PKArray(0),0)&_
","&tools_prepare_input(tools_coalesce(Session("DATE_FROM"),"null"))&_
","&tools_prepare_input(tools_coalesce(Session("DATE_TO"),"null"))&_
","&tools_coalesce(Session("APPLICANT_NUMBER"),0)&_
","&tools_prepare_input(tools_coalesce(Session("RENEWAL_POL_PK"),0))&_
","&tools_prepare_input(Session("FROM_CURRENCY"))&_
","&Session("PREMIUM_BEFORE_ROUNDING")&_
","&Session("ROUNDING_PENCE")&_
","&Session("PREMIUM")&_

","&tools_coalesce(Session("CC_PK"),1)&_
","&tools_coalesce(Session("CC_PK"),1)&_
",getdate()"&_
",getdate()"&_
")"

rs2use.open
' 23/12/2003 WF return insert Id
rs2use.source = "select @@IDENTITY as last_pk"
rs2use.open

quote_pk = rs2use.Fields(0).value
quoteSaveQuote2DB = quote_pk
Else

 

[lazydays]

After all that I have worked it out!

set rs2use.source as the first query then rs2use.open

then

set rs2use.source as the second query then rs2.use.open


So simple!!

Thanks for your input guys!