Data Not Displaying From SQL Express 2005

[brendan.mcgrath]

Hi All

DB - SQL Express 2005
ST - ASP VBScript
Dev Env OS - Win XP IIS5

I am trying to retrieve records from the DB and write them into a csv
file/display onscreen for further processing. What is happening is the
records are retrieved but when I write them into the file or display
them on screen only the first and ninth fields display (fAddress1 &
description) all the rest just come out blank.
If I comment out the first field then the 2nd and ninth display and so
on.
I have tried loading the data into individual variables but the same
still happens and this is now begining to get rather annoying ;-]

Code is below for writing the file:-

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/mws/Connections/connsmp.asp" -->
<%

varSQL = "SELECT TEstJob.fNotes, TEstJob.fUserID,
TEstJob.fInitialLoggedBy, TEstJob.fInitialLogDateTime, "
varSQL = varSQL & "TEstJob.fClientOrderNo,
TEstJob.fEngsSubiesAssigned2NonCompleteVisits, TEstSite.fName AS
SITENAME, "
varSQL = varSQL & "TEstContact.fName AS CONTACTNAME,
TEstContact.fDirectPhoneNo, TEstContact.fAddressPostCode,
TEstContact.fAddressCounty, "
varSQL = varSQL & "TEstContact.fAddressCity, TEstContact.fAddressArea,
TEstContact.fAddress2, TEstContact.fAddress1, "
varSQL = varSQL & "TEstJobType.fName AS JOBTYPENAME,
est_groups.description "
varSQL = varSQL & "FROM TEstJob LEFT JOIN TEstSite ON TEstJob.fSite =
TEstSite.fID "
varSQL = varSQL & "LEFT JOIN TEstContact ON TEstSite.fSiteAddress =
TEstContact.fID "
varSQL = varSQL & "LEFT JOIN TEstJobType ON TEstJob.fJobType =
TEstJobType.fUserID "
varSQL = varSQL & "LEFT JOIN est_groups ON TEstJob.fInitialLoggedBy =
est_groups.fid "
varSQL = varSQL & "WHERE TEstJob.fJobStatus = 4"

Dim rsJobs
Dim rsJobs_numRows

Set rsJobs = Server.CreateObject("ADODB.Recordset")
rsJobs.ActiveConnection = MM_connsmp_STRING
rsJobs.Source = varSQL
rsJobs.CursorType = 0
rsJobs.CursorLocation = 2
rsJobs.LockType = 1
rsJobs.Open()

rsJobs_numRows = 0
%>

<%
If NOT rsJobs.EOF Then
Set FSO = Server.CreateObject("Scripting.FileSystemObject")
Set theFile = FSO.CreateTextFile(Server.MapPath("/MWS/jobs.csv"),
true, false)

While Not rsJobs.EOF
varRecordLine = rsJobs.Fields.Item("fAddress1").Value & "," &
rsJobs.Fields.Item("fAddress2").Value & ","
varRecordLine = varRecordLine &
rsJobs.Fields.Item("fAddressArea").Value & "," &
rsJobs.Fields.Item("fAddressCity").Value & ","
varRecordLine = varRecordLine &
rsJobs.Fields.Item("fAddressCounty").Value & "," &
rsJobs.Fields.Item("fAddressPostCode").Value & ","
varRecordLine = varRecordLine & rsJobs.Fields.Item("fUserID").Value
& "," & rsJobs.Fields.Item("CONTACTNAME").Value & ","
varRecordLine = varRecordLine &
rsJobs.Fields.Item("description").Value & "," &
rsJobs.Fields.Item("fInitialLogDateTime").Value & ","
varRecordLine = varRecordLine &
rsJobs.Fields.Item("fDirectPhoneNo").Value & "," &
rsJobs.Fields.Item("JOBTYPENAME").Value & ","
varRecordLine = varRecordLine & rsJobs.Fields.Item("fNotes").Value &
"," &
rsJobs.Fields.Item("fEngsSubiesAssigned2NonCompleteVisits").Value
theFile.WriteLine(varRecordLine)
rsJobs.MoveNext()

Wend

theFile.Close
rsJobs.Close()
Set rsJobs = Nothing
Set FSO = Nothing
Response.Redirect("/MWS/jobs.csv")
End If
%>

 

[Old Pedant]

Probably caused by something like this:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80

But in any case, you could fix this and make it oodles more efficient by
using GetString. Thus:

<!--#include virtual="/mws/Connections/connsmp.asp" -->
<%
Dim rs, conn, SQL
SQL = "SELECT TC.fAddress1, TC.fAddress2, TC.fAddressArea, TC.fAddressCity,
TC.fAddressCounty, TC.fAddressPostCode, " _
& " TJ.fUserID, TC.fName, EG.Description, TJ.fInitialLogDateTime,
" _
& " TC.fDirectPhoneNo, TestJobType, TJ.fNotes,
TJ.fEngsSubiesAssigned2NonCompleteVisits "
& " FROM TEstJob AS TJ LEFT JOIN TEstSite AS TS ON TJ.fSite = TS.fID " _
& " LEFT JOIN TEstContact AS TC ON TS.fSiteAddress = TC.fID " _
& " LEFT JOIN TEstJobType AS TJT ON TJ.fJobType = TJT.fUserID " _
& " LEFT JOIN est_groups AS EG ON TJ.fInitialLoggedBy = EG.fid " _
& " WHERE TJ.fJobStatus = 4"


Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connsmp_STRING
Set rs = conn.Execute( SQL )

If NOT rs.EOF Then
Set FSO = Server.CreateObject("Scripting.FileSystemObject")
Set theFile = FSO.CreateTextFile(Server.MapPath("/MWS/jobs.csv"), true,
false)
theFile.Write rs.GetString( , , "," )
theFile.Close
End If
rs.close
conn.close
%>

Note that your CSV file will fall flat on its face if any of your fields
have a comma in them, but the GETSTRING() solution is no different than your
original in that regard.

 

[PeakFreak]

Probably caused by something like this:http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80

But in any case, you could fix this and make it oodles more efficient by
using GetString.  Thus:

<!--#include virtual="/mws/Connections/connsmp.asp" -->
<%
Dim rs, conn, SQL
SQL = "SELECT TC.fAddress1, TC.fAddress2, TC.fAddressArea, TC.fAddressCity,
TC.fAddressCounty, TC.fAddressPostCode, " _
    &       " TJ.fUserID, TC.fName, EG.Description, TJ.fInitialLogDateTime,
" _
    &       " TC.fDirectPhoneNo, TestJobType, TJ.fNotes,
TJ.fEngsSubiesAssigned2NonCompleteVisits "
    & " FROM TEstJob AS TJ LEFT JOIN TEstSite AS TS ON TJ.fSite = TS.fID " _
    & " LEFT JOIN TEstContact AS TC ON TS.fSiteAddress = TC.fID " _
    & " LEFT JOIN TEstJobType AS TJT ON TJ.fJobType = TJT.fUserID "_
    & " LEFT JOIN est_groups AS EG ON TJ.fInitialLoggedBy = EG.fid " _
    & " WHERE TJ.fJobStatus = 4"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connsmp_STRING
Set rs = conn.Execute( SQL )

If NOT rs.EOF Then
    Set FSO = Server.CreateObject("Scripting.FileSystemObject")
    Set theFile = FSO.CreateTextFile(Server.MapPath("/MWS/jobs.csv"), true,
false)
    theFile.Write rs.GetString( , , "," )
    theFile.Close
End If
rs.close
conn.close
%>

Note that your CSV file will fall flat on its face if any of your fields
have a comma in them, but the GETSTRING() solution is no different than your
original in that regard.

Hi Old Pendant.

Awesome, that has worked a treat, thank you very much for your help on
this.
The GetString() function is brilliant.
I appreciate what you say about the csv falling on it's backside, I
was intending resolving that matter by quote delimiting the fields, I
just needed to get the basic export working first.
Does the GetString() have the ability to insert the quote delimiting
automatically?

I like what you do with the prefixing for the table names, e.g TC, TJ
etc, I ta\ke it these are just a quick way of creating an alias for
the tablename?

Thanks agian
Bren

 

[Bob Barrows [MVP]]

Hi Old Pendant.

Awesome, that has worked a treat, thank you very much for your help on
this.
The GetString() function is brilliant.
I appreciate what you say about the csv falling on it's backside, I
was intending resolving that matter by quote delimiting the fields, I
just needed to get the basic export working first.
Does the GetString() have the ability to insert the quote delimiting
automatically?

No, but you can do it in your sql statement:

SQL = "SELECT '"' + TC.fAddress1 + '"' As Address1, ...

I like what you do with the prefixing for the table names, e.g TC, TJ
etc, I ta\ke it these are just a quick way of creating an alias for
the tablename?

It's not "a quick way": it's THE way to assign aliases for the table names.
It also works for column names.

 

[Bob Barrows [MVP]]

No, but you can do it in your sql statement:

SQL = "SELECT '"' + TC.fAddress1 + '"' As Address1, ...

sigh - again, too much in a rush. Those quotes need to be escaped:

SQL = "SELECT '""' + TC.fAddress1 + '""' As Address1, ...

 

[PeakFreak]

Hi Again

Thanks for the pointers Bob, I have just one more quick question and
then I should be able to stop bothering you.
I now need to display the same data on screen so after looking deeper
into the GetString() function I came across the GetRows() function so
I am now using that to load the recordset into a 2 dimensional array,
which is fine apart from when I try to display the TJ.fNotes data
(varchar (max) datatype, again it doesn't display and the element of
the array that stores this column data is zero length. Any ideas as
this is bally frustrating ;-)

Code below.

Cheers
Bren

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/mws/Connections/connsmp.asp" -->
<%
Dim varSQL, rsJobs, arrJobs, conn

varSQL = "SELECT TC.fName, TC.fAddress1, TC.fAddress2,
TC.fAddressArea, TC.fAddressCity, TC.fAddressCounty, "
varSQL = varSQL & "TC.fAddressPostCode, TC.fDirectPhoneNo, TJ.fUserID,
TJ.fInitialLogDateTime, TJ.fNotes, "
varSQL = varSQL & "TJ.fEngsSubiesAssigned2NonCompleteVisits,
TJ.fClientOrderNo, EG.Description, TJT.fName, "
varSQL = varSQL & "TCN.fName, TCN.fUserID, TCL.fCompanyName,
TCL.fUserID "
varSQL = varSQL & "FROM TEstJob AS TJ "
varSQL = varSQL & "LEFT JOIN TEstSite AS TS ON TJ.fSite = TS.fID "
varSQL = varSQL & "LEFT JOIN TEstContact AS TC ON TS.fSiteAddress =
TC.fID "
varSQL = varSQL & "LEFT JOIN TEstJobType AS TJT ON TJ.fJobType =
TJT.fUserID "
varSQL = varSQL & "LEFT JOIN est_groups AS EG ON TJ.fInitialLoggedBy =
EG.fid "
varSQL = varSQL & "LEFT JOIN TEstContract AS TCN ON TJ.fContract =
TCN.fID "
varSQL = varSQL & "LEFT JOIN TEstClient AS TCL ON TCL.fID =
TCN.fClient "
varSQL = varSQL & "WHERE TJ.fJobStatus = 4 "

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connsmp_STRING
Set rsJobs = conn.Execute(varSQL)

If Not rsJobs.EOF Then
arrJobs = rsJobs.GetRows()
End If

rsJobs.Close
Set rsJobs = Nothing
conn.Close
Set conn = Nothing

%>

Here is the HTML I am using to display the TJ.fNotes column, all other
array elements display fine apart from the varchar(max), all the rest
are varchar(255).

<tr>
<td colspan="4" align="left" valign="top"><div
align="left">Description of Works <br />
<%=(arrJobs(10, iRowCntr))%>&nbsp;</div></td>
</tr>