|
In SQL Server use
SELECT @OrderId=SCOPE_IDENTITY()
Instead
|
|
|
|
|
Hi,
Nice article but how about inserting relational data from an xml file?
|
|
|
|
|
Great code example and well documented.
Thank you very much for taking the time to illustrate both Access 2000/2002 and SQL Server examples.
It was exactly what I was looking for.
|
|
|
|
|
string strSQL = "SELECT USERID FROM TBUSERS WHERE USERNAME='" + strReportTo + "'";
OdbcCommand myCommand = new OdbcCommand(strSQL, myConnection); myConnection.Close();
I need to get the values of USERID and put it in a string.
Please help
|
|
|
|
|
I have to import a table with telefone numbers from database a into database b. Ofcourse I don't need to import the existing ones. Therefore I have to use a sql insert witch compares the data of database a with database b.
It's no problem at all when you use twot tables in obe database. You just use the command and the executenonquery.
But how do you implement this on a dataset.
Who can help me?
|
|
|
|
|
thanks! please help me:how to search in dataset and dataAdaper.
|
|
|
|
|
|
I have a similar setup in my own project as described in this article. I have a foreign key relationship between my two tables. I am getting the following error message:
Unhandled Exception: System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOR
EIGN KEY constraint 'FK_ServiceAmounts_Services'. The conflict occurred in database 'finApp', table
'Services', column 'ID'.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
Thanks in advance for the help!
|
|
|
|
|
Try to disable the constraints in the dataset by setting the EnableConstraints option to false, before you send the changes to the database. After the update, you can re-enable it using the same property.
Mauricio Ritter - Brazil
Sonorking now: 100.13560 MRitter
English is not my native language so, if you find any spelling erros in my posts, please let me know.
|
|
|
|
|
I know nothing someone place help.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace Project_Two
{
///
/// Summary description for Form1.
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Label FIRSTNAMELabel;
private System.Windows.Forms.Label LastNameLabel;
private System.Windows.Forms.Label ADDRESSLABEL;
private System.Windows.Forms.Label CITYLABEL;
private System.Windows.Forms.Label STATELABEL;
private System.Windows.Forms.Label ZIPLABEL;
private System.Windows.Forms.Label EMAILLABEL;
private System.Windows.Forms.Label HMPHONELABEL;
private System.Windows.Forms.Label CELLPHONELabel;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private Project_Two.DataSet1 dataSet11;
private System.Windows.Forms.Button FORWARDButton;
private System.Windows.Forms.Button BackButton;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button ADDButton;
private System.Windows.Forms.Button DeleteButton;
private System.Windows.Forms.Button EditButton;
private System.Windows.Forms.Button SaveButton;
private System.Windows.Forms.Button LastButton;
private System.Windows.Forms.Button FirstButton;
private System.Windows.Forms.Button SearchButton;
private System.Windows.Forms.TextBox AddressText;
private System.Windows.Forms.TextBox CityText;
private System.Windows.Forms.TextBox StateText;
private System.Windows.Forms.TextBox ZipcodeText;
private System.Windows.Forms.TextBox EmailText;
private System.Windows.Forms.TextBox HomeNumberText;
private System.Windows.Forms.TextBox CellphoneText;
private System.Windows.Forms.TextBox FirstNameTxt;
private System.Windows.Forms.TextBox LastNameText;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.FIRSTNAMELabel = new System.Windows.Forms.Label();
this.LastNameLabel = new System.Windows.Forms.Label();
this.ADDRESSLABEL = new System.Windows.Forms.Label();
this.AddressText = new System.Windows.Forms.TextBox();
this.dataSet11 = new Project_Two.DataSet1();
this.CITYLABEL = new System.Windows.Forms.Label();
this.CityText = new System.Windows.Forms.TextBox();
this.STATELABEL = new System.Windows.Forms.Label();
this.StateText = new System.Windows.Forms.TextBox();
this.ZIPLABEL = new System.Windows.Forms.Label();
this.ZipcodeText = new System.Windows.Forms.TextBox();
this.EMAILLABEL = new System.Windows.Forms.Label();
this.EmailText = new System.Windows.Forms.TextBox();
this.HMPHONELABEL = new System.Windows.Forms.Label();
this.HomeNumberText = new System.Windows.Forms.TextBox();
this.CELLPHONELabel = new System.Windows.Forms.Label();
this.CellphoneText = new System.Windows.Forms.TextBox();
this.FORWARDButton = new System.Windows.Forms.Button();
this.ADDButton = new System.Windows.Forms.Button();
this.BackButton = new System.Windows.Forms.Button();
this.EditButton = new System.Windows.Forms.Button();
this.DeleteButton = new System.Windows.Forms.Button();
this.FirstNameTxt = new System.Windows.Forms.TextBox();
this.LastNameText = new System.Windows.Forms.TextBox();
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.label1 = new System.Windows.Forms.Label();
this.FirstButton = new System.Windows.Forms.Button();
this.LastButton = new System.Windows.Forms.Button();
this.SaveButton = new System.Windows.Forms.Button();
this.SearchButton = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
this.SuspendLayout();
//
// FIRSTNAMELabel
//
this.FIRSTNAMELabel.Location = new System.Drawing.Point(40, 88);
this.FIRSTNAMELabel.Name = "FIRSTNAMELabel";
this.FIRSTNAMELabel.Size = new System.Drawing.Size(96, 24);
this.FIRSTNAMELabel.TabIndex = 1;
this.FIRSTNAMELabel.Text = "FIRST NAME";
//
// LastNameLabel
//
this.LastNameLabel.Location = new System.Drawing.Point(40, 128);
this.LastNameLabel.Name = "LastNameLabel";
this.LastNameLabel.Size = new System.Drawing.Size(80, 24);
this.LastNameLabel.TabIndex = 2;
this.LastNameLabel.Text = "LAST NAME";
//
// ADDRESSLABEL
//
this.ADDRESSLABEL.Location = new System.Drawing.Point(40, 160);
this.ADDRESSLABEL.Name = "ADDRESSLABEL";
this.ADDRESSLABEL.Size = new System.Drawing.Size(72, 24);
this.ADDRESSLABEL.TabIndex = 4;
this.ADDRESSLABEL.Text = "ADDRESS";
//
// AddressText
//
this.AddressText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.Address"));
this.AddressText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.AddressText.Location = new System.Drawing.Point(184, 160);
this.AddressText.Name = "AddressText";
this.AddressText.Size = new System.Drawing.Size(160, 26);
this.AddressText.TabIndex = 5;
this.AddressText.Text = "";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
//
// CITYLABEL
//
this.CITYLABEL.Location = new System.Drawing.Point(40, 192);
this.CITYLABEL.Name = "CITYLABEL";
this.CITYLABEL.Size = new System.Drawing.Size(64, 24);
this.CITYLABEL.TabIndex = 6;
this.CITYLABEL.Text = "CITY";
//
// CityText
//
this.CityText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.City"));
this.CityText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.CityText.Location = new System.Drawing.Point(184, 192);
this.CityText.Name = "CityText";
this.CityText.Size = new System.Drawing.Size(160, 26);
this.CityText.TabIndex = 7;
this.CityText.Text = "";
//
// STATELABEL
//
this.STATELABEL.Location = new System.Drawing.Point(40, 232);
this.STATELABEL.Name = "STATELABEL";
this.STATELABEL.Size = new System.Drawing.Size(64, 24);
this.STATELABEL.TabIndex = 8;
this.STATELABEL.Text = "STATE";
//
// StateText
//
this.StateText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.State"));
this.StateText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.StateText.Location = new System.Drawing.Point(184, 224);
this.StateText.Name = "StateText";
this.StateText.Size = new System.Drawing.Size(56, 26);
this.StateText.TabIndex = 9;
this.StateText.Text = "";
//
// ZIPLABEL
//
this.ZIPLABEL.Location = new System.Drawing.Point(32, 272);
this.ZIPLABEL.Name = "ZIPLABEL";
this.ZIPLABEL.Size = new System.Drawing.Size(64, 24);
this.ZIPLABEL.TabIndex = 10;
this.ZIPLABEL.Text = "ZIP CODE";
//
// ZipcodeText
//
this.ZipcodeText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.Zip"));
this.ZipcodeText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.ZipcodeText.Location = new System.Drawing.Point(184, 264);
this.ZipcodeText.Name = "ZipcodeText";
this.ZipcodeText.Size = new System.Drawing.Size(104, 26);
this.ZipcodeText.TabIndex = 11;
this.ZipcodeText.Text = "";
//
// EMAILLABEL
//
this.EMAILLABEL.Location = new System.Drawing.Point(32, 304);
this.EMAILLABEL.Name = "EMAILLABEL";
this.EMAILLABEL.Size = new System.Drawing.Size(72, 24);
this.EMAILLABEL.TabIndex = 12;
this.EMAILLABEL.Text = "E-MAIL ID#";
//
// EmailText
//
this.EmailText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.E-Mail ID#"));
this.EmailText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.EmailText.Location = new System.Drawing.Point(184, 304);
this.EmailText.Name = "EmailText";
this.EmailText.Size = new System.Drawing.Size(160, 26);
this.EmailText.TabIndex = 13;
this.EmailText.Text = "";
//
// HMPHONELABEL
//
this.HMPHONELABEL.Location = new System.Drawing.Point(32, 344);
this.HMPHONELABEL.Name = "HMPHONELABEL";
this.HMPHONELABEL.Size = new System.Drawing.Size(56, 24);
this.HMPHONELABEL.TabIndex = 14;
this.HMPHONELABEL.Text = "HOME#";
//
// HomeNumberText
//
this.HomeNumberText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.Home Phone #"));
this.HomeNumberText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.HomeNumberText.Location = new System.Drawing.Point(184, 336);
this.HomeNumberText.Name = "HomeNumberText";
this.HomeNumberText.Size = new System.Drawing.Size(160, 26);
this.HomeNumberText.TabIndex = 15;
this.HomeNumberText.Text = "";
//
// CELLPHONELabel
//
this.CELLPHONELabel.Location = new System.Drawing.Point(32, 376);
this.CELLPHONELabel.Name = "CELLPHONELabel";
this.CELLPHONELabel.Size = new System.Drawing.Size(88, 24);
this.CELLPHONELabel.TabIndex = 16;
this.CELLPHONELabel.Text = "CELLPHONE#";
//
// CellphoneText
//
this.CellphoneText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.Cell Phone #"));
this.CellphoneText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.CellphoneText.Location = new System.Drawing.Point(184, 368);
this.CellphoneText.Name = "CellphoneText";
this.CellphoneText.Size = new System.Drawing.Size(160, 26);
this.CellphoneText.TabIndex = 17;
this.CellphoneText.Text = "";
//
// FORWARDButton
//
this.FORWARDButton.Location = new System.Drawing.Point(240, 424);
this.FORWARDButton.Name = "FORWARDButton";
this.FORWARDButton.Size = new System.Drawing.Size(72, 40);
this.FORWARDButton.TabIndex = 20;
this.FORWARDButton.Text = "&FORWARD";
this.FORWARDButton.Click += new System.EventHandler(this.FORWARDButton_Click);
//
// ADDButton
//
this.ADDButton.Location = new System.Drawing.Point(488, 88);
this.ADDButton.Name = "ADDButton";
this.ADDButton.Size = new System.Drawing.Size(112, 48);
this.ADDButton.TabIndex = 21;
this.ADDButton.Text = "&ADD NEW RECORD";
this.ADDButton.Click += new System.EventHandler(this.ADDButton_Click);
//
// BackButton
//
this.BackButton.Location = new System.Drawing.Point(152, 424);
this.BackButton.Name = "BackButton";
this.BackButton.Size = new System.Drawing.Size(72, 40);
this.BackButton.TabIndex = 22;
this.BackButton.Text = "&GO BACK";
this.BackButton.Click += new System.EventHandler(this.BackButton_Click);
//
// EditButton
//
this.EditButton.Location = new System.Drawing.Point(480, 144);
this.EditButton.Name = "EditButton";
this.EditButton.Size = new System.Drawing.Size(120, 56);
this.EditButton.TabIndex = 23;
this.EditButton.Text = "&EDIT RECORD";
this.EditButton.Click += new System.EventHandler(this.Edit_Click);
//
// DeleteButton
//
this.DeleteButton.Location = new System.Drawing.Point(480, 208);
this.DeleteButton.Name = "DeleteButton";
this.DeleteButton.Size = new System.Drawing.Size(120, 56);
this.DeleteButton.TabIndex = 24;
this.DeleteButton.Text = "&DELETE RECORD";
//
// FirstNameTxt
//
this.FirstNameTxt.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.First name"));
this.FirstNameTxt.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.FirstNameTxt.Location = new System.Drawing.Point(184, 88);
this.FirstNameTxt.Name = "FirstNameTxt";
this.FirstNameTxt.Size = new System.Drawing.Size(160, 26);
this.FirstNameTxt.TabIndex = 0;
this.FirstNameTxt.Text = "";
//
// LastNameText
//
this.LastNameText.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataSet11, "Table1.Last name"));
this.LastNameText.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.LastNameText.Location = new System.Drawing.Point(184, 128);
this.LastNameText.Name = "LastNameText";
this.LastNameText.Size = new System.Drawing.Size(160, 26);
this.LastNameText.TabIndex = 3;
this.LastNameText.Text = "";
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Table1", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("Address", "Address"),
new System.Data.Common.DataColumnMapping("Cell Phone #", "Cell Phone #"),
new System.Data.Common.DataColumnMapping("City", "City"),
new System.Data.Common.DataColumnMapping("E-Mail ID#", "E-Mail ID#"),
new System.Data.Common.DataColumnMapping("First name", "First name"),
new System.Data.Common.DataColumnMapping("Home Phone #", "Home Phone #"),
new System.Data.Common.DataColumnMapping("Id#", "Id#"),
new System.Data.Common.DataColumnMapping("Last name", "Last name"),
new System.Data.Common.DataColumnMapping("State", "State"),
new System.Data.Common.DataColumnMapping("Zip", "Zip")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = @"DELETE FROM Table1 WHERE ([Id#] = ?) AND (Address = ? OR ? IS NULL AND Address IS NULL) AND ([Cell Phone #] = ? OR ? IS NULL AND [Cell Phone #] IS NULL) AND (City = ? OR ? IS NULL AND City IS NULL) AND ([E-Mail ID#] = ? OR ? IS NULL AND [E-Mail ID#] IS NULL) AND ([First name] = ? OR ? IS NULL AND [First name] IS NULL) AND ([Home Phone #] = ? OR ? IS NULL AND [Home Phone #] IS NULL) AND ([Last name] = ? OR ? IS NULL AND [Last name] IS NULL) AND (State = ? OR ? IS NULL AND State IS NULL) AND (Zip = ? OR ? IS NULL AND Zip IS NULL)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Id_", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Id#", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Cell_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Cell Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Cell_Phone__1", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Cell Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_E_Mail_ID_", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "E-Mail ID#", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_E_Mail_ID_1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "E-Mail ID#", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_First_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "First name", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_First_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "First name", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Home_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Home Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Home_Phone__1", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Home Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Last_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Last name", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Last_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Last name", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_State", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "State", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_State1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "State", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Zip", System.Data.OleDb.OleDbType.VarWChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Zip", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Zip1", System.Data.OleDb.OleDbType.VarWChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Zip", System.Data.DataRowVersion.Original, null));
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\ITFN2314\Project Two for ITFN2314.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";
this.oleDbConnection1.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(this.oleDbConnection1_InfoMessage);
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO Table1(Address, [Cell Phone #], City, [E-Mail ID#], [First name], [Ho" +
"me Phone #], [Last name], State, Zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Address", System.Data.OleDb.OleDbType.VarWChar, 50, "Address"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Cell_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, "Cell Phone #"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("City", System.Data.OleDb.OleDbType.VarWChar, 50, "City"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("E_Mail_ID_", System.Data.OleDb.OleDbType.VarWChar, 50, "E-Mail ID#"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("First_name", System.Data.OleDb.OleDbType.VarWChar, 50, "First name"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Home_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, "Home Phone #"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Last_name", System.Data.OleDb.OleDbType.VarWChar, 50, "Last name"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("State", System.Data.OleDb.OleDbType.VarWChar, 50, "State"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Zip", System.Data.OleDb.OleDbType.VarWChar, 5, "Zip"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT Address, [Cell Phone #], City, [E-Mail ID#], [First name], [Home Phone #]," +
" [Id#], [Last name], State, Zip FROM Table1";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = @"UPDATE Table1 SET Address = ?, [Cell Phone #] = ?, City = ?, [E-Mail ID#] = ?, [First name] = ?, [Home Phone #] = ?, [Last name] = ?, State = ?, Zip = ? WHERE ([Id#] = ?) AND (Address = ? OR ? IS NULL AND Address IS NULL) AND ([Cell Phone #] = ? OR ? IS NULL AND [Cell Phone #] IS NULL) AND (City = ? OR ? IS NULL AND City IS NULL) AND ([E-Mail ID#] = ? OR ? IS NULL AND [E-Mail ID#] IS NULL) AND ([First name] = ? OR ? IS NULL AND [First name] IS NULL) AND ([Home Phone #] = ? OR ? IS NULL AND [Home Phone #] IS NULL) AND ([Last name] = ? OR ? IS NULL AND [Last name] IS NULL) AND (State = ? OR ? IS NULL AND State IS NULL) AND (Zip = ? OR ? IS NULL AND Zip IS NULL)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Address", System.Data.OleDb.OleDbType.VarWChar, 50, "Address"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Cell_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, "Cell Phone #"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("City", System.Data.OleDb.OleDbType.VarWChar, 50, "City"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("E_Mail_ID_", System.Data.OleDb.OleDbType.VarWChar, 50, "E-Mail ID#"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("First_name", System.Data.OleDb.OleDbType.VarWChar, 50, "First name"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Home_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, "Home Phone #"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Last_name", System.Data.OleDb.OleDbType.VarWChar, 50, "Last name"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("State", System.Data.OleDb.OleDbType.VarWChar, 50, "State"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Zip", System.Data.OleDb.OleDbType.VarWChar, 5, "Zip"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Id_", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Id#", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Cell_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Cell Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Cell_Phone__1", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Cell Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_E_Mail_ID_", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "E-Mail ID#", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_E_Mail_ID_1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "E-Mail ID#", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_First_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "First name", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_First_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "First name", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Home_Phone__", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Home Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Home_Phone__1", System.Data.OleDb.OleDbType.VarWChar, 12, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Home Phone #", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Last_name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Last name", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Last_name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Last name", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_State", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "State", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_State1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "State", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Zip", System.Data.OleDb.OleDbType.VarWChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Zip", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Zip1", System.Data.OleDb.OleDbType.VarWChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Zip", System.Data.DataRowVersion.Original, null));
//
// label1
//
this.label1.Font = new System.Drawing.Font("Monotype Corsiva", 15.75F, ((System.Drawing.FontStyle)((System.Drawing.FontStyle.Bold | System.Drawing.FontStyle.Italic))), System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.label1.Location = new System.Drawing.Point(304, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(168, 24);
this.label1.TabIndex = 25;
this.label1.Text = "CONTACT LIST";
//
// FirstButton
//
this.FirstButton.Location = new System.Drawing.Point(64, 424);
this.FirstButton.Name = "FirstButton";
this.FirstButton.Size = new System.Drawing.Size(75, 40);
this.FirstButton.TabIndex = 26;
this.FirstButton.Text = "&FIRST";
this.FirstButton.Click += new System.EventHandler(this.FirstButton_Click);
//
// LastButton
//
this.LastButton.Location = new System.Drawing.Point(328, 424);
this.LastButton.Name = "LastButton";
this.LastButton.Size = new System.Drawing.Size(75, 40);
this.LastButton.TabIndex = 27;
this.LastButton.Text = "&LAST";
this.LastButton.Click += new System.EventHandler(this.LastButton_Click);
//
// SaveButton
//
this.SaveButton.Location = new System.Drawing.Point(480, 88);
this.SaveButton.Name = "SaveButton";
this.SaveButton.Size = new System.Drawing.Size(120, 48);
this.SaveButton.TabIndex = 28;
this.SaveButton.Text = "&SAVE RECORD";
this.SaveButton.Visible = false;
this.SaveButton.Click += new System.EventHandler(this.SaveButton_Click);
//
// SearchButton
//
this.SearchButton.Location = new System.Drawing.Point(480, 272);
this.SearchButton.Name = "SearchButton";
this.SearchButton.Size = new System.Drawing.Size(120, 48);
this.SearchButton.TabIndex = 29;
this.SearchButton.Text = "&SEARCH";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.BackColor = System.Drawing.Color.LightSkyBlue;
this.ClientSize = new System.Drawing.Size(776, 526);
this.Controls.Add(this.SearchButton);
this.Controls.Add(this.SaveButton);
this.Controls.Add(this.LastButton);
this.Controls.Add(this.FirstButton);
this.Controls.Add(this.label1);
this.Controls.Add(this.DeleteButton);
this.Controls.Add(this.EditButton);
this.Controls.Add(this.BackButton);
this.Controls.Add(this.ADDButton);
this.Controls.Add(this.FORWARDButton);
this.Controls.Add(this.CellphoneText);
this.Controls.Add(this.HomeNumberText);
this.Controls.Add(this.EmailText);
this.Controls.Add(this.ZipcodeText);
this.Controls.Add(this.StateText);
this.Controls.Add(this.CityText);
this.Controls.Add(this.AddressText);
this.Controls.Add(this.FirstNameTxt);
this.Controls.Add(this.LastNameText);
this.Controls.Add(this.CELLPHONELabel);
this.Controls.Add(this.HMPHONELABEL);
this.Controls.Add(this.EMAILLABEL);
this.Controls.Add(this.ZIPLABEL);
this.Controls.Add(this.STATELABEL);
this.Controls.Add(this.CITYLABEL);
this.Controls.Add(this.ADDRESSLABEL);
this.Controls.Add(this.LastNameLabel);
this.Controls.Add(this.FIRSTNAMELabel);
this.ForeColor = System.Drawing.Color.MediumBlue;
this.Name = "Form1";
this.Text = "Project Two";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
this.ResumeLayout(false);
}
#endregion
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
oleDbDataAdapter1.Fill(dataSet11);
oleDbConnection1.Close();
}
private void FORWARDButton_Click(object sender, System.EventArgs e)
{
this.BindingContext[dataSet11, "Table1"].Position += 1;
}
private void BackButton_Click(object sender, System.EventArgs e)
{
this.BindingContext[dataSet11, "Table1"].Position -=1;
}
private void oleDbConnection1_InfoMessage(object sender, System.Data.OleDb.OleDbInfoMessageEventArgs e)
{
}
private void ADDButton_Click(object sender, System.EventArgs e)
{
this.BindingContext[dataSet11, "Table1"].AddNew();
ADDButton.Visible = false;
SaveButton.Visible = true;
}
private void SaveButton_Click(object sender, System.EventArgs e)
{
ADDButton.Visible = true;
SaveButton.Visible = false;
System.Data.OleDb.OleDbCommand cmd;
cmd = new System.Data.OleDb.OleDbCommand ("Insert INTO Table1 (ID#, First name, Last name, Address, City,State,Zip Code,e-mail ID#,Home Phone#, Cellphone#) Values ('', FirstNameTxt, LastNameText, AddressText, CityText, ZipcodeText, EmailText,HomeNumberText,CellphoneText) ");
oleDbConnection1.Open();
oleDbDataAdapter1.Update(dataSet11);
oleDbConnection1.Close();
}
private void Edit_Click(object sender, System.EventArgs e)
{
}
private void LastButton_Click(object sender, System.EventArgs e)
{
this.BindingContext[dataSet11, "Table1"].Position = this.BindingContext[dataSet11, "Table1"].Count-1;
}
private void FirstButton_Click(object sender, System.EventArgs e)
{
this.BindingContext[dataSet11, "Table1"].Position = 0;
}
}
}
|
|
|
|
|
|
unhandled exception of type System.DAta.SQLClient.SQLException
on oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
I'm unable to update OrderDetails.
I probably miss somthing durong my database creation.
Does the OrderId column inOrderDetails is IDENTITY to?
Kevin
|
|
|
|
|
Yeah... you probably need to mark the OrderDetails column as IDENTITY.
hope this helps
|
|
|
|
|
I was wondering if someone could answer a general question....
What are the benefits and draw backs of the different types of databases?
Access vs FoxPro vs Oracle vs mySQL?
It seems everyone or the majority just choose access and move on...
Any thoughts or feedback would be appreciated.
Scott
|
|
|
|
|
Hi Scott,
I miss at least SQL Server (incl. MSDE), DB2, PostgreDB and SAP DB.
So for choosing the right database you might consider license price, performance, database provider, column and table limitations (size, data types), developer knowledge and so on. As it is for me, I would never choose Access if I can't help it. I was just bitten too often by its limitations So I usually prefer the MSDE for a start.
For a start about database comparison you might want to read: http://www.mssqlcity.com/Articles/Compare/Compare.htm
mk
|
|
|
|
|
I definately miss Firebird, I'm using it now with C# and the ADO.NET provider and it works excellent...
modified 22-Nov-21 21:01pm.
|
|
|
|
|
Just wanted to point out that this article can be made even more useful if one considers the fact that one does not need to create a separate data adapter for each table. As long as SQL syntax is followed for multiple statements within a query, the data adapter's Fill() method will create those many tables. Of course, one still has to go in and fix the table names. For SQL Server 2000 or MSDE 2000, one could use a query such as "SELECT * FROM TABLE1; SELECT * FROM TABLE2".
|
|
|
|
|
It is ok for select. But is it possible to update multiple tables with single DataAdapter?
|
|
|
|
|
Not in the current ADO.NET implementation - since one data adapter can only hold one each of the SQL SELECT/UPDATE/INSERT/DELETE commands. An approach I have taken is to create a collection of data adapters and command builders for each table I want to update (my updates are very simplistic though - no complex joins, etc.), use those data adapters to fill ONE dataset (one dataset per database, each dataset can hold as many tables from THAT database), and then call the update method for each data adapter passing in the dataset object for each table that you want to update. I simply loop through all the data adapters - only those tables that have any changes in the dataset get updated, the rest are simply ignored.
|
|
|
|
|
(Sorry about the Formatting...)
Ok, the example is nice, but you never explained a very important thing.
When using the CommandBuilder , it is VERY important where you declare it! If you have Declare a CommandBuilder is a Routine, and set an Adapter to it there, and then you expect that the Adapter's Commands will STAY that way in another Routine...you are wrong!
It is important to Declare the CommandBuilder outside of a Routine. Or, use this code to Build Commands for an Adapter and have them "stick"!
'Routine to Refresh the DataAdapter
Private Sub RefreshDataAdapter(ByVal Adapter As SqlClient.SqlDataAdapter, ByVal SelectCommand As String)
Dim sqlCon As SqlClient.SqlConnection
Dim cmdBuilder As SqlClient.SqlCommandBuilder
Dim sqlCmd As SqlClient.SqlCommand, sqlParam As SqlClient.SqlParameter
Dim sqlInsertParams(), sqlUpdateParams(), sqlDeleteParams() As SqlClient.SqlParameter
Dim sqlInsert, sqlUpdate, sqlDelete As String
Dim bRefresh, bInsert, bUpdate, bDelete As Boolean
Try
'Validate the Adapter (Basically, try to Exit if there is not work to be done)
If (IsNothing(Adapter)) Then Adapter = New SqlClient.SqlDataAdapter
If (IsNothing(Adapter.SelectCommand)) Then Adapter.SelectCommand = New SqlClient.SqlCommand(SelectCommand)
If (Adapter.SelectCommand.CommandText = "") And (SelectCommand = "") Then
Throw New Exception("The SqlClient.SqlDataAdapter cannot be Refreshed because the 'SelectCommand' was not initialized properly.")
ElseIf (Adapter.SelectCommand.CommandText <> SelectCommand) Then
Adapter.SelectCommand.CommandText = SelectCommand
bRefresh = (IsNothing(Adapter.InsertCommand) OrElse (Adapter.InsertCommand.CommandText = ""))
If (Not bRefresh) Then bRefresh = (IsNothing(Adapter.UpdateCommand) OrElse (Adapter.UpdateCommand.CommandText = ""))
If (Not bRefresh) Then bRefresh = (IsNothing(Adapter.DeleteCommand) OrElse (Adapter.DeleteCommand.CommandText = ""))
If (Not bRefresh) Then Return 'Nothing to do, all Commands are initialized
Else
Return 'Nothing to do, the Select Commands match
End If
'Load the Adapter's Commands from the DataSource
'The CommandBuilder will not overwrite existing DataCommands, so this SHOULD only retrieve Commands that are null.
'However, the CommandBuilder destroys any Commands after being disconnected from the Adapter,
'so the Information needs cached, and then re-built
'Attempt to Connect
If (_DS.GetConnectString() = "") Then Throw New Exception("The DataService is not Configured properly. ConnectionString = Nothing.") 'Exit if the DataService is not Configured
sqlCon = New SqlClient.SqlConnection(_DS.GetConnectString()) : sqlCon.Open()
'Retrieve the Adapter Information by using the Command Builder
Adapter.SelectCommand.Connection = sqlCon
cmdBuilder = New SqlClient.SqlCommandBuilder(Adapter)
cmdBuilder.RefreshSchema()
'Copy the Information Generated from the Command Builder
ReDim sqlInsertParams(cmdBuilder.GetInsertCommand().Parameters.Count - 1)
ReDim sqlUpdateParams(cmdBuilder.GetUpdateCommand().Parameters.Count - 1)
ReDim sqlDeleteParams(cmdBuilder.GetDeleteCommand().Parameters.Count - 1)
cmdBuilder.GetInsertCommand().Parameters.CopyTo(sqlInsertParams, 0)
cmdBuilder.GetUpdateCommand().Parameters.CopyTo(sqlUpdateParams, 0)
cmdBuilder.GetDeleteCommand().Parameters.CopyTo(sqlDeleteParams, 0)
sqlInsert = cmdBuilder.GetInsertCommand.CommandText
sqlUpdate = cmdBuilder.GetUpdateCommand.CommandText
sqlDelete = cmdBuilder.GetDeleteCommand.CommandText
cmdBuilder.Dispose() : cmdBuilder = Nothing
'Rebuild the Commands after UnBinding from the Command Builder
If (IsNothing(Adapter.InsertCommand)) Then Adapter.InsertCommand = New SqlClient.SqlCommand(sqlInsert) : bInsert = True
If (IsNothing(Adapter.UpdateCommand)) Then Adapter.UpdateCommand = New SqlClient.SqlCommand(sqlUpdate) : bUpdate = True
If (IsNothing(Adapter.DeleteCommand)) Then Adapter.DeleteCommand = New SqlClient.SqlCommand(sqlDelete) : bDelete = True
'Build the Insert Parameters
If (bInsert) Then
For i As Integer = 0 To sqlInsertParams.Length - 1
Adapter.InsertCommand.Parameters.Add(sqlInsertParams(i).ParameterName, sqlInsertParams(i).SqlDbType)
With Adapter.InsertCommand.Parameters(i)
.IsNullable = sqlInsertParams(i).IsNullable
.Offset = sqlInsertParams(i).Offset
.Precision = sqlInsertParams(i).Precision
.Scale = sqlInsertParams(i).Scale
.Size = sqlInsertParams(i).Size
.SourceColumn = sqlInsertParams(i).SourceColumn
.SourceVersion = sqlInsertParams(i).SourceVersion
.Value = sqlInsertParams(i).Value
End With
Next i
End If
Erase sqlInsertParams
'Build the Update Parameters
If (bUpdate) Then
For i As Integer = 0 To sqlUpdateParams.Length - 1
Adapter.UpdateCommand.Parameters.Add(sqlUpdateParams(i).ParameterName, sqlUpdateParams(i).SqlDbType)
With Adapter.UpdateCommand.Parameters(i)
.IsNullable = sqlUpdateParams(i).IsNullable
.Offset = sqlUpdateParams(i).Offset
.Precision = sqlUpdateParams(i).Precision
.Scale = sqlUpdateParams(i).Scale
.Size = sqlUpdateParams(i).Size
.SourceColumn = sqlUpdateParams(i).SourceColumn
.SourceVersion = sqlUpdateParams(i).SourceVersion
.Value = sqlUpdateParams(i).Value
End With
Next i
End If
Erase sqlUpdateParams
'Build the Delete Parameters
If (bDelete) Then
For i As Integer = 0 To sqlDeleteParams.Length - 1
Adapter.DeleteCommand.Parameters.Add(sqlDeleteParams(i).ParameterName, sqlDeleteParams(i).SqlDbType)
With Adapter.DeleteCommand.Parameters(i)
.IsNullable = sqlDeleteParams(i).IsNullable
.Offset = sqlDeleteParams(i).Offset
.Precision = sqlDeleteParams(i).Precision
.Scale = sqlDeleteParams(i).Scale
.Size = sqlDeleteParams(i).Size
.SourceColumn = sqlDeleteParams(i).SourceColumn
.SourceVersion = sqlDeleteParams(i).SourceVersion
.Value = sqlDeleteParams(i).Value
End With
Next i
End If
Erase sqlDeleteParams
Catch ex As SqlClient.SqlException : MsgBox("SQLException: " & ex.ToString)
Catch ex As Exception : MsgBox(ex.ToString)
Finally
sqlParam = Nothing : sqlCmd = Nothing
sqlInsertParams = Nothing : sqlUpdateParams = Nothing : sqlDeleteParams = Nothing
If (Not IsNothing(cmdBuilder)) Then cmdBuilder.Dispose() : cmdBuilder = Nothing
If (Not IsNothing(sqlCon)) Then sqlCon.Dispose() : sqlCon = Nothing
End Try
End Sub
|
|
|
|
|
@IDENTITY Value can be changed in a trigger, attached to updating table,
if the trigger inserts any rows in another table.
(sybase servers does)
in this case the identity value will be equal to newly generated primary
key of a child table.
|
|
|
|
|
You are correct. UniqueIDs (GUIDs) are much better! I suggest that anyone using Identities in SQL Sever use Select Ident_Current('Orders') which will return the newly inserted ID for that Table.
If you need to re-set the Identity Columns in a Database:
DBCC CheckIdent('Orders', ReSeed, 0) . Even if you Re-Seed with data in the Table, the Next Time a Row is inserted, it will Auto Calculate the IDs for the Rows.
|
|
|
|
|
GUIDs are too big and too slow.
Proper select statement would be:
"SELECT SCOPE_IDENTITY()"
>Select Ident_Current('Orders')
may return IDENTITY of row inserted by other session
Krzemo
|
|
|
|
|
I will agree with the Scope Identity thing, however, GUIDs are NOT slow! It all depends on if you have a need for Replication / Synchronization. In this case, Auto-Numbers absolutely...SUCK!!! I know it CAN be done, but it's horrible!
|
|
|
|
|
Is it possible to create a new table in a dataset in memory and then update MS Access database, using the OleDbDataAdapter? How?
Why do I want to do that?
The 'primary key' definition seems to disappear when an MS Access table populates the
dataset in memory, using an OleDbDataAdapter.
Or is there any other reason that the OleDbCommandBuilder does not work?
|
|
|
|
|