works well - i.e an update on the CMS page populates the sql table and then the asp page reflects this update too.
only problem is if the price input field is left empty once submitted 0 in entered in to the sql table?? there's no js validation on the cms and the price column is set to integer and it's default value is set to NULL.
edit.asp code
Expand|Select|Wrap|Line Numbers
- <%
- 'declare you variables
- Dim SQL, connection, recordset
- Dim sConnString, ID 'receive the id of the record passed through querystring
- ID = Trim(Request("ID"))
- 'ID=request.querystring("ID")
- 'ID=request.form("ID") 'receive the hidden form ID
- 'Declare the SQL statement that will query the database
- SQL="SELECT * FROM UK_Specials WHERE ID='" & ID & "' "
- 'Create an ADO connection and recordset object
- Set Connection = Server.CreateObject("ADODB.Connection")
- Set Recordset = Server.CreateObject("ADODB.Recordset")
- 'define the connection string, specify database
- 'driver and the location of database
- sConnString="Provider=SQLOLEDB;Data Source=********" & _
- "Initial Catalog=MYDBNAME;User ID=****;Password=*************"
- 'Open the connection to our database
- Connection.Open(sConnString)
- 'Open the recordset object, execute the SQL statement
- 'and return the record with the id that was sent
- recordset.Open SQL,connection
- %>
- <div align="center" style="width:360px; padding:10px">
- <form name="UKSpecialsUpdate" id="UKSpecialsUpdate" method="post" onSubmit="return validate();" action="update.asp" />
- <input type="hidden" name="ID" id="ID" value="<%= recordset("ID")%>" />
- <table align="center" width="100%" cellpadding="0" cellspacing="0">
- <tr>
- <td style="font-weight:bold; color:#01207B"><%= recordset("Flight_Route")%></td>
- <td style="font-weight:bold; text-align:right"><a style="text-align:right" href="javascript: self.close ()">Close Window</a></td>
- </tr>
- </table>
- <table align="center" width="100%" cellpadding="0" cellspacing="0" style="margin:20px 0 0 0">
- <tr>
- <td colspan="2" style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Price</td>
- <td style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Month</td>
- <td style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Date/s</td>
- </tr>
- <tr>
- <td align="right" style="padding:0 5px 5px 0">£</td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="PriceBand1" type="text" id="PriceBand1" size="4" maxlength="4" value="<%= recordset("Price_Band_1")%>"></td>
- <td align="right" style="padding:0 0 5px 5px">
- <input name="MonthBand1" type="text" id="MonthBand1" size="4" maxlength="4" value="<%= recordset("Month_Band_1")%>"></td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="DateBand1" type="text" id="DateBand1" size="30" maxlength="100" value="<%= recordset("Date_Band_1")%>"></td>
- </tr>
- <tr>
- <td align="right" style="padding:0 5px 5px 0">£</td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="PriceBand2" type="text" id="PriceBand2" size="4" maxlength="4" value="<%= recordset("Price_Band_2")%>"></td>
- <td align="right" style="padding:0 0 5px 5px">
- <input name="MonthBand2" type="text" id="MonthBand2" size="4" maxlength="4" value="<%= recordset("Month_Band_2")%>"></td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="DateBand2" type="text" id="DateBand2" size="30" maxlength="100" value="<%= recordset("Date_Band_2")%>"></td>
- </tr>
- <tr>
- <td align="right" style="padding:0 5px 5px 0">£</td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="PriceBand3" type="text" id="PriceBand3" size="4" maxlength="4" value="<%= recordset("Price_Band_3")%>"></td>
- <td align="right" style="padding:0 0 5px 5px">
- <input name="MonthBand3" type="text" id="MonthBand3" size="4" maxlength="4" value="<%= recordset("Month_Band_3")%>"></td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="DateBand3" type="text" id="DateBand3" size="30" maxlength="100" value="<%= recordset("Date_Band_3")%>"></td>
- </tr>
- <tr>
- <td align="right" style="padding:0 5px 5px 0">£</td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="PriceBand4" type="text" id="PriceBand4" size="4" maxlength="4" value="<%= recordset("Price_Band_4")%>"></td>
- <td align="right" style="padding:0 0 5px 5px">
- <input name="MonthBand4" type="text" id="MonthBand4" size="4" maxlength="4" value="<%= recordset("Month_Band_4")%>"></td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="DateBand4" type="text" id="DateBand4" size="30" maxlength="100" value="<%= recordset("Date_Band_4")%>"></td>
- </tr>
- <tr>
- <td align="right" style="padding:0 5px 5px 0">£</td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="PriceBand5" type="text" id="PriceBand5" size="4" maxlength="4" value="<%= recordset("Price_Band_5")%>"></td>
- <td align="right" style="padding:0 0 5px 5px">
- <input name="MonthBand5" type="text" id="MonthBand5" size="4" maxlength="4" value="<%= recordset("Month_Band_5")%>"></td>
- <td align="right" style="padding:0 0 5px 0">
- <input name="DateBand5" type="text" id="DateBand5" size="30" maxlength="100" value="<%= recordset("Date_Band_5")%>"></td>
- </tr>
- <tr>
- <td colspan="4" style="padding:20px 0 0 0" align="right"><input class="button" type="submit" value="UPDATE" /></td>
- </tr>
- </table>
- </form>
- </div>
- <%
- 'close the connection and recordset objects
- recordset.Close
- Set recordset=Nothing
- connection.Close
- Set connection=Nothing
- %>
Expand|Select|Wrap|Line Numbers
- <%
- 'declare your variables
- Dim connection, sSQL, sConnString, ID
- Price_Band_1=Request.Form("PriceBand1")
- Month_Band_1=Request.Form("MonthBand1")
- Date_Band_1=Request.Form("DateBand1")
- Price_Band_2=Request.Form("PriceBand2")
- Month_Band_2=Request.Form("MonthBand2")
- Date_Band_2=Request.Form("DateBand2")
- Price_Band_3=Request.Form("PriceBand3")
- Month_Band_3=Request.Form("MonthBand3")
- Date_Band_3=Request.Form("DateBand3")
- Price_Band_4=Request.Form("PriceBand4")
- Month_Band_4=Request.Form("MonthBand4")
- Date_Band_4=Request.Form("DateBand4")
- Price_Band_5=Request.Form("PriceBand5")
- Month_Band_5=Request.Form("MonthBand5")
- Date_Band_5=Request.Form("DateBand5")
- ID=request.form("ID") 'receive the hidden form ID
- 'declare SQL statement that will query the database
- sSQL="UPDATE UK_Specials SET " & _
- "Price_Band_1='" & Price_Band_1 & "', Month_Band_1='" & Month_Band_1 & "', Date_Band_1='" & Date_Band_1 & "'," & _
- "Price_Band_2='" & Price_Band_2 & "', Month_Band_2='" & Month_Band_2 & "', Date_Band_2='" & Date_Band_2 & "'," & _
- "Price_Band_3='" & Price_Band_3 & "', Month_Band_3='" & Month_Band_3 & "', Date_Band_3='" & Date_Band_3 & "'," & _
- "Price_Band_4='" & Price_Band_4 & "', Month_Band_4='" & Month_Band_4 & "', Date_Band_4='" & Date_Band_4 & "'," & _
- "Price_Band_5='" & Price_Band_5 & "', Month_Band_5='" & Month_Band_5 & "', Date_Band_5='" & Date_Band_5 & "' WHERE ID='" & ID & "' "
- 'create an ADO connection object
- Set connection = Server.CreateObject("ADODB.connection")
- 'define the connection string, specify database
- 'driver and the location of database
- sConnString="Provider=SQLOLEDB;Data Source=************;" & _
- "Initial Catalog=MYDBNAME;User ID=**********;Password=**********"
- 'Open the connection to the database
- Connection.Open sConnString
- 'Execute the SQL statement
- Connection.Execute sSQL
- 'Now close the connection object
- connection.Close
- Set connection = Nothing
- Response.write "<p style=""padding:100px 0 0 0; text-align:center; font-weight:bold; font-size:18px"">" & _
- "Prices / Dates Updated!</p>"
- %>
Omar.