empty input field submits and enters 0 in sql table??

[omar999]

I have a CMS which displays some flight routes, alongside prices, dates which is using asp and sql server 05.

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

<%
'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">&pound;</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">&pound;</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">&pound;</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">&pound;</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">&pound;</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
%>

update.asp code

<%

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

%>

thanks in advance
Omar.

[Nicodemas]

If you do a

Response.Write Typename(Price_Band_1)

on line 6 of update.asp, what does it say?

Also, I noted in your SQL string that you are saving this information as a string. I know this because you have wrapped the value from the form in apostrophes. Is this the way your database is set up - are those fields CHAR/VARCHAR/TEXT-based data type fields? Check your database setup and let me know.

This last could definitely be part of the problem. I do not see why you would store the number as a string value if it is meant to reflect a price.

[omar999]

hi nicodemas

Response.Write Typename(Price_ Band_1) returns Empty on the update.asp page... im still fairly weak with my asp so I have no idea what this means?

the ID, Price_Band fields are set as type integer in the table whereas the date, month and other fields are all set as varchar(50).. is this wrong?

shall I remove all apostrophe's from my Update statement to fix??

please advise

[Nicodemas]

Empty basically means it has no value assigned to it. The question to ask yourself is, why is PriceBrand1 Empty? Also, according to standard SQL, integer data type fields' values should not be enclosed in apostrophes. So, yes, remove the apostrophes from around your integer fields' values.

[omar999]

some priceband fields are empty and purposely contain a NULL value in the table. This is a requirement where there are no prices to be inserted in some priceband fields. And I cant enter 0 as I also perform a minimum price calculated based on all records to therefore if there is no price NULL is what I enter as this works for me.

I will try to remove the apostrophes from around my integer field values and will report back progress..

[omar999]

UPDATE: i've taken your advice and by removing the apostrophe's from around my integer field values - only problem now is when I leave the field Price_Band_1 empty I get the following error

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near ','.
/prices-databasetest/update.asp, line 71

'declare SQL statement that will query the database
sSQL="UPDATE UK_SpecialsTEST 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 & "' "

please advise as I'm not sure where the problem is