add item

  • Thread starter Thread starter John
  • Start date Start date
J

John

I know this shouldn't be that difficult, but, could someone give me the best
practice code to add an item to a database in VB when you have a dataset
containing only a subset of the full table and the item has an
autoincremented ID? It seems adding the item to the dataset and then
updating is not the correct way since it is not the complete table.
 
For better help, you may want to describe the exact behavior/error you
have...

My guess would be that the insert just handles the field you have in your
dataset. Other fields will have their default value. If they have no default
value and are declared NOT NULL, the DB will reject the insertion as values
are not satisfying the DB definition.

You may want to check that all non nullable fields that are not in your
dataset have a default value (else please always include some description of
the behavior/error you have so that helpers don't have to guess).
 
Sorry, my understanding was that you wanted to solve some kind of issue as
it seems you would like to use a different approach.

I see no reason to do things differently. The only difference is that it
would fail if not null columns don't have a default value defined server
side but apart that just do the same than if you had all the columns...
 
Hi John,

Based on my understanding, you have a dataset in your project, which
contains only a part of the fields of the corresponding database table. The
database table has an autoincremented ID field. You want the best practice
to add a record in the database table. If I'm off base, please feel free to
correct me.

You have two options to add a record to a datababase table. One option is
to use TableAdapter that VS IDE generates when you drag a database table
from Server Explorer onto the dataset designer. The other option is to use
a SqlCommand object.

Option 1:
The following is the steps of using TableAdapter.

1. Add a dataset into your project and drag the database table from the
Server Explorer onto the dataset in the designer, which will create a
datatable with all fields of the database table and a corresponding data
adapter.

2. Delete some of the fields you don't want from the datatable and the
commands (SelectCommand, UpdateCommand and InsertCommand and DeleteCommand)
in the corresponding data adapter will update automatically.

3. Add an instance of the dataset on the form. Add a new row into the
datatable and call the Udpate method of TableAdapter to save the new row
into the database. The following is the sample code.

DataRow row = this.dataSet11.TableName.NewRow();
// row[0] is the autoincremented column, we needn't set the value for this
column
row[1] = 11;
row[2] = 22;
row[3] = 33;
this.dataSet11.TableName.Rows.Add(row);

DataSet1TableAdapters.TableNameTableAdapter tableAdapter = new
DataSet1TableAdapters.TableNameTableAdapter();
// the code below saves the new row into the database
tableAdapter.Update(this.dataSet11.TableName);

// accept changes in the datatable after the new row is saved into the
database
this.dataSet11.TableName.AcceptChanges();

Option 2:

The following is the sample code of using SqlCommand to save a new row into
the database.

DataRow row = this.dataSet11.TableName.NewRow();
row[1] = 11;
row[2] = 22;
row[3] = 33;
this.dataSet11.TableName.Rows.Add(row);

string cmdText = "Insert into AutoInscreTable(Column1,Column2,Column3)
values("
+ row[1].ToString() + "," + row[2].ToString() + "," +
row[3].ToString() + ")";
SqlConnection con = new SqlConnection("Data Source=database host
name;Initial Catalog=DataBaseName;User ID = username; Password = password");
SqlCommand cmd = new SqlCommand(cmdText,con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

You could choose one from the above two options as you like. In my opinion,
I prefer the option1which is more convenient and needn't many lines of code.

Note that no matter which options you choose, you should ensure that those
fields that aren't included in the datatable and don't allow null value
have default values, as Patrice has suggested, otherwise, error will occur
when you add a new row into the database.

Hope this helps.
If you have anything unclear, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top