help to translate my pseudocode to asp

PeterL · Apr 29, 2004

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

Steven Burn · Apr 29, 2004

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

PeterL · Apr 30, 2004

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")) & " "
Response.Write "" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
""
ElseIf datum <>rs1("Dag") then
datum = rs1("Dag")
ElseIf datum = rs1("ID") then
Response.Write "" & rs1("Dag") & " " & "" &
rs1("FluidNamn") & " " & "" & rs1("Volym") & " ml "
Else
Response.Write "" & rs1("FluidNamn") & " " & "" &
rs1("Volym") & " ml "
End If
rs1.MoveNext
Loop
vbalans.Close
Set vbalans = Nothing
%>

Can please some explain why this code failed??

Bob Barrows [MVP] · Apr 30, 2004

PeterL said:
I tried with this, but failed:

Can please some explain why this code failed??

Not unless you tell us what "failed" means: error message? incorrect result?

Bob Barrows

PeterL · May 1, 2004

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")) & " "
Response.Write "" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
""
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 "" & rs1("Dag") & " " & "" &
rs1("FluidNamn") & " " & "" & rs1("Volym") & " ml "
If datum = CDate(rs1("Dag")) AND datum = rs1("ID") then
Response.Write "" & rs1("FluidNamn") & " " & "" &
rs1("Volym") & " ml "
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

Bob Barrows · May 1, 2004

PeterL said:
Now I changed My code to:

Get to following error:

I am assuming this is the result of the response.write strSQL1 statement:

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

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

PeterL said:
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

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

PeterL · May 1, 2004

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")) & " "
Response.Write "" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
""
Else
Response.Write "Make your choice"
End If
Response.Write "" & rs1("Dag") & " " & "" &
rs1("FluidNamn") & " " & "" & rs1("Volym") & " ml "
If anvdag = CDate(rs1("Dag")) AND anvdag = rs1("ID") then
Response.Write "" & rs1("FluidNamn") & " " & "" &
rs1("Volym") & " ml "
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>Personnummer</TD>
<TD>Efternamn</TD>
<TD>Förnamn</TD>
<TD>Visa rapport</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>"

swp · May 1, 2004

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")) & " "
Response.Write "" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
""
Else
Response.Write "Make your choice"
End If
Response.Write "" & rs1("Dag") & " " & "" &
rs1("FluidNamn") & " " & "" & rs1("Volym") & " ml "
If anvdag = CDate(rs1("Dag")) AND anvdag = rs1("ID") then
Response.Write "" & rs1("FluidNamn") & " " & "" &
rs1("Volym") & " ml "
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:

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

<FORM ACTION="visa_rapporten102.asp" METHOD="post">
<TABLE border=1>
<TR>
<TD>Personnummer</TD>
<TD>Efternamn</TD>
<TD>Förnamn</TD>
<TD>Visa rapport</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>"

hope this helps,
swp

PeterL · May 2, 2004

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

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 " " & strSQL & ""
'formatera till svensk personnummer
Function SvPnr(txt)
personnummer =Left(txt,6) & " - " & Right(txt,4)
SvPnr = personnummer
End Function
Response.Write SvPnr(rs1("PatID")) & " "
Response.Write "" & rs1("ftEnamn") & ", " & rs1("ftFnamn") &
""
%>
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 3, 2004

I have answered 2 of my questions by myself:> 3 additional questions:

2) if I alse want to display other properties (e.g Energy and Na) how
would you change the strSQL

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 &""

3)If I wanted to add all "volym" can you please give me a code
suggestion?

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 & " "
Response.Write "Totala energi är " & e & " "
vbalans.Close
Set vbalans = Nothing
%>

get info from 2 tables with 2 condition	3	May 2, 2004
add up for each date	3	May 4, 2004
My `warn delete` subcommand is not working as expected, please help me out	0	Nov 29, 2021
Help with my responsive home page	2	Dec 14, 2022
Problem in Returning Recordset in ASP	1	Apr 12, 2007
I need help in understanding these files on my phone, Could someone help me understand these files? Urgent help needed. Please help.	1	Jun 4, 2023
Looking to start a project but I need help	2	Mar 8, 2023
Needing Help With My First JavaScript script	1	Mar 22, 2018

help to translate my pseudocode to asp

PeterL

Steven Burn

PeterL

Bob Barrows [MVP]

PeterL

Bob Barrows

Bob Barrows

PeterL

swp

PeterL

PeterL

PeterL

Ask a Question

Similar Threads

Staff online

Members online

Forum statistics

Latest Threads