Solved

ASP stored procedure

Posted on 2004-08-22
9
511 Views
Last Modified: 2008-02-01
Hi

 I'm having problems trying to get my head around how to make this stored procedure work.
My form is called full.asp - once you select an entry it will bring up all the info on that particular sale. The problem is that we have to show the sales who is responsible for the sale. These are assigned according to postcode [RepPostCode].

The stored procedure below sorts out how to show the sales person or installer according to postcode but how do I call the SP from within the asp form?

The form code is below the SP's.

cheers guys

Miles

______________________________________________________
Stored Procedure = sp_salesdefault
_______________________________________________________

CREATE procedure sp_salesdefault AS SELECT
[uapID],
 rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS SALESCONSULTANT,
rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS INSTALLER
      
FROM [UAP].[dbo].[uapForm] UF,[UAP].[dbo].[uapRepArea] URA,
[UAP].[dbo].[uapReps] UR
WHERE SUBSTRING([PostCode],1,2) = URA.RepPostCode
and URA.RepID = UR.repID

GO

_____________________________________
Form = uapForm
_____________________________________


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
if session("group") = 1 then
response.redirect("/contents/display/full.asp?id=" & request.querystring("id"))
end if
if session("group") = 3 then
response.redirect("/contents/display_uap/full.asp?id=" & request.querystring("id"))
end if
if session("group") = 4 then
response.redirect("/contents/display_vo/full.asp?id=" & request.querystring("id"))
end if
%>

<!--#include file="../../Connections/UAP.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables


If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then



  MM_editConnection = MM_UAP_STRING
  MM_editTable = "dbo.uapForm"
  MM_editColumn = "uapID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "mail.asp?id=" + postcode + ""
 
  MM_fieldsStr  = "AutoExchangeClientID|value"
  MM_columnsStr = "AutoExchangeClientID|',none,''"
 
  MM_fieldsStr  = "VSReferenceNumber|value"
  MM_columnsStr = "VSReferenceNumber|',none,''"
 

  ' create the MM_fields and MM_columns arrays
  MM_fields2 = Split(MM_fieldsStr2, "|")
  MM_columns2 = Split(MM_columnsStr2, "|")
 
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
 
   ' set the form values 2
   'For MM_i = LBound(MM_fields2) To UBound(MM_fields2) Step 2
  '  MM_fields2(MM_i+1) = CStr(Request.Form(MM_fields2(MM_i)))
 ' Next
 
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If

 
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>





<%
Dim rsDisplayAll__MMColParam
rsDisplayAll__MMColParam = "1"
If (Request.QueryString("id") <> "") Then
  rsDisplayAll__MMColParam = Request.QueryString("id")
End If
%>
<%
Dim rsDisplayAll
Dim rsDisplayAll_numRows

Set rsDisplayAll = Server.CreateObject("ADODB.Recordset")
rsDisplayAll.ActiveConnection = MM_UAP_STRING
rsDisplayAll.Source = "SELECT *  FROM dbo.uapForm  WHERE uapID = " + Replace(rsDisplayAll__MMColParam, "'", "''") + ""
rsDisplayAll.CursorType = 0
rsDisplayAll.CursorLocation = 2
rsDisplayAll.LockType = 1
rsDisplayAll.Open()

rsDisplayAll_numRows = 0
%>

<html>
<head>
<title>UAP Logging System</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="/design/style.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);

function MM_openBrWindow(theURL,winName,features) { //v2.0
  window.open(theURL,winName,features);
}
//-->
</script>
</head>

<body>
<!-- #include virtual ="/design/includes/header.asp" -->
<p>&nbsp;</p>
<p>&nbsp;</p>

<div style="position: absolute; left: 10px; width: 25%">
  <iframe width="100%" height="500" src="../display/notes.asp?id=<%=(rsDisplayAll.Fields.Item("uapID").Value)%>" style="position: relative;"></iframe>
</div>

<%
MM_editAction = MM_editAction & "&postcode=" & rsDisplayAll.Fields.Item("PostCode").Value
%>

<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
 
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="2"><strong>Contact Details</strong></td>
    </tr>
    <tr>
      <td align="right">Dealership Name:</td>
      <td><%=(rsDisplayAll.Fields.Item("DealershipName").Value)%></td>
    </tr>
    <tr>
      <td align="right">Trading Name:</td>
      <td><%=(rsDisplayAll.Fields.Item("TradingName").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Sales Contact:</td>
      <td><%=(rsDisplayAll.Fields.Item("SalesContact").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Address:</td>
      <td><%=(rsDisplayAll.Fields.Item("Address").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Town:</td>
      <td><%=(rsDisplayAll.Fields.Item("Town").Value)%> </td>
    </tr>
    <tr>
      <td align="right">County:</td>
      <td><%=(rsDisplayAll.Fields.Item("County").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Post Code:</td>
      <td><%=(rsDisplayAll.Fields.Item("PostCode").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Phone Number:</td>
      <td><%=(rsDisplayAll.Fields.Item("PhoneNumber").Value)%> </td>
    </tr>
    <tr>
      <td align="right">Fax Number:</td>
      <td><%=(rsDisplayAll.Fields.Item("FaxNumber").Value)%> </td>
    </tr>
    <tr>
      <td align="right">E-mail:</td>
      <td><%=(rsDisplayAll.Fields.Item("Email").Value)%> </td>
    </tr>
    <tr>
      <td align="right" valign="top">Enquiry Form Preference:</td>
      <td><%
if (rsDisplayAll.Fields.Item("EnqFormPref").Value) = "False"  then
Response.Write("Fax")

else

if Trim(rsDisplayAll.Fields.Item("EnqFormPref").Value) = "True" then
Response.Write("Email")
else

end if

end if
%> </td>
    </tr>
    <tr>
      <td align="right">Web Address:</td>
      <td><%=(rsDisplayAll.Fields.Item("WebAddress").Value)%> </td>
    </tr>
    <tr>
      <td align="right" valign="top">Franchises:</td>
      <td><%=(rsDisplayAll.Fields.Item("Franchises").Value)%></td>
    </tr>
    <tr>
      <td align="right" valign="top">Services:</td>
      <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td>New Sales:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_New").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_New" type="checkbox" id="Servs_New" value="1" disabled="disabled">
            </td>
          </tr>
          <tr>
            <td>Used Sales:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_Used").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_Used" type="checkbox" id="Servs_Used" value="1" disabled="disabled">
            </td>
          </tr>
          <tr>
            <td>Service:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_Service").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_Service" type="checkbox" id="Servs_Service" value="1" disabled="disabled">
            </td>
          </tr>
          <tr>
            <td>Body Shop:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_BodyShop").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_BodyShop" type="checkbox" id="Servs_BodyShop" value="1" disabled="disabled">
            </td>
          </tr>
          <tr>
            <td>Parts:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_Parts").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_Parts" type="checkbox" id="Servs_Parts" value="1" disabled="disabled">
            </td>
          </tr>
          <tr>
            <td>Showroom:</td>
            <td>
<input <%If (CStr((rsDisplayAll.Fields.Item("Servs_Showroom").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="Servs_Showroom" type="checkbox" id="Servs_Showroom" value="1" disabled="disabled">
            </td>
          </tr>
        </table></td>
    </tr>
    <tr>
      <td colspan="2"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="4"><strong>PC Minimum Requirements</strong></td>
    </tr>
    <tr>
      <td colspan="4"><em>Does the computer that will run Trader Desk have the
        following minimum requirements?</em></td>
    </tr>
    <tr>
      <td align="right">&nbsp;</td>
      <td>Yes</td>
      <td>No</td>
      <td>Help</td>
    </tr>
    <tr>
      <td>300 MHz Processor </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_Proc").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Proc" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_Proc").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Proc" value="0" disabled="disabled"></td>
      <td align="center"><strong><a onclick="helpText(content[0], m1);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>64 MB RAM </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_RAM").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_RAM" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_RAM").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_RAM" value="0" disabled="disabled"></td>
      <td align="center"><strong><a onclick="helpText(content[1], m1);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>500 MB Free Hard Disk Space </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_DiskSpace").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_DiskSpace" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_DiskSpace").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_DiskSpace" value="0" disabled="disabled"></td>
      <td align="center"><strong><a onclick="helpText(content[2], m1);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>800x600 16 bit Colour Monitor </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_Resolution").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Resolution" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_Resolution").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Resolution" value="0" disabled="disabled"></td>
      <td align="center"><strong><a onclick="helpText(content[3], m1);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>16 Speed CD ROM Drive </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_CD").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_CD" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_CD").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_CD" value="0" disabled="disabled"></td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td>56K modem and phone line and/or Internet connection </td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("PC_Modem").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Modem" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("PC_Modem").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="PC_Modem" value="0" disabled="disabled"></td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr align="center">
      <td colspan="4"><span id="m1" style="font-weight: bold;">&nbsp;</span></td>
    </tr>
    <tr>
      <td colspan="4"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="4"><strong>Internet Connection</strong></td>
    </tr>
    <tr>
      <td colspan="4"><em>Does the computer that Trader Desk will be installed
        on:</em></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td align="center">Yes</td>
      <td align="center">No</td>
      <td align="center">Help</td>
    </tr>
    <tr>
      <td>Have connection to the Internet? (NOT via your manufacturer) </td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("Internet_HaveCon").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_HaveCon" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("Internet_HaveCon").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_HaveCon" value="0" disabled="disabled">
      </td>
      <td align="center"><strong><a onclick="helpText(content[4], m2);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>What Internet Service Provider do you use at present?</td>
      <td colspan="3" align="center"><%=(rsDisplayAll.Fields.Item("Internet_ISP").Value)%>
      </td>
    </tr>
    <tr>
      <td>Do you know your Username and Password of your Internet connection?</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("Internet_UserPass").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_UserPass" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("Internet_UserPass").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_UserPass" value="0" disabled="disabled">
      </td>
      <td align="center"><strong><a onclick="helpText(content[5], m2);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td>Is the connection via an independent ISDN/ADSL Line?</td>
      <td colspan="3" align="center"><%=(rsDisplayAll.Fields.Item("Internet_ISDN").Value)%>
      </td>
    </tr>
    <tr>
      <td>Is the connection via a modem (min 56k recommended) with phone line</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("Internet_Modem").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_Modem" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("Internet_Modem").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_Modem" value="0" disabled="disabled">
      </td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td>Is the connection via a Network? (see question 3)</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("Internet_Network").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> name="Internet_Network" type="radio" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("Internet_Network").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> name="Internet_Network" type="radio" value="0" disabled="disabled">
      </td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td>Do you use AOL or CompuServe to connect to the Internet?</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("Internet_AOL").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_AOL" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("Internet_AOL").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Internet_AOL" value="0" disabled="disabled">
      </td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="4"><em>AOL 5 and CompuServe CANNOT be used as your ISP when
        using Trader Desk. We can supply an alternative ISP to you at no extra
        cost.</em></td>
    </tr>
    <tr>
      <td colspan="4"><span id="m2" style="font-weight: bold;">&nbsp;</span></td>
    </tr>
    <tr>
      <td colspan="4"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="4"><strong>Network Internet Connection</strong></td>
    </tr>
    <tr>
      <td colspan="4"><em>If your connection is via a network:</em></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td align="center">Yes</td>
      <td align="center">No</td>
      <td align="center">Help</td>
    </tr>
    <tr>
      <td>Is the connection via a Proxy Server?</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("NetInt_Proxy").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="NetInt_Proxy" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("NetInt_Proxy").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="NetInt_Proxy" value="0" disabled="disabled">
      </td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td>If YES, please state which one, for example: Microsoft </td>
      <td colspan="3" align="center"><%=(rsDisplayAll.Fields.Item("NetInt_Provider").Value)%>
      </td>
    </tr>
    <tr>
      <td>If YES, are the TCP/IP ports 21 and 1433 enabled for incoming and outgoing
        traffic?</td>
      <td align="center"><input <%If (CStr((rsDisplayAll.Fields.Item("NetInt_Ports").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="NetInt_Ports" value="1" disabled="disabled">
      </td>
      <td align="center"> <input <%If (CStr((rsDisplayAll.Fields.Item("NetInt_Ports").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="NetInt_Ports" value="0" disabled="disabled">
      </td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="4"><em>A proxy server makes connection to the Internet - TCP/IP
        communicates through a number of channels (ports) data between the server
        and the Internet. Data sent from Trader Desk to the Internet has to be
        sent through ports 21 and 1433. Net Pilot only uses port 80 - and therefore
        cannot send Trader Desk data.</em><br /> <br />
        If you are using Brighton or a Satellite Link, please be aware that Trader
        Desk CANNOT be installed under any circumstances.</td>
    </tr>
    <tr>
      <td colspan="4"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="4"><strong>Machine Ownership</strong></td>
    </tr>
    <tr>
      <td align="right">&nbsp;</td>
      <td>Yes</td>
      <td>No</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>Do you own the machine(s) that will be used to run Trader Desk?</td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("Ownership_Own").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Ownership_Own" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("Ownership_Own").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Ownership_Own" value="0" disabled="disabled"></td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td>If NO, have you been given the authority to put other software on the
        machine?</td>
      <td><input <%If (CStr((rsDisplayAll.Fields.Item("Ownership_Authority").Value)) = CStr("True")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Ownership_Authority" value="1" disabled="disabled"></td>
      <td> <input <%If (CStr((rsDisplayAll.Fields.Item("Ownership_Authority").Value)) = CStr("False")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Ownership_Authority" value="0" disabled="disabled"></td>
      <td align="center">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="4"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td colspan="4"><strong>Windows Operating System</strong></td>
    </tr>
    <tr>
      <td align="right">&nbsp;</td>
      <td align="center">Yes</td>
      <td align="center">No</td>
      <td align="center">Help</td>
    </tr>
    <tr>
      <td>What operating system is on the machine(s) that will run Trader Desk?      
        For Example: Windows 98/95/ME/NT4/2000/XP</td>
      <td colspan="3" align="center"><%=(rsDisplayAll.Fields.Item("Windows_OS").Value)%>
      </td>
    </tr>
    <tr>
      <td>If Trader Desk is to be installed onto a Windows NT4/2000/XP server
        or workstation, have you given the user(s) of Trader Desk admin rights
        for their local machine(s)?</td>
      <td align="center"> <input type="radio" name="Windows_Admin" value="1" disabled="disabled"></td>
      <td align="center">
<input type="radio" name="Windows_Admin" value="0" disabled="disabled"></td>
      <td align="center"><strong><a onclick="helpText(content[6], m3);" style="cursor: help;">?</a></strong></td>
    </tr>
    <tr>
      <td colspan="4"><span id="m3" style="font-weight: bold;">&nbsp;</span></td>
    </tr>
    <tr>
      <td colspan="4"><hr></td>
    </tr>
    <tr align="center">
      <td colspan="4"><a href="dms_popup.htm" target="_blank" onClick="MM_openBrWindow('dms_popup.htm','uapop','scrollbars=yes,resizable=yes,width=550,height=350'); return false;">View
        DMS Details</a></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td width="50%" align="right">Signed by:</td>
      <td width="50%"><%=(rsDisplayAll.Fields.Item("SignedBy").Value)%> </td>
    </tr>
    <tr>
      <td width="50%" align="right">Date (dd/mm/yyyy):</td>
      <td width="50%"><%=(rsDisplayAll.Fields.Item("SignedDate").Value)%> </td>
    </tr>
    <tr>
      <td colspan="2"><hr></td>
    </tr>
  </table>
  <table width="40%" border="0" align="center" cellpadding="0" cellspacing="5">
    <tr>
      <td width="50%" align="right">Sales Consultant:</td>
      <td> <input name="SalesConsultant" type="text" disabled="disabled" id="SalesConsultant" value="<%= Trim((rsDisplayAll.Fields.Item("SalesConsultant").Value)) %>"></td>
    </tr>
    <tr>
      <td width="50%" align="right">Installer:</td>
      <td> <input name="Installer" type="text" disabled="disabled" id="Installer" value="<%= Trim((rsDisplayAll.Fields.Item("Installer").Value)) %>"></td>
    </tr>
    <tr>
      <td align="right">Installation Date:</td>
      <td> <input name="InstallationDate" type="text" disabled="disabled" id="InstallationDate" value="<%= Trim((rsDisplayAll.Fields.Item("InstallationDate").Value)) %>"></td>
    </tr>
    <tr>
      <td width="50%" align="right"> VS Ref Number:</td>
      <td> <input name="VSReferenceNumber" disabled="disabled" type="text" id="VSReferenceNumber" value="<%= Trim((rsDisplayAll.Fields.Item("VSReferenceNumber").Value)) %>">
     </td>
    </tr>
    <tr>
      <td align="center"><div align="right">AutoExchangeClientID<br>
          <br>
        </div></td>
      <td align="center"> <div align="left">
          <input name="AutoExchangeClientID" disabled="disabled" type="text" value="<%= Trim((rsDisplayAll.Fields.Item("AutoExchangeClientID").Value))%>">
          <input type="hidden" name="postcode" id="postcode" value="<%=(rsDisplayAll.Fields.Item("PostCode").Value)%>">
        </div></td>
    </tr>
    <tr>
      <td align="center"><input type="submit" name="Submit" value="Submit" class="button"></td>
      <td align="center"><a href="edit.aspx" target="_blank">edit VS Ref Number<br> or AutoExchangeClientID</a> </td>
    </tr>
  </table>
  <p>&nbsp;</p>
  <input type="hidden" name="MM_insert" value="form1">
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordId" value="<%= rsDisplayAll.Fields.Item("uapID").Value %>">
</form>
<p>&nbsp;</p>
<p>
<!-- #include virtual ="/design/includes/footer.asp" -->
</p>
</body>
</html>
<%
rsDisplayAll.Close()
Set rsDisplayAll = Nothing
%>



0
Comment
Question by:mwoolgar
  • 4
  • 4
9 Comments
 
LVL 2

Expert Comment

by:lgawlik
ID: 11865997
My question is, why are you using a stored procedure here.  You aren't processing anything, just looking up data.  Sure, the data is formatted a bit differently than what you have stored in your tables (SALESCONSULTANT, INSTALLER), but you're not passing in values to calculate anything.

It appears what you would really want is a SQL View.  Something along the lines of


CREATE VIEW dbo.v_SalesDefault
AS

SELECT
  [uapID],
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS SALESCONSULTANT,
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS INSTALLER
FROM
  [UAP].[dbo].[uapForm] UF,
  [UAP].[dbo].[uapRepArea] URA,
  [UAP].[dbo].[uapReps] UR
WHERE
  SUBSTRING([PostCode],1,2) = URA.RepPostCode
  and URA.RepID = UR.repID

GO

Now, you can treat your view the same way you treat a table.  In your ASP code you'd simply create the database connection and query the database for something like,

rsDisplayAll.Source = "SELECT uapID, SALESCONSULTANT, INSTALLER  FROM dbo.v_SalesDefault WHERE uapID = " + Replace(rsDisplayAll__MMColParam, "'", "''") + ""

After your recordset is returned simply refer to the columns as
rsDisplayAll.Fields.Item("uapID").Value
rsDisplayAll.Fields.Item("SALESCONSULTANT").Value
rsDisplayAll.Fields.Item("INSTALLER").Value
;just as you've done in the code above.

Hope this helps
Lgawlik
0
 

Author Comment

by:mwoolgar
ID: 11868842
Thanks Lgawlik

I can see the logic in this. However, I get an error because I'm trying to show everything in uapForm as well as the salesconsultant or installer related to that id.
 
This is what I put into my code to replace the old one:

rsDisplayAll.Source = "SELECT uapID, SALESCONSULTANT, INSTALLER  FROM dbo.v_SalesDefault WHERE uapID = " + Replace(rsDisplayAll__MMColParam, "'", "''") + ""

Thanks

0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11870681
To call a stored procedure you would set up a command object with the command type of adCmdStoredProc...  http://devguru.com/Technologies/ado/quickref/command_commandtype.html
Set CommandText to the name of the sp...
Then add the parameters...
http://devguru.com/Technologies/ado/quickref/command_createparameter.html
and execute it...
http://devguru.com/Technologies/ado/quickref/command_execute.html
0
 
LVL 2

Expert Comment

by:lgawlik
ID: 11871344
Ok, I see what you're after.

Change your v_SalesDefault to

--*************************
CREATE VIEW dbo.v_SalesDefault
AS

SELECT
  UF.*,    -- this is the line that has changed
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS SALESCONSULTANT,
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS INSTALLER
FROM
  [UAP].[dbo].[uapForm] UF,
  [UAP].[dbo].[uapRepArea] URA,
  [UAP].[dbo].[uapReps] UR
WHERE
  SUBSTRING([PostCode],1,2) = URA.RepPostCode
  and URA.RepID = UR.repID

GO
--*************************

This will provide all columns in the uapForm (UF) table followed by the SALESCONSULTANT and INSTALLER fields.



Then change the rsDisplayAll.Source in your code to be

rsDisplayAll.Source = "SELECT *  FROM dbo.v_SalesDefault  WHERE uapID = " + Replace(rsDisplayAll__MMColParam, "'", "''") + ""

Hope this helps,
lgawlik
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Expert Comment

by:lgawlik
ID: 11887830
Miles,

How is that last suggestion working, or not working?
Let me know if you need any further assistance.

lgawlik
0
 

Author Comment

by:mwoolgar
ID: 11897919
Hi Lgawlik

I've been in meetings most of today so I'll try it again tomorrow. I tried it quickly and had a few problems but I want to test it again properly.

cheers for now

Miles
0
 

Author Comment

by:mwoolgar
ID: 11902326
Right, I'm back.

Unfortunately the new view code you gave me didn't work - something to do with columns clashing.
Rather than concentrating on the view itself - i.e. keep with the original view. How would you go about calling the view?

In another forum someone suggested this:


<%
if (IsNull(rsDisplayAll.Fields.Item("SalesConsultant").Value) = True) then
response.write(rsDisplayAll2.Item("salesconsultant").value)
else
end if
%>

So if there is no salesman in the field call a second recordset and get it to call the view. don't know yet if that will work.

Miles
0
 
LVL 2

Accepted Solution

by:
lgawlik earned 125 total points
ID: 11904126
Miles,

You use a view the exact same way you use a table.  A view is a way to look at a predefined sql statement as a table.
If you are getting 'column clashing', I suspect the definition of the view is the problem but without knowing the structure or the three tables involved I have no way of determining what the clash could possibly be.

Knowing you are using the
  [UAP].[dbo].[uapForm]
  [UAP].[dbo].[uapRepArea]
  [UAP].[dbo].[uapReps]
tables, I can guess that some columns may reside in multiple tables (typically FK columns)

The view I've written above does this:
1) Pull all data columns from the uapForm table (no problem there, no possible clashes)
2) Add a column called SalesConsultant, if the RepFirstName and RepLastName are defined in multiple tables (like uapForm, uapRepArea, uapReps)
3) Add a column called Installer, again if the RepFirstName and RepLastName are defined in multiple tables (like uapForm, uapRepArea, uapReps)

The Where clause could potentially have a name ambiguity problem with the use of the PostCode column.  For example, if the uapForm and uapReps tables both have a column named PostCode, you'd get an error stating that an ambiguous column was found.

Another thing I like to do is to explicity list each of the possible data columns instead of list all using '*'.  If changed the code below to reflect these changes

--*************************
CREATE VIEW dbo.v_SalesDefault
AS

SELECT
  UF.uapID,
  UF.PostCode,
  UF.DealershipName,
  UF.TradingName,
  UF.SalesContact,
  UF.Address,
  UF.Town,
  UF.County,
  UF.PhoneNumber,
  UF.FaxNumber,
  UF.Email,
  UF.EnqFormPref,
  UF.WebAddress,
  UF.Franchises,
  UF.Servs_New,
  UF.Servs_Used,
  UF.Servs_Service,
  UF.Servs_BodyShop,
  UF.Servs_Parts,
  UF.Servs_Showroom,
  UF.PC_Proc,
  UF.PC_RAM,
  UF.PC_DiskSpace,
  UF.PC_Resolution,
  UF.PC_CD,
  UF.PC_Modem,
  UF.Internet_HaveCon,
  UF.Internet_ISP,
  UF.Internet_UserPass,
  UF.Internet_ISDN,
  UF.Internet_Modem,
  UF.Internet_Network,
  UF.Internet_AOL,
  UF.NetInt_Proxy,
  UF.NetInt_Provider,
  UF.NetInt_Ports,
  UF.Ownership_Own,
  UF.Ownership_Authority,
  UF.Windows_OS,
  UF.SignedBy,
  UF.SignedDate,
  UF.InstallationDate,
  UF.VSReferenceNumber,
  UF.AutoExchangeClientID,
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS SALESCONSULTANT,
  rtrim(ltrim( RepFirstName))+' '+ rtrim(ltrim( RepSurname)) AS INSTALLER
FROM
  [UAP].[dbo].[uapForm] UF,
  [UAP].[dbo].[uapRepArea] URA,
  [UAP].[dbo].[uapReps] UR
WHERE
  SUBSTRING([UF.PostCode],1,2) = URA.RepPostCode
  and URA.RepID = UR.repID

GO
--*************************
0
 

Author Comment

by:mwoolgar
ID: 11950005
Thanks Lgawlik

I tried your method and with a few modifications got it to work.

I also found a far easier method which one of the dba's suggested.

He suggested creating two triggers on the table so that every time the postcode is entered a trigger automatically enters in the sales consultant and/or installer into their respective fields in the uapForm.

The triggers are:

--------------------------------
UpdateInstaller
--------------------------------


CREATE Trigger [UpdateInstaller]
On [dbo].[uapForm]
For Insert, Update
As
If Update (PostCode)
Begin
Update uapForm Set Installer =
(
      Select rtrim(ltrim(RepFirstName))+' '+ rtrim(ltrim(RepSurname))
      From uapReps, uapRepArea
      Where uapRepArea.RepPostCode = Substring([PostCode],1,2)
      And uapReps.RepID = uapRepArea.RepID
)
Where Installer = ''
End


------------------------
UpdateSalesConsultant
------------------------

CREATE Trigger [UpdateSalesConsultant]
On [dbo].[uapForm]
For Insert, Update
As
If Update (PostCode)
Begin
Update uapForm Set SalesConsultant =
(
      Select rtrim(ltrim(RepFirstName))+' '+ rtrim(ltrim(RepSurname))
      From uapReps, uapRepArea
      Where uapRepArea.RepPostCode = Substring([PostCode],1,2)
      And uapReps.RepID = uapRepArea.RepID
)
Where SalesConsultant = ''
End


Many thanks for your time and patience. I hope this example helps others.

Cheers

Miles
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Problem to be resolved in this article Currently, development of website and web application can be done without writing thousands of lines of programming code by hand. Description This can be done through by using a open source framework such …
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now