DataTable and autoincremental Database Fields

Hi,

I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:

Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF.

This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:

Column 'CarID' does not allow nulls.

Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.

Thanks a lot have a nice weekend.

Regards Johannes

Hi,

i figured out something new. Regarding the line in which the error occurs it happens when I am trying to add a new row into the Datatable.

That's my code:

1[System.ComponentModel.DataObjectMethodAttribute2 (System.ComponentModel.DataObjectMethodType.Insert,true)]3public bool InsertCar(int CarType,string CarNumberPlate,string CarMileage,4string CarServiceRange,bool CarSummerTires,bool CarWinterTires,bool CarHitch,5int CarCarageID, Guid CarUserID, DateTime CarPurchased,bool CarSold)6 {7 Fleet.CarsDataTable cars =new Fleet.CarsDataTable();8 Fleet.CarsRow car = cars.NewCarsRow();91011 car.CarType = CarType;12 car.CarNumberPlate = CarNumberPlate;13 car.CarMileage = CarMileage;14 car.CarServiceRange = CarServiceRange;15 car.CarSummerTires = CarSummerTires;16 car.CarWinterTires = CarWinterTires;17 car.CarHitch = CarHitch;18 car.CarGarageID = CarCarageID;19 car.CarUserID = CarUserID;20 car.CarPurchased = CarPurchased;21 car.CarSold = CarSold;2223 cars.AddCarsRow(car);24int rowsAffected = Adapter.Update(cars);2526return rowsAffected == 1;27 }

The error occurs in line 23. Depeding on the settings in my Fleed.xsd file for CarID, as mentioned above the error changes.

Okay tried something else and fixed it. I reconfigured my TableAdapter and activatedRefresh the data table.No it works perfectly. Ig anyone could explain that to me I would be happy.

Thanks a lot.

Regards Johannes

DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

?

For applications that are put in a folder on the user's computer, the database folder uses the application folder.

?

For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

? For applications that are put in a folder on the user's computer, the database folder uses the application folder. ? For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

sql

dataset problem

hi,

i am using dataset for passing value to crystal report.
when the stored procedure contains 2 tables then how to create the dataset1.xsd for two table.
query with join works fine in QA.
i tried by giving two tables in dataset schema but how to give two tables with selected fields as per the query.
which table i should mention in fill method.

please tell me a procedure how to do this.

i tried an alternative method also.
by creating dataset at runtime using adapter.
but without filteration as per query all data appears in the report.

thanksafter setting dataset using adapter, aand setting it to crystal use record selection formula.|||iam using crystal report.net in vb.net
can you send a sample code for this.
your help will be appreciated.|||Hi u can code something like this.

Dim srcCr As Object
Dim rptDoc As New ReportDocument

srcCr = rptDoc
srcCr.SetDataSource(dsObj) --dsobj is ur dataset
rptDoc.Load("\reports\abc.rpt")

srcCr.RecordSelectionFormula = "{command.AccID}=124"

hope it helps you

DataSet .xsd use / Re-use concepts?

Hi all,

I've asked this question on another forum on the 2nd October 2006 and has gone unanswered to date.

I'd like to discuss SQL Express DataSet controls use / re-use concepts.

As I have been so busy sorting out the ins-n-outs and the dos-n-dont's of database connections n usage I haven't had a chance to focus on this issue.

Let me exagerate to make my point.

Lets say I have 10 forms.
Each has the standard DataSet, BindingSource, TableAdapter, and BindingNavigator.
Each form has the exact same general (broad scope: select * from myTable, myTable2 etc) query.

My solutions explorer has 1 Dataset .xsd for each forms query:
myDataset01.xsd
myDataset02.xsd
myDataset03.xsd
....
myDataset10.xsd

Is this how I should handle the datasets?
Keeping all dataset/querys seperate. One for each form. And possible multiples for each form?
(In case I want to adjust the number of returned fields on a specific form.)

Or is there a better way (not thru code) to have each form point to the same dataset controls?
i.e. have form2 thru form10 point to the datacontrols on form1? (doesn't sound right now that I've written it.)

Or is there a better way (not thru code) to have each form point to the same Dataset .xsd?
And should I?
i.e. reuse the one Dataset .xsd.

At the moment I'm thinking its all just text any way.
And each dataset has to be reconnected and data retrieved from the database anyway.

So does it really matter?

As I recall, I was having so many crashes when setting up the dataset .xsd that I found it was safer to have just one dataset per query.

I've ordered VS 2005 Pro and hope its more stable.

--

I've also found that if I update the SQL Express Table thru Management Studio
(and I believe thru the database explorer in the vb.net IDE) that the .xsd file gets lost.

i.e. I have to go thru and duplicate the changes.

Is there an easier way?

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

hmmm, I'm wondering if its the way I've written this query.

Or could it be I'm so off the mark that it would take too long to answer?

Can anyone out there please confirm that they:

1) Understand this query
2) Don't know the answer
3) Would also like to know the answer

|||

I've just had a quick look at another developers DataSet.

There is only one dataset.

It contains all the Tables in the database.

In the dataset designer there are additional queries at the bottom of each table.

I wonder how you access these additional queries thru code?

|||

Jeez! LOL! Now I'm rolling...

Thru the dataset designer you can right click on the table and select "Add new querie"
The wizard will step you thru then ask you to name your query and give you a starting value of "Fillby"
You need to add text to FillBy like FillByCity

Dim cityValue As String = "Seattle"
CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, cityValue)

http://msdn2.microsoft.com/en-us/library/kda44dwy(VS.80).aspx