Saving Confirmation_ID

[pinkpoppy]

Hi There,

I have an application in ASP.net. It is an application where users enter their required information, then it saves to our SQL database. I am having some issues when the user is done entering their info and clicks the "submit" button, which is then saved to the database. The Confirmation_ID properties are: int; required; unique constraint enforced and is system generated.

Here is a snip of the method.

 private void saveRecord()
        {
            DataTable dtDataForm1=new DataTable();
            DataTable dtDataForm2=new DataTable();
            if (Session["sesEntry1"] != null && Session["sesEntry2"] != null)
            {
                dtDataForm1 = (DataTable)Session["Entry1"];
                dtDataForm2 = (DataTable)Session["Entry2"];

                DataSet RTables = new DataSet();

                DataTable dt1 = new DataTable();
                dt1.TableName = "Report_Header";
                //DataColumn conf_Id = new DataColumn("Confirmation_ID", typeof(int));
                //conf_Id.AutoIncrement = true;
                //conf_Id.AutoIncrementSeed = 4;
                //dt1.Columns.Add(conf_Id);
                dt1.Columns.Add("Confirmation_ID", typeof(int));
                dt1.Columns.Add("FirstName", typeof(int));
                dt1.Columns.Add("LastName", typeof(int));
                dt1.Columns.Add("MiddleName", typeof(string));
                dt1.Columns.Add("Month", typeof(int));
                dt1.Columns.Add("Year", typeof(int));
                dt1.Columns.Add("Create_User", typeof(string));
                dt1.Columns.Add("Create_Date", typeof(string));
                dt1.Columns.Add("Modify_User", typeof(string));
                dt1.Columns.Add("Modify_Date", typeof(string));

                if (dtDataForm1.Rows.Count > 0)
                {
                    DataRow r1 = dt1.NewRow();
                    r1["Confirmation_ID"] = 7;
                    r1["FirstName"] = Convert.ToInt32(dtDataForm1.Rows[0]["Clerk_ID"]);
                    r1["LastName"] = Convert.ToInt32(dtDataForm1.Rows[0]["checkBookID"]);
                    r1["MiddleName"] = dtDataForm1.Rows[0]["County"].ToString();
                    r1["Month"] = Convert.ToInt32(dtDataForm1.Rows[0]["month"]);
                    r1["Year"] = Convert.ToInt32(dtDataForm1.Rows[0]["year"]);
                    r1["Create_User"] = "test()";
                    r1["Create_Date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    r1["Modify_User"] = "";
                    r1["Modify_Date"] = "";
                    dt1.Rows.Add(r1);
                }

I have this piece of code below commented because it issues an error when we try to save the information.

                //DataColumn conf_Id = new DataColumn("Confirmation_ID", typeof(int));
                //conf_Id.AutoIncrement = true;
                //conf_Id.AutoIncrementSeed = 1;
                //dt1.Columns.Add(conf_Id);

It seems to save a record when I manually enters the confirmation_id number.

dt1.Columns.Add("Confirmation_ID", typeof(int));

 r1["Confirmation_ID"] = 7;

Does anyone have an idea why this is occurring? Any help/suggestions is very much appreciated!

 

[philhege]

Although the specifics of this question are better asked in a .NET group, I'll take a stab.

It appears you want a unique value associated with the new record in your table (but I don't know for sure since the actual code to insert the record is missing). Most of us simply create an identity column in the SQL table. SQL Server takes care of the value assignment on INSERT. Your INSERT procedure should specify every column you wish to populate from the form, with the exception of the identity element.

-----------
With business clients like mine, you'd be better off herding cats.

 

[SQLBill]

Whenever you have something not working...it always helps to post the error. Having the error message helps us determine where the issue might be. As philhege point out, you seem to be creating and inserting a unique value. The trouble might be that the Confirmation_ID column is an Identity column. That would cause it to create its own 'autonumber'. You can't just insert values into that column. Here are two solutions if that is the case:

Assume Col1 is INT, IDENTITY (1,1)
and the last row has col1 = 37

INSERT INTO mytable (Col2, Col3)
VALUES ('hello', '57);

That would make the new row look like:

col1 col2 col3
38 hello 57

Now let's say you want to insert your own value into Col1.

SET IDENTITY_INSERT mytable ON
INSERT INTO mytable (col1, col2, col3)
VALUES (101, 'hello', 57)

That would make the new row be:
Col1 Col2 Col3
101 hello 57

But without knowing how the table is set up and what error(s) you are getting..it's kind of hard to trouble shoot.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875