Charlotte Foust
cfoust at infostatsystems.com
Fri Aug 1 17:39:01 CDT 2008
I work in a controlled production environment, Dan. I can't just create
a new SQL Server table. Our tables (through Management Studio) won't
let me enter a zero or minus one, no matter how sweetly I talk to them.
We may have set up triggers, but I haven't located them yet, and I don't
have time to search. Linking in Access is irrelevant. Access
"translates" the values in a linked table. We're working in VB.Net, so
we have to deal with the native peculiarities of ADO.Net, SQL Server and
Jet. There really isn't any such thing as a linked table in .Net.
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, August 01, 2008 3:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] upsizing
When I was entering data directly into the table, I had the table open
in SQL Server Management Studio.
Then, in Access, which was linked to that same table in SQL Server, I
could detect and change the value using True and False via a recordset.
I really did do this! Try it yourself - create a new SQL Server table,
create a bit type field, and see what you can type into it. Then in
Access link to that table, and see what values you get out of it.
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Friday, August 01, 2008 4:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] upsizing
No, Dan. Access handles -1 and 0 just fine, but not SQL Server. As far
as entering data directly into the table, how are you accessing the
table? I'm looking at it in SQL Server, which is quite different from
looking at a linked table in Access. If you're looking at a linked
table, we aren't in the same conversation. When you link a table to
Access, some stuff gets translated into "Access" even if it starts out
as Excel, SQL Server or text. It has nothing to do with allowing nulls
on the field. Furthermore, although we have defaults for bit fields,
that only applies to new data, so legacy data, where we might not have
captured the value at all, could be null. In that case, Null is a
legitimate piece of data since we can't guess at what the value should
be.
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, August 01, 2008 11:16 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] upsizing
Hi Charlotte,
I entered data directly into a SQL Server 2005 table bit field. I was
just testing to see what information it would even take. The only data
that field would take was a -1 or a 0. I turned off Allow Nulls.
Then, using a recordset in Access 2003, I was able to read the -1 as
True and the 0 as False.
Given that, your code will work. But you might be able to test for True
and False instead, IF you disallow nulls for the bit fields that are
expected to be either True or False.
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Friday, August 01, 2008 10:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] upsizing
No, we use bits. Nobody enters data directly into a table, so I'm not
sure what you're doing or which database you're talking about. If
you're working with 2007, we aren't even in the same conversation. We
stayed with 2002 and use a 2000 data format. We interact with either BE
using SQL and/or dataadapters. ADO has problems with evaluating True
and False if those values are embedded in the SQL, so we have to use
numbers for that or else use OleDbParameters to pass in True and allow
ADO to figure out which value to use. Here's a sample of what I mean.
We're building the SQL string in code an then executing it. Notice the
"CostCodes.CarryForward <> 0" bit. CarryForward is a bit field.
ExecuteNonQuery is a wrapper for
System.Data.OleDb.OleDbCommand.ExecuteNonQuery.
Public Sub CarryForwardDailyCosts(ByVal jobID As String, ByVal
reportNo As Short) _
Implements
IDailyReportData.CarryForwardDailyCosts
Dim strSQL As New Text.StringBuilder
strSQL.Append("INSERT INTO DailyCosts (JobID, ReportNo, IDNo,
AccountCode, DailyCost, AFENo, Vendor, TransferFlag, Notes)")
strSQL.AppendFormat(" SELECT DailyCosts.JobID, {0} As ReportNo,
DailyCosts.IDNo, DailyCosts.AccountCode, DailyCosts.DailyCost," ,
reportNo)
strSQL.Append(" DailyCosts.AFENo, DailyCosts.Vendor, 1 as
TransferFlag,")
'strSQL.Append(strNote)
strSQL.Append(" DailyCosts.Notes")
strSQL.Append(" FROM DailyCosts INNER JOIN CostCodes ON
DailyCosts.AccountCode = CostCodes.AccountCode")
strSQL.AppendFormat(" WHERE DailyCosts.JobID = '{0}' AND
DailyCosts.ReportNo = {1} AND CostCodes.CarryForward <> 0", _
jobID, reportNo - 1S)
OleDbHelper.ExecuteNonQuery(New OleDbCommand(strSQL.ToString))
End Sub
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, August 01, 2008 5:25 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] upsizing
Well,
That wasn't what I saw with my testing. In fact, with a bit field, I
couldn't even enter a 1 directly in the table.
Are you talking about using a smallint field for True or False?
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Thursday, July 31, 2008 7:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] upsizing
In Access, that's true. In SQL Server, 1 is true and 0 is false. If
you throw a -1 at it, it spits up all over you.
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Thursday, July 31, 2008 3:03 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] upsizing
With the BE I just upsized, all the Yes/No fields became bits. I went
through all those fields and set Allow Nulls to False.
Now the only to values that can be entered into the bit fields are -1
and 0.
I set up a recordset to change iteratively change the value from True to
False then True then False, etc, and everything worked just as if I had
been using an Access table.
I went back and tested one field by setting Allow Nulls to True. My
code then displayed the value as False if I set the field to Null in
code.
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, July 31, 2008 4:37 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] upsizing
Hi Charlotte
One way to get around this - if you really need to handle the value as a
numeric - is to use Abs():
If Abs([YourYesNoField]) = 1 Then
' something
End If
Another is to use DataTableAdapters and/or DAL to isolate you from this
low-level stuff. I couldn't imagine anything else than this is what you
do, but your reply indicates not. Is that really so?
/gustav
>>> cfoust at infostatsystems.com 31-07-2008 17:25 >>>
We have to be careful in our .Net code to always test for 0 or <> 0
because we run against both Access and SQL Server. Trying to test for
True crashes on one or the other.
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Wednesday, July 30, 2008 4:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] upsizing
oooooooooooooo.... I like that. :)
Susan H.
> Possiby because Access uses -1 for True and you can't represent that
> with a bit?
>
> What value does SSMA store insert a True record (1 or -1)?
>
>
> On 30 Jul 2008 at 18:45, Susan Harkins wrote:
>
>> SSMA converts Access Yes/No fields to tinyint, instead of bit -- does
>> anyone have an explanation for that?
>>
>> My guess is it's because SQL Server's bit didn't support NULL's until
>> 7.0, but it seems like the wizard would keep up with something like
>> that. I really can't recall if Access' Yes/No always supported NULLs
>> or not.
>>
>> Susan H.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com