CODEWITHSUNDEEP
c#sqlsql-server
SamMan
SamMan

Reputation: 59

Adding a unique identifier to a c# form to be inserted into an mssql database

I am trying to link my c# form to a database that can hold all the data that is inserted in the form.

the other four fields are being inserted into the database correctly.

the problem that I am having is that every time a new record is created, a unique identifier needs to be generated in the last field of the SQL table.

The only way I can do this is by using a guid. But the problem is that I need the Identifier to start from 1 and increment.

I'm not sure how to do this in C# so any help provided would be excellent.

Here is my code

protected void Unnamed1_Click(object sender, EventArgs e)
{
    string box1value = TextBox1.Text;
    string box2value = TextBox2.Text;
    string box3value = TextBox3.Text;

    SqlConnection myConnection = new SqlConnection(sConnection);

    string sql = "INSERT INTO Part1Table (CustomerDetails, TechnologyPartner, ResponseDate, FormID, RowID) VALUES (@CustomerDetails, @TechnologyPartner, @ResponseDate, @FormID, @RowID)";



    myConnection.Open();
    try
    {
        // create a db command objet using the sql and db connection
        SqlCommand cmdIns = new SqlCommand(sql, myConnection);
        //box1value
        cmdIns.Parameters.Add("@CustomerDetails", SqlDbType.Char);
        cmdIns.Parameters["@CustomerDetails"].Value = box1value;
        //box2value
        cmdIns.Parameters.Add("@TechnologyPartner", SqlDbType.Char);
        cmdIns.Parameters["@TechnologyPartner"].Value = box2value;
        //box3value
        cmdIns.Parameters.Add("@ResponseDate", SqlDbType.DateTime);
        cmdIns.Parameters["@ResponseDate"].Value = box3value;

        cmdIns.Parameters.Add("@FormID", SqlDbType.Int);
        cmdIns.Parameters["@FormID"].Value = 1;

        cmdIns.Parameters.Add("@RowID", SqlDbType.UniqueIdentifier);
        cmdIns.Parameters["@RowID"].Value = Guid.NewGuid();

        // run the query
        cmdIns.ExecuteNonQuery();
        // end the command object
        cmdIns.Dispose();
        cmdIns = null;
    }
    catch(Exception ex)
    {
        Response.Write(ex);
    }

    myConnection.Close();

Upvotes: 1

Views: 112

Answers (1)

Steve
Steve

Reputation: 216293

You are looking for an IDENTITY COLUMN

For Example

CREATE TABLE [dbo].[Part1Table](
   [CustomerDetail] [nvarchar](50) NULL,
   ....
   [RowID] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
    [RowID] ASC
)ON [PRIMARY]

If you have an identity column, the job to add an increment value to every new record, is passed to the database engine.

Of course you cannot pass a value yourself for this column, but you read the value assigned by the database using the command SCOPE_IDENTITY

 SELECT SCOPE_IDENTITY()

So, summarizing you could write

string sql = @"INSERT INTO Part1Table (CustomerDetails, TechnologyPartner, ResponseDate, 
               FormID) VALUES (@CustomerDetails, @TechnologyPartner, @ResponseDate, @FormID)";
using(SqlConnection myConnection = new SqlConnection(sConnection))
SqlCommand cmdIns = new SqlCommand(sql, myConnection);
{
   try
   {
       myConnection.Open();
       cmdIns.Parameters.Add("@CustomerDetails", SqlDbType.Char);

       cmdIns.Parameters["@CustomerDetails"].Value = box1value;
       //box2value
       cmdIns.Parameters.Add("@TechnologyPartner", SqlDbType.Char);
       cmdIns.Parameters["@TechnologyPartner"].Value = box2value;
       //box3value
       cmdIns.Parameters.Add("@ResponseDate", SqlDbType.DateTime);
       cmdIns.Parameters["@ResponseDate"].Value = box3value;

       cmdIns.Parameters.Add("@FormID", SqlDbType.Int);
       cmdIns.Parameters["@FormID"].Value = 1;

       // No parameter for the ROWID column. 
       // It will be an error to try to insert 
       // a value for that column

       cmdIns.ExecuteNonQuery();

       // Read back the value using the SAME connection used for the insert
       cmdIns.Parameters.Clear();
       cmdIns.CommandText = "SELECT SCOPE_IDENTITY()";
       object result = cmdIns.ExecuteScalar();
       int newRowID = Convert.ToInt32(result);

    }
}

Upvotes: 2

Related Questions