Trucsweb.com

Forum de discussion

 Forum « Programmation ASP » (archives)

Pb d'affichage de données

Envoyé: 27 octobre 2005, 6h42 par karen


Bonjour à tous ,

J'ai récupéré le programme d'une personne sur une mission et je dois faire des modif

J'ai un problème d'affichage des champs de la requête.

Lorsque je fais un document.write rs.field(1), je n'ai pas le même résultat que lui dans son tableau.

voilà le source :

<%@ Language=VBScript %>
<html>


<form method="POST" action="index.asp">

<SCRIPT LANGUAGE="JavaScript">
<!--
function popup(URL)
{
info = null;
info = window.open(URL,"Details","scrollbars=1,menubar=0,resizable=0,width=1100,height=500,top=5,left=5");
info.focus();
}
function Navig (nb, lien) {
parent.frames[nb].location = lien;
}
-->
</script>
<%
strCnn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=;Data Source=HQ-SQL02"
Set Conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
conn.ConnectionString = strCnn
conn.Open
%>

<%
If session("PassOK")= "OK" then

sql="SELECT DISTINCT CAST(SUBSTRING(CAST(IDMONTH AS CHAR(6)),1,LEN(IDMONTH)-4) AS DECIMAL(2,0)) FROM [LECTA_SALES].DBO.[MONTH]"
rs.open sql,conn
ListeMois= "<option>" & request.form("MOIS") & "</option>"
ValeurMois= " AND SUBSTRING([LECTA_SALES].DBO.MONTH.MONTHDESC,1,LEN([LECTA_SALES].DBO.MONTH.MONTHDESC)-4) ='" & request.form("MOIS") & "'"
TableMois="[SALES_ODS].DBO.[SAFE_DATA_" & ucase(left(request.form("MOIS"),3)) & "]"
while not rs.eof
ListeMois= ListeMois & "<option>" & MonthName(rs.fields(0)) & "</option>"
rs.movenext
wend
rs.close

sql="SELECT DISTINCT IDYEAR FROM [LECTA_SALES].DBO.[YEAR]"
rs.open sql,conn
ListeAnnee= "<option>" & request.form("ANNEE") & "</option>"
ValeurAnnee= " AND [LECTA_SALES].DBO.YEAR.IDYEAR =" & request.form("ANNEE")
while not rs.eof
ListeAnnee= ListeAnnee & "<option>" & rs.fields(0) & "</option>"
rs.movenext
wend
rs.close

ValeurCompany= " AND [LECTA_SALES].DBO.COMPANY.COMPDES = '" & session("COMP") & "'"

sql="SELECT DISTINCT FORMDES FROM [LECTA_SALES].DBO.[FORMPRES]"
rs.open sql,conn
if request.form("FORMAT")<>"All Format" and request.form("FORMAT")<>"" then
ListeFormat= "<option>" & request.form("FORMAT") & "</option><option>All Format</option>"
'ValeurFormat= " AND [LECTA_SALES].DBO.FORMPRES.FORMDES = '" & request.form("FORMAT") & "'"
ValeurFormat= " AND " & TableMois & ".FORMCOD = '" & left(request.form("FORMAT"),1) & "'"
else
ListeFormat= "<option>All Format</option>"
end if
while not rs.eof
ListeFormat= ListeFormat & "<option>" & rs.fields(0) & "</option>"
rs.movenext
wend
rs.close

if request.form("INVDEL")="Invoiced" or request.form("INVDEL")="" then
ListeCountry= "<option>Invoiced</option>"
ValeurCountry= " AND [LECTA_SALES].DBO.COUNTRY.CTRYCOD=" & TableMois & ".CTRYINVCOD "
else
ListeCountry= "<option>Delivered</option>"
ValeurCountry= " AND [LECTA_SALES].DBO.COUNTRY.CTRYCOD=" & TableMois & ".CTRYCOD "
end if
%>

<p><select size="1" name="MOIS">
<%Response.Write ListeMois%>
</select>
<select size="1" name="ANNEE">
<%Response.Write ListeAnnee%>
</select>
<b><font face="Verdana"><% response.write session("COMP")%></font></b>
<select size="1" name="FORMAT">
<%Response.Write ListeFormat%>
</select>
<select size="1" name="INVDEL">
<%Response.Write ListeCountry%>
<option>Invoiced</option>
<option>Delivered</option>
</select>
<input name='Filter' type='Submit' value='Filter'>
</p>

<%
if request.form("Validation")<>"" then

Dim Mailer
Set Mailer = Server.CreateObject("CDONTS.NewMail")
Mailer.To = "workflow@lecta.com"
Mailer.From = "Workflow"
Mailer.Subject = "Validation of " & session("COMP") & "'s the Values"
Mailer.Body = "Validation of the Values for " & request.form("MOIS") & " - " & request.form("ANNEE") & " - " & session("COMP") & " are OK"
Mailer.Importance=2
Mailer.Send
response.write "<p align=center><b><font face='Verdana'>A validation mail has been sent</font></b></p>"

sql= "DELETE FROM [SALES_ODS].DBO.CHECK_TABLE_LOADED WHERE IDTABLELOADED = '" & _
request.form("MOIS") & "-" & request.form("ANNEE") & "-" & session("COMP") & "'"

conn.Execute sql

end if

if request.form("MOIS")<>"" and request.form("ANNEE")<>"" then

sql="SELECT distinct" & _
" [LECTA_SALES].[dbo].[FACT_TABLE].[invhnum]," & _
" [LECTA_SALES].[dbo].[FACT_TABLE].[invdate]" & _
" FROM [LECTA_SALES].[dbo].[FACT_TABLE]," & TableMois & "," & _
" [LECTA_SALES].DBO.[COMPANY], [LECTA_SALES].DBO.[DATE]," & _
" [LECTA_SALES].DBO.[MONTH]," & _
" [LECTA_SALES].DBO.[QUARTER]," & _
" [LECTA_SALES].DBO.[YEAR]" & _
" WHERE" & _
" ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR" & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH" & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD)" & _
" AND ([LECTA_SALES].[dbo].[FACT_TABLE].[invhnum]=" & TableMois & ".[invhnum])" & _
ValeurAnnee & ValeurCompany & _
" AND (RTRIM (cast(month([LECTA_SALES].[dbo].[FACT_TABLE].[invdate]) as char(2))) + cast(year([LECTA_SALES].[dbo].[FACT_TABLE].[invdate]) as char(4))) <>" & _
" (SELECT distinct [LECTA_SALES].DBO.[DATE].[IDMONTH]" & _
" FROM [LECTA_SALES].DBO.[DATE]," & _
" [LECTA_SALES].DBO.[MONTH]," & _
" [LECTA_SALES].DBO.[QUARTER]," & _
" [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR" & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH" & _
" AND (SUBSTRING([LECTA_SALES].DBO.MONTH.MONTHDESC,1,LEN([LECTA_SALES].DBO.MONTH.MONTHDESC)-4) ='" & request.form("MOIS") & "'" & _
" AND [LECTA_SALES].DBO.YEAR.IDYEAR = " & request.form("ANNEE") & ")))"


rs.open sql,conn
if rs.bof= false then
rs.MoveFirst
Response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>WARNING : We inform you that if you validate these values, the following invoices will be updated.</font></b></p>"

Response.Write "<div align='center'><table border='1' width='1%' id='table1' style='font-family: Verdana; font-size: 8pt; border-collapse:collapse'>"

while not rs.eof
Response.Write "<tr>"
Response.Write "<td bgcolor='#FF0000'><b>"
Response.Write rs.fields(0)
Response.Write "</b></td>"
Response.Write "<td bgcolor='#FF0000'><b>"
Response.Write rs.fields(1)
Response.Write "</b></td>"
Response.Write "</tr>"
rs.movenext

wend



end if
rs.close



sql= "SELECT COUNT(*) FROM [SALES_ODS].DBO.CHECK_TABLE_LOADED WHERE IDTABLELOADED = '" & _
request.form("MOIS") & "-" & request.form("ANNEE") & "-" & session("COMP") & "'"
rs.open sql,conn
if rs.fields(0)<>0 then
response.write "<input name='Validation' type='Submit' value='Validation of the Values " & request.form("MOIS") & " - " & request.form("ANNEE") & " - " & session("COMP") & "'></p>"
else
response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>You can't validated those values.</font></b></p>"
end if
rs.close

sql="SELECT [LECTA_SALES].DBO.COUNTRY.CTRYDES AS COUNTRY," & _
" SUM(" & TableMois & ".[GSPREUT]* " & TableMois & ".[INVOWGT]) AS [GROSS_SALES_PRICE]," & _
" SUM(" & TableMois & ".[TRNBEUT]* " & TableMois & ".[INVOWGT]/1000) AS [TURNOVER_BONUS]," & _
" SUM(" & TableMois & ".[NSPREUT]* " & TableMois & ".[INVOWGT]/1000) AS [NET_SALES_PRICE]," & _
" SUM(" & TableMois & ".[COMMEUT]* " & TableMois & ".[INVOWGT]/1000) AS [SELLING_COST]," & _
" SUM(" & TableMois & ".[DISTEUT]* " & TableMois & ".[INVOWGT]/1000) AS [DISTR_COST]," & _
" SUM(" & TableMois & ".[EXWPEUT]* " & TableMois & ".[INVOWGT]/1000) AS [EX_WORKS_PRICE]," & _
" SUM(" & TableMois & ".[EXWMEUT]* " & TableMois & ".[INVOWGT]/1000) AS [EX_WORKS_MARGIN]," & _
" SUM(" & TableMois & ".INVOWGT) /1000 AS TONS" & _


" FROM " & TableMois & ", [LECTA_SALES].DBO.[COUNTRY], [LECTA_SALES].DBO.[COMPANY]," & _
" [LECTA_SALES].DBO.[DATE], [LECTA_SALES].DBO.[MONTH], [LECTA_SALES].DBO.[QUARTER], [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR " & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH " & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE)" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD" & _
ValeurCountry & ValeurMois & ValeurAnnee & ValeurCompany & ValeurFormat & _
" AND " & TableMois & ".[DATEPROCESS] = " & _
" ( SELECT MAX(" & TableMois & ".[DATEPROCESS])" & _
" FROM " & TableMois & ", [LECTA_SALES].DBO.[COMPANY], [LECTA_SALES].DBO.[DATE], [LECTA_SALES].DBO.[MONTH], [LECTA_SALES].DBO.[QUARTER], [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR " & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH " & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE)" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD" & _
ValeurMois & ValeurAnnee & ValeurCompany & " )" & _
" GROUP BY [LECTA_SALES].DBO.COUNTRY.CTRYDES"


rs.open sql,conn



' affichage du nom des champs

Response.Write "<div align='center'><table border='1' width='1%' id='table1' style='font-family: Verdana; font-size: 8pt; border-collapse:collapse'>"
Response.Write "<tr>"
for i=1 to rs.fields.count step 1
Response.Write "<td bgcolor='#C0C0C0'><b>"
Response.Write rs.fields(i-1).name
Response.Write "</b></td>"
next
Response.Write "</tr>"



nb_enr=0 'le nb d'enregistrement pour le calcul des moyennes
tons_tot=0 'total des TONS
GROSS_SALES_PRICE_tot=0 'total GROSS_SALES_PRICE
SELLING_COST_tot=0 'total SELLING_COST
DISTR_COST_tot=0 'total DISTR_COST
TURNOVER_BONUS_tot=0 'total TURNOVER_BONUS
EX_WORKS_PRICE_tot=0 'total EX_WORKS_PRICE_tot
EX_WORKS_MARGIN_tot=0 'total EX_WORKS_MARGIN
NET_SALES_PRICE_tot=0 'total NET_SALES_PRICE


'calcul des totaux

rs.MoveFirst
do while not rs.EOF

nb_enr=nb_enr+1

tons_tot=tons_tot+formatnumber(rs.Fields("tons"))

GROSS_SALES_PRICE_tot=GROSS_SALES_PRICE_tot+formatnumber(rs.Fields("GROSS_SALES_PRICE"))

TURNOVER_BONUS_tot=TURNOVER_BONUS_tot+formatnumber(rs.Fields("TURNOVER_BONUS"))

SELLING_COST_tot=SELLING_COST_tot+formatnumber(rs.Fields("SELLING_COST"))

DISTR_COST_tot=DISTR_COST_tot+formatnumber(rs.Fields("DISTR_COST"))

EX_WORKS_PRICE_tot=EX_WORKS_PRICE_tot+formatnumber(rs.Fields("EX_WORKS_PRICE"))

EX_WORKS_MARGIN_tot=EX_WORKS_MARGIN_tot+formatnumber(rs.Fields("EX_WORKS_MARGIN"))

NET_SALES_PRICE_tot=NET_SALES_PRICE_tot+formatnumber(rs.Fields("NET_SALES_PRICE"))

rs.MoveNext
loop


'affichage du premier champs pour comparer avec les valeurs du tableau
rs.MoveFirst
do while not rs.EOF
Response.Write round(rs.Fields(1))
Response.Write " "
rs.MoveNext
loop




if rs.bof= false then
rs.MoveFirst
lg=1
while not rs.eof
lg=lg+1

' affichage des pays avec le lien
Response.Write "<tr>" '1 ere ligne
Response.Write "<td><a href=javascript:popup('\\Form01.asp?Qry=" & replace(rs.fields(0)," ","%20") & "&Qry1=" & replace(request.form("MOIS")," ","%20") & "&Qry2=" & replace(request.form("ANNEE")," ","%20") & "&Qry3=" & replace(session("COMP")," ","%20") & "&Qry4=" & replace(request.form("FORMAT")," ","%20") & "&Qry5=" & replace(request.form("INVDEL")," ","%20") & "')>"
Response.Write rs.fields(0)
Response.Write "</a></td>"'1ere colonne




for i=2 to rs.fields.count-1 step 1
Response.Write "<td><P ALIGN='RIGHT'>" '2 eme colonne
if IsNull(rs.fields("TONS")) then
Tons=0
else
Tons=FormatNumber(rs.fields("TONS"),3)
end if
if IsNull(rs.fields(i-1)) then
Col=0
else
Col=FormatNumber(rs.fields(i-1),3)
end if
if Tons=0 then
Response.Write "0"
else
Response.Write (Round(FormatNumber(Col/Tons,3)))
end if
Response.Write "</P></td>"
next

Response.Write "<td><P ALIGN='RIGHT'>" '3eme colonne
Response.Write Round(Tons)
Response.Write "</P></td>"
Response.Write "</tr>"

rs.movenext

wend

' affichage des totaux à la dernière ligne du tableau

response.write "<tr>"
response.write "<td><b>TOTAUX</td><td><b><P ALIGN='right'>"&GROSS_SALES_PRICE_tot&"</td><td><P ALIGN='right'><b>"&TURNOVER_BONUS_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&NET_SALES_PRICE_tot&"</td><td><P ALIGN='right'><b>"&SELLING_COST_tot&"</td><td><P ALIGN='right'><b>"&DISTR_COST_tot&"</td><td><P ALIGN='right'><b>"&EX_WORKS_PRICE_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&EX_WORKS_MARGIN_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&tons_tot&"</td>"
response.write "</tr></p>"

rs.close

end if

else


response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>Select a month and a year.</font></b></p>"

end if
else
%>

<SCRIPT LANGUAGE='JavaScript'>
<!--"
info = null;
info = window.open("\\Log.asp?id=<%Response.Write Request.QueryString("id")%>","Log","scrollbars=0,menubar=0,resizable=0,width=300,height=300,top=300,left=300");
info.focus();
-->
</script>

<%
end if


conn.close

%>

</FORM>
</html>

Pourriez-vous m'aider.

Merci d'avance

Karen


Réponses

 Aucune réponse à ce message... 
Aucun médias sociaux
X

Trucsweb.com Connexion

X

Trucsweb.com Mot de passe perdu

X

Trucsweb.com Conditions générales

Conditions

Responsabilité

La responsabilité des Trucsweb.com ne pourra être engagée en cas de faits indépendants de sa volonté. Les informations mises à disposition sur ce site le sont uniquement à titre purement informatif et ne sauraient constituer en aucun cas un conseil ou une recommandation de quelque nature que ce soit.

Aucun contrôle n'est exercé sur les références et ressources externes, l'utilisateur reconnaît que les Trucsweb.com n'assume aucune responsabilité relative à la mise à disposition de ces ressources, et ne peut être tenue responsable quant à leur contenu.

Droit applicable et juridiction compétente

Les règles en matière de droit, applicables aux contenus et aux transmissions de données sur et autour du site, sont déterminées par la loi canadienne. En cas de litige, n'ayant pu faire l'objet d'un accord à l'amiable, seuls les tribunaux canadien sont compétents.

X

Trucsweb.com Trucsweb

X

Trucsweb.com Glossaire

X

Trucsweb.com Trucsweb

X

Trucsweb.com Trucsweb

Conditions

Aucun message!

Merci.

X
Aucun message!
X

Trucsweb.com Créer un compte