Updata Database and Null Values (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

dzirkelb1 -> Updata Database and Null Values (3/2/2005 14:22:39)

I wish to update my database using asp...all works fine; however, once I get a null value that I wish to update, then it errors out...meaning, I want to update the field FoodBuy from Cheeseburger to Null in the Access Database. I will have approximately 30 fields that can contain null values.

The error I get is:

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.

quote:


<!--#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 tblSixDollarServiceResults SET StoreID=::StoreID::, Manager='::Manager::', FoodBuy='::FoodBuy::', Entryway1=::Entryway1::, Entryway2=::Entryway2::, Entryway3=::Entryway3::, EntrywayComments='::EntrywayComments::' WHERE (EvalCode=::EvalCode::)"
fp_sDefault="FoodBuy=&EvalCode="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="HardeesEnterData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->


there will be more fields added...in the process of adding them now.



rdouglass -> RE: Updata Database and Null Values (3/2/2005 15:04:30)

Which fields have a Null when you try to do an UPDATE that fails?

With the line:

UPDATE tblSixDollarServiceResults SET StoreID=::StoreID::, ...

is StoreID a PK field?



dzirkelb1 -> RE: Updata Database and Null Values (3/2/2005 15:21:05)

The PK field is EvalCode...any of the fields that are Entryway(x), Front(x), Dining(x), etc etc (Dining1, Front2, Entryway2, etc etc)....here is the full, working (minus null values) update query:

quote:


<!--#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 tblSixDollarServiceResults SET StoreID=::StoreID::, Manager='::Manager::', FoodBuy='::FoodBuy::', Entryway1=::Entryway1::, Entryway2=::Entryway2::, Entryway3=::Entryway3::, EntrywayComments='::EntrywayComments::', Drive1=::Drive1::, Drive2=::Drive2::, Drive3=::Drive3::, Drive4=::Drive4::, Drive5=::Drive5::, Drive6=::Drive6::, DriveTime='::DriveTime::', DriveComments='::DriveComments::', Front1=::Front1::, Front2=::Front2::, Front3=::Front3::, Front4=::Front4::, FrontComments='::FrontComments::', Dining1=::Dining1::, Dining2=::Dining2::, Dining3=::Dining3::, Dining4=::Dining4::, Dining5=::Dining5::, Dining6=::Dining6::, DiningTime='::DiningTime::', Dining7=::Dining7::, Dining8=::Dining8::, Dining9=::Dining9::, Dining10=::Dining10::, Dining11=::Dining11::, DiningComments='::DiningComments::', Product1=::Product1::, Product2=::Product2::, Product3=::Product3::, Product4=::Product4::, Product5=::Product5::, Product6=::Product6::, Product7=::Product7::, Product8=::Product8::, ProductComments='::ProductComments::', AdditionalComments='::AdditionalComments::', Edited=1 WHERE (EvalCode=::EvalCode::)"
fp_sDefault="FoodBuy=&EvalCode="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%""></td></tr>"
fp_sDataConn="HardeesEnterData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->



rdouglass -> RE: Updata Database and Null Values (3/2/2005 15:40:48)

So that one works. Can you post one that doesn't work? Or is it that one fails once you have a Null value?

How do you determine what ones are Null? How are you trying to UPDATE it - with what SQL are you using?

Are you using this format?

myField = null

and not:

myField = 'null'

Just some thoughts.



dzirkelb1 -> RE: Updata Database and Null Values (3/2/2005 16:12:54)

That is the sql query used to update the query....if a value is left blank, meaning Entryway1=null, then I want the field to be Entryway=null (not show null...just have nothing there)

I currently pass nothing if I want a null value to show up...If I want the value to be null, then I pass "", instead of 'null' or null...I pass nothing instead.

The ones that are null are n/a...each question has yes, no, n/a...n/a equals null.

The posted works fine until I get a null value, then it bails out on me. I have another one with the same problem; however, they are all the same. I pass nothing for a value, and I try to update the field to nothing, and it errors out...my other one is this:

quote:


<!--#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 tblSixDollarServiceResults SET Operator='"&Operator&"', ShopperID="&ShopperID&", Price="&Price&", EvalTime=#"&EvalTime&"#, EvalDate=#"&EvalDate&"#, EntrywayPointsPossible="&EntrywayPointsPossible&", EntrywayPointsEarned="&EntrywayPointsEarned&",DrivePointsEarned ="&DrivePointsEarned &",DrivePointsPossible="&DrivePointsPossible&", FrontPointsPossible="&FrontPointsPossible&",FrontPointsEarned="&FrontPointsEarned&",DiningPointsPossible="&DiningPointsPossible&", DiningPointsEarned="&DiningPointsEarned&",ProductPointsEarned="&ProductPointsEarned&",ProductPointsPossible="&ProductPointsPossible&",PointsPossible="&PointsPossible&",PointsEarned="&PointsEarned&" WHERE EvalCode="&EvalCode&""
fp_sDefault="PointsPossible=&PointsEarned="
fp_sNoRecords=""
fp_sDataConn="HardeesEnterData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%><!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->


This one errors out when points possible and points earned fields are blank...they are retrieved from:

quote:


FOR i=1 to 3
IF Request.querystring("Entryway" & i) <> "" THEN
EntrywayPointsPossible=EntrywayPointsPossible+1
IF cint(Request.querystring("Entryway" & i))=1 THEN
EntrywayPointsEarned=EntrywayPointsEarned+1
END IF
END IF
NEXT
%>

<%
FOR i=1 to 6
IF Request.querystring("Drive" & i) <> "" THEN
DrivePointsPossible=DrivePointsPossible+1
IF cint(Request.querystring("Drive" & i))=1 THEN
DrivePointsEarned =DrivePointsEarned +1
END IF
END IF
NEXT
%>

<%
FOR i=1 to 4
IF Request.querystring("Front" & i) <> "" THEN
FrontPointsPossible=FrontPointsPossible+1
IF cint(Request.querystring("Front" & i))=1 THEN
FrontPointsEarned=FrontPointsEarned+1
END IF
END IF
NEXT
%>

<%
FOR i=1 to 11
IF Request.querystring("Dining" & i) <> "" THEN
DiningPointsPossible=DiningPointsPossible+1
IF cint(Request.querystring("Dining" & i))=1 THEN
DiningPointsEarned=DiningPointsEarned+1
END IF
END IF
NEXT
%>

<%
FOR i=1 to 8
IF Request.querystring("Product" & i) <> "" THEN
ProductPointsPossible=ProductPointsPossible+1
IF cint(Request.querystring("Product" & i))=1 THEN
ProductPointsEarned=ProductPointsEarned+1
END IF
END IF
NEXT
%>

<%PointsPossible=EntrywayPointsPossible+DrivePointsPossible+FrontPointsPossible+DiningPointsPossible+ProductPointsPossible%>
<%PointsEarned=EntrywayPointsEarned+DrivePointsEarned+FrontPointsEarned+DiningPointsEarned+ProductPointsEarned%>


For this example, the drive ones are relavant...if the mystery shop is for a dine in, then drive thu questions become n/a...hence, drivepossible and driveearned are both null, which gets me an error on my update.



rdouglass -> RE: Updata Database and Null Values (3/2/2005 16:16:42)

quote:

IF Request.querystring("Entryway" & i) <> "" THEN


I still don't see where you're setting the value to null (an empty string is not a null value) but you may be choking when you do the line above. Try it this way:

IF trim(Request.querystring("Entryway" & i)&"") <> "" THEN
...

I suspect cint is choking without it.



dzirkelb1 -> RE: Updata Database and Null Values (3/2/2005 16:31:43)

That isn't really relavant at this time as everything works above..lets simplify it:


http://www.msultd.com/form1.asp This has an update form on it...at this time, it is only updating ID 3...so, change whatever is in there to whatever you wish and it will post to http://www.msultd.com/form2.asp...all works fine.

Now, try to update it to a null value...meaning, delete what is in the text box, and hit submit...this is where it errors out...is that more clear?




dzirkelb1 -> RE: Updata Database and Null Values (3/2/2005 16:32:42)

the code for form1.asp is 
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 2</title>
</head>

<body>

<table width="100%" border="1">
	<thead>
		<tr>
			<th ALIGN="LEFT"><b>ID</b></th>
			<th ALIGN="LEFT"><b>T1</b></th>
			<th ALIGN="LEFT"><b>Remote_computer_name</b></th>
			<th ALIGN="LEFT"><b>User_name</b></th>
			<th ALIGN="LEFT"><b>Browser_type</b></th>
			<th ALIGN="LEFT"><b>Timestamp</b></th>
		</tr>
	</thead>
	<tbody>
		<!--#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="SELECT * FROM Results WHERE (ID=3)"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="new_page_2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&T1=202&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
			<td>
			<%=FP_FieldVal(fp_rs,"ID")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"T1")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Remote_computer_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"User_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Browser_type")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Timestamp")%></td>
		</tr>
	
</tbody>
</table>
<form method="POST" action="form2.asp">
	<p> </p>
	<p>
	<input type="text" name="T1" size="20" value="<%=FP_FieldVal(fp_rs,"T1")%>"></p>
	<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
	<!--#include file="_fpclass/fpdbrgn2.inc"-->
</body>

</html>


the code for form2.asp is 
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 2</title>
</head>

<body>

<table width="100%" border="1">
	<thead>
	</thead>
	<tbody>
		<!--#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 Results SET T1='::T1::' WHERE (ID=3)"
fp_sDefault="T1="
fp_sNoRecords=""
fp_sDataConn="new_page_2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<table width="100%" border="1">
	<thead>
		<tr>
			<th ALIGN="LEFT"><b>ID</b></th>
			<th ALIGN="LEFT"><b>T1</b></th>
			<th ALIGN="LEFT"><b>Remote_computer_name</b></th>
			<th ALIGN="LEFT"><b>User_name</b></th>
			<th ALIGN="LEFT"><b>Browser_type</b></th>
			<th ALIGN="LEFT"><b>Timestamp</b></th>
		</tr>
	</thead>
	<tbody>
		<!--#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="SELECT * FROM Results WHERE (T1='::T1::')"
fp_sDefault="T1="
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="new_page_2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&T1=202&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
			<td>
			<%=FP_FieldVal(fp_rs,"ID")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"T1")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Remote_computer_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"User_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Browser_type")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Timestamp")%></td>
		</tr>
		<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

</body>

</html>



BeTheBall -> RE: Updata Database and Null Values (3/2/2005 19:50:32)

Open the db table in Access and in design view go to the field and change Allow Zero Length to Yes,



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 10:15:25)

The majority of these fields are number fields so I don't have that option to allow zero length...the only thing stored in these fields are 1, 0, or nothing.

Some other fields add up those columns to make it, say, 3 for possible and 2 for earned, then percentage is .66



BeTheBall -> RE: Updata Database and Null Values (3/3/2005 10:25:24)

For number fields, you will need to do some sort of If/Then analysis. For example,

If Request.Form("yourfield") ="" Then
yourVariable = 0
Else
yourVariable = Request.Form("yourfield")



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 10:29:24)

Won't that place a 0 in the field thouth? I ulitmately wish to update fields to a blank value...if the value is 1, then I want to update it to nothing...simple as goign into the database and deleting it, but, of course, impossible to the meger mind of me in asp :)



rdouglass -> RE: Updata Database and Null Values (3/3/2005 11:28:43)

quote:

If Request.Form("yourfield") ="" Then
yourVariable = 0
Else
yourVariable = Request.Form("yourfield")


If Request.Form("yourfield") ="" Then
yourVariable = null
Else
yourVariable = Request.Form("yourfield")
END IF

As duane is suggesting (and I've been suggesting) if you have a blank text field, you'll have to change it to something first; either 1,0, or null NOT a blank string. As you already know, an empty string is not null but empty. Remember the syntax is:

MyField = null

and not:

MyField = 'null'

You will probably need to loop thru those fields and check to see if they are indeed empty. And AFAIK you won't be able to do it in the SQL itself, but you'll have to check the fields and then pass values to the SQL.

That any help?



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 11:49:50)

Ahh, I understand now...let me test :)



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 12:03:07)

This works perfect if the field is a text (allow zero length). However, when I change the field to be a number, then it errors out.

Here is what I have for form2

<%IF request.form("T1")="" THEN
T1=null
Else
T1=request.form("T1")
end if%>
<%response.write(T1)%>

sql is:

fp_sQry="UPDATE Results SET T1="&T1&" WHERE (ID=3)"

Error is:

Description: Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft JET Database Engine

The same error comes when the value is blank in the database and I try to update it to anything, like 5.

It currently is set to nothing in the database..try to update it to any number and this error comes. If I change the value from blank to any number, then update to that number to another number, then it works fine.




BeTheBall -> RE: Updata Database and Null Values (3/3/2005 12:05:27)

Can you use 0 for numeric?

<%IF request.form("T1")="" THEN
T1=0
Else
T1=request.form("T1")
end if%>
<%response.write(T1)%>



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 12:10:45)

Ya, that works fine...however, it then updates the database to a 0 instead of empty. Also, I just tested it where the field is already blank, and tried to update the field from blank to 5, and it didn't work.



BeTheBall -> RE: Updata Database and Null Values (3/3/2005 12:40:14)

Just to summarize. If you want to update a text field in your db to be blank (like deleting the value directly in Access), then set the field to allow zero-length strings and then if your form field is blank, then the field will be cleared out with no other processing needed.

Your SQL will simply be:

UPDATE tblName SET dbfld = '::formfield::' WHERE ID = 3

If the field is numeric, you need to evaluate the form field value, put it into a variable and then use the variable in your UPDATE. For example,

If Request.Form("T1") ="" Then
myVariable = Null
Else
myVariable = Request.Form("T1")

Then, you SQL will be:

UPDATE tblName SET dbfld = "&myVariable&" WHERE ID =3

The point is the process varies slightly for a numeric db field and a text db field.



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 12:46:57)

Correct...I understand all of that; however, it doesn't work...I have the EXACT same code as you do above and it errors out. 

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 2</title>
</head>

<body>

<%IF request.form("T1")="" THEN
T1=Null
Else
T1=request.form("T1")
end if%>
<%response.write(T1)%>

<table width="100%" border="1">
	<thead>
	</thead>
	<tbody>
		<!--#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 Results SET T1="&T1&" WHERE (ID=3)"
fp_sDefault="T1="
fp_sNoRecords=""
fp_sDataConn="new_page_2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<table width="100%" border="1">
	<thead>
		<tr>
			<th ALIGN="LEFT"><b>ID</b></th>
			<th ALIGN="LEFT"><b>T1</b></th>
			<th ALIGN="LEFT"><b>Remote_computer_name</b></th>
			<th ALIGN="LEFT"><b>User_name</b></th>
			<th ALIGN="LEFT"><b>Browser_type</b></th>
			<th ALIGN="LEFT"><b>Timestamp</b></th>
		</tr>
	</thead>
	<tbody>
		<!--#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="SELECT * FROM Results WHERE (ID=3)"
fp_sDefault="T1="
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="new_page_2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&T1=202&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
			<td>
			<%=FP_FieldVal(fp_rs,"ID")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"T1")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Remote_computer_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"User_name")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Browser_type")%></td>
			<td>
			<%=FP_FieldVal(fp_rs,"Timestamp")%></td>
		</tr>
		<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

</body>

</html>


it still gives the same error. I tried updating the value of 5 to nothing, meaning, in my textbox, I deleted the value so nothing was displayed; then, I hit submit.

Could that be the problem that it is trying to send a blank text value instead of a blank numeric value? That would cause the myvariable=null to fail I would think?



BeTheBall -> RE: Updata Database and Null Values (3/3/2005 12:52:09)

When went to your test page last night, it allowed me to update the field T1 to "test". That tells me that the field is text, not numeric. Have you since changed it to numeric? If not, that is probably why the code given here is throwing an error.



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 12:59:26)

Ya, I changed that soem posts above (as I saw you had it set to test)...that made me test the theory of the text field and updating to a blank...that works great.

So, I then changed it to a numeric field and this is wher we are coming up with all the problems. I just can't figure out a way to update a blank value to something of value and vise versa.



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 13:04:58)

whats that snippet of code I place to display what the sql query outputs? meaning, i want it to display on my screen fp_sQry="UPDATE Results SET T1="&T1&" WHERE (ID=3)" but replace &T1& with the value I am sending to the database?



BeTheBall -> RE: Updata Database and Null Values (3/3/2005 13:05:21)

OK. Please don't be insulted as I am just making sure I understand exactly what we are up against. Here is my question. When I visit the page and try to enter "test", I now see a javascript alert telling me to enter a numeric value. That is a good idea. However, did you open the db in Access and change the field from text to numeric? In other words, javascript validation of the form field is not enought, the db field must be set to numeric for the code we have provided to work properly and if you are getting a Data type mismatch, that makes me think the db field is still set as text.



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 13:15:16)

no insult taken....any sort of idea on anything here is fine with me....it seems so easy and I wish it was something like that that fixes it!

However, I have changed the field in the access database to be numeric. I was hoping that somehow chaning that would force a numeric value to be passed as I am kind of thinking my form is passing a text value, and then it is being inserted as numeric...meaning, its passing 5 as text 5, but being put in the database as numeric 5.



Spooky -> RE: Updata Database and Null Values (3/3/2005 13:18:50)

Can you do :

<%=fp_sQry%> on the page please?



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 13:20:07)

ya, thats the snippet...its in place



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 13:21:30)

it shows UPDATE Results SET T1= WHERE (ID=3)



Spooky -> RE: Updata Database and Null Values (3/3/2005 13:22:21)

change :

T1=Null

to

T1="null"



dzirkelb1 -> RE: Updata Database and Null Values (3/3/2005 13:24:55)

...speechless...

thanks dwayne, rdouglas, and spooky for all your help on this issue...it was absolutely driving me nuts, and, for the other issue I had with blank values also. The feeling of vomit in the stomach is gone for it works!!



Spooky -> RE: Updata Database and Null Values (3/3/2005 13:27:36)

The reason is - the sql string isnt passing null. Sure youve tried to set "t1" to null, however the SQL string doesnt know that.
So, you need to enforce the fact that the text "null"is used in the SQL string.

Also, youll want to trim the input :

<%IF trim(request.form("T1"))="" THEN



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1083984