Solved

ASP stored procedure

Posted on 2004-08-22
9
523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
what is the best Integrated development environment 2 41
Is sending authentication through SOAP secure? 3 38
compare date to getdate() 8 18
JS to redirect to prev page 8 25
When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

730 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