| Author |
 Topic  |
|
Astralis
Senior Member
USA
1218 Posts |
 Posted - 28 December 2004 : 03:48:18
|
I just converted via Access Upsize wizard onto a SQL Server 2000 database. Expecting all True/False fields to be converted to 1/0 (which they were), I began changing the code to reflect this. But, when I tested it, the checkboxes in the form that I was using to update data were not being filled in with the "if value=1 then "checked" etc... So, I looked in the database via EM to make certain that the fields were converted to bit type 1/0 and they were. I even tested it by watching the 1/0 switch as I sent data to the database. But, it still wasn't registering with the checked fields. So, I created a response.write to see what the value was of one of the variables of the check fields. Well, to my suprise, the value came back as "True", not "1" as it said in the database. I clicked the form and changed it to "False" and saw that it now said "0" in the database.
There is no function in my code that is automatically converting these numbers to true/false type. Does SQL Server 2000 preserve the True/False type? If so, then I should switch it to "If value=True then "checked" right?
Any explanations?
BTW, I'm absolutely positive it's not really reading from an Access db because the connection string no longer exists and the Access database is not on the server. I also switched from IE to Mozilla to see if this was somehow a cookie error.
I've always been taught SQL Server doesn't use True/False. |
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 28 December 2004 : 17:35:08
|
| Can anyone confirm whether SQL Server 2000 does or does not preserve True/False fields during an Access upsize using Access's upsize wizard? |
 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 28 December 2004 : 17:48:13
|
| AFAIK, it converts a yes/no field to a bit (1/0) field. ASP should still recognise the DB output of these DB fields as true/false. If you have more problems try using cbool() function to make sure your value is boolean before checking it. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 28 December 2004 : 17:54:01
|
AFAIK? That's a new one. What does it mean?
That is very interesting. I'm new to SQL Server and never knew about that. So, any 1 or 0 will be seen as True/False by ASP unless it's a boolean value? And, making it a boolean value is the only way to make it 1 or 0? |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 28 December 2004 : 18:18:10
|
As Far As I Know.
ASP is pretty clever in interpreting values as boolean - True or False (it has to be as variables aren't defined as an explicit type.)
In ASP: 1, "1", true and "true" should all = TRUE, whereas 0, "0", false and "false" = FALSE
in fact in most cases, a value bigger other than zero is also TRUE.
if it doesnt seem to be working for some reason the using cbool() will convert the value to either a true or false e.g.
myvariable = 1
response.write cbool(myvariable) should output "true"
|
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 28 December 2004 18:52:13 |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 28 December 2004 : 18:46:27
|
| Thanks D3mon. I've learned a lot. |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 28 December 2004 : 18:50:06
|
try this code:
<%
myVariable1 = 1
myVariable2 = "1"
myVariable3 = "true"
myVariable4 = true
if myVariable1 then response.write "myvariable1 = true<br />"
if myVariable2 then response.write "myvariable2 = true<br />"
if myVariable3 then response.write "myvariable3 = true<br />"
if myVariable4 then response.write "myvariable4 = true<br />"
%><br><%
if myVariable1 = true then response.write "myvariable1 = true<br />"
if myVariable2 = true then response.write "myvariable2 = true<br />"
if myVariable3 = true then response.write "myvariable3 = true<br />"
if myVariable4 = true then response.write "myvariable4 = true<br />"
%><br><%
response.write "cbool(myvariable1) = " & cbool(myVariable1) & "<br />"
response.write "cbool(myvariable2) = " & cbool(myVariable2) & "<br />"
response.write "cbool(myvariable3) = " & cbool(myVariable3) & "<br />"
response.write "cbool(myvariable4) = " & cbool(myVariable4) & "<br /><br />"
%><hr><%
myVariable5 = 0
myVariable6 = "0"
myVariable7 = "false"
myVariable8 = false
if not myVariable5 then response.write "myvariable5 = false<br />"
if not myVariable6 then response.write "myvariable6 = false<br />"
if not myVariable7 then response.write "myvariable7 = false<br />"
if not myVariable8 then response.write "myvariable8 = false<br />"
%><br><%
if myVariable5 = false then response.write "myvariable5 = false<br />"
if myVariable6 = false then response.write "myvariable6 = false<br />"
if myVariable7 = false then response.write "myvariable7 = false<br />"
if myVariable8 = false then response.write "myvariable8 = false<br />"
%><br><%
response.write "cbool(myvariable5) = " & cbool(myVariable5) & "<br />"
response.write "cbool(myvariable6) = " & cbool(myVariable6) & "<br />"
response.write "cbool(myvariable7) = " & cbool(myVariable7) & "<br />"
response.write "cbool(myvariable8) = " & cbool(myVariable8) & "<br /><br />"
%>
You'll realise that the most effective way to determine whether any variable value equates to True or False is to use Cbool(). On the downside, if you feed cbool() a value it can't convert, say for example: "a" then it will choke. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 29 December 2004 : 01:57:12
|
D3mon,
Thanks!
Okay, now what happens to a SQL Query where it asks: "Where variable = True"?
How do I convert that? Regardless if it is "1" or "true", even though it is a 1 or 0 in the database, it will throw errors such as this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid column name 'True'.
/login/admin/statistics.asp, line 54 |
Edited by - Astralis on 29 December 2004 02:11:16 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 29 December 2004 : 02:41:32
|
I could think of a couple speculative possibilities...
strsql = "SELECT BLAH FROM BLAH WHERE VAR=" & True
strsql = "SELECT BLAH FROM BLAH WHERE VAR=" & 1
|
-Stim |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 29 December 2004 : 03:02:55
|
Okay...something odd. Perhaps it is not reading the new document. I changed the True to 1 and got that error presuming there was a problem. Then, I added some response.writes and they never showed up on the page. I then made sure I was FTPing to the correct folder and that I was editing the correct document. I was doing everything correct. Then, I noticed that the error was appearing on the same line ALTHOUGH the response.write should have increased it by at least one line. Thinking this was odd, I added a lot more commented text at the top of the page. I logged out, logged back in, refreshed, and got the same error on the same exact line number. Impossible, right? I also tested it using a different browser.
Any explanation? |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 29 December 2004 : 03:39:53
|
Yep it's a problem with the host, CrytalTech. Anything that I FTP is not actually getting updated even though the FTP shows that the documents are updated. I'm not sure how FTP connections work on the server end, though.
They said:
We are currently having an issue on some servers where asp and aspx pages are being cached. I have manually recycled your application pool which should refresh the data. Until we have a permanent fix, we can do this again if the problem reoccurs. |
Edited by - Astralis on 29 December 2004 03:58:59 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 29 December 2004 : 07:03:58
|
With SQL queries just use the default condition ( true ) like this:
say I had a bit column called 'specialoffer' and I wanted to select all the products from a table on special offer...
my SQL query might be:
SELECT ProductID, ProductName, ProductPrice FROM tblProducts WHERE specialoffer
SQL recognises that specialoffer is a bit field and reads my query as:
...WHERE specialoffer = 1 - as this is the default condition
If I wanted to select all the products NOT on special offer, I would query:
SELECT ProductID, ProductName, ProductPrice FROM tblProducts WHERE NOT specialoffer
SQL reads that as:
...WHERE specialoffer = 0
HTH |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 29 December 2004 07:04:58 |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 31 December 2004 : 03:56:58
|
D3mon,
Thanks for that! Great lesson!
Okay, to the inevitable checkbox problem. I've already run into it. I need to insert a checkbox value into a bit-type field. I'm getting Type Mismatch errors, of course. So, how do I do it?
In other words, I have an asp page with a form that contains a check box. The contents of this form are being submitted to a SQL Server 2000 database. The field in the database associated with the check box is of data type "bit". How do I convert the checkbox value to something acceptable to SQL? |
Edited by - Astralis on 31 December 2004 04:25:22 |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 31 December 2004 : 04:44:57
|
This is the work-around that I did on the post page:
if Request.form("newsletter")<>"" then
newsletter = Request.form("newsletter")
else
newsletter = 0
end if
|
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 31 December 2004 : 06:23:13
|
give the checkbox a value of "1" like: <input type="checkbox" name="blah" value="1" />
then on the next page, check for its value like:
myVariable = request.form("blah")
if myVariable <> 1 then myVariable = 0 |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 31 December 2004 10:19:15 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 31 December 2004 : 07:11:28
|
D3mon you'd still get a mismatch on that, it'd read it in as a string and your checking for an integer...
at least I've run into problems with this
fixed by throwing cint() around the request.form |
-Stim |
|
|
|
Topic |
|
Topic Locked
