C# SQLDataAdapter - Issues with INSERT

Question

Please refer to the question that started it all for me: C# SQLDataAdapter - Not inserting data into DB

I'm developing an archival system, well building on to it I should say. But my latest issue looks like this once I hit var rowsAffected = sqlAdapter.InsertCommand.ExecuteNonQuery();:

Additional information: The parameterized query '(@TableID int,@RowID int,@RowState tinyint,@UserID int,@XMLHisto' expects the parameter '@TableID', which was not supplied.

Now here's where it potentially gets a little tricky.. the tables that I'm trying to INSERT into, they don't have any primary or foreign keys associated to them. Not my design. I'm wondering if the above execute() call is having troubles with the INSERT because of this. I dont' have anything that I need to set as @@Identity because.. well I don't have anything like that available.

Thoughts?

Update:

As requested here's the query and the sql cmd..

string strInsertSQL = "INSERT INTO <tableName> (TableID, RowID, ModifyDate, RowState, UserID, XMLHistory, RowDescription) " +
                            "VALUES (@TableID, @RowID, GetDate(), @RowState, @UserID, @XMLHistory, @RowDescription); SET @NewID = @@Identity"; // SET statement needed? 

sqlComm = new SqlCommand(strInsertSQL, sqlConnArchive);
sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 4, "TableID"); // Not sure if this is needed since primary key doesn't exist? 
// More params added here

Update:

On a whim I wanted to check to see what the InsertCommand.Parameters held. I took a look at @TableID value and it was Null.

sqlAdapter.Fill(myDS.myTable); // myTable.count = 7k+
var x = sqlAdapter.InsertCommand.Parameters; // x-@tableID value = null
var rowsAffected = sqlAdapter.InsertCommand.ExecuteNonQuery();

I have a feeling that's why I'm seeing this error. Anyone have any thoughts on how I can resolve this?

You haven't included the SQL for InsertCommand. How can we see what is wrong? — Glen Thomas
– Glen Thomas, Commented Sep 4, 2015 at 0:00
sorry unfortunately no on the schema. But please do check the update I just included up top. — Tony D.
– Tony D., Commented Sep 4, 2015 at 0:29

Shane · Accepted Answer · 2015-09-04 02:00:22Z

1

The error is because it is null. Make the TableID column as Identity in the table.

answered Sep 4, 2015 at 2:00

Shane

113 bronze badges

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

2 Comments

Tony D. Over a year ago

I'm not up on my datasets, but can you do that if your table doesn't have a primary key?

Shane Over a year ago

yes. we can do that. Primary key has nothing to do with Identity column.

Rick Liddle · Accepted Answer · 2015-09-04 02:20:18Z

0

I don't see where you're actually setting the value of the parameter(s). You add the parameter, but your code doesn't show that you ever populate the parameter with a value.

sqlComm = new SqlCommand(strInsertSQL, sqlConnArchive);
var tableIdParam = new SqlParameter("@TableId", SqlDbType.Int, 4);
tableIdParam.Value = 99; //whatever id value you want to set here
sqlComm.Parameters.Add(tableIdParam);

If, on the other hand, the TableId column has an identity specifcation, then modify your sql string and leave TableId out of it altogether - the database will provide it on insert.

answered Sep 4, 2015 at 2:20

Rick Liddle

2,7041 gold badge19 silver badges31 bronze badges

3 Comments

Tony D. Over a year ago

Hm. Well I'm getting data back and I can confirm that it is populating the dataset.datatable of the intended target. It's just the issue is that the insert back to the DB is still not working.

Rick Liddle Over a year ago

OK. Can you include a more complete sample? All of the code that deals with the INSERT would be ideal, from where you declare the SQL string to the point you call ExecuteNonQuery().

Tony D. Over a year ago

Your comment got me thinking, so I created a function for the general save and set the values of the params. sqlAdapter.InsertCommand.Parameters[0].Value = tableID; "tableID being a param that I pass back. etc etc.. thanks, this worked for me.

Abdul Saleem · Accepted Answer · 2015-09-04 03:57:02Z

0

Yes of course you are not supplying the value..

In your method

sqlComm.Parameters.Add("@TableID", SqlDbType.Int, 4, "TableID")
// the value '4' here specifies the size of the column not value.
// for example max length of a string if you are about to supply a varchar

.

You can supply a value by two methods

Either

sqlComm.Parameters.Add("@TableID", SqlDbType.Int).Value = 4;

Or

sqlComm.Parameters.AddWithValue("@TableID", 4);

answered Sep 4, 2015 at 3:57

Abdul Saleem

10.7k6 gold badges47 silver badges47 bronze badges

Comments

Ppp · Accepted Answer · 2015-09-04 04:21:59Z

0

You are creating data adapter but the way you use the data adapter is like using a SqlCommand.

Refer this for way to use data adapter: Using Data Adapter

To use a sql command,

string strInsertSQL = "INSERT INTO <tableName> (TableID, RowID, ModifyDate, RowState, UserID, XMLHistory, RowDescription) " +
                            "VALUES (@TableID, @RowID, GetDate(), @RowState, @UserID, @XMLHistory, @RowDescription)"

sqlComm = new SqlCommand(strInsertSQL, sqlConnArchive);
sqlComm.Parameters.Add("@TableID", SqlDbType.Int).Value = 1; //Set it to the value you want to insert
..... the rest of the parameters

sqlConnArchive.Open();
sqlComm.ExecuteNonQuery();

answered Sep 4, 2015 at 4:21

Ppp

1,0159 silver badges14 bronze badges

Collectives™ on Stack Overflow

C# SQLDataAdapter - Issues with INSERT

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related