Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Data Not Displaying From SQL Express 2005

Reply
Thread Tools

Data Not Displaying From SQL Express 2005

 
 
brendan.mcgrath@smith-group.com
Guest
Posts: n/a
 
      07-11-2008
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("fEngsSubiesAssigned2NonComplet eVisits").Value
theFile.WriteLine(varRecordLine)
rsJobs.MoveNext()

Wend

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


 
Reply With Quote
 
 
 
 
Old Pedant
Guest
Posts: n/a
 
      07-11-2008
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.



 
Reply With Quote
 
 
 
 
PeakFreak
Guest
Posts: n/a
 
      07-16-2008
On 12 Jul, 00:00, Old Pedant <(E-Mail Removed)>
wrote:
> 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
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-16-2008
PeakFreak wrote:
> On 12 Jul, 00:00, Old Pedant <(E-Mail Removed)>
> wrote:
>> 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:

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


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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-16-2008
Bob Barrows [MVP] wrote:
> 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, ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
PeakFreak
Guest
Posts: n/a
 
      07-17-2008
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>
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Web Developer 2005 and SQL Server Express 2005 Brad Brening ASP .Net 0 03-01-2007 04:28 PM
xp sp2 visual studio 2005 sql express 2005 bookcatalagapp seacoff@gmail.com ASP .Net 1 10-19-2006 03:09 PM
Migrating ASPNETDB from SQL 2005 Express edition to SQL 2000 =?Utf-8?B?RGVyZWs=?= ASP .Net 1 06-09-2006 01:40 PM
Visual Web Developer 2005 Express and SQL 2005 Express Jake Henderson ASP .Net Web Services 0 03-10-2006 10:18 PM
Please help: steps to go from SQL Express and SQL 2005 Production =?Utf-8?B?dmE=?= ASP .Net 4 02-22-2006 08:46 PM



Advertisments