C# SQLDataAdapter - Not inserting data into DB

Question

Here's what I got: User selects from a checklistbox of database names one they'd like to archive. Switch case in place to catch the selection.

case "userSelection":
                sqlAdapter = CreateMyAdapter("dbName", true, sqlconn, null);
                sqlAdapter.SelectCommand.CommandText += "";
                sqlAdapter.Fill(myDS.tableName);
                sqlAdapter.Dispose();

The adapter:

private SqlDataAdapter CreateMyAdapter(string TableName, bool IncludeUpdates, SqlConnection sqlConn, SqlTransaction sqlTran)
{
    SqlDataAdapter sqlAdapter = null;
    SqlConnection sqlConnArchive = new SqlConnection();

    strSQL = "SELECT " + TableName + ".* FROM " + TableName;
    sqlAdapter = new SqlDataAdapter(strSQL, sqlConn);

    // Right here, I create another sqlConnection that is pointed to
    // another datasource.

    sqlConnArchive = getThisOtherConnection();

    SqlCommand sqlComm;

    if (IncludeUpdates)
    {
        string strInsertSQL = "<insertQuery>";

        sqlComm = new SqlCommand(strInsertSQL, sqlConnArchive);
        sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 0, "TableID");
        // More params here...

        sqlAdapter.InsertCommand = sqlComm;

        // Update

        // Delete

    }
 }
        return sqlAdapter;

The issue: As you can see sqlConn is the connection that is tied to the SELECT command. And sqlConnArchive is tied to the INSERT. The thought here is that I could select the data from DB_1 if you will, and insert it into DB_2 using the same SQLDataAdapter. But the issue that I'm running into is trying to insert. The select works fine, and at this line sqlAdapter.Fill(myDS.tableName); once fill executes the data is there. But the INSERT isn't working.

A few things:

I tested to see if perhaps SQLDataAdapter couldn't handle multiple datasources/connections, switched things around so it was pointing the the same DB just different tables, and I'm seeing the same results.
I've confirmed that the issue does not reside within the INSERT query.
There are no errors, just steps right over in debug.
I have tried several permutations of .Update() and none of them worked. This project that I've been assigned, throughout the entire thing it appears that .Fill(); is what is submitting the data back to the DB.
I've tested the database side and connectivity is a go. No issues with login, etc etc..

Any help is greatly appreciated.

Please note - I tried to place an even larger emphasis on the word "greatly" but was limited by my toolset. Apparently SOF doesn't support bold, blink, underline, flames, or embedded music.

Dave Zych · Accepted Answer · 2015-09-03 21:12:42Z

1

I think you want ExecuteNonQuery.

var rowsAffected = sqlAdapter.InsertCommand.ExecuteNonQuery();

This executes the statement and then returns the number of rows affected. The Fill method won't run any InsertCommands.

answered Sep 3, 2015 at 21:12

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tony D. Over a year ago

Hey it got somewhere. Any thoughts on this though? "Additional information: The parameterized query '(@TableID int,@RowID int,@otherValue tinyint,@otherID int,@somethingElse' expects the parameter '@TableID', which was not supplied." I ask because this is specified in code:

SqlParameter sqlParam = sqlComm.Parameters.Add("@NewID", SqlDbType.Int, 0, "TableID");                     sqlParam.Direction = ParameterDirection.Output;                     sqlAdapter.InsertCommand = sqlComm;

Dave Zych Over a year ago

That statement is adding a parameter for @NewID with the value "TableID". It isn't adding a parameter for @TableID. I think you want sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 0, tableId) where tableId is some int variable.

Tony D. Over a year ago

Hm ok. Well it seems to be requiring this: SqlParameter sqlParam = sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 0, "TableID");. But when I set that, it says that TableID has already been declared. And if I touch anything in the sqlComm.parameters sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 0, "TableID");, it has the same error as above except with the next line item. So like instead of saying it is expecting a TableID it now is looking for a RowID, and so on. Thoughts?

Dave Zych Over a year ago

@TonyD. You should probably ask another question with this information, it seems too complicated to fit into comments.

Tony D. Over a year ago

as suggested: stackoverflow.com/questions/32387164/…

|

Collectives™ on Stack Overflow

C# SQLDataAdapter - Not inserting data into DB

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related