[/quote] <% If IsObject(Session("s1a_conn")) Then Set conn = Session("s1a_conn") Else Set conn = Server.CreateObject("ADODB.Connection") conn.open "s1a","UID","pwd" Set Session("s1a_conn") = conn End If sql = "SELECT DISTINCT Count(dbo.tbl_common_ticket.vendor_code) AS ticket_number, dbo.tbl_common_ticket.vendor_code AS vendor_description FROM dbo.tbl_common_ticket WHERE (((dbo.tbl_common_ticket.ticket_status_code)=3) AND ((dbo.tbl_common_ticket.work_queue_code)=1)) GROUP BY dbo.tbl_common_ticket.vendor_code ORDER BY dbo.tbl_common_ticket.vendor_code " Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn, -1, -1 If rs.eof Then rs.AddNew End If Set Session("VZE_O_NM_rs") = rs %> <% On Error Resume Next rs.MoveFirst do while Not rs.eof rs.Fields("vendor_description").Value = vendor_name select case vendor_name case 1 vendor_name "VZE" case else vendor_namee "Other" End Select %> <% rs.MoveNext loop %>
Vendor Statistics
<%=Server.HTMLEncode(rs.Fields("vendor_description").Value )%> <%=Server.HTMLEncode(rs.Fields("ticket_number").Value)%>
Any help in working with this formatting is appreciated. Thanks, Brandon *** Sent via Developersdex [url]http://www.developersdex.com[/url] *** Don't just participate in USENET...get rewarded for it! [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <#qmn25qLEHA.1556@TK2MSFTNGP10.phx.gbl> [ref] => [htmlstate] => on_nl2br [postusername] => Brandon [ip] => [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> ASP SQL query question - ASP Database

ASP SQL query question - ASP Database

Greetings, I have a question about a database query from an asp page. My query is functional and produces the data that I need; however, one column contains a numeric value that I need to translate to a name. For example, the number 1 needs to show up as "VZE" on the loaded web page. The second column will be the number of tickets, this will not need to be modified. This is the current resulting web page: Vendor Statistics 1 50 2 12 3 8 4 18 5 8 6 5 7 13 8 14 10 3 11 44 ...

  1. #1

    Default ASP SQL query question

    Greetings,

    I have a question about a database query from an asp page. My query is
    functional and produces the data that I need; however, one column
    contains a numeric value that I need to translate to a name. For
    example, the number 1 needs to show up as "VZE" on the loaded web page.
    The second column will be the number of tickets, this will not need to
    be modified.

    This is the current resulting web page:

    Vendor Statistics
    1 50
    2 12
    3 8
    4 18
    5 8
    6 5
    7 13
    8 14
    10 3
    11 44


    This is the code to the query and page layout.

    <%Language=VBScript%>
    <!-- #include virtual="adovbs.inc" -->
    <HTML><HEAD><META HTTP-EQUIV="Content-Type"
    CONTENT="text/html;cht=windows-1252"><TITLE>O/NM</TITLE></HEAD><BODY
    >
    <%


    If IsObject(Session("s1a_conn")) Then
    Set conn = Session("s1a_conn")
    Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "s1a","UID","pwd"
    Set Session("s1a_conn") = conn
    End If

    sql = "SELECT DISTINCT Count(dbo.tbl_common_ticket.vendor_code) AS
    ticket_number, dbo.tbl_common_ticket.vendor_code AS vendor_description
    FROM dbo.tbl_common_ticket WHERE
    (((dbo.tbl_common_ticket.ticket_status_code)=3) AND
    ((dbo.tbl_common_ticket.work_queue_code)=1)) GROUP BY
    dbo.tbl_common_ticket.vendor_code ORDER BY
    dbo.tbl_common_ticket.vendor_code "
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, -1, -1
    If rs.eof Then
    rs.AddNew
    End If
    Set Session("VZE_O_NM_rs") = rs

    %>
    <TABLE width=100px border=0px align=left>
    <THEAD>
    <TH colspan="2">
    <font face="courier" size="1" COLOR=#000000>Vendor
    Statistics</FONT></TH>
    </THEAD>
    <TBODY>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof

    rs.Fields("vendor_description").Value = vendor_name

    select case vendor_name
    case 1 vendor_name "VZE"
    case else vendor_namee "Other"
    End Select

    %>
    <TR VALIGN=TOP>
    </TR>
    <TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><font face="courier" size="1"
    COLOR=#000000><%=Server.HTMLEncode(rs.Fields("vend or_description").Value
    )%></font></b></td>
    <TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><font face="courier" size="1"
    COLOR=#000000><%=Server.HTMLEncode(rs.Fields("tick et_number").Value)%></
    FONT></TD>
    <%
    rs.MoveNext
    loop
    %>
    </TBODY>
    </TABLE>
    </BODY>
    </HTML>

    Any help in working with this formatting is appreciated.

    Thanks,
    Brandon

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brandon Guest

  2. #2

    Default RE: ASP SQL query question

    Where are you getting the vendor name from? I see no place where you retrieve the vendor name associated with your vendor number. Is it in another table in the database? If so, you need to include a join to that table in your SQL and select the field that contains vendor name in your SELECT statement

    BTW, you will probably see a fairly significant perfomance improvment by putting your SQL i a stored procedure on the server and calling that from the web page

    Joh

    John Beschler Guest

  3. #3

    Default RE: ASP SQL query question

    Thanks for your reply.

    The vendor name is not a part of any db. I know what the translations
    are
    supposed to be, so I wanted to use code to translate the vendor number
    to a
    name before displaying the html.

    Unfortunately, stored procedures are not likely to happen. I work for a
    large
    company and the dba is most likely not going to add procedures for such
    a
    small project.

    Thanks for your help!

    Brandon

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brandon Guest

  4. #4

    Default Re: ASP SQL query question

    Brandon wrote:
    > Greetings,
    >
    > I have a question about a database query from an asp page. My query is
    > functional and produces the data that I need; however, one column
    > contains a numeric value that I need to translate to a name. For
    > example, the number 1 needs to show up as "VZE" on the loaded web
    > page. The second column will be the number of tickets, this will not
    > need to be modified.
    >
    > This is the current resulting web page:
    >
    > Vendor Statistics
    > 1 50
    > 2 12
    > 3 8
    > 4 18
    > 5 8
    > 6 5
    > 7 13
    > 8 14
    > 10 3
    > 11 44
    >
    >
    Please show the intended results.

    Bob Barrows
    --
    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 Guest

  5. #5

    Default Re: ASP SQL query question


    The current web page is:

    Vendor Statistics
    1 50
    2 12
    3 8
    4 18
    5 8
    6 5
    7 13
    8 14
    10 3
    11 44

    And my intention is to turn the left column (1-11) into names that are
    not
    pulled from a db, rather they are to be translated in the page code. 1
    needs
    to be VZE, 2 is VZW, and so forth:

    Vendor Statistics
    VZE 50
    VZW 12
    AAA 8
    BBB 18
    CCC 8
    DDD 5

    etc.

    Thanks again.

    Brandon

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brandon Guest

  6. #6

    Default Re: ASP SQL query question

    Brandon wrote:
    > The current web page is:
    >
    > Vendor Statistics
    > 1 50
    > 2 12
    > 3 8
    > 4 18
    > 5 8
    > 6 5
    > 7 13
    > 8 14
    > 10 3
    > 11 44
    >
    > And my intention is to turn the left column (1-11) into names that are
    > not
    > pulled from a db, rather they are to be translated in the page code. 1
    > needs
    > to be VZE, 2 is VZW, and so forth:
    >
    > Vendor Statistics
    > VZE 50
    > VZW 12
    > AAA 8
    > BBB 18
    > CCC 8
    > DDD 5
    >
    > etc.
    >
    So are these names hard-coded somewhere? Or do you intend to hard-code them
    into every page that needs to display vendor data (ughhhh!)?

    My suggestion is to create a lookup table for these names in your database,
    since you say such a lookup table does not already exist.

    CREATE TABLE VendorLookup (
    vendor_code int,
    vendor_description varchar(5) )

    Populate it with the vendor codes and names. This will allow you to do the
    following in your query (I am going to switch to using table aliases to make
    this mess a little more readable - plus I got rid of the unneeded DISTINCT
    keyword - the GROUP BY is already guaranteeing distinct rows):

    sql = "SELECT Count(t.vendor_code) AS
    ticket_number, v.vendor_description
    FROM dbo.tbl_common_ticket t INNER JOIN dbo.Vendor_lookup v
    ON t.vendor_code = v.vendor_code
    WHERE t.ticket_status_code=3 AND t.work_queue_code=1
    GROUP BY t.vendor_code, v.vendor_description
    ORDER BY t.vendor_code "

    If for some strange reason you can't create a lookup table, I suggest
    creating an xml file. I am not going to waste time showing you how to do
    this unless you reply that you cannot create a lookup table

    Bob Barrows

    --
    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 Guest

  7. #7

    Default Re: ASP SQL query question


    Thanks for the help, Bob. Your suggestion worked great and I have the
    web
    page I was aiming to create.

    Thanks again,
    Brandon

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brandon Guest

Similar Threads

  1. Query Question Please HELP
    By flooker in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 26th, 04:45 PM
  2. Query Question
    By Jason MacKenzie in forum Coldfusion - Advanced Techniques
    Replies: 51
    Last Post: November 18th, 04:58 AM
  3. SOS! Query Question
    By design in progress in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 11th, 05:39 AM
  4. 6.1 Query of Query Question. Ref to own Col
    By smcgovern in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 24th, 10:17 PM
  5. SQL query question
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: February 3rd, 11:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139