C＃中的SqlDataAdapter - 用INSERT问题 [英] C# SQLDataAdapter - Issues with INSERT

问题描述

请参阅开始这一切对我的问题是：<一href="http://stackoverflow.com/questions/32385295/c-sharp-sqldataadapter-not-inserting-data-into-db/32385507#32385507">C# SqlDataAdapter的 - 不将数据插入到数据库

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

我开发一个归档系统，以及建立在它我应该说。但我的最新问题是这样的，一旦我打 VAR的RowsAffected = sqlAdapter.InsertCommand.ExecuteNonQuery（）; ：

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();:

补充信息：参数化查询（@TableID INT，@的RowID INT，@的RowState TINYINT，@用户名INT，@ XMLHisto预计参数@TableID'，但未提供该

现在这里就是它可能会变得有点棘手..我试图插入到表中，他们没有与之关联的任何主键和外键。不是我的设计。我想知道，如果上述execute（）调用是有，因为这个麻烦的INSERT。我不'有什么，我需要设置为@@身份，因为......好，我没有这样的东西可用。

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.

思考？

更新

根据要求，这里的查询和SQL CMD ..

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

更新：

在一时兴起，我想检查，看看有什么召开的InsertCommand.Parameters。我看了看@TableID的价值，它是零。

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?

推荐答案

我不明白，你实际上是在设置参数（S）的值。您添加参数，但你的code并不表明你曾经填充值参数。

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);

如果，在另一方面，TABLEID列有一个标识specifcation，然后修改您的SQL字符串，并留下TABLEID出它完全 - 数据库将提供它插入

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.

这篇关于C＃中的SqlDataAdapter - 用INSERT问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！