Data Not Displaying From SQL Express 2005

Discussion in 'ASP General' started by brendan.mcgrath@smith-group.com, Jul 11, 2008.

  1. Guest

    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
    %>
     
    , Jul 11, 2008
    #1
    1. Advertising

  2. Old Pedant Guest

    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.
     
    Old Pedant, Jul 12, 2008
    #2
    1. Advertising

  3. PeakFreak Guest

    On 12 Jul, 00:00, Old Pedant <>
    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
     
    PeakFreak, Jul 16, 2008
    #3
  4. PeakFreak wrote:
    > On 12 Jul, 00:00, Old Pedant <>
    > 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"
     
    Bob Barrows [MVP], Jul 16, 2008
    #4
  5. 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.
     
    Bob Barrows [MVP], Jul 16, 2008
    #5
  6. PeakFreak Guest

    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>
     
    PeakFreak, Jul 17, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ad
    Replies:
    7
    Views:
    703
    Scott Allen
    Apr 11, 2005
  2. =?Utf-8?B?dmE=?=
    Replies:
    4
    Views:
    2,867
    =?Utf-8?B?dmE=?=
    Feb 22, 2006
  3. Replies:
    1
    Views:
    502
  4. Brad  Brening
    Replies:
    0
    Views:
    471
    Brad Brening
    Mar 1, 2007
  5. Jake Henderson

    Visual Web Developer 2005 Express and SQL 2005 Express

    Jake Henderson, Mar 10, 2006, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    263
    Jake Henderson
    Mar 10, 2006
Loading...

Share This Page