Data in table through dataset or bindingsource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a gui build from a dataset. The gui consists of an ID field (identy
and key field in the database) and a name field.
I have a textfile with only the names in it. I want to fill the database
with the names from the textfile. The ID's have to be generated by the
database.
My first try was:

//The textfile is read into an arraylist al...
if (al.Count > 0)
{
filmsBindingSource.AllowNew = true;
foreach (string[] s in al)
{
filmsBindingSource.AddNew();
nameTextBox.Text = s[0].Trim();
}
}

After that the gui is updated and shows the inserted records. A push on the
save button in the bindingsourcenavigator should do the trick. But than it
complains about the identity field in the database: exeption, 'column FilmID
is constrained to be unique. Value '5' already exists'. The database is
however empty (I experimented with the database, but deleted all records).

Next try was through inserting records in the dataset:

//The textfile is read into an arraylist al...
if (al.Count > 0)
{
foreach (string[] s in al)
{
newFilmRow = filmsDBDataSet.Tables["Films"].NewRow();
newFilmRow["Titel"] = s[0].Trim();
filmsDBDataSet.Tables["Films"].Rows.Add(newFilmRow);
}
}
After this I see the records in de gui. A save from the
bindingsourcenavigator leads to the same exeption.

Questions are:
- how do I fill the dataset / bindingsource?
- how do I handle the identity field? After the fills shown above, the
identity field in the gui starts with 0 but this should be 1.
- What is the relation between the gui identity field and the database
identy field?
- What method should I use to fill the records?

Thnaks in advance,
Eric Algera (NL)
 
Eric Algera said:
Next try was through inserting records in the dataset:
//The textfile is read into an arraylist al...
if (al.Count > 0)
{
foreach (string[] s in al)
{
newFilmRow = filmsDBDataSet.Tables["Films"].NewRow();
newFilmRow["Titel"] = s[0].Trim();
filmsDBDataSet.Tables["Films"].Rows.Add(newFilmRow);
}
}
Click to expand...

Eric,
Now that you have the DataSet / DataTable filled in from text file,
you just need a SqlConnection / SqlDataAdapter o insert the rows
into a table in your database. This assumes that the columns in your
DataTable match the columns in your database table. Plus your table
should have a primary key.

When adding rows to the DataTable, set the primary key value to
go negative when adding the DataRow. e.g. 1st row = -1, 2nd row
= -2. That way the values will not clash when the database generates
the new key value.

Next create a SqlDataAdapter with only the InsertCommand set.
Then call the SqlDataAdapter's Update method. Since the
DataRow were added above, they will have a RowState of
inserted. And hence the data be inserted into your database
table when Update is called.
http://msdn.microsoft.com/library/d...emdatacommondbdataadapterclassupdatetopic.asp

To get the newly generated identity values, check out the following
http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp

HTH.
 
"Carl Prothman" schreef:
Eric Algera said:
Next try was through inserting records in the dataset:
//The textfile is read into an arraylist al...
if (al.Count > 0)
{
foreach (string[] s in al)
{
newFilmRow = filmsDBDataSet.Tables["Films"].NewRow();
newFilmRow["Titel"] = s[0].Trim();
filmsDBDataSet.Tables["Films"].Rows.Add(newFilmRow);
}
}
Click to expand...

Eric,
Now that you have the DataSet / DataTable filled in from text file,
you just need a SqlConnection / SqlDataAdapter o insert the rows
into a table in your database. This assumes that the columns in your
DataTable match the columns in your database table. Plus your table
should have a primary key.

When adding rows to the DataTable, set the primary key value to
go negative when adding the DataRow. e.g. 1st row = -1, 2nd row
= -2. That way the values will not clash when the database generates
the new key value.

Next create a SqlDataAdapter with only the InsertCommand set.
Then call the SqlDataAdapter's Update method. Since the
DataRow were added above, they will have a RowState of
inserted. And hence the data be inserted into your database
table when Update is called.
http://msdn.microsoft.com/library/d...emdatacommondbdataadapterclassupdatetopic.asp

To get the newly generated identity values, check out the following
http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp

HTH.
Click to expand...
Carl, thanks for your answer. I'm not sure I understand you though.
I pulled the dataset table to the form. That way I have a tableadapter,
bindingsource and the dataset. All fields on the form are bound to the
dataset through the bindingsource. If I click the 'save' button in de
bindingsourcenavigator the following code is being executed:
this.Validate();
this.filmsBindingSource.EndEdit();
this.filmsTableAdapter.Update(this.filmsDBDataSet.Films);

So the tableadapter is updated and it has an Insert command.
On the form are the fields: filmID and filmName. I only read the filmName
from a textfile. The filmID gets an automatic number when the names are read.
With the bindingsourcenavigator I can move through the dataset records and
see the filmID (starting with 0 instead of 1 like I said in the
tabledefinition) and the filmName. All looks well. After I push the 'save'
button I get the exeption.
The generated filmID's are unique but generated in the dataset. If I update
the table (through tabladapter.update) the table gets filled. What fills the
ID field in the table? Do these values come from the dataset? Or is the table
generating these values again? Do I have to generate these ID's in the
dataset?

Thanks, Eric.
 
Eric,
I have a gui build from a dataset. The gui consists of an ID field (identy
and key field in the database) and a name field.
I have a textfile with only the names in it. I want to fill the database
with the names from the textfile. The ID's have to be generated by the
database.
Click to expand...

If it is a new database, than you know already that the last sentence is the
worst start in AdoNet.

The best start is to use a GUID (Unique Identifier)

Just as addition,

Cor
 
Eric Algera said:
"Carl Prothman" schreef:
The generated filmID's are unique but generated in the dataset.
If I update the table (through tabladapter.update) the table gets filled.

Good.

What fills the ID field in the table? Do these values come from the
dataset?
Click to expand...

Sql Server generates the new PK ID value. You must have the table's primary
key column set as an Identity column in Sql Server, with numbers starting
from
1 and incrementing by 1.
Or is the table generating these values again? Do I have to generate these ID's in the dataset?
Click to expand...

In order for you to re-populate the DataTable with the new Indentity values
generated by Sql Server during the INSERT, concatenate a SELECT
command right after your INSERT command (seperated with a ";"). The
Select command should have a where clause such as:
SELECT filmID, filmName FROM Films WHERE filmID = IDENT_CURRENT('Films')

Then after the Update method is called, the DataSet should be updated with
latest values.

HTH

Thanks,
Carl Prothman
 
Carl Prothman said:
Sql Server generates the new PK ID value. You must have the table's primary
key column set as an Identity column in Sql Server, with numbers starting
from
1 and incrementing by 1.


In order for you to re-populate the DataTable with the new Indentity values
generated by Sql Server during the INSERT, concatenate a SELECT
command right after your INSERT command (seperated with a ";"). The
Select command should have a where clause such as:
SELECT filmID, filmName FROM Films WHERE filmID = IDENT_CURRENT('Films')

Then after the Update method is called, the DataSet should be updated with
latest values.

HTH

Thanks,
Carl Prothman
Click to expand...

Carl,

thanks for your time and explanation. It was very helpfull.

Eric.
 
Eric,
Now that you have the DataSet / DataTable filled in from text file,
you just need a SqlConnection / SqlDataAdapter o insert the rows
into a table in your database. This assumes that the columns in your
DataTable match the columns in your database table. Plus your table
should have a primary key.

When adding rows to the DataTable, set the primary key value to
go negative when adding the DataRow. e.g. 1st row = -1, 2nd row
= -2. That way the values will not clash when the database generates
the new key value.
Click to expand...

Sorry Eric for hijacking your thread but I have a question for Carl.

Carl, the solution you provided will work very nice in case when DataTable
is created from the scratch.
What about the case when DataTable is populated from database:
Dim cn As New SqlConnection("server=localhost;integrated
security=true;database=northwind")
Dim daCust As New SqlDataAdapter("Select * From Employees", cn)
ds = New DataSet()
daCust.Fill(ds, "Employees")
and EmployeeID column is Identity column with Identity Seed = 1 and Identity
Increment = 1. Is there a way to
modify the DataTable Identity column after data adapter fills dataset? What
is the best practice in situations
like this?

Thanks,
John
 
jaryry said:
Carl, the solution you provided will work very nice in case when DataTable
is created from the scratch.

What about the case when DataTable is populated from database:
Dim cn As New SqlConnection("server=localhost;integrated
security=true;database=northwind")
Dim daCust As New SqlDataAdapter("Select * From Employees", cn)
ds = New DataSet()
daCust.Fill(ds, "Employees")

with EmployeeID column is Identity column with Identity Seed = 1 and
Identity Increment = 1.
Is there a way to modify the DataTable Identity column after data adapter
fills dataset?
Click to expand...

Why do you want to change the EmployeeID, which was generated by
Sql Server? How would you identity the record back in the database?

Remember the data in the DataTable is a snapshot of what's in the
backend database (using above code).

What is the best practice in situations like this?
Click to expand...

The best practice is not to change the generated identity column once it's
been generated by the backend database.

You may know this already, but it's worth repeating...
If you want to update the employee row(s) in the backend database,
you'll need to code the SqlDataAdapter's InsertCommand,
UpdateCommand, and DeleteCommand. Using the above
SqlDataAdapter constructor syntax, only the SelectCommand is
generated. Then once your done making changes to the employee(s)
rows in the DataTable, you would then call the SqlDataAdapter's Update
method. The Update method uses each RowState to determine which
InsertCommand, UpdateCommand, or DeleteCommand gets called.

Of course if you want to set the identity value when you insert a row:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

Or if you need to reseed identities in SQL Server IDENTITY columns
http://msdn2.microsoft.com/en-US/library/ms176057.aspx
 
Thank you very much Carl.

I run few tests on disconnected DataSets and all is clear now :)
Sorry for asking stupid questions, I should run these tests first
instead of posting questions on this newsgroup.

Thanks again,
John
 
Back
Top