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
|
Dim sConn = ConfigurationManager.ConnectionStrings("SiteConnectionString").ConnectionString.ToString()
Dim oConn = New SqlConnection(sConn)
oConn.Open()
Dim i As Integer
If Request.Form("Choisir") = "Update" Then
' création des csv
For i = 1 To Request.Form("NbrPart")
' on va récuperer le nom du partenaire
Dim Mycommand8 As SqlCommand = oConn.CreateCommand()
Mycommand8.CommandText = "select * FROM Partenaire where NumAuto=" & Request.Form("Partenaire" & i)
Dim RsPart2 As SqlDataReader = Mycommand8.ExecuteReader()
RsPart2.Read()
Dim NomPartenaire As String = RsPart2("IdPartenaire")
Dim email As String = RsPart2("Email")
Dim CodeAgencePartenaire As String = RsPart2("CodeAgencePartenaire")
Dim CodeAgenceInterne As String = RsPart2("CodeAgenceInterne")
Dim AdresseFtp As String = RsPart2("AdresseFtp")
Dim UtilFtp As String = RsPart2("UtilFtp")
Dim MotPasseFtp As String = RsPart2("MotPasseFtp")
Dim FormatFichier As String= RsPart2("FormatFichier")
Dim NumAutoPartenaire As String = RsPart2("NumAuto")
Dim fs As FileStream
Dim sw As StreamWriter
......
' écriture du xml
sw.WriteLine("<?xml version=""1.0"" encoding=""utf-8""?>")
' sw.WriteLine(" <!DOCTYPE xsl:stylesheet [<!ENTITY nbsp "" "">")
' ecriture informations contenues dans les annonces
' on va parcourir les annonces et voir si elle sont cochées
Dim Mycommand9 As SqlCommand = oConn.CreateCommand()
Mycommand9.CommandText = "select * FROM annonce order by titre asc"
Dim Mycommand15 As SqlCommand = oConn.CreateCommand()
Dim Mycommand10 As SqlCommand = oConn.CreateCommand()
Dim RsAnnonces2 As SqlDataReader = Mycommand9.ExecuteReader()
While RsAnnonces2.Read()
If Request.Form("Partenaire_" & i & "_" & RsAnnonces2("numauto")) = "ok" Then ' on verifie si le partenaire a bien été coché
' on cohe la case
Mycommand10.CommandText = "Update Annonce set CaseCoche" & NumAutoPartenaire & "=1 where numauto=" & RsAnnonces2("NumAuto")
'Mycommand10.ExecuteNonQuery()
'Response.Write("Update Annonce set CaseCoche" & NumAutoPartenaire & "=-1 where numauto=" & RsAnnonces2("NumAuto"))
'response.write sqlUpdate
sw.WriteLine("<Annonce>")
sw.WriteLine("<Reference>" & RsAnnonces2("reference") & "</Reference> ")
'Création des champs titres du fichier CSV
' on recupere le type
Dim Mycommand11 As SqlCommand = oConn.CreateCommand()
Mycommand11.CommandText = "select * FROM type where numauto=" & RsAnnonces2("fk_type")
Dim RsType2 As SqlDataReader = Mycommand11.ExecuteReader()
RsType2.Read()
Dim typeAnnonce As String = RsType2("titre")
' la categorie
Dim Mycommand12 As SqlCommand = oConn.CreateCommand()
Mycommand12.CommandText = "select * FROM Categorie where numauto=" & RsAnnonces2("fk_cat")
Dim RsCat2 As SqlDataReader = Mycommand12.ExecuteReader()
RsCat2.Read()
Dim cat As String = RsCat2("titre")
'recup du secteur
Dim Mycommand13 As SqlCommand = oConn.CreateCommand()
Mycommand13.CommandText = "select * FROM secteur where numauto=" & RsAnnonces2("fk_secteur")
Dim RsSecteur2 As SqlDataReader = Mycommand13.ExecuteReader()
RsSecteur2.Read()
Dim secteur As String = RsSecteur2("titre")
Dim CodePostal As String = RsSecteur2("CodePostal")
Dim descriptif As String = Replace(RsAnnonces2("descriptif"), "<DIV id=menu>", " ")
descriptif = Replace(descriptif, "</DIV>", " ")
descriptif = Replace(descriptif, "<DIV>", " ")
descriptif = Replace(descriptif, "<P class=MsoNormal style=""MARGIN: 0cm 0cm 0pt"">", " <P>")
descriptif = Replace(descriptif, "<SPAN style=""FONT-SIZE: 9pt; FONT-FAMILY: Arial"">", " ")
descriptif = Replace(descriptif, "</SPAN>", " ")
descriptif = Replace(descriptif, "<BR>", " ")
descriptif = Replace(descriptif, "<BR />", " ")
sw.WriteLine("<TypeBien>" & cat & "</TypeBien>")
sw.WriteLine("<Rubrique>" & typeAnnonce & "</Rubrique>")
sw.WriteLine("<DateCreation>" & RsAnnonces2("DateCreation") & "</DateCreation>")
sw.WriteLine("<DateModification>" & RsAnnonces2("DateModif") & "</DateModification> ")
sw.WriteLine("<CodePostal>" & CodePostal & "</CodePostal>")
sw.WriteLine("<Ville>" & secteur & "</Ville>")
sw.WriteLine("<Titre>" & RsAnnonces2("titre") & "</Titre> ")
sw.WriteLine("<Texte> <![CDATA[" & Trim(descriptif) & "]]></Texte> ")
sw.WriteLine("<Prix>" & RsAnnonces2("prix") & " Euros</Prix>")
If RsAnnonces2("Honoraire") <> 0 Then
sw.WriteLine("<Honoraires>" & RsAnnonces2("Honoraire") & "</Honoraires>")
End If
sw.WriteLine("<NbPieces>" & RsAnnonces2("NbrPiece") & "</NbPieces>")
Dim Mycommand14 As SqlCommand = oConn.CreateCommand()
Mycommand14.CommandText = "select * FROM Photos where reference='" & RsAnnonces2("reference") & "'"
Dim RsPhoto As SqlDataReader = Mycommand14.ExecuteReader()
RsPhoto.Read()
Dim nbrPhoto As Integer = 0
While RsPhoto.Read
sw.WriteLine("<URL_Photo> " & RsPhoto("photo") & "</URL_Photo>")
nbrPhoto = nbrPhoto + 1
End While
sw.WriteLine("</Annonce>")
Else
'Mycommand15.CommandTimeout = 5000
Dim CaseCoche As String
CaseCoche = "CaseCoche" & NumAutoPartenaire
Mycommand15.CommandText = "Update Annonce set " & CaseCoche & "=0 where NumAuto=" & RsAnnonces2("NumAuto")
Mycommand15.ExecuteNonQuery()
Mycommand15.Dispose()
Mycommand15 = Nothing
Response.Write("Update Annonce set CaseCoche" & NumAutoPartenaire & "=0 where NumAuto=" & RsAnnonces2("NumAuto"))
End If |
Probleme de datareader et executenonquery
Répondre avec citation
Partager