UpdateCommand vs. InsertCommand

[cokelite]

Using .NET v. 1.1.

Why is it, that using UpdateCommand i am able to add rows to a table, but the process is painfully slow. When trying to use InsertCommand, i get a strange error {"ERROR [07002] COUNT field incorrect" }, though there isn't such field in the DataSet/Table.

Soo, this code works:

public void UpdateData(SelectConn i_enConn, DataSet i_dsTmp, string i_strTable)
    {
      OdbcCommandBuilder CommandBuilder = null; 
      OdbcDataAdapter daAdapter = null; 
      OdbcConnection connConnection = null; 
      OdbcTransaction trans = null;
      
      try {
        try {
          switch (i_enConn) {
            case SelectConn.From :
              connConnection = m_connFrom;
              break;

            case SelectConn.To :
              connConnection = m_connTo;
              break;
          } 
        }
        catch (System.Exception exx) {
          string foobar = exx.Message.Trim();
          connConnection = null;
        }

        if (connConnection != null) {
          trans = connConnection.BeginTransaction(IsolationLevel.Serializable); 
          daAdapter = new OdbcDataAdapter();
          OdbcCommand cmd1 = new OdbcCommand("SELECT * FROM " + i_strTable, connConnection, trans);
          daAdapter.SelectCommand = cmd1;
          CommandBuilder = new OdbcCommandBuilder(daAdapter); 

          daAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand();
          
          OdbcCommand cmd2 = new OdbcCommand(daAdapter.UpdateCommand.CommandText, connConnection, trans);
          
          daAdapter.UpdateCommand = cmd2;
          daAdapter.Update(i_dsTmp);  
          trans.Commit();
        } 
      }
      catch (System.Exception ex) {
        throw ex;
      }
    }

But this one doesn't:

public void UpdateData(SelectConn i_enConn, DataSet i_dsTmp, string i_strTable)
    {
      OdbcCommandBuilder CommandBuilder = null; 
      OdbcDataAdapter daAdapter = null; 
      OdbcConnection connConnection = null; 
      OdbcTransaction trans = null;
      
      try {
        try {
          switch (i_enConn) {
            case SelectConn.From :
              connConnection = m_connFrom;
              break;

            case SelectConn.To :
              connConnection = m_connTo;
              break;
          } 
        }
        catch (System.Exception exx) {
          string foobar = exx.Message.Trim();
          connConnection = null;
        }

        if (connConnection != null) {
          trans = connConnection.BeginTransaction(IsolationLevel.Serializable); 
          daAdapter = new OdbcDataAdapter();
          OdbcCommand cmd1 = new OdbcCommand("SELECT * FROM " + i_strTable, connConnection, trans);
          daAdapter.SelectCommand = cmd1;
          CommandBuilder = new OdbcCommandBuilder(daAdapter); 

          daAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
          OdbcCommand cmd2 = new OdbcCommand(daAdapter.InsertCommand.CommandText, connConnection, trans);
          daAdapter.InsertCommand = cmd2;
          daAdapter.Update(i_dsTmp);  //{"ERROR [07002] COUNT field incorrect" }
          trans.Commit();
        } 
      }
      catch (System.Exception ex) {
        throw ex;
      }
    }

[cjard]

Update is not designed for insert.. it's designed for update, so your entire post doesnt make much sense.

What DB are you using?

[JonnyPoet]

Your code:

... 
daAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
connConnection, trans); // whats this ?
OdbcCommand cmd2 = new OdbcCommand(daAdapter.InsertCommand.CommandText, connConnection, trans);
// look into the debugger whats CommandText Value shows up here 
daAdapter.InsertCommand = cmd2;
daAdapter.Update(i_dsTmp); //{"ERROR [07002] COUNT field incorrect" }

It seems me there is somehing lost by transfering it to here. ( red line )
`Regarding debugging: look what command text shows up

[cokelite]

For cjard's information:

"DataAdapter.Update Method
Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named 'Table'."

The DataSet is generated from SqlServer and is written to SolidServer.

For JonnyPoet:
The resulting CommandText seems to be valid.
"INSERT INTO TableX(Field1, Field2, Field3 ) VALUES ( ? , ? , ?)"

(connConnection, trans); // whats this ? That was a typo in my post, sorry.

[JonnyPoet]

For cjard's information:

The DataSet is generated from SqlServer and is written to SolidServer.

For JonnyPoet:
The resulting CommandText seems to be valid.
"INSERT INTO TableX(Field1, Field2, Field3 ) VALUES ( ? , ? , ?)"

(connConnection, trans); // whats this ? That was a typo in my post, sorry.

Trerrornummber 7007 says that the number of fields is incorrect so have you added all parameters correctly ? or are there fields missing which needs to get data ( must be written ) and you havn't inserted their data? I would look for nummber of fileds needed to insert and if all the data in the params are available

[cokelite]

You are right, cmd1.Parameters.Count == 0. I thought the parameters were generated by SelectCommand.

This method i'm fighting with needs to be generic. Any idea how i'll get the parameters populated automatically ?

The dataset i_dsTmp and the table i_strTable have identical structure, except that the table is empty when daAdapter.Update starts.

[JonnyPoet]

You are right, cmd1.Parameters.Count == 0. I thought the parameters were generated by SelectCommand.

This method i'm fighting with needs to be generic. Any idea how i'll get the parameters populated automatically ?

The dataset i_dsTmp and the table i_strTable have identical structure, except that the table is empty when daAdapter.Update starts.

Its useless to try to insert an empty Tabels Data What do you mean with 'generic'? I cannot see any 'T' params here. Or do you mean it has to work independent of which data to be inserted. Tell a bit more what you want to achieve because often when you are stuck its simple the wrong approach. Have you thought about using an Interface for parts which should be usable in a more 'generic' way?

Re automatically populated ? Where are the data stored which should be inserted to the database. Is there a specific DataSet ?

[cokelite]

When calling my method i'm passing it as parameters the connection to database, the DataSet filled with data and a name of an empty database table into which i want to copy the data from DataSet.

public void UpdateData(SelectConn i_enConn, DataSet i_dsTmp, string i_strTable)

And yes, i do mean it has to work independent of which data to be inserted. The tables vary but the DataSet always has the same field names of same type as the table.

When using UpdateCommand (see my first code example in the first post) it works and i don't have to specify the parameters, they are generated automatically but the performance is poor.

          daAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand();
          OdbcCommand cmd2 = new OdbcCommand(daAdapter.UpdateCommand.CommandText, connConnection, trans);
          daAdapter.UpdateCommand = cmd2;
          daAdapter.Update(i_dsTmp);

But when i try to do the same with InsertCommand in hope of better performance, i end up dealing with missisng parameter definitions, or something..

          daAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
          OdbcCommand cmd2 = new OdbcCommand(daAdapter.InsertCommand.CommandText, connConnection, trans);
          daAdapter.InsertCommand = cmd2;
          daAdapter.Update(i_dsTmp);  //{"ERROR [07002] COUNT field incorrect" }

[JonnyPoet]

Hi !

IMHO you simple need to Fill the dataadapter BEFORE you are using insert

dataAdapter.Fill(i_dsTmp);

And after this using your Insert cmd. Because doing this in the update cycle is to late. There was nothing to insert obviously so it gets the error.

[cokelite]

Uhh, it sounded promising but unfortunately filling DataAdapter before defining CommandBuilder didn't make any difference, i still get the same error

daAdapter.Update(i_dsTmp);  //{"ERROR [07002] COUNT field incorrect" }

[cokelite]

I believe i kinda figured it out. One must define the InsertCommand but then assign that to UpdateCommand. BUT.. the performance still sucks.

Here's the working version.

    public void UpdateData(SelectConn i_enConn, DataSet i_dsTmp, string i_strTable)
    {
      OdbcCommandBuilder CommandBuilder = null; 
      OdbcDataAdapter daAdapter = null; 
      OdbcConnection connConnection = null; 
      OdbcTransaction trans = null;
      try {
        try {
          switch (i_enConn) {
            case SelectConn.From :
              connConnection = m_connFrom;
              break;
            case SelectConn.To :
              connConnection = m_connTo;
              break;
          } 
        }
        catch (System.Exception exx) {
          string foobar = exx.Message.Trim();
          connConnection = null;
        }

        if (connConnection != null) {
          trans = connConnection.BeginTransaction(IsolationLevel.Serializable); 
          daAdapter = new OdbcDataAdapter();
          daAdapter.SelectCommand = new OdbcCommand("SELECT * FROM " + i_strTable, connConnection, trans);
          CommandBuilder = new OdbcCommandBuilder(daAdapter); 
          daAdapter.UpdateCommand = CommandBuilder.GetInsertCommand();
          daAdapter.Update(i_dsTmp); 
          trans.Commit();
        } 
      }
      catch (System.Exception ex) {
        throw ex;
      }
    }