Add Unique Key For Nullable Columns - SQL Server [Resolved]

Posted by Sharpcnet under C# on 11/3/2013 | Points: 10 | Views : 1584 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Using sql server 2008 R2 and would like to apply unique key constraint to nullable columns. Tried the following code.

CREATE UNIQUE INDEX UK_BranchCode ON tblBranch(BranchCode) WHERE BranchCode IS NOT NULL;

The target column is BranchCode. Now, there is one record in the table that has NULL for BranchCode. When I try to insert a new record, with Null BranchCode, from c#, it gives an error saying - Cannot insert duplicate key for unique index UK_BranchCode. But, when I manually insert a record in the database, it does accept null values. Where am I going wrong.

Guid gId = Guid.NewGuid();
cmd = new sqlcommand("insert into tblBranch(BranchId,BranchCode) 
                      values(@BranchId,@BranchCode)",con);
cmd.Parameters.AddWithValue("@BranchId",gId);
cmd.Parameters.AddWithValue("@BranchCode",txtBranchCode.Text);//empty text here
con.Open();
cmd.ExecuteNonQuery();
cmd.Close();

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/3/2013 [Member] [MVP] Platinum | Points: 50

0	try the following code: Guid gId = Guid.NewGuid(); cmd = new SqlCommand("insert into tblBranch(BranchId,BranchCode) values(@BranchId,@BranchCode)",con); cmd.Parameters.AddWithValue("@BranchId",gId); if (txtBranchCode.Text == string.Empty) { // Text box is empty. cmd.Parameters.AddWithValue("@BranchCode", DBNull.Value);//empty text here } else { cmd.Parameters.AddWithValue("@BranchCode", txtBranchCode.Text); } con.Open(); cmd.ExecuteNonQuery(); con .Close(); Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

try the following code:


Guid gId = Guid.NewGuid();

cmd = new SqlCommand("insert into tblBranch(BranchId,BranchCode) 

                      values(@BranchId,@BranchCode)",con);

cmd.Parameters.AddWithValue("@BranchId",gId);



if (txtBranchCode.Text == string.Empty)

{

    // Text box is empty.

    cmd.Parameters.AddWithValue("@BranchCode", DBNull.Value);//empty text here 

}

else

{

    cmd.Parameters.AddWithValue("@BranchCode", txtBranchCode.Text); 

}



con.Open();

cmd.ExecuteNonQuery();

con .Close();

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/4/2013 [Member] [MVP] Platinum | Points: 50

0	try this.... if (txtBranchCode.Text == string.Empty){ // Text box is empty. obj.BranchCode =null ;//empty text here } else{ obj.BranchCode = txtBranchCode.Text; } Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 11/3/2013 [Member] [MVP] Platinum | Points: 25

0	May be it is getting txtBranchCode.Text value as empty string ('') only..... Rather try to insert DBNull value for empty string Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sharpcnet on: 11/4/2013 [Member] Starter | Points: 25

0	Thank You. That worked. Could you plz tell me, how would it be in the following case Entities DB = new Entities(); tblBranch obj = new tblBranch(); obj.BranchCode = txtBranchCode.Text.Length > 0 ? txtBranchCode.Text : DBNull.value;//error here DB.AddToTblBranches(obj); DB.SaveChanges(); Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Thank You. That worked. Could you plz tell me, how would it be in the following case

Entities DB = new Entities();
tblBranch obj = new tblBranch();
obj.BranchCode = txtBranchCode.Text.Length > 0 ? txtBranchCode.Text : DBNull.value;//error here
DB.AddToTblBranches(obj);
DB.SaveChanges();

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/4/2013 [Member] [MVP] Platinum | Points: 25

0	Here txtBranchCode.Text is string value and DBNull.value is object... So it will throw error... Entities DB = new Entities(); tblBranch obj = new tblBranch(); if (txtBranchCode.Text == string.Empty) { // Text box is empty. obj.BranchCode =DBNull.Value;//empty text here } else { obj.BranchCode = txtBranchCode.Text; } DB.AddToTblBranches(obj); DB.SaveChanges(); Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Here txtBranchCode.Text is string value and DBNull.value is object... So it will throw error...

Entities DB = new Entities();
tblBranch obj = new tblBranch();
if (txtBranchCode.Text == string.Empty)
{
    // Text box is empty.
      obj.BranchCode =DBNull.Value;//empty text here 
}
else
{
       obj.BranchCode = txtBranchCode.Text; 
} 
DB.AddToTblBranches(obj);
DB.SaveChanges();

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 11/4/2013 [Member] Starter | Points: 25

0	Ah, I tried the same following your earlier answer, but it errors at DBNUll.Value, saying cannot implicitly convert system.dbnull to string Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sharpcnet on: 11/4/2013 [Member] Starter | Points: 25

0	Awesome It worked. Thanks a Lot... Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 11/4/2013 [Member] [MVP] Platinum | Points: 25

0	You are welcome :) Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts