473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Data Not Displaying From SQL Express 2005

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
%>
Jul 11 '08 #1
5 1783
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.

Jul 11 '08 #2
On 12 Jul, 00:00, Old Pedant <OldPed...@discussions.microsoft.com>
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
Jul 16 '08 #3
PeakFreak wrote:
On 12 Jul, 00:00, Old Pedant <OldPed...@discussions.microsoft.com>
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"
Jul 16 '08 #4
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.
Jul 16 '08 #5
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>
Jul 17 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: jlea | last post by:
Does anyone know of a nice little .NET class that throws the contents of a supplied file to a scrollable window/form/control?
9
by: MR | last post by:
I get the following Exception "The data at the root level is invalid. Line 1, position 642" whenever I try to deserialize an incoming SOAP message. The incoming message is formed well and its...
6
by: | last post by:
Hi all, is there a better way to stream binary data stored in a table in sql 2005 to a browser in .net 2.0? Or is the code same as in .net 1.1? We noticed that in certain heavy load scenarios,...
0
by: erlend.dalen | last post by:
Hi all! I'm a newbie to C# and .NET and I'm playing around in Visual C# 2005 Express Edition. I tried to use the wizards to create a table in SQL 2005 Express Ed. and displaying that data in a...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
1
by: Peter | last post by:
I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005...
15
by: Kevin Hanken | last post by:
Hi, Pretty much a newbie to aspnet and xp -- I am using XP Pro, Service Pack 2. I installed IIS, set up a virtual directory, created an aspx file and pasted in a little code from the w3schools...
8
by: Lykins | last post by:
We currently use Access 2003 in our company and have had this issues from every version from Access 97 to 2003. We deal with large databases and run a lot of queries over tables with millions of...
3
by: RichT | last post by:
Hi all, I am experiencing some odd behaviour with a DataGridView. The DataGridView is bound to a DataTable, which is populated with data from a csv file. The column Headings appear fine,...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.