关于SqlDataAdapter InsertCommand [英] About SqlDataAdapter InsertCommand

问题描述

您好:

我正在使用存储过程插入新记录，此存储过程只是插入一个guid并将表的主键返回给应用程序....现在，该表具有一个不接受空值的日期字段，并且已经设置了作为默认值，当前日期；因此，从数据适配器对象im调用update方法后，出现错误，因为im不提供日期值.

这是我的代码

Hi there:

Im using a store procedure to insert a new record, this storeprocedure is just inserting a guid and returning the table''s primary key to the application....now, the table has a date field which doesnt accept nulls and also has set as a default value the current date; so after calling the update method from data adapter object im getting an error cuz im not providing the date value.

This is my code

public void CreateIdentity()
        {
            string selectSP = "SelectNewDoc";
            string insertSP = "InsertNewDoc";
            SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["connectionStr"]);
            
            SqlDataAdapter da = (SqlDataAdapter)_daArrayList[0];
            
            da.InsertCommand = new SqlCommand(insertSP, cn);
            da.InsertCommand.CommandType = CommandType.StoredProcedure;
            da.InsertCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier, 16, "guid");

            SqlParameter myParm = da.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "pk");
            myParm.Direction = ParameterDirection.Output;

            DataSet ds = new DataSet();
            cn.Open();
            da.FillSchema(ds, System.Data.SchemaType.Source, "contracts");
            DataRow newRow = ds.Tables[0].NewRow();
            
            Guid newContactID = new Guid(getEmployeeGUID(currentUser()));

            newRow["DtCreated"] = DateTime.Today;
            newRow["ckey"] = newContactID;
            
            ds.Tables[0].Rows.Add(newRow);

            da.Update(ds, "contracts");
            
        }

现在，只要可以，我就将"DtCreated"值添加到newrow变量中，这样错误就消失了.

现在的问题

如果存储过程只是将GUID值作为参数，为什么数据适配器要求我指定日期值?.

这是否意味着即使我在存储过程中仅插入guid(如果我将其他值添加到新行中)，也将要同时插入所有这些值?.

now as u can c I added the "DtCreated" value to the newrow variable, by doing this the error goes away.

Now the questions

If the store procedure is just taking as a parameter the guid value, why the data adapter requires me to specify the date value?.

Is this means that even if my store procedure is only inserting the guid if i add other values to the new row all these values are gonna be inserted at the same time?.

How does the insert command actually works??

推荐答案

如果您在数据库表中指定了任何不为null的字段，并且未为其提供默认值，那么当您尝试插入一条记录而没有显式设置该字段的值时，您将得到一个错误-因为SQL不知道要赋予它什么值.它不仅会发明数据，还会引发错误，以便您意识到问题并可以解决.

就个人而言，在准备好要插入几乎整个行的信息之前，我不会创建该行-创建仅带有Guid ID的行是毫无意义的(而且确实很危险)，因为该行不能被其他任何人使用应用程序连接到数据库，直到您填补了空白.这就是为什么SQL会抱怨您的字段没有值的原因:如果它让记录存在且其中包含未知数据，则它确实可能使其他程序弄乱！

If you have specified any fields in you database table that are not null, and not provided a default value for them, then you will get an error when you try to insert a record without explicitly setting the value of that field - because SQL does not know what value to give it. It can''t just invent data, so it throws an error so that you are aware of the problem and can fix it.

Personally, I would not create the row until I had information ready to insert for pretty much the complete row - creating a row with just a Guid ID is a fairly pointless (and indeed dangerous) exercise, as the row is not usable by any other applications connected to the database until you have filled in the gaps. That is why SQL complains about your fields not having a value: if it let the record exists with unknown data in it, it could really mess up other programs!

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