codice:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath("test.xls") & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"
set rs = server.createobject("ADODB.recordset")
'questo sarà il nome del file
sql = "SELECT * FROM [Export$]"
set rs = server.createobject("ADODB.recordset")
rs.open sql, conn
if rs.EOF then
response.write("NESSUN DATO IN ARCHIVIO")
else
'titoli dei campi da estrarre
response.write "<table border='1'><tr>"
response.write "<td>Registration Date</td>"
response.write "<td>Company Name</td>"
.
.
response.write "</tr>"
do while not rs.EOF
response.write "<tr>"
response.write "<td>"
response.write rs("0")
response.write "</td>"
response.write "<td>"
response.write rs("1")
response.write "</td>"
response.write "<td>"
response.write rs("2")
response.write "</td>"
response.write "<td>"
response.write rs("3")
response.write "</td>"
response.write "<td>"
response.write rs("4")
response.write "</td>"
response.write "<td>"
response.write rs("5")
response.write "</td>"
response.write "<td>"
response.write rs("6")
response.write "</td>"
response.write "<td>"
response.write rs("7")
response.write "</td>"
response.write "<td>"
response.write rs("8")
response.write "</td>"
response.write "<td>"
response.write rs("9")
response.write "</td>"
response.write "<td>"
response.write rs("10")
response.write "</td>"
response.write "<td>"
response.write rs("11")
response.write "</td>"
response.write "<td>"
response.write rs("12")
response.write "</td>"
response.write "<td>"
response.write rs("13")
response.write "</td>"
response.write "<td>"
response.write rs("14")
response.write "</td>"
response.write "<td>"
response.write rs("15")
response.write "</td>"
response.write "<td>"
response.write rs("16")
response.write "</td>"
response.write "<td>"
response.write rs("17")
response.write "</td>"
response.write "<td>"
response.write rs("18")
response.write "</td>"
response.write "<td>"
response.write rs("19")
response.write "</td>"
response.write "<td>"
response.write rs("20")
response.write "</td>"
response.write "<td>"
response.write rs("21")
response.write "</td>"
response.write "<td>"
response.write rs("22")
response.write "</td>"
response.write "<td>"
response.write rs("23")
response.write "</td>"
response.write "<td>"
response.write rs("24")
response.write "</td>"
response.write "<td>"
response.write rs("25")
response.write "</td>"
response.write "<td>"
response.write rs("26")
response.write "</td>"
response.write "</tr>"
Dim varRegistrationDate, varCompanyName, varGenderTitle, varFirstName, varLastName, varBirthDate, varEmailAddress, varCommPrefMail, varCommPrefEmail, varCommPrefShare, varCommPrefPhone, varCommPrefMobilePhone, varCommPrefSms, varCpcNumber, varSerialNumber, varDatePurchased, varPurchasePlaceType, varDealerName, varDealerCity, varOutletFactory, varProductRegistrationAddressStreet, varProductRegistrationAddressHouseNumber, varProductRegistrationAddressCity, varProductRegistrationAddressCountry, varProductRegistrationAddressDayPhone, varProductRegistrationAddressPostalCode, varProductRegistrationAddressMobilePhone
varRegistrationDate = rs("0")
varCompanyName = rs("1")
if Len(Trim(varCompanyName)) > 0 then
varCompanyName=Replace(varCompanyName,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCompanyName = rs("1")
end if
varGenderTitle = rs("2")
if Len(Trim(varGenderTitle)) > 0 then
varGenderTitle=Replace(varGenderTitle,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varGenderTitle = rs("2")
end if
varFirstName = rs("3")
if Len(Trim(varFirstName)) > 0 then
varFirstName=Replace(varFirstName,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varFirstName = rs("3")
end if
varLastName = rs("4")
if Len(Trim(varLastName)) > 0 then
varLastName=Replace(varLastName,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varLastName = rs("4")
end if
varBirthDate = rs("5")
varEmailAddress = rs("6")
varCommPrefMail = rs("7")
varCommPrefEmail = rs("8")
varCommPrefShare = rs("9")
if Len(Trim(varCommPrefShare)) > 0 then
varCommPrefShare=Replace(varCommPrefShare,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCommPrefShare = rs("9")
end if
varCommPrefPhone = rs("10")
if Len(Trim(varCommPrefPhone)) > 0 then
varCommPrefPhone=Replace(varCommPrefPhone,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCommPrefPhone = rs("10")
end if
varCommPrefMobilePhone = rs("11")
if Len(Trim(varCommPrefMobilePhone)) > 0 then
varCommPrefMobilePhone=Replace(varCommPrefMobilePhone,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCommPrefMobilePhone = rs("11")
end if
varCommPrefSms = rs("12")
if Len(Trim(varCommPrefSms)) > 0 then
varCommPrefSms=Replace(varCommPrefSms,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCommPrefSms = rs("12")
end if
varCpcNumber = rs("13")
if Len(Trim(varCpcNumber)) > 0 then
varCpcNumber=Replace(varCpcNumber,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varCpcNumber = rs("13")
end if
varSerialNumber = rs("14")
if Len(Trim(varSerialNumber)) > 0 then
varSerialNumber=Replace(varSerialNumber,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varSerialNumber = rs("14")
end if
varDatePurchased = rs("15")
varPurchasePlaceType = rs("16")
if Len(Trim(varPurchasePlaceType)) > 0 then
varPurchasePlaceType=Replace(varPurchasePlaceType,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varPurchasePlaceType = rs("16")
end if
varDealerName = rs("17")
if Len(Trim(varDealerName)) > 0 then
varDealerName=Replace(varDealerName,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varDealerName = rs("17")
end if
varDealerCity = rs("18")
if Len(Trim(varDealerCity)) > 0 then
varDealerCity=Replace(varDealerCity,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varDealerCity = rs("18")
end if
varOutletFactory = rs("19")
if Len(Trim(varOutletFactory)) > 0 then
varOutletFactory=Replace(varOutletFactory,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varOutletFactory = rs("19")
end if
varProductRegistrationAddressStreet=rs("20")
if Len(Trim(varProductRegistrationAddressStreet)) > 0 then
varProductRegistrationAddressStreet=Replace(varProductRegistrationAddressStreet,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressStreet = rs("20")
end if
varProductRegistrationAddressHouseNumber = rs("21")
if Len(Trim(varGenderTitle)) > 0 then
varProductRegistrationAddressHouseNumber=Replace(varProductRegistrationAddressHouseNumber,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressHouseNumber = rs("21")
end if
varProductRegistrationAddressCity = rs("22")
if Len(Trim(varGenderTitle)) > 0 then
varProductRegistrationAddressCity=Replace(varProductRegistrationAddressCity,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressCity = rs("22")
end if
varProductRegistrationAddressCountry = rs("23")
if Len(Trim(varProductRegistrationAddressCountry)) > 0 then
varProductRegistrationAddressCountry=Replace(varProductRegistrationAddressCountry,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressCountry = rs("23")
end if
varProductRegistrationAddressDayPhone = rs("24")
if Len(Trim(varProductRegistrationAddressDayPhone)) > 0 then
varProductRegistrationAddressDayPhone=Replace(varProductRegistrationAddressDayPhone,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressDayPhone = rs("24")
end if
varProductRegistrationAddressPostalCode = rs("25")
if Len(Trim(varProductRegistrationAddressPostalCode)) > 0 then
varProductRegistrationAddressPostalCode=Replace(varProductRegistrationAddressPostalCode,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressPostalCode = rs("25")
end if
varProductRegistrationAddressMobilePhone = rs("26")
if Len(Trim(varProductRegistrationAddressMobilePhone)) > 0 then
varProductRegistrationAddressMobilePhone=Replace(varProductRegistrationAddressMobilePhone,"'","''")
'Response.Write("La variabile contiene un valore")
else
'Response.Write(" La variabile NON contiene un valore")
varProductRegistrationAddressMobilePhone = rs("26")
end if
Set ConnDown=Server.CreateObject("ADODB.Connection")
ConnDown.Open "Driver={SQL Server}; Server=localhost; Database=xxxxxx; Uid=xxxx; Pwd=xxxx;"
SqlInsert = "SELECT emailAddress FROM portfolio WHERE emailAddress = '" & varEmailAddress & "'"
Set rsInsert = ConnDown.Execute(SqlInsert)
If rsInsert.EOF Then
strSQL = "INSERT INTO portfolio (registrationDate, companyName, genderTitle, firstName, lastName, birthDate, emailAddress, commPrefMail, commPrefEmail, commPrefShare, commPrefPhone, commPrefMobilePhone, commPrefSms, cpcNumber, serialNumber, datePurchased, purchasePlaceType, dealerName, dealerCity, outletFactory, productRegistrationAddressStreet, productRegistrationAddressHouseNumber, productRegistrationAddressCity, ProductRegistrationAddressCountry, productRegistrationAddressDayPhone, productRegistrationAddressPostalCode, productRegistrationAddressMobilePhone) VALUES (CONVERT(DATETIME,'"&varRegistrationDate&"',103), '"&varCompanyName&"', '"&varGenderTitle&"', '"&varFirstName&"', '"&varLastName&"', CONVERT(DATETIME,'"&varBirthDate&"',103), '"&varEmailAddress&"', '"&varCommPrefMail&"', '"&varCommPrefEmail&"', '"&varCommPrefShare&"', '"&varCommPrefPhone&"', '"&varCommPrefMobilePhone&"', '"&varCommPrefSms&"', '"&varCpcNumber&"', '"&varSerialNumber&"', CONVERT(DATETIME,'"&varDatePurchased&"',103), '"&varPurchasePlaceType&"', '"&varDealerName&"', '"&varDealerCity&"', '"&varOutletFactory&"', '"&varProductRegistrationAddressStreet&"', '"&varProductRegistrationAddressHouseNumber&"', '"&varProductRegistrationAddressCity&"', '"&varProductRegistrationAddressCountry&"', '"&varProductRegistrationAddressDayPhone&"', '"&varProductRegistrationAddressPostalCode&"', '"&varProductRegistrationAddressMobilePhone&"')"
Set RECdown = CreateObject("ADODB.Recordset")
set RECdown = connDown.execute(strSQL)
Else
Response.Write(varEmailAddress)
Response.Write(" è già presente nel database.
")
End If
rsInsert.Close
Set rsInsert = Nothing
'strSQL = "INSERT INTO cpc (cpc, descrizione, modello, famiglia) VALUES ('"&varcpc&"', '"&vardescrizione&"', '"&varmodello&"', '"&varfamiglia&"')"
'Set RECdown = CreateObject("ADODB.Recordset")
'set RECdown = connDown.execute(strSQL)
rs.movenext
loop
response.write "</table>"
end if
rs.close
set rs = nothing
response.flush
response.end()
%>
Rispondi quotando