|
| |
|
|
adam2804
Posts: 34
Joined: 6/6/2006
Status: offline
 |
Inserting a null value - 7/19/2006 6:56:22
Hello,
I am using a drop down list to insert a value to a database, and if no option is selected then it inserts a null value to the database. Although I can't seem to get it to insert a <NULL> value it seems to insert a 'blank' value which disrupts other queries on the site.
I have tried:
<option value =Null>
<option value ="">
<option value ="NULL">
all seem to have the same result. Does anyone have any ideas what to use, as I can't seem to find any advice anywhere.
Thanks.
Adam
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Inserting a null value - 7/19/2006 9:24:09
The problem isn't in your dropdown but in your SQL.
You probably have SQL that is trying to fill a text field and the SQL probably looks like:
..., '" & Request.form("myDropdown") & "',...
or
.., '::myDropdown::', ...
Well a Null is *not* a text value and so cannot be handled like that. You have to check for a value first using a conditional statement (IF...THEN) to decide whether to use Null or not and then pass a variable to the SQL.
Many times it's easier using just a blank.
If you must use Null, can you post your SQL statement here or at least say whether you're using a DRW or not?
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
adam2804
Posts: 34
Joined: 6/6/2006
Status: offline
|
RE: Inserting a null value - 7/19/2006 9:36:57
Thanks that has helped me a lot, as I was getting frustrated with the drop down and not thought about the sql (what a dummy!)
here's the sql that updates the db:
<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="BureauManagerSQL" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="FALSE" b-listseparator="FALSE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql="UPDATE TB_MasterPrices SET<br> Unit = ::Unit::,<br> Price = ::Price::,<br> Billing_ID = ::Billing_ID::,<br> BillUsing = '::BillUsing::'<br>WHERE<br> ID = '::ID::' AND<br> WorkType = '::WorkType::'" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Unit=&Price=&Billing_ID=&BillUsing=&ID=&WorkType=" s-norecordsfound="Pricing details updated." i-maxrecords="256" i-groupsize="0" botid="15" u-dblib="../../_fpclass/fpdblib.inc" u-dbrgn1="../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the start of a Database Results region.</font></td></tr></table>" startspan b-WasTableFormat="FALSE" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes --><!--#include file="../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE TB_MasterPrices SET Unit = ::Unit::, Price = ::Price::, Billing_ID = ::Billing_ID::, BillUsing = '::BillUsing::' WHERE ID = '::ID::' AND WorkType = '::WorkType::'"
fp_sDefault="Unit=&Price=&Billing_ID=&BillUsing=&ID=&WorkType="
fp_sNoRecords="Pricing details updated."
fp_sDataConn="BureauManagerSQL"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=15
fp_iRegion=BOTID
%>
<!--#include file="../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="19871" --><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="../../_fpclass/fpdbrgn2.inc"-->
It's the BillUsing that's in question by the way.
Where would I put the if statement and how do I write a 'proper' null value to the db.
I'm affraid it's going to be a lot easier to update with a null as there are about 7 other pages that are effected because of this.
Thanks for your help,
Adam
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Inserting a null value - 7/19/2006 9:52:24
First would be to backup your page and put this DRW on a Spooky Diet. (See the tutorials section of this site.)
Then, on your dropdown pick a value that you can use to identify what Null will be. You can just use Null, "", or sometimes I use something that would never be in a word like "QQQ". Anyways, let's assume you use the word "Null" as the value being returned from the dropdown.
Now on the page that has the dieted DRW, put this code near the top of the page:
<%DIM myBillUsingVariable
IF trim(request.form("BillUsing")&"") = "Null" THEN
myBillUsingVariable = "Null"
ELSE
myBillUsingVariable = "'" & trim(request.form("BillUsing")&"") & "'"
END IF%>
(I am assuming the dropdown name is "BillUsing".) Be careful with that script since there is a mix of both quotes and apostrohes in there and they absolutely do make a difference.
Now you edit the DRW line here:
fp_sQry="UPDATE TB_MasterPrices SET Unit = ::Unit::, Price = ::Price::, Billing_ID = ::Billing_ID::, BillUsing = '::BillUsing::' WHERE ID = '::ID::' AND WorkType = '::WorkType::'"
to look like this:
fp_sQry="UPDATE TB_MasterPrices SET Unit = ::Unit::, Price = ::Price::, Billing_ID = ::Billing_ID::, BillUsing = " & myBillUsingVariable & " WHERE ID = '::ID::' AND WorkType = '::WorkType::'"
See what I did? We check first wheter we want to send a text string or Null to the SQL and then craft the SQL statement to reflect our choice using the variable.
That help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
adam2804
Posts: 34
Joined: 6/6/2006
Status: offline
|
RE: Inserting a null value - 7/19/2006 10:48:38
Thanks,
In each part I think that the functionality works. However don't think I'm putting the If statement in the right place, as it runs as soon as the page loads, rather than when the form is submitted. Where would you put the code without seeing the page? At the moment the code is just below the <body> tag.
Regards,
Adam
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Inserting a null value - 7/19/2006 11:20:56
quote:
it runs as soon as the page loads
Yes, but why would that be a problem? We're not telling the DRW to run. At worst, we're assigning an empty string to a variable.
Or does some error display when you first enter the page?
That script (if I built it properly) should affect nothing on that page whether you're posting to it or not.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
