Populating another database with records from an asp page.

[Miguel Orrego]

Apologies for the length of this mail but code is included.

Have a page that pulls data from one database & displays the results, using
a loop to display each record on the page:

The select Query:

strSQLtickets = "SELECT
CALL.CALLID,CALASGN.ID20OPERID,CALL.CALLDAT,CALL.N AME,CUSEMAIL.NOTETEXT,CALL
..FACLSRN,CALCDESC.NOTETEXT FROM CALL INNER JOIN CALCDESC ON CALL.CDESC =
CALCDESC.NOTEKEY INNER JOIN CALASGN ON CALL.CALLID = CALASGN.ID20CALLID
INNER JOIN CUSTMER ON CALL.CUSTID = CUSTMER.CUSTID INNER JOIN CUSEMAIL ON
CUSTMER.EMAIL = CUSEMAIL.NOTEKEY WHERE ((CALL.STATUS) IN ('OPEN','PENDING'))
AND ((CALASGN.ID20GRPID) = 'GLO_BM') ORDER BY CALL.CALLDAT DESC, CALL.CALLID
DESC"

The loop:

<% do while not rstickets.EOF 'begin loop that runs while queries still
return data
%>

<table width="100%" border="1" align="center" cellpadding=4 cellspacing=0
bordercolor="#003366" bgcolor="#FFFF99">
<tr bgcolor="#336699">
<th><font color="#FFFFFF" size="2" face="Tahoma">Call ID</font></th>
<th><font color="#FFFFFF" size="2"
face="Tahoma">Application</font></th>
<th><font color="#FFFFFF" size="2" face="Tahoma">Call Date</font></th>
<th><font color="#FFFFFF" size="2" face="Tahoma">Customer</font></th>
<th><font color="#FFFFFF" size="2" face="Tahoma">Email</font></th>
<th><font color="#FFFFFF" size="2" face="Tahoma">GLOBM
Issue</font></th>
</tr>
<tr valign="top" bgcolor="#FFFFFF">
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=zero(rstickets.fields(0))%></font><font
color="#000000"></a></font></div></td>
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=rstickets.fields(1)%></font></div></td>
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=dat(rstickets.fields(2))%></font></div></td>
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=rstickets.fields(3)%></font></div></td>
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=rstickets.fields(4)%></font></div></td>
<td align="center"> <div align="center"><font face="Tahoma"
color="#000000" size="2"><%=rstickets.fields(5)%></font></div></td>
</tr>
</table>

I want to put a button next to each record that is displayed on the page,
that when pressed would populate the data from that record to another
database. I can get my head around the update page that I would use, but my
question is, how can I get this button to ctreate a new record in the other
database with just the information for that record. I had though of
declaring some variables and giving them the value of each field, like:

Dim CallDate
CallDate = rstickets.fields(3)

But surely this variable would contain the date for all the records
returned, not just a specific one.

Is there any way of doing this? Do I perhaps not use a loop and do it some
other way? Any help appreciated.

Thanks.

[Bob Lehmann]

Please consider fomatting your SQL so it is easily read...

SELECT
column_1, column_2, ......
FROM
table_name
WHERE
condition
ORDER BY
column_name

If you must use caps, keywords only.

Bob Lehmann

"Miguel Orrego" <miguel@stressedmonkey.net-nospam> wrote in message
news:3f04429f$0$958$cc9e4d1f@news.dial.pipex.com.. .

> Apologies for the length of this mail but code is included.
>
> Have a page that pulls data from one database & displays the results,

using

> a loop to display each record on the page:
>
> The select Query:
>
> strSQLtickets = "SELECT
>

CALL.CALLID,CALASGN.ID20OPERID,CALL.CALLDAT,CALL.N AME,CUSEMAIL.NOTETEXT,CALL

> .FACLSRN,CALCDESC.NOTETEXT FROM CALL INNER JOIN CALCDESC ON CALL.CDESC =
> CALCDESC.NOTEKEY INNER JOIN CALASGN ON CALL.CALLID = CALASGN.ID20CALLID
> INNER JOIN CUSTMER ON CALL.CUSTID = CUSTMER.CUSTID INNER JOIN CUSEMAIL ON
> CUSTMER.EMAIL = CUSEMAIL.NOTEKEY WHERE ((CALL.STATUS) IN

('OPEN','PENDING'))

> AND ((CALASGN.ID20GRPID) = 'GLO_BM') ORDER BY CALL.CALLDAT DESC,

CALL.CALLID

> DESC"
>
> The loop:
>
> <% do while not rstickets.EOF 'begin loop that runs while queries

still

> return data
> %>
>
> <table width="100%" border="1" align="center" cellpadding=4

cellspacing=0

> bordercolor="#003366" bgcolor="#FFFF99">
> <tr bgcolor="#336699">
> <th><font color="#FFFFFF" size="2" face="Tahoma">Call ID</font></th>
> <th><font color="#FFFFFF" size="2"
> face="Tahoma">Application</font></th>
> <th><font color="#FFFFFF" size="2" face="Tahoma">Call

Date</font></th>

> <th><font color="#FFFFFF" size="2"

face="Tahoma">Customer</font></th>

> <th><font color="#FFFFFF" size="2" face="Tahoma">Email</font></th>
> <th><font color="#FFFFFF" size="2" face="Tahoma">GLOBM
> Issue</font></th>
> </tr>
> <tr valign="top" bgcolor="#FFFFFF">
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=zero(rstickets.fields(0))%></font><font
> color="#000000"></a></font></div></td>
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=rstickets.fields(1)%></font></div></td>
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=dat(rstickets.fields(2))%></font></div></td>
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=rstickets.fields(3)%></font></div></td>
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=rstickets.fields(4)%></font></div></td>
> <td align="center"> <div align="center"><font face="Tahoma"
> color="#000000" size="2"><%=rstickets.fields(5)%></font></div></td>
> </tr>
> </table>
>
> I want to put a button next to each record that is displayed on the page,
> that when pressed would populate the data from that record to another
> database. I can get my head around the update page that I would use, but

my

> question is, how can I get this button to ctreate a new record in the

other

> database with just the information for that record. I had though of
> declaring some variables and giving them the value of each field, like:
>
> Dim CallDate
> CallDate = rstickets.fields(3)
>
> But surely this variable would contain the date for all the records
> returned, not just a specific one.
>
> Is there any way of doing this? Do I perhaps not use a loop and do it some
> other way? Any help appreciated.
>
> Thanks.
>
>

[Jeff Cochran]

On Thu, 3 Jul 2003 15:50:05 +0100, "Miguel Orrego"
<miguel@stressedmonkey.net-nospam> wrote:

>Apologies for the length of this mail but code is included.

I cut the code because it's not relevant to my response here, which is
general and not specific code.

>I want to put a button next to each record that is displayed on the page,
>that when pressed would populate the data from that record to another
>database. I can get my head around the update page that I would use, but my
>question is, how can I get this button to ctreate a new record in the other
>database with just the information for that record. I had though of
>declaring some variables and giving them the value of each field, like:
>
>Dim CallDate
>CallDate = rstickets.fields(3)
>
>But surely this variable would contain the date for all the records
>returned, not just a specific one.
>
>Is there any way of doing this? Do I perhaps not use a loop and do it some
>other way? Any help appreciated.

You need a key field to use for this (not sure from your query exactly
what your database structure is, maybe CALLID is the right key). Then
in your code you do something like:

Response.Write rs.Fields("CALLID") & <A HREF='modify.asp?ID=" &
rs.Fields("CALLID") & "'>Copy to new table</A>"

That creates a display line with a link to the ASP page that does the
INSERT or UPDATE into the new table.

Your Modify.asp does something like:

ID=request.querystring("ID")
SQLstmt = "SELECT * FROM OldTable WHERE CALLID='" & ID & "'"

That gets the single record from the old table, then do an INSERT or
UPDATE or whatever to put it into the new table. You'll want to check
to see if the record exists and UPDATE or if not INSERT of course.

Jeff


===================================
Jeff Cochran (IIS MVP)
[email]jcochran.nospam@naplesgov.com[/email] - Munged of Course

I don't get much time to respond to direct email,
so posts here will have a better chance of getting
an answer. Besides, everyone benefits here.

Suggested resources:
[url]http://www.iisfaq.com/[/url]
[url]http://www.iisanswers.com/[/url]
[url]http://www.iistoolshed.com/[/url]
[url]http://securityadmin.info/[/url]
[url]http://www.aspfaq.com/[/url]
[url]http://support.microsoft.com/[/url]
====================================

[David C Holley]

Two options:

Option 1.
Format the HTML so that each record is contained in its own form with
the appropriate form fields. (U'll probably want to have these form
fields hidden to discourage the user from changing the values.) When the
button is clicked, the form would be submitted to the *.asp page that
would insert the records. (If you want to use an image to handle the
submit try using this statement in the <img> tag:
onClick=document.frmInformationRequest.submit();
(May need quotes, I'm quite tired at the moment).

Option 2.
Create a form where all of the form fields are hidden and match
one-for-one the fields of the record. Format each record so that each
value exists in its own <td></td> cell in a table. Name each <td> tag
appropriately with an index value:
<td id=name_1>
<td id=email_1>
<td id=phone_1>
<td id=name_2>
<td id=email_2>
<td id=phone_2>
Name each button according to the record's index id=record_1. In the
onClick event for the button call a function which loops through each
<td> tag, grabs the value and updates the value of the hidden fields in
the form. The function would accept a value that indicates which record
to copy (ex: onClick=insertRecord(1);).

Option 1 will be easier to implement, however Option 2 will be a little
bit more fun. While Opt2 is a bit more complex conceptually, the actual
function shouldn't be that difficult to write. The FOR loop in
loadCalendar() function on this page

[url]http://www.gatewayorlando.com/content/reservationrequest.asp[/url]

does basically what I'm describing. The nested FOR loop and the multiple
IF...THENs make it complex, but that's only because its manipulating a
table with specific constraints (7 cells across, 5 rows down). The

document.getElementById("td_w" + weekIndex + "d" + dayIndex).innerHTML =

statement is the workhorse in the function.

David

Jeff Cochran wrote:

> On Thu, 3 Jul 2003 15:50:05 +0100, "Miguel Orrego"
> <miguel@stressedmonkey.net-nospam> wrote:
>
>

>>Apologies for the length of this mail but code is included.

>
>
> I cut the code because it's not relevant to my response here, which is
> general and not specific code.
>
>

>>I want to put a button next to each record that is displayed on the page,
>>that when pressed would populate the data from that record to another
>>database. I can get my head around the update page that I would use, but my
>>question is, how can I get this button to ctreate a new record in the other
>>database with just the information for that record. I had though of
>>declaring some variables and giving them the value of each field, like:
>>
>>Dim CallDate
>>CallDate = rstickets.fields(3)
>>
>>But surely this variable would contain the date for all the records
>>returned, not just a specific one.
>>
>>Is there any way of doing this? Do I perhaps not use a loop and do it some
>>other way? Any help appreciated.

>
>
> You need a key field to use for this (not sure from your query exactly
> what your database structure is, maybe CALLID is the right key). Then
> in your code you do something like:
>
> Response.Write rs.Fields("CALLID") & <A HREF='modify.asp?ID=" &
> rs.Fields("CALLID") & "'>Copy to new table</A>"
>
> That creates a display line with a link to the ASP page that does the
> INSERT or UPDATE into the new table.
>
> Your Modify.asp does something like:
>
> ID=request.querystring("ID")
> SQLstmt = "SELECT * FROM OldTable WHERE CALLID='" & ID & "'"
>
> That gets the single record from the old table, then do an INSERT or
> UPDATE or whatever to put it into the new table. You'll want to check
> to see if the record exists and UPDATE or if not INSERT of course.
>
> Jeff
>
>
> ===================================
> Jeff Cochran (IIS MVP)
> [email]jcochran.nospam@naplesgov.com[/email] - Munged of Course
>
> I don't get much time to respond to direct email,
> so posts here will have a better chance of getting
> an answer. Besides, everyone benefits here.
>
> Suggested resources:
> [url]http://www.iisfaq.com/[/url]
> [url]http://www.iisanswers.com/[/url]
> [url]http://www.iistoolshed.com/[/url]
> [url]http://securityadmin.info/[/url]
> [url]http://www.aspfaq.com/[/url]
> [url]http://support.microsoft.com/[/url]
> ====================================