[AccessD] Making SQL Server NewID() work with a dataset in.net

Charlotte Foust cfoust at infostatsystems.com
Wed Feb 23 17:02:26 CST 2005


And that's mostly true, but XML is primarily used to *exchange* data or
as an embedded store for lookup data that doesn't change or for
temporary tables.  I can't honestly imagine trying to build a backend in
XML. Yikes!  Furthermore, you can create a typed dataset on an xml file
and that includes not only indexes and constraints, but can even include
relationships to other tables/files, so ... "mostly true". 

Charlotte Foust


-----Original Message-----
From: dmcafee at pacbell.net [mailto:dmcafee at pacbell.net] 
Sent: Wednesday, February 23, 2005 2:38 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset
in.net


< back peddling >
I worded that wrong. I had just finished reading a chapter and was
wondering the same thing you stated (that is BE stuff, not FE). Here's a
quote:

"Another problem with ADO was that it was based on relational database
model (which, a few years ago, was the 'greatest new thing' that refused
to consider the ordered lists of data - a database had no such thing as
a first, second or last record and so on). But XML-based data systems
require hierarchical data that is, in two words, NOT RELATIONAL"

D



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Wednesday, February 23, 2005 1:11 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset
in.net


>>return to flat file-ism
What on earth are you talking about?  The relational aspects are
provided by the database, not by .Net, VB or even the Access UI.  It's
the database engine that provides that.

Charlotte Foust


-----Original Message-----
From: dmcafee at pacbell.net [mailto:dmcafee at pacbell.net]
Sent: Wednesday, February 23, 2005 11:20 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset
in.net


Exactly. I'm in the same boat as you and JC (IIRC). I'm now working with
.Net (C# & VB mainly).

The company I work for would like to move all mdb/ADPs to .NET some day.
I have started converting some of my sample databases as a learning
project. Some things a neat, others are not.

My boss asked if I could create a VB or C# program that would allow a
user to enter some information, connect to SQL and retrieve some more
data, then talk to some barcode label software so barcodes can be
printed out.

I created an ADP and designed my own UPC-A/EAN13 barcodes on the fly in
VBA and used the Free 3of 9 font and had a working copy in 3 days
(creating the dynamic barcaode was the hardest part). At our meeting to
see how long it would take me to complete the task, I already had a
working copy. They were amazed when they found out that it was create in
Access. One program, enter the info and it prints out the barcode, all
in one step.

I've always felt VB was great for creating DLLs or non DB use, which is
why I am probably having such a hard time with .Net and their return to
flat file-ism.

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Wednesday, February 23, 2005 11:03 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset
in.net


Hi D

So many choices. I know of very few database applications where speed
isn't a priority.

/gustav

>>> dmcafee at pacbell.net 23-02-2005 18:29:37 >>>
This is what I just read yesterday:

Moving from ADO to ADO.NET
If you've used ADO, you'll want to understand how the syntax of ADO.NET
differs. But before exploring the changes, be aware that you CAN you ADO
in a VB.NET program if you want. Instead of making ADO.NET itself
backward compatible with ADO, MS has chosen to make ASP.NET embrace ADO
for those who can't or won't make the move over to ADO.NET.

Rest assured that the entire ADO object model is still available. Now,
though, you also have available an additional set of classes particular
to the .NET framework, collectively referred to as ADO.NET

When you connect to a database using ASP.NET, you can use the native SQL
provider or the ADO.NET provider. If you are accessing a data source
such as Access, Excel, CSV or some other straightforward variety, you
must use the ADO.NET provider.

If your db is MS SQL Server, you can use the somewhat faster SQL
provider. The ADO.NET provider also works with SQL but is a bit slower.

So, as Charlotte mentioned, maybe using datasets for Access and do it
the old fashioned way for SQL. I'm also having a fun time in my
transition to .Net :(

D



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Wednesday, February 23, 2005 8:30 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset in
.net


Yes, it is.  The problem only occurs with a SQL Server backend.  An
Access backend doesn't experience the problem.  One of the nice things
about .Net is that you can create separate data providers to handle SQL
Server and Access, so the details are not dealt with by the UI
programmer, only by the data tier programmer.  Of course, if you happen
to be both ... <g>

Charlotte Foust


-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk]
Sent: Wednesday, February 23, 2005 1:09 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Making SQL Server NewID() work with a dataset in .net


Hi all using dotnet.

Snipped this from the ElementK Tips.
I have not used the datagrid from dotnet and have to ask if this really
is the way to handle this task? From an Access view it seems like moving
to the stone age.

Or would I just use another more clever grid?

/gustav


Making SQL Server primary key NEWID() default values work with a Visual
Basic .NET DataSet (Visual Basic .NET/SQL 2000)

Designing a SQL Server table to use the uniqueidentifier datatype for
its primary key column and then giving it a default with the NEWID()
function is a great way to manage your data; however, there can be a
slight snag if you use ADO.NET's DataSet in Visual Basic .NET with such
a table. In this scenario, adding records in a DataGrid in Visual Basic
.NET will cause an error. The primary key field doesn't allow NULL
values. We know that the key field will be defaulted on the SQL Server
side, but Visual Basic .NET enforces the NULL before the data is pushed
to SQL Server. The solution is easy, since we have access to the dataset
XML. First, we can delete the key from the dataset table by
right-clicking on the table and selecting the Delete Key. The field
isn't deleted, just the key indicator for the field. Now the field won't
require a unique value, but we still need to allow the value to be NULL.
This is done by adding minOccurs="0" to the <xs:element> code for this
field, as in the example below:

	<xs:element
		name="KeyFieldName"
		msdata:DataType="System.Guid, mscorlib,
version=1.0.3300.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089"
		type="xs:string"
		minOccurs="0" />

Now records can be added to the grid without raising any errors, and SQL
Server will use the NEWID() default for primary key column values once
the table is updated.

--
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



More information about the AccessD mailing list