Inserting data with OleDbDataAdapter

[Salte2]

I haven't dealt much with databases before, so this is probably an easy question, but here goes:

I am trying to insert a new row into a table in a database. The table has earlier been loaded into a DataSet (objDataSet), and the Connection has also been created (objCon) without problems.
I've tried to do this in two different ways but the first one doesnt seem to work properly and the second one causes an error:

1)

string strSQL = "INSERT INTO........";
OleDbDataAdapter objAda = new OleDbDataAdapter();
objAda.InsertCommand = new OleDbCommand(strSQL, objCon);

So how do i make this insertcommand execute? I have tried using

objAda.Update(objDataSet, "TableName");

this will run fine, but nothing happens to the table in the database.

2)

DataRow row = objDataSet.Tables["TableName"].NewRow();
<assign data to new row>
objDataSet.Tables["TableName"].Rows.Add(row);
OleDbDataAdapter objAda = new OleDbDataAdapter();
objAda.UpdateCommand = new OleDbCommand("UPDATE TableName", objCon);
objAda.Update(objDataSet, "TableName");


this will cause an error saying "Update requires a valid InsertCommand when passed DataRow collection with new rows." So what should this InsertCommand look like?

 

[sbdSolna]

When you working with DataSet and DataAdapters, you have to set Delete, Insert, Select and Update commands in the DataAdapter for every table you want to update.

Example: I'm working with several tables and I make a
DataAdapter for each Table.
I hope you understand the code it is just copied from a program that working.

/// <summary>
/// Creates commands for update of table PD_UNIT_STN through sqlStationDataAdapter
/// </summary>
public void createStationDataAdapter() {
 StringBuilder cmd = new StringBuilder();
 cmd.Append("select * from ").Append(TableStation);
 cmd.Append(" order by stationid, year, unitcode, bachchlevel");
SqlCommand command = new SqlCommand(cmd.ToString(), this.sqlConnection);
command.CommandType = CommandType.Text;
this.sqlStationDataAdapter.SelectCommand = command;

cmd.Remove(0, cmd.Length);
cmd.Append("update ").Append(TableStation);
cmd.Append(" set year = @year, unitcode = @unitcode, batchlevel = @batchlevel, ");
cmd.Append("di_id = @di_id, prefix = @prefix, rs_id = @rs_id ");
cmd.Append("where stationid = @org_stationid and year = @org_year ");
cmd.Append("and batchlevel = @org_batchlevel and prefix = @org_prefix ");
cmd.Append("and di_id = @org_diid and rs_id = @org_rsid");
			command = new SqlCommand(cmd.ToString(), this.sqlConnection);
			command.CommandType = CommandType.Text;
command.CommandText = cmd.ToString();
command.Parameters.Add("@stationid", SqlDbType.NVarChar, 3, "stationid");
command.Parameters.Add("@year", SqlDbType.Char, 4, "year");
			command.Parameters.Add("@unitcode", SqlDbType.NVarChar, 10, "unitcode");
command.Parameters.Add("@batchlevel", SqlDbType.Int, 4, "batchlevel");
			command.Parameters.Add("@di_id", SqlDbType.NVarChar, 1, "di_id");
command.Parameters.Add("@prefix", SqlDbType.NVarChar, 3, "prefix");
command.Parameters.Add("@rs_id", SqlDbType.NVarChar, 3, "rs_id");
command.Parameters.Add(new SqlParameter("@org_stationid", SqlDbType.NVarChar, 3, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "stationid", DataRowVersion.Original, null));
command.Parameters.Add(new SqlParameter("@org_year", SqlDbType.Char, 4, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "year", DataRowVersion.Original, null));
command.Parameters.Add(new SqlParameter("@org_batchlevel", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "batchlevel", DataRowVersion.Original, null));
command.Parameters.Add(new SqlParameter("@org_prefix", SqlDbType.NVarChar, 1, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "prefix", DataRowVersion.Original, null));
command.Parameters.Add(new SqlParameter("@org_diid", SqlDbType.NVarChar, 1, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "di_id", DataRowVersion.Original, null));
command.Parameters.Add(new SqlParameter("@org_rsid", SqlDbType.NVarChar, 3, ParameterDirection.Input, false, ((Byte) (0)), ((Byte) (0)), "rs_id", DataRowVersion.Original, null));
this.sqlStationDataAdapter.UpdateCommand = command;

cmd.Remove(0, cmd.Length);
cmd.Append("insert into ").Append(TableStation).Append("(");
cmd.Append("stationid, year, batchlevel, prefix, di_id, rs_id, unitcode) ");
cmd.Append("values (");
cmd.Append("@stationid, @year, @batchlevel, @prefix, @di_id, @rs_id, @unitcode)");
command = new SqlCommand(cmd.ToString(), this.sqlConnection);
command.Parameters.Add("@stationid", SqlDbType.NVarChar, 3, "stationid");
command.Parameters.Add("@year", SqlDbType.Char, 4, "year");
			command.Parameters.Add("@batchlevel", SqlDbType.Int, 4, "batchlevel");
command.Parameters.Add("@di_id", SqlDbType.NVarChar, 1, "di_id");
command.Parameters.Add("@prefix", SqlDbType.NVarChar, 3, "prefix");
command.Parameters.Add("@rs_id", SqlDbType.NVarChar, 3, "rs_id");
command.Parameters.Add("@unitcode", SqlDbType.NVarChar, 10, "unitcode");
command.CommandType = CommandType.Text;
this.sqlStationDataAdapter.InsertCommand = command;

cmd.Remove(0, cmd.Length);
cmd.Append("delete from ").Append(this.TableStation);
cmd.Append(" where StationId = @Org_StationId");
command = new SqlCommand(cmd.ToString(), this.sqlConnection);
command.Parameters.Add(new SqlParameter("@Org_StationId", SqlDbType.Char, 3, ParameterDirection.Input, false
, ((Byte) (0)), ((Byte) (0)), "StationId", DataRowVersion.Original, null));
this.sqlStationDataAdapter.DeleteCommand = command;
}

 

[obislavu]

You should use Parameters property of the SqlCommand object to build the Inser. Update or Delete commands.
The previous post is an example.
You can also use ExecuteNonQuery() on the SqlCommand object to get INSERT executed.

// Somewhere the SqlConnection con object exists

SqlCommand objCommand = new SqlCommand();
objCommand.Connection = con;
SqlDataAdapter DataAdapter = new SqlDataAdapter();
DataAdapter.SelectCommand = objCommand;
// Use INSERT into to add a record to a table with 3 columns of type char
string sInsert="INSERT INTO table (Column1, Column2, Column3) VALUES ('val1','val2','val3')";
// How to execute the command:

objCommand.CommandText=sInsert;
try
{
objCommand.ExecuteNonQuery();
}
catch
{
}

string sDelete="Delete from table WHRER Column1 LIKE '9%000'";

objCommand.CommandText=sDelete;

try
{
objCommand.ExecuteNonQuery();
}
catch
{
}

Use the technique shows in the previous post to build the sInsert/sDelete strings and call
ExecuteNonQuery();
There are also other ways.
-obislavu-

 

[obislavu]

Sorry, you were asking about Oledb* and I used Sql*. Replace Sql by Oledb.
-obislavu-