By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,162 Members | 1,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,162 IT Pros & Developers. It's quick & easy.

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

100+
P: 120
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
Expand|Select|Wrap|Line Numbers
  1. <%
  2. 'declare you variables
  3. Dim SQL, connection, recordset
  4. Dim sConnString, ID 'receive the id of the record passed through querystring
  5. ID = Trim(Request("ID"))
  6. 'ID=request.querystring("ID")
  7. 'ID=request.form("ID") 'receive the hidden form ID
  8.  
  9. 'Declare the SQL statement that will query the database
  10. SQL="SELECT * FROM UK_Specials WHERE ID='" & ID & "' "
  11.  
  12. 'Create an ADO connection and recordset object
  13. Set Connection = Server.CreateObject("ADODB.Connection")
  14. Set Recordset = Server.CreateObject("ADODB.Recordset")
  15.  
  16. 'define the connection string, specify database
  17. 'driver and the location of database
  18. sConnString="Provider=SQLOLEDB;Data Source=********" & _
  19. "Initial Catalog=MYDBNAME;User ID=****;Password=*************"
  20.  
  21. 'Open the connection to our database
  22. Connection.Open(sConnString)
  23.  
  24. 'Open the recordset object, execute the SQL statement
  25. 'and return the record with the id that was sent
  26. recordset.Open SQL,connection
  27. %>
  28.  
  29. <div align="center" style="width:360px; padding:10px">
  30.  
  31. <form name="UKSpecialsUpdate" id="UKSpecialsUpdate" method="post" onSubmit="return validate();" action="update.asp" />
  32. <input type="hidden" name="ID" id="ID" value="<%= recordset("ID")%>" />
  33. <table align="center" width="100%" cellpadding="0" cellspacing="0">
  34.   <tr>
  35.     <td style="font-weight:bold; color:#01207B"><%= recordset("Flight_Route")%></td>
  36.     <td style="font-weight:bold; text-align:right"><a style="text-align:right" href="javascript: self.close ()">Close Window</a></td>
  37.   </tr>
  38. </table>
  39.  
  40. <table align="center" width="100%" cellpadding="0" cellspacing="0" style="margin:20px 0 0 0">
  41.   <tr>
  42.     <td colspan="2" style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Price</td>
  43.     <td style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Month</td>
  44.     <td style="font-weight:bold; font-size:15px; padding:0 0 5px 0; text-align:right">Date/s</td>
  45.   </tr>
  46.   <tr>
  47.     <td align="right" style="padding:0 5px 5px 0">&pound;</td>
  48.     <td align="right" style="padding:0 0 5px 0">
  49.     <input name="PriceBand1" type="text" id="PriceBand1" size="4" maxlength="4" value="<%= recordset("Price_Band_1")%>"></td>
  50.     <td align="right" style="padding:0 0 5px 5px">
  51.     <input name="MonthBand1" type="text" id="MonthBand1" size="4" maxlength="4" value="<%= recordset("Month_Band_1")%>"></td>
  52.     <td align="right" style="padding:0 0 5px 0">
  53.     <input name="DateBand1" type="text" id="DateBand1" size="30" maxlength="100" value="<%= recordset("Date_Band_1")%>"></td>
  54.   </tr>
  55.   <tr>
  56.     <td align="right" style="padding:0 5px 5px 0">&pound;</td>
  57.     <td align="right" style="padding:0 0 5px 0">
  58.     <input name="PriceBand2" type="text" id="PriceBand2" size="4" maxlength="4" value="<%= recordset("Price_Band_2")%>"></td>
  59.     <td align="right" style="padding:0 0 5px 5px">
  60.     <input name="MonthBand2" type="text" id="MonthBand2" size="4" maxlength="4" value="<%= recordset("Month_Band_2")%>"></td>
  61.     <td align="right" style="padding:0 0 5px 0">
  62.     <input name="DateBand2" type="text" id="DateBand2" size="30" maxlength="100" value="<%= recordset("Date_Band_2")%>"></td>
  63.   </tr>
  64.   <tr>
  65.     <td align="right" style="padding:0 5px 5px 0">&pound;</td>
  66.     <td align="right" style="padding:0 0 5px 0">
  67.     <input name="PriceBand3" type="text" id="PriceBand3" size="4" maxlength="4" value="<%= recordset("Price_Band_3")%>"></td>
  68.     <td align="right" style="padding:0 0 5px 5px">
  69.     <input name="MonthBand3" type="text" id="MonthBand3" size="4" maxlength="4" value="<%= recordset("Month_Band_3")%>"></td>
  70.     <td align="right" style="padding:0 0 5px 0">
  71.     <input name="DateBand3" type="text" id="DateBand3" size="30" maxlength="100" value="<%= recordset("Date_Band_3")%>"></td>
  72.   </tr>
  73.   <tr>
  74.     <td align="right" style="padding:0 5px 5px 0">&pound;</td>
  75.     <td align="right" style="padding:0 0 5px 0">
  76.     <input name="PriceBand4" type="text" id="PriceBand4" size="4" maxlength="4" value="<%= recordset("Price_Band_4")%>"></td>
  77.     <td align="right" style="padding:0 0 5px 5px">
  78.     <input name="MonthBand4" type="text" id="MonthBand4" size="4" maxlength="4" value="<%= recordset("Month_Band_4")%>"></td>
  79.     <td align="right" style="padding:0 0 5px 0">
  80.     <input name="DateBand4" type="text" id="DateBand4" size="30" maxlength="100" value="<%= recordset("Date_Band_4")%>"></td>
  81.   </tr>
  82.   <tr>
  83.     <td align="right" style="padding:0 5px 5px 0">&pound;</td>
  84.     <td align="right" style="padding:0 0 5px 0">
  85.     <input name="PriceBand5" type="text" id="PriceBand5" size="4" maxlength="4" value="<%= recordset("Price_Band_5")%>"></td>
  86.     <td align="right" style="padding:0 0 5px 5px">
  87.     <input name="MonthBand5" type="text" id="MonthBand5" size="4" maxlength="4" value="<%= recordset("Month_Band_5")%>"></td>
  88.     <td align="right" style="padding:0 0 5px 0">
  89.     <input name="DateBand5" type="text" id="DateBand5" size="30" maxlength="100" value="<%= recordset("Date_Band_5")%>"></td>
  90.   </tr>
  91.   <tr>
  92.     <td colspan="4" style="padding:20px 0 0 0" align="right"><input class="button" type="submit" value="UPDATE" /></td>
  93.   </tr>
  94. </table>
  95. </form>
  96.  
  97. </div>
  98.  
  99. <%
  100. 'close the connection and recordset objects
  101. recordset.Close
  102. Set recordset=Nothing
  103. connection.Close
  104. Set connection=Nothing
  105. %>
  106.  
update.asp code
Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. 'declare your variables
  4. Dim connection, sSQL, sConnString, ID
  5. Price_Band_1=Request.Form("PriceBand1")
  6. Month_Band_1=Request.Form("MonthBand1")
  7. Date_Band_1=Request.Form("DateBand1")
  8. Price_Band_2=Request.Form("PriceBand2")
  9. Month_Band_2=Request.Form("MonthBand2")
  10. Date_Band_2=Request.Form("DateBand2")
  11. Price_Band_3=Request.Form("PriceBand3")
  12. Month_Band_3=Request.Form("MonthBand3")
  13. Date_Band_3=Request.Form("DateBand3")
  14. Price_Band_4=Request.Form("PriceBand4")
  15. Month_Band_4=Request.Form("MonthBand4")
  16. Date_Band_4=Request.Form("DateBand4")
  17. Price_Band_5=Request.Form("PriceBand5")
  18. Month_Band_5=Request.Form("MonthBand5")
  19. Date_Band_5=Request.Form("DateBand5")
  20. ID=request.form("ID") 'receive the hidden form ID
  21.  
  22. 'declare SQL statement that will query the database
  23. sSQL="UPDATE UK_Specials SET " & _
  24. "Price_Band_1='" & Price_Band_1 & "', Month_Band_1='" & Month_Band_1 & "', Date_Band_1='" & Date_Band_1 & "'," & _
  25. "Price_Band_2='" & Price_Band_2 & "', Month_Band_2='" & Month_Band_2 & "', Date_Band_2='" & Date_Band_2 & "'," & _ 
  26. "Price_Band_3='" & Price_Band_3 & "', Month_Band_3='" & Month_Band_3 & "', Date_Band_3='" & Date_Band_3 & "'," & _ 
  27. "Price_Band_4='" & Price_Band_4 & "', Month_Band_4='" & Month_Band_4 & "', Date_Band_4='" & Date_Band_4 & "'," & _ 
  28. "Price_Band_5='" & Price_Band_5 & "', Month_Band_5='" & Month_Band_5 & "', Date_Band_5='" & Date_Band_5 & "'  WHERE ID='" & ID & "' "
  29.  
  30. 'create an ADO connection object
  31. Set connection = Server.CreateObject("ADODB.connection")
  32.  
  33. 'define the connection string, specify database
  34. 'driver and the location of database
  35. sConnString="Provider=SQLOLEDB;Data Source=************;" & _
  36. "Initial Catalog=MYDBNAME;User ID=**********;Password=**********"
  37.  
  38. 'Open the connection to the database
  39. Connection.Open sConnString
  40.  
  41. 'Execute the SQL statement
  42. Connection.Execute sSQL
  43.  
  44. 'Now close the connection object
  45. connection.Close
  46. Set connection = Nothing
  47.  
  48. Response.write "<p style=""padding:100px 0 0 0; text-align:center; font-weight:bold; font-size:18px"">" & _
  49. "Prices / Dates Updated!</p>"
  50.  
  51. %>
  52.  
thanks in advance
Omar.
Sep 29 '10 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 164
If you do a
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 29 '10 #2

100+
P: 120
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
Sep 30 '10 #3

Expert 100+
P: 164
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.
Sep 30 '10 #4

100+
P: 120
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..
Oct 1 '10 #5

100+
P: 120
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
Expand|Select|Wrap|Line Numbers
  1. 'declare SQL statement that will query the database
  2. sSQL="UPDATE UK_SpecialsTEST SET " & _
  3. "Price_Band_1=" & Price_Band_1 & ", Month_Band_1='" & Month_Band_1 & "', Date_Band_1='" & Date_Band_1 & "'," & _
  4. "Price_Band_2=" & Price_Band_2 & ", Month_Band_2='" & Month_Band_2 & "', Date_Band_2='" & Date_Band_2 & "'," & _ 
  5. "Price_Band_3=" & Price_Band_3 & ", Month_Band_3='" & Month_Band_3 & "', Date_Band_3='" & Date_Band_3 & "'," & _ 
  6. "Price_Band_4=" & Price_Band_4 & ", Month_Band_4='" & Month_Band_4 & "', Date_Band_4='" & Date_Band_4 & "'," & _ 
  7. "Price_Band_5=" & Price_Band_5 & ", Month_Band_5='" & Month_Band_5 & "', Date_Band_5='" & Date_Band_5 & "'  WHERE ID='" & ID & "' "
  8.  
please advise as I'm not sure where the problem is
Oct 1 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.