help to translate my pseudocode to asp

Discussion in 'ASP General' started by PeterL, Apr 29, 2004.

  1. PeterL

    PeterL Guest

    Please help me out:
    I´ve got a tblVetskebalans on this page
    http://www12.brinkster.com/vatskebalans/tblvetskebalans11.asp

    As you see 1 sk "Personnummer"has many date.. Personnummer = rs1("ID")
    Please help me with this code:
    1) rs1("ID") = "&Request.Form(""rapport"")&"
    2) find the first date where this ID occures
    3) list al post that exist for this date (and this ID)
    4) find the next date

    Any halp will be appreciated
    /Peter
     
    PeterL, Apr 29, 2004
    #1
    1. Advertisements

  2. PeterL

    Steven Burn Guest

    strTemp = rst("ID")
    strDate = Date()

    Do Until rst.eof
    If strTemp = strDate Then
    Response.write strTemp & " contains " & strDate
    End if
    rst.moveNext
    Loop

    --

    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    Personal favourites

    WebGrid - www.webgrid.co.uk
    Freeware Arena - www.freewarearena.com
    Freeware Home - www.freewarehome.com
    Pricelessware - http://www.pricelessware.org
    Practically Nerded - http://mvps.org/PracticallyNerded/
    Bugs, Glitches n stuff - http://mvps.org/inetexplorer/Darnit.htm
    Calendar of Updates - http://www.dozleng.com/updates/index.php?&act=calendar
     
    Steven Burn, Apr 29, 2004
    #2
    1. Advertisements

  3. PeterL

    PeterL Guest

    I tried with this, but failed:
    <%
    Set vbalans = Server.CreateObject("ADODB.Connection")
    vbalans.Provider = "Microsoft.Jet.OLEDB.4.0"
    MdbFilePath = Server.MapPath("\vatskebalans\db\vbalans.mdb")
    vbalans.ConnectionString = "Data Source='" & MdbFilePath & "'"
    vbalans.open
    strSQL1 = "SELECT tblFluids.FluidNamn, tblFluids.FluidID,
    tblVetskebalans.Dag, " &_
    " tblVetskebalans.PatID AS patid, tblVetskebalans.FluidID,
    tblVetskebalans.Volym," &_
    " tblPersonuppgifter.PatID AS ID, tblPersonuppgifter.ftEnamn,
    tblPersonuppgifter.ftFnamn" &_
    " FROM tblVetskebalans, tblFluids, tblPersonuppgifter WHERE" &_
    " tblVetskebalans.FluidID = tblFluids.FluidID AND " &_
    " tblPersonuppgifter.PatID = "&Request.Form("rapport")&" ORDER by dag
    ASC"
    datum =""
    'formatera till svensk personnummer
    Function SvPnr(txt)
    personnummer =Left(txt,6) & " - " & Right(txt,4)
    SvPnr = personnummer
    End Function

    Set rs1 = vbalans.Execute (strSQL1)
    Do While Not rs1.EOF
    If rs1("ID") = "&Request.Form("rapport")&" then
    Response.Write SvPnr(rs1("ID")) & "<BR>"
    Response.Write "<B>" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
    "</B><P><P><P>"
    ElseIf datum <>rs1("Dag") then
    datum = rs1("Dag")
    ElseIf datum = rs1("ID") then
    Response.Write "<B>" & rs1("Dag") & "</B><BR>" & "<I>" &
    rs1("FluidNamn") & "</I> " & "<U>" & rs1("Volym") & " ml</U><BR>"
    Else
    Response.Write "<I>" & rs1("FluidNamn") & "</I> " & "<U>" &
    rs1("Volym") & " ml</U><BR>"
    End If
    rs1.MoveNext
    Loop
    vbalans.Close
    Set vbalans = Nothing
    %>

    Can please some explain why this code failed??
     
    PeterL, Apr 30, 2004
    #3
  4. Not unless you tell us what "failed" means: error message? incorrect result?

    Bob Barrows
     
    Bob Barrows [MVP], Apr 30, 2004
    #4
  5. PeterL

    PeterL Guest

    Now I changed My code to:
    <%
    Set vbalans = Server.CreateObject("ADODB.Connection")
    vbalans.Provider = "Microsoft.Jet.OLEDB.4.0"
    MdbFilePath = Server.MapPath("\vatskebalans\db\vbalans.mdb")
    vbalans.ConnectionString = "Data Source='" & MdbFilePath & "'"
    vbalans.open
    strSQL1 = "SELECT tblFluids.FluidNamn, tblFluids.FluidID,
    tblVetskebalans.Dag, " &_
    " tblVetskebalans.PatID AS patid, tblVetskebalans.FluidID,
    tblVetskebalans.Volym," &_
    " tblPersonuppgifter.PatID AS ID, tblPersonuppgifter.ftEnamn,
    tblPersonuppgifter.ftFnamn" &_
    " FROM tblVetskebalans, tblFluids, tblPersonuppgifter WHERE" &_
    " tblVetskebalans.FluidID = tblFluids.FluidID AND " &_
    " ID = '"& Request.Form("rapport")&"' ORDER by Dag ASC"
    Response.Write strSQL1
    datum =""
    'formatera till svensk personnummer
    Function SvPnr(txt)
    personnummer =Left(txt,6) & " - " & Right(txt,4)
    SvPnr = personnummer
    End Function

    Set rs1 = vbalans.Execute (strSQL1)
    Do While Not rs1.EOF
    If rs1("ID") = Request.Form("rapport") then
    Response.Write SvPnr(rs1("ID")) & "<BR>"
    Response.Write "<B>" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
    "</B><P><P><P>"
    Else
    Response.Write "You have to make your choise"
    End If

    If datum <>rs1("Dag") AND datum = CDate(rs1("Dag")) AND datum =
    rs1("ID") then
    Response.Write "<B>" & rs1("Dag") & "</B><BR>" & "<I>" &
    rs1("FluidNamn") & "</I> " & "<U>" & rs1("Volym") & " ml</U><BR>"
    If datum = CDate(rs1("Dag")) AND datum = rs1("ID") then
    Response.Write "<I>" & rs1("FluidNamn") & "</I> " & "<U>" &
    rs1("Volym") & " ml</U><BR>"
    Else
    Response.Write "No more date found"
    End If
    End If
    rs1.MoveNext
    Loop
    vbalans.Close
    Set vbalans = Nothing
    %>
    Get to following error:
    SELECT tblFluids.FluidNamn, tblFluids.FluidID, tblVetskebalans.Dag,
    tblVetskebalans.PatID AS patid, tblVetskebalans.FluidID,
    tblVetskebalans.Volym, tblPersonuppgifter.PatID AS ID,
    tblPersonuppgifter.ftEnamn, tblPersonuppgifter.ftFnamn FROM
    tblVetskebalans, tblFluids, tblPersonuppgifter WHERE
    tblVetskebalans.FluidID = tblFluids.FluidID AND ID = ''1111111111''
    ORDER by Dag ASC
    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression
    'tblVetskebalans.FluidID = tblFluids.FluidID AND ID = ''1111111111'''

    All names are correctly spelled
     
    PeterL, May 1, 2004
    #5
  6. PeterL

    Bob Barrows Guest

    I am assuming this is the result of the response.write strSQL1 statement:
    If ID is a numeric field, then you should not be surrounding it with quotes.

    If ID is text, then you have another problem. To determine what that problem
    is,

    open your database in Access
    click into the Queries tab on the database window
    create a new query in Design view
    close the Choose Tables dialog without selecting a table
    switch to SQL View
    copy and paste the above statement from the browser window into the SQL View
    window
    attempt to run it.

    You may get a more informative error message.

    Bob Barrows
     
    Bob Barrows, May 1, 2004
    #6
  7. PeterL

    Bob Barrows Guest

    Oh wait, I see the error: neither of your tables has a column called "ID". I
    see a PatID and a FluidID, but no ID. You cannot use a column alias (AS ID)
    in your WHERE clause. You have to use the actual column name. I assume you
    are trying to filter the PatID from one of the two tables that contains that
    column. Since it is ambiguous, you need to explicitly qualify it in your
    WHERE clause, just as you did in the SELECT clause:

    either
    .... AND tblPersonuppgifter.PatID = ''1111111111''

    or
    .... AND tblVetskebalans.PatID = ''1111111111''


    Additionally, you need to do a better job of joining these tables. I prefer
    to use table aliases to make the code a little more compact and readable. In
    addition, I see you are returning the same data twice (why return the
    FluidID and PatID from both tables? It's redundant and increases the data
    being transmitted over the network for no good reason)

    Give this query a try (obviously this is not tested, but it should be close
    to what you need):

    SELECT f.FluidNamn, f.FluidID, v.Dag, v.PatID, v.Volym,
    p.ftEnamn, p.ftFnamn
    FROM (tblVetskebalans v INNER JOIN tblFluids f
    ON v.FluidID = f.FluidID) INNER JOIN
    tblPersonuppgifter p ON v.PatID = p.PatID
    WHERE p.PatID = ''1111111111''

    You should try this out in Access before attempting to run it in asp.

    Bob Barrows
     
    Bob Barrows, May 1, 2004
    #7
  8. PeterL

    PeterL Guest

    I changed my code:
    <%
    Set vbalans = Server.CreateObject("ADODB.Connection")
    vbalans.Provider = "Microsoft.Jet.OLEDB.4.0"
    MdbFilePath = Server.MapPath("\vatskebalans\db\vbalans.mdb")
    vbalans.ConnectionString = "Data Source='" & MdbFilePath & "'"
    vbalans.open
    strSQL1 = "SELECT tblFluids.FluidNamn, tblFluids.FluidID,
    tblVetskebalans.Dag, " &_
    " tblVetskebalans.PatID AS patid, tblVetskebalans.FluidID,
    tblVetskebalans.Volym," &_
    " tblPersonuppgifter.PatID AS ID, tblPersonuppgifter.ftEnamn,
    tblPersonuppgifter.ftFnamn" &_
    " FROM tblVetskebalans, tblFluids, tblPersonuppgifter WHERE" &_
    " tblVetskebalans.FluidID = tblFluids.FluidID AND " &_
    " ID = '"& Request.Form("rapport")&"' ORDER by Dag ASC"
    Response.Write strSQL1
    useddate =""
    Set rs1 = vbalans.Execute (strSQL1)
    'formatera till svensk personnummer
    Function SvPnr(txt)
    personnummer =Left(txt,6) & " - " & Right(txt,4)
    SvPnr = personnummer
    End Function
    Set rs1 = vbalans.Execute (strSQL1)
    Do While Not rs1.EOF
    If rs1("ID") = Request.Form("rapport") then
    Response.Write SvPnr(rs1("ID")) & "<BR>"
    Response.Write "<B>" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
    "</B><P><P><P>"
    Else
    Response.Write "Make your choice"
    End If
    Response.Write "<B>" & rs1("Dag") & "</B><BR>" & "<I>" &
    rs1("FluidNamn") & "</I> " & "<U>" & rs1("Volym") & " ml</U><BR>"
    If anvdag = CDate(rs1("Dag")) AND anvdag = rs1("ID") then
    Response.Write "<I>" & rs1("FluidNamn") & "</I> " & "<U>" &
    rs1("Volym") & " ml</U><BR>"
    Else
    Response.Write "Nomore date"
    End If
    End If
    rs1.MoveNext
    Loop
    vbalans.Close
    Set vbalans = Nothing

    %>
    I get the folloing print out:
    SELECT tblFluids.FluidNamn, tblFluids.FluidID, tblVetskebalans.Dag,
    tblVetskebalans.PatID AS patid, tblVetskebalans.FluidID,
    tblVetskebalans.Volym, tblPersonuppgifter.PatID AS ID,
    tblPersonuppgifter.ftEnamn, tblPersonuppgifter.ftFnamn FROM
    tblVetskebalans, tblFluids, tblPersonuppgifter WHERE
    tblVetskebalans.FluidID = tblFluids.FluidID AND ID = ''1111111111''
    ORDER by Dag ASC
    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression
    'tblVetskebalans.FluidID = tblFluids.FluidID AND ID = ''1111111111'''

    This indicate thare is something wrong with my form:
    <FORM ACTION="visa_rapporten102.asp" METHOD="post">
    <TABLE border=1>
    <TR>
    <TD><B>Personnummer</B></TD>
    <TD><B>Efternamn</B></TD>
    <TD><B>Förnamn</B></TD>
    <TD><B>Visa rapport</B></TD>
    </TR>
    <%
    Set objRS = objConn.Execute(strSQL)
    Do While Not objRS.EOF
    Response.Write "<TR><TD>" & objRS("PatID") & "</TD>"
    Response.Write "<TD>" & objRS("ftEnamn") & "</TD>"
    Response.Write "<TD>" & objRS("ftFnamn") & "</TD>"
    Response.Write "<TD><INPUT NAME=""rapport"" TYPE=""checkbox""
    VALUE=""'"& objRS("PatID") &"'""></TD></TR>"
    objRS.MoveNext
    Loop
    Response.Write "<TR ALIGN=""RIGHT"" VALIGN=""MIDDLE"">"
    Response.Write "<TD COLSPAN=""3"">"
    Response.Write "<INPUT TYPE=""submit"" VALUE=""Rapport"">"
    Response.Write "</TD></TR>"
    Response.Write "</TABLE></FORM>"
     
    PeterL, May 1, 2004
    #8
  9. PeterL

    swp Guest

    no. it indicates that you have used a set of double single quotes
    ('') instead of a set of single set of double quotes (") in the 2nd
    part of your WHERE clause above. it is saying "ID=''" (an empty
    string literal) and then continues on with an integer literal
    consisting of many one digits followed by another empty string
    literal.

    ID=''1111111111'' is not correct
    ID="1111111111" is correct
    ID='1111111111' is also correct

    also, use table pseudonyms to make your code more readable. for
    example, instead of saying:
    SELECT tblBigFatUglyName.col1 AS Name, tblBigFatUglyName.col2 AS
    Account
    FROM tblBigFatUglyName
    WHERE tblBigFatUglyName.col3 IS NOT NULL
    ORDER BY tblBigFatUglyName.col4
    you could use this:
    SELECT t.col1 AS Name, t.col2 AS Account
    FROM tblBigFatUglyName t
    WHERE t.col3 IS NOT NULL
    ORDER BY t.col4
    hope this helps,
    swp
     
    swp, May 1, 2004
    #9
  10. PeterL

    PeterL Guest

    thnx4 your answer swp...
    so since I get ID='1111111111' with Response.Write strSQL1 It´s
    correct to use "&Request.Form("rapport")&" in ny strSQL1 even though I
    use access 2000, and have set the field property to text?
    BTW - is there any way on this group to highlight code, quotes? And/or
    using Bóld?
     
    PeterL, May 2, 2004
    #10
  11. PeterL

    PeterL Guest

    the code looks like this:
    <%
    pat = Request.Form("rapport")
    Set vbalans = Server.CreateObject("ADODB.Connection")
    vbalans.Provider = "Microsoft.Jet.OLEDB.4.0"
    MdbFilePath = Server.MapPath("\vatskebalans\db\vbalans.mdb")
    vbalans.ConnectionString = "Data Source='" & MdbFilePath & "'"
    vbalans.open
    strSQL = "SELECT f.FluidNamn, f.FluidID, v.Dag, v.PatID, v.Volym," &_
    " p.ftEnamn, p.ftFnamn FROM (tblVetskebalans v INNER JOIN
    tblFluids f"&_
    " ON v.FluidID = f.FluidID) INNER JOIN tblPersonuppgifter p
    ON v.PatID = p.PatID" &_
    " WHERE p.PatID = " & pat &""
    Set rs1 = vbalans.Execute (strSQL)
    Response.Write "<BR><B>" & strSQL & "</B><P>"
    'formatera till svensk personnummer
    Function SvPnr(txt)
    personnummer =Left(txt,6) & " - " & Right(txt,4)
    SvPnr = personnummer
    End Function
    Response.Write SvPnr(rs1("PatID")) & "<BR>"
    Response.Write "<B>" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
    "</B><P><P><P>"
    %>
    And seems working. The outprint:
    [QOTE]SELECT f.FluidNamn, f.FluidID, v.Dag, v.PatID, v.Volym,
    p.ftEnamn, p.ftFnamn FROM (tblVetskebalans v INNER JOIN tblFluids f ON
    v.FluidID = f.FluidID) INNER JOIN tblPersonuppgifter p ON v.PatID =
    p.PatID WHERE p.PatID = '1111111111'
    111111 - 1111
    Elvansson, Elvan
    Datum Vätska Volym
    2004-03-21 Blod 1000
    2004-03-21 Sondmat 550
    2004-03-21 Sondmat 550
    2004-03-21 Sondmat 550
    2004-04-29 Albumin 76
    2004-05-02 Proviva 123
    [/QOTE]
    You can compare
    http://www12.brinkster.com/vatskebalans/tblvetskebalans11.asp

    3 additional questions:
    1) please explane your strSQL. Why use joints? I´m not familiar with
    joint/innerjoint as such
    2) if I alse want to display other properties (e.g Energy and Na) how
    would you change the strSQL
    3)If I wanted to add all "volym" can you please give me a code
    suggestion?

    Thanx for al your help
    /Peter
     
    PeterL, May 2, 2004
    #11
  12. PeterL

    PeterL Guest

    I have answered 2 of my questions by myself:> 3 additional questions:
    strSQL = "SELECT f.FluidNamn, f.FluidID, v.Dag, v.PatID, v.Volym,
    v.Energi," &_
    " p.ftEnamn, p.ftFnamn FROM (tblVetskebalans v INNER JOIN
    tblFluids f"&_
    " ON v.FluidID = f.FluidID) INNER JOIN tblPersonuppgifter p
    ON v.PatID = p.PatID" &_
    " WHERE p.PatID = "& pat &""
    v = 0
    e = 0
    Do While Not rs1.EOF
    v = v + cint(rs1("Volym"))
    e = e + cint(rs1("Energi"))
    Response.Write "<TR><TD>" & CDate(rs1("Dag") )& "</TD>"
    Response.Write "<TD>" & rs1("FluidNamn") & "</TD>"
    Response.Write "<TD>" & rs1("Volym") & "</TD>"
    Response.Write "<TD>" & rs1("Energi") & "</TD></TR>"
    rs1.MoveNext
    Loop
    Response.Write "Totala volymen är " & v & "<BR>"
    Response.Write "Totala energi är " & e & "<BR>"
    vbalans.Close
    Set vbalans = Nothing
    %>
     
    PeterL, May 3, 2004
    #12
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.