insert data help!

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hello!
I use a DataAdapeter ( and DataSet) to access to my database. I have get all
data well (fill the dataset), but when I try to update it, I got nothing!
The code is below. Pls help me

conn = new SqlConnection(strConn);
sda = new SqlDataAdapter();
ds = new DataSet();
ds.DataSetName = "ds";
string tmp = "SELECT * FROM Personal";
SqlCommand selectCmd = new SqlCommand(tmp, conn);
sda.SelectCommand = selectCmd;
sda.Fill(ds, "Personal");
conn.Close();


SqlCommand insertCmd = new SqlCommand("InsertNewPerson",
sda.SelectCommand.Connection);
insertCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = new SqlParameter[4];
parms[0] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
parms[1] = new SqlParameter("@code", SqlDbType.Char, 4);
parms[2] = new SqlParameter("@type", SqlDbType.Char, 1);
parms[3] = new SqlParameter("@isAvailable", SqlDbType.TinyInt);
for (int i = 0; i < 4; i++)
insertCmd.Parameters.Add(parms);

insertCmd.Parameters[0].Value = "NewName";
insertCmd.Parameters[1].Value = "0000";
insertCmd.Parameters[2].Value = "M";
insertCmd.Parameters[3].Value = 1;
sda.Update(ds,"Personal");

the storedProc:
REATE PROCEDURE InsertNewPerson(
@name nvarchar(50),
@code char(4),
@type char(1),
@isAvailable tinyint
)AS
insert into Personal values(@name, @code, @type, @isAvailable)
GO
 
Hi:Alex
I think the problem is RowState.
When you use sda.Update(ds,"Personal"),
the Adapter will check the Personal's
RowState, but in your code ,the RowState
must be UnChanged,because you didn't do
anyting with that table, so when you call Update, adapter do nothing.
So,try to add new row to you table
System.Data.DataRow drTemp= new System.Data.DataRow();
drTemp(0)="NewName";
drTemp(1)="0000";
drTemp(2)="M";
drTemp(3)="1";
ds.Tables["personal"].Rows.Add(drTemp);
then update again.
 
well,

1) there is no insert command associated with the data adpater. also no
modification were done to the dataset to require any updates anyway.

2) you create a sqlcommand, set its parameters, but never execute - so again
nothing happens.

-- bruce (sqlwork.com)

| Hello!
| I use a DataAdapeter ( and DataSet) to access to my database. I have get
all
| data well (fill the dataset), but when I try to update it, I got nothing!
| The code is below. Pls help me
|
| conn = new SqlConnection(strConn);
| sda = new SqlDataAdapter();
| ds = new DataSet();
| ds.DataSetName = "ds";
| string tmp = "SELECT * FROM Personal";
| SqlCommand selectCmd = new SqlCommand(tmp, conn);
| sda.SelectCommand = selectCmd;
| sda.Fill(ds, "Personal");
| conn.Close();
|
|
| SqlCommand insertCmd = new SqlCommand("InsertNewPerson",
| sda.SelectCommand.Connection);
| insertCmd.CommandType = CommandType.StoredProcedure;
| SqlParameter[] parms = new SqlParameter[4];
| parms[0] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
| parms[1] = new SqlParameter("@code", SqlDbType.Char, 4);
| parms[2] = new SqlParameter("@type", SqlDbType.Char, 1);
| parms[3] = new SqlParameter("@isAvailable", SqlDbType.TinyInt);
| for (int i = 0; i < 4; i++)
| insertCmd.Parameters.Add(parms);
|
| insertCmd.Parameters[0].Value = "NewName";
| insertCmd.Parameters[1].Value = "0000";
| insertCmd.Parameters[2].Value = "M";
| insertCmd.Parameters[3].Value = 1;
| sda.Update(ds,"Personal");
|
| the storedProc:
| REATE PROCEDURE InsertNewPerson(
| @name nvarchar(50),
| @code char(4),
| @type char(1),
| @isAvailable tinyint
| )AS
| insert into Personal values(@name, @code, @type, @isAvailable)
| GO
|
|
|
 
Hello! Thanks for answer


1) there is no insert command associated with the data adpater. also no
modification were done to the dataset to require any updates anyway.
Click to expand...


If there is no insert commnd, why when I call Rows.Add() without assignment
of InsertCommand, the Error: Update requires a valid InsertCommand when
passed DataRow collection with new rows.



2) you create a sqlcommand, set its parameters, but never execute - so again
nothing happens.
Click to expand...


How to execute this sqlcommand? I have wondered , but don't know how.





-- bruce (sqlwork.com)

| Hello!
| I use a DataAdapeter ( and DataSet) to access to my database. I have get
all
| data well (fill the dataset), but when I try to update it, I got nothing!
| The code is below. Pls help me
|
| conn = new SqlConnection(strConn);
| sda = new SqlDataAdapter();
| ds = new DataSet();
| ds.DataSetName = "ds";
| string tmp = "SELECT * FROM Personal";
| SqlCommand selectCmd = new SqlCommand(tmp, conn);
| sda.SelectCommand = selectCmd;
| sda.Fill(ds, "Personal");
| conn.Close();
|
|
| SqlCommand insertCmd = new SqlCommand("InsertNewPerson",
| sda.SelectCommand.Connection);
| insertCmd.CommandType = CommandType.StoredProcedure;
| SqlParameter[] parms = new SqlParameter[4];
| parms[0] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
| parms[1] = new SqlParameter("@code", SqlDbType.Char, 4);
| parms[2] = new SqlParameter("@type", SqlDbType.Char, 1);
| parms[3] = new SqlParameter("@isAvailable", SqlDbType.TinyInt);
| for (int i = 0; i < 4; i++)
| insertCmd.Parameters.Add(parms);
|
| insertCmd.Parameters[0].Value = "NewName";
| insertCmd.Parameters[1].Value = "0000";
| insertCmd.Parameters[2].Value = "M";
| insertCmd.Parameters[3].Value = 1;
| sda.Update(ds,"Personal");
|
| the storedProc:
| REATE PROCEDURE InsertNewPerson(
| @name nvarchar(50),
| @code char(4),
| @type char(1),
| @isAvailable tinyint
| )AS
| insert into Personal values(@name, @code, @type, @isAvailable)
| GO
|
|
|
Click to expand...
 
Hello, I have tried with your code, but got nothing. My DataSet is not
accosiated with any DataGrid, so how do i mark the RowState now ?
 
This code works well , even I don't understand very well , but it works. Why
must I use simultaneously InserCommand and NewRow???

PersonCard personCard = new PersonCard();
personCard.ShowDialog();
if (personCard.BtnClick != BaseDialog.ButtonClick.ButtonOK)
return;
SqlCommand insertCmd = new SqlCommand("InsertNewPerson",
sda.SelectCommand.Connection);
insertCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = new SqlParameter[4];
parms[0] = new SqlParameter("@name", SqlDbType.NVarChar, 50);
parms[1] = new SqlParameter("@code", SqlDbType.Char, 4);
parms[2] = new SqlParameter("@type", SqlDbType.Char, 1);
parms[3] = new SqlParameter("@isAvailable", SqlDbType.TinyInt);
for (int i = 0; i < 4; i++)
insertCmd.Parameters.Add(parms);
sda.InsertCommand = insertCmd;

for (int i = 0; i < 2; i++)
insertCmd.Parameters.Value = personCard;
insertCmd.Parameters[2].Value = strPersonalType[selectedInd];
insertCmd.Parameters[3].Value = 1;


DataTable dtc = ds.Tables[tblPersonalName];
DataRow newRow = dtc.NewRow();
newRow["name"] = personCard[0];
newRow["code"] = personCard[1];
newRow["type"] = strPersonalType[selectedInd];
dtc.Rows.Add(newRow);
sda.Update(ds,tblPersonalName);


personCard is a dialog for Input information about new person.
personCard - only indexer for advantage.
 
Hi:Alex
My english is very bad, So I try My best to explain clearly.
DataSet(DataTable)is offline copy of the dataSoure(any kind
of data,DataBase,xml,Excel).DataTable is a Collection of DataRow,
when you use add a new row to a DataTable(But did not add to datasource
yet),this row's state is Added.But if you want to transfer this newrow
to datasource, you must use dataapater's update,this command will check
every row's state in the DataTable, if the row's state is Added,the
dataapater
will create Insert command(automatically, if you select to create
Insert,Update,
Delete command automatically when you create dataadapter. or you can use
CommandBuilder to create it automatically,like this
CommandBuilder CmdBuild = new CommandBuilder(your dataadapter); )

This method is for dataadaper.
Another method is to use command to change datasource directly.(like yours
Insert command)
Sometimes The command method is more fast,but you can execute one command
for one time,
and you must to connect to database per time .(dataadaper' update method
just once).
 
Thank you very much!
In fact, I understand the mechanisms that you told me. But I don't
understand why in my example ( that i have posted) does not work if I use
only addNewRow to dataset and call Update. It always informs that: There is
no InsertCommand. Ofcourse I can solve my problem by call InsertCommand
(Query) by accessing directly to my Database, it works successfully, But I
still want to know the full thing about AddNewRow to DataSet and Update it.
Can you give me the solution for it.
Thanks again!
 
Hi:Alex
If you use VisualStudio's Data Adapter Configuration Wizard ,at fourth step,
in advance Item(left button), you can select create Insert,Update,Delete
command
automatically.If you select it ,after you create Data Adapter,in your code
,like this
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();

this.daMaster.DeleteCommand = this.oleDbDeleteCommand1;
this.daMaster.InsertCommand = this.oleDbInsertCommand1;
this.daMaster.SelectCommand = this.oleDbSelectCommand1;
this.daMaster.UpdateCommand = this.oleDbUpdateCommand1;

this.oleDbDeleteCommand1.CommandText = "DELETE FROM order_master WHERE
(order_no = ?)";
this.oleDbDeleteCommand1.Connection = this.LocalConn;
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_order_no",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "order_no", System.Data.DataRowVersion.Original, null));

this.oleDbInsertCommand1.CommandText = "INSERT INTO order_master(ship_to_no,
pad_no, sales_no, company_no, branch, specia" +
"l_information, shipping_instruction, billing_instruction, create_date,
state, up" +
"load, order_no_remote, upload_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, " +
"?)";
this.oleDbInsertCommand1.Connection = this.LocalConn;
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ship_to_no",
System.Data.OleDb.OleDbType.Integer, 0, "ship_to_no"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("pad_no",
System.Data.OleDb.OleDbType.VarWChar, 6, "pad_no"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("sales_no",
System.Data.OleDb.OleDbType.Integer, 0, "sales_no"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("company_no",
System.Data.OleDb.OleDbType.VarWChar, 5, "company_no"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("branch",
System.Data.OleDb.OleDbType.VarWChar, 12, "branch"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("special_information",
System.Data.OleDb.OleDbType.VarWChar, 50, "special_information"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("shipping_instruction",
System.Data.OleDb.OleDbType.VarWChar, 50, "shipping_instruction"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("billing_instruction",
System.Data.OleDb.OleDbType.VarWChar, 50, "billing_instruction"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("create_date",
System.Data.OleDb.OleDbType.DBDate, 0, "create_date"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("state",
System.Data.OleDb.OleDbType.VarWChar, 10, "state"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("upload",
System.Data.OleDb.OleDbType.Boolean, 2, "upload"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("order_no_remote",
System.Data.OleDb.OleDbType.Integer, 0, "order_no_remote"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("upload_date",
System.Data.OleDb.OleDbType.DBDate, 0, "upload_date"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT order_no, ship_to_no,
pad_no, sales_no, company_no, branch, special_inform" +
"ation, shipping_instruction, billing_instruction, create_date, state,
upload, or" +
"der_no_remote, upload_date FROM order_master WHERE (state = ?)";
this.oleDbSelectCommand1.Connection = this.LocalConn;
this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("state",
System.Data.OleDb.OleDbType.VarWChar, 10, "state"));
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = @"UPDATE order_master SET
ship_to_no = ?, pad_no = ?, sales_no = ?, company_no = ?, branch = ?,
special_information = ?, shipping_instruction = ?, billing_instruction = ?,
create_date = ?, state = ?, upload = ?, order_no_remote = ?, upload_date = ?
WHERE (order_no = ?)";
this.oleDbUpdateCommand1.Connection = this.LocalConn;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ship_to_no",
System.Data.OleDb.OleDbType.Integer, 0, "ship_to_no"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("pad_no",
System.Data.OleDb.OleDbType.VarWChar, 6, "pad_no"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("sales_no",
System.Data.OleDb.OleDbType.Integer, 0, "sales_no"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("company_no",
System.Data.OleDb.OleDbType.VarWChar, 5, "company_no"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("branch",
System.Data.OleDb.OleDbType.VarWChar, 12, "branch"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("special_information",
System.Data.OleDb.OleDbType.VarWChar, 50, "special_information"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("shipping_instruction",
System.Data.OleDb.OleDbType.VarWChar, 50, "shipping_instruction"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("billing_instruction",
System.Data.OleDb.OleDbType.VarWChar, 50, "billing_instruction"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("create_date",
System.Data.OleDb.OleDbType.DBDate, 0, "create_date"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("state",
System.Data.OleDb.OleDbType.VarWChar, 10, "state"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("upload",
System.Data.OleDb.OleDbType.Boolean, 2, "upload"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("order_no_remote",
System.Data.OleDb.OleDbType.Integer, 0, "order_no_remote"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("upload_date",
System.Data.OleDb.OleDbType.DBDate, 0, "upload_date"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_order_no",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "order_no", System.Data.DataRowVersion.Original, null));


These command was creted by VisualStudio, but you can create yours command
manually,is same thing,
after those command, you can define the command text and paramaters ,(like
yours Insert command)
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();

this.daMaster.DeleteCommand = this.oleDbDeleteCommand1;
this.daMaster.InsertCommand = this.oleDbInsertCommand1;
this.daMaster.SelectCommand = this.oleDbSelectCommand1;
this.daMaster.UpdateCommand = this.oleDbUpdateCommand1;

After you define these command for DataAdaper(daMaster in my code),if you
changed the datatable(AddNewRow,change value,
delete..)when you call Update,system will depend on the
RowState(Added,Modified,Deleted)to search associated command(
Insert,Update,Delete),that's all .
Keep Touch, Have a nice Day!!!
 
Thank you so much ! Yonggangwang!
My prog is now working well .
Thanks again for your enthusiasm!
Alex!
 
Back
Top