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

Posted by Sharpcnet under C# on 11/3/2013 | Points: 10 | Views : 1086 | Status : [Member] | Replies : 8
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();





Responses

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

Up
0
Down

Resolved
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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response