" & vbcrlf
Response.write HTMLList
objRS.Close
objRS2.Close
Set objRS = Nothing
Set objRS2 = nothing
End Function
' For Bill To Address
Function GenerateBillToFile
sqlQuery = "SELECT Company.Name as CompanyName, Contacts.Name as PersonName, Orders.BillTo_Address1, Orders.BillTo_Address2, Orders.BillTo_City, Orders.BillTo_State, Orders.BillTo_Zip From Company left join Orders on Company.ID=Orders.Company_ID left join Contacts on Contacts.ID = Orders.Contact_ID where Orders.Job_ID =" & request("job_id") & " and Orders.status <> 'Incomplete';"
sqlQuery2 = "SELECT * FROM Job WHERE ID = " & request("job_id")
set objRS = objConn.Execute(sqlQuery)
set objRS2 = objConn.Execute(sqlQuery2)
Job = objRS2("Job")
'----- List records -----
intCounter = 0
intFieldCount = objRS.Fields.Count - 1
JobDescription = objRS2("Job")
HTMLList = " ('Bill To' addresses) Bidders List for " & JobDescription & "" & vbcrlf MailingList = "Company Name, Contact Name, Address 1, Address 2, City, State, Zip, "&vbcrlf do while not objRS.EOF intCounter = intCounter + 1 For i = 0 To intFieldCount varFieldValue = objRS(i) intType = objRS(i).Type SELECT CASE objRS(i).Name CASE "ShipTo City" if (trim(varfieldvalue & "") <> "") then HTMLList = HTMLList & varFieldValue & ", " end if MailingList = MailingList & varFieldValue & "," CASE "ShipTo State" if (trim(varfieldvalue & "") <> "") then HTMLList = HTMLList & varFieldValue & " " end if MailingList = MailingList & varFieldValue & "," CASE "ShipTo Zip" if (trim(varfieldvalue & "") <> "") then HTMLList = HTMLList & varFieldValue & " " end if MailingList = MailingList & varFieldValue CASE ELSE if (trim(varfieldvalue & "") <> "") then HTMLList = HTMLList & varFieldValue & " " & vbcrlf end if MailingList = MailingList & varFieldValue & "," END SELECT Next HTMLList = HTMLList & " " & vbcrlf MailingList = MailingList & vbcrlf objRS.Movenext loop ' remove spaces and other invalid characters from the jobdescription name Do While InStr(1, JobDescription, " ") JobDescription = Replace(JobDescription, " ", "") JobDescription = Replace(JobDescription, ",", "") JobDescription = Replace(JobDescription, "\", "") JobDescription = Replace(JobDescription, "/", "") JobDescription = Replace(JobDescription, "`", "") JobDescription = Replace(JobDescription, "'", "") JobDescription = Replace(JobDescription, ";", "") JobDescription = Replace(JobDescription, ":", "") Loop Job = Replace(Job, ",", "") Job = Replace(Job, "/", "") Job = Replace(Job, "\", "") Job = Replace(Job, ".", "") Job = Replace(Job, "`", "") Job = Replace(Job, "'", "") Job = Replace(Job, ":", "") Job = Replace(Job, ";", "") Job = Replace(Job, "-", "") Job = Replace(Job, "@", "") Job = Replace(Job, "#", "") Job = Replace(Job, "&", "") Response.Write " (Bill to addresses) Right click here and select 'Save target as...' to download this Comma Seperated Values (*.CSV) Text File. " Response.write "You can then import this file into Access, Excel, or any other program which supports comma seperated data importation." Response.write "
|