PDA

View Full Version : Affiliate File

Corey Bryant
08-19-2003, 04:16 AM
Having some issues with a affiliate log. It does not seem to show the moneys owed. I think it is in the connection string:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="datafunc.asp"-->
<%
dim Sql
dim Sql2
dim SqlAff
dim dateRange
dim sUserName
dim oRsAff
dim oRsAffLog
Dim AffID

sUserName = Session("MM_Username")
dateRange = Request.Form("dateRange")

Call OpenDB()

SqlAff = "SELECT * FROM Affiliates WHERE Username = '" & sUserName & "'"

Set oRsAff = dbConn.Execute(SqlAff)


if not oRsAff.EOF Then
AffID = oRsAff("AffiliateID")
Else

End if

Select case dateRange

Case 13
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '01/01/2003' AND '12/31/2003')"
Case 1
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '01/01/2003' AND '01/31/2003')"
Case 2
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '02/01/2003' AND '02/29/2003')"
Case 3
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '03/01/2003' AND '03/31/2003')"
Case 4
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '04/01/2003' AND '04/31/2003')"
Case 5
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '05/01/2003' AND '05/31/2003')"
Case 6
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '06/01/2003' AND '06/31/2003')"
Case 7
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '07/01/2003' AND '07/31/2003')"
Case 8
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '08/01/2003' AND '08/31/2003')"
Case 9
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '09/01/2003' AND '09/31/2003')"
Case 10
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '10/01/2003' AND '10/31/2003')"
Case 11
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '11/01/2003' AND '11/31/2003')"
Case 12
Sql = "SELECT aff_log.* FROM aff_log, Affiliates WHERE (aff_log.AffiliateID = " & AffID & " AND Affiliates.AffiliateID = " & AffID & ") AND (Affiliates.approved = 'y' AND aff_log.signupDate BETWEEN '12/01/2003' AND '12/31/2003')"

End Select

'Response.Write(dateRange)
'Response.End()

if dateRange <> "" Then

Set oRsAffLog = dbConn.Execute(Sql)

End if

%>
<HTML><!-- InstanceBegin template="/Templates/merchant_Temp.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<HEAD>

<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<META name="description" content="MerchantAccounts 4 Less is an industry leader in credit card processing for traditional and Internet businesses. Get A Merchant Account Today! 98% Merchant Application Approval!">
<META name="keywords" content="merchant account, Cardservice International, credit card, virtual terminal, online purchasing, credit card terminal, accept credit cards, visa, mastercard, american express, discover, e-commerce, payment solutions, online purchasing, credit card terminals, process credit cards, e-commerce, payment solutions">
<link rel="stylesheet" href="style.css" type="text/css">
<!-- InstanceBeginEditable name="doctitle" -->
<TITLE>Affordable Merchant Accounts 4 Less - Affiliates Sign Up</TITLE>
<!-- InstanceEndEditable --><style type="text/css">
.FormTextField
{
border:1px groove #3D5C85;
background-color: #D7DFE9;
font-family: arial;
font-size: 10pt;
color: #000000;

}
.button
{
border-style:solid;
border-width:1;
background-color: #D7DFE9;
color: #3D5C85;
font-family: Verdana;
font-weight:bold
}
</style>
<script src="pop-up.js">
</script>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</HEAD>
<BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<TABLE border=0 cellpadding=0 cellspacing=0>
<TR>
<TD rowspan=5>
<IMG src="credit_card_head.jpg" width="168" height="142"></TD>
<TD colspan=2 bgcolor=#3D5C85 class="topcell">
<IMG src="spacer.gif" width=612 height=32></TD>
</TR>
<TR>
<TD rowspan=3>
<IMG src="merchant_account_head2.jpg" width="155" height="66"></TD>
<TD>
<IMG src="credit_cards_head3.jpg" width="457" height="19"></TD>
</TR>
<TR>
<TD>
<IMG src="merchant_credit_cards.jpg" width="457" height="29"></TD>
</TR>
<TR>
<TD>
<IMG src="slogan.jpg" width="457" height="18"></TD>
</TR>
<TR>
<TD colspan=2>
<IMG src="process_credit_cards_head4.gif" width="612" height="44"></TD>
</TR>
</TABLE>

<table width="780" cellpadding="0" cellspacing="0">
<tr>
<td width="153" bgcolor="#375A83">

<script type='text/javascript'>
function Go(){return}
</script>

<script type='text/javascript' src='credit_card_menu.js'></script>

<script type='text/javascript' src='menu_com.js'></script>


</td>
<td width="627" background="bg1.gif">

<table width="627" cellpadding="10"><tr><td>
<!-- content -->
<!-- InstanceBeginEditable name="mainEditRegion" -->
<h3>Affiliates - Log</h3>
<p>&nbsp;</p>
<p align="center">

<img border="0" src="merchant_account.gif" width="493" height="10" alt="Affiliates"> </p>

<table width="493" border="0" align="center">
<tr>
<td colspan="2" bgcolor="#336699"><font color="#FFFFFF">&nbsp;<strong>Affiliate <%=oRsAff("AffiliateID")%></strong></font></td>
</tr>
<tr>
<td width="213" valign="top">&nbsp;Affiliate Name</td>
<td width="275">&nbsp;<%=oRsAff("FNAme")%>&nbsp;<%=oRsAff("LName")%></td>
</tr>
<tr>
<td valign="top">&nbsp;Affiliate Company</td>
<td>&nbsp;<%=oRsAff("Company")%></td>
</tr>
<tr>
<td height="26" valign="top">&nbsp;Select Date Range</td>
<td align="right" valign="top">
<form name="AffLogForm" method="post" action="aff_log.asp">
<!--<form name="AffLogForm" method="post" action="debug.asp">-->
<select name="dateRange" class="FormTextField">
<option value="13">All</option>
<option value="1">January</option>
<option value="2">Febuary</option>
<option value="3">March</option>
<option value="4">April</option>
<option value="5">May</option>
<option value="6">June</option>
<option value="7">July</option>
<option value="8">August</option>
<option value="9">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>&nbsp;<input name="" type="submit" value="go" class="button">
</form>
</td>
</tr>
<tr>
<td colspan="2">&nbsp;To update your information click <a href="aff_update.asp?number=<%=AffID%>">here</a></td>
</tr>
<tr>
<td colspan="2">
<table width="492" border="0" cellpadding="0" cellspacing="0">
<tr bgcolor="#336699">
<td width="127">&nbsp;<font color="#FFFFFF"><strong>Date</strong></font></td>
<td width="292">&nbsp;<font color="#FFFFFF"><strong>Company</strong></font></td>

<td width="68">&nbsp;<strong><font color="#FFFFFF">Amount</font></strong></td>
</tr>
<%
if dateRange <> "" Then
do while not oRsAffLog.eof%>
<tr>
<td>&nbsp;<%=oRsAffLog("signupDate")%></td>
<td>&nbsp;<%=oRsAffLog("Company")%></td>
<td>&nbsp;<%=FormatCurrency(oRsAffLog("amount"))%></td>
</tr>
<%
oRsAffLog.movenext
loop
else %>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<% end if%>
</table>
</td>

</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
Click
<a href="banner.asp">here</a> to add banners and/or co-branded
web pages
to your
website. In the meantime, if you have any questions please feel free to e-mail
us at <a href="mailto:affiliates@merchantaccounts4less.com">
affiliates@merchantaccounts4less.com</a>.&nbsp;
<!-- InstanceEndEditable -->
</td></tr></table>

</td>
</tr>
</table>
<!--#INCLUDE file="footer.asp" -->
</BODY>
<!-- InstanceEnd --></HTML>
<%

Call CloseDB()
%>

and in the datafunc.asp file:

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
' functions to open and close the database connections
Dim rs, dbConn

Function OpenDB()
set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Provider=sqloledb;Data Source=64.26.22.77,1433;Network Library=DBMSSOCN;Initial Catalog=payit;User ID=payit;Password=crb_rg72;"
End Function

Function CloseDB()
' close cursor and database connection
Set rs = Nothing
' NOTICE! I used to have this as OBJECT, but
' found out later that it is now being returned as CONNECTION
' so update your code!
if ucase(TypeName(dbConn)) = "CONNECTION" then
dbConn.Close
Set dbConn = Nothing
end if
End Function

Function OpenRecordSet(recset, tablename)
Call OpenDB()
Set recset = Server.CreateObject("ADODB.Recordset")
recset.Open tablename, dbConn, 0, 1
End Function

Function CloseRecordSet(recset)
Set recset = Nothing
Call CloseDB()
End Function
</SCRIPT>

I had some work done on this, but the programmer is not around right now & I need to make sure this is working properly. Thanks!
raf
08-19-2003, 08:49 AM
Can you be somewhat more specific about the problem (since i doubt anyone will read through all that code without knowing what to look for) + if the parametervalues in the connectionstring are the real pwd and userID etc, then i'd remove them or replace them with *

Whet db-format are you using?

Calling the connectionstring with a function ? Never seen that + it's a bad idea cause the connectionparameters shouldn't be included in the source, but should be stored in a server side include with a .asp extension. And since the connection is opened only once, why use a function anyway?


I think the problem is that you don't open the recorset. You open a connection with

Call OpenDB()

but i think you should open the recordset there (since it also calls OpenDB()
So i'd replace Call OpenDB with Call OpenRecordSet(oRsAff, ????tablename????)
But that OpenRecordset() looks incorrect to me. Instead of the tablename, you need the sql-variable.

So maybe try it like

dim Sql
dim Sql2
dim SqlAff
dim dateRange
dim sUserName
dim oRsAff
dim oRsAffLog
Dim AffID

sUserName = Session("MM_Username")
dateRange = Request.Form("dateRange")

dim dbConn, oRsAff
set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Provider=sqloledb;Data Source=$$$$$$$,$$$$$;Network Library=$$$$$$;Initial Catalog=$$$$$$$;User ID=*****;Password=*****;"
Set oRsAff = Server.CreateObject("ADODB.Recordset")

SqlAff = "SELECT * FROM Affiliates WHERE Username = '" & sUserName & "'"
oRsAff .Open SqlAff, dbConn, 0, 1

if not oRsAff.EOF Then
AffID = oRsAff("AffiliateID")
Else
....
Corey Bryant
08-19-2003, 01:57 PM
Sorry what this is supposed to do is tell a person how much they got paid for that month or for all months. I tried your code but it shot back an error:
Microsoft VBScript compilation error '800a0411'

Name redefined

/merchantaccounts4le/aff_log.asp, line 16

dim dbConn, oRsAff
------------^

The guy who designed this, I had some questions about but it can be difficult locating someone.

Thanks!
raf
08-19-2003, 03:11 PM
Just remove the ", oRsAff" on that line. It was already dimed at the top of the page, but i didn't see that.

or use

dim Sql
dim Sql2
dim SqlAff
dim dateRange
dim sUserName
dim oRsAff
dim oRsAffLog
Dim AffID
dim dbConn

sUserName = Session("MM_Username")
dateRange = Request.Form("dateRange")

set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Provider=sqloledb;Data Source=$$$$$$$,$$$$$;Network Library=$$$$$$;Initial Catalog=$$$$$$$;User ID=*****;Password=*****;"
Set oRsAff = Server.CreateObject("ADODB.Recordset")

SqlAff = "SELECT * FROM Affiliates WHERE Username = '" & sUserName & "'"
oRsAff .Open SqlAff, dbConn, 0, 1

if not oRsAff.EOF Then
AffID = oRsAff("AffiliateID")
Else
....


I think it's real poor code. Sory. (Hope you didn't pay for it)
The select case isn't necessary. The functions aren't necessary, the connectioninfo should be in a server side include, the use of inline css, the way the months dropdown is created !!
Corey Bryant
08-19-2003, 03:14 PM
Thanks. I think that part helps with some of the other that I had found!