I created a form in which I have 4 textboxes and the out of the box bindingnavigator. I am displaying data from a single table and I would like to make the delete button work... and I can't. Here is the method I am using to refresh/add the data from the db to my bindingsource and data source (which I am calling the in the load of the form):
public void Fill_DataSource()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRDBConnection"].ConnectionString);
try
{
conn.Open();
SqlDataAdapter da1 = new SqlDataAdapter(new SqlCommand("select * from BusinessGroups", conn));
DataSet ds = new DataSet();
da1.Fill(ds);
BGbindSource.ResetBindings(false);
BGbindSource.DataSource = ds.Tables[0];
bindingNavigator1.BindingSource = BGbindSource;
//BusinessGroupCode
textBox1.DataBindings.Clear();
textBox1.DataBindings.Add(new Binding("Text", this.BGbindSource, "BusinessGroupCode", true, DataSourceUpdateMode.OnPropertyChanged));
//BusinessGroupName
textBox2.DataBindings.Clear();
textBox2.DataBindings.Add(new Binding("Text", this.BGbindSource, "BusinessGroupName", true, DataSourceUpdateMode.OnPropertyChanged));
//BusinessGroupDesc
textBox3.DataBindings.Clear();
textBox3.DataBindings.Add(new Binding("Text", this.BGbindSource, "BusinessGroupDescription", true, DataSourceUpdateMode.OnPropertyChanged));
//BusinessGroupId
textBox4.DataBindings.Clear();
textBox4.DataBindings.Add(new Binding("Text", this.BGbindSource, "BGId", true, DataSourceUpdateMode.OnPropertyChanged));
}
catch (Exception)
{
toolStripStatusLabel3.Text = "Database Is Offline or the Connection is not set correctly!";
}
finally
{
conn.Close();
}
}
And here is the code behind the delete button:
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRDBConnection"].ConnectionString);
try
{
conn.Open();
SqlCommand sqlcmd = new SqlCommand("delete from BusinessGroups where BGId=@BGId",conn);
//BGId
SqlParameter param4 = new SqlParameter("@BGId", SqlDbType.Int);
if (textBox4.Text.Trim() == "")
{
param4.Value = -999;
}
else
{
param4.Value = textBox4.Text;
}
sqlcmd.Parameters.Add(param4);
try
{
sqlcmd.ExecuteNonQuery();
//MessageBox.Show("Successfully deleted!");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error! Could not delete the requested information!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Fill_DataSource();
BGbindSource.ResetBindings(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error! Could not delete the requested information!", MessageBoxButtons.OK, MessageBoxIcon.Error);
//throw;
}
finally
{
if (conn != null) conn.Close();
}
}
If I press it, it will delete the previous item in my binding, to be more accurate, using breakpoints, I noticed that the BGId paramater always takes the value of the previous BGId, I mean not the one that I am currently positioned on, but the previous one. Why is this happening and how can I solve it? Thanks a lot! Edited: To respond to your questions, here is the code behind the save button who does works as expected:
private void bindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
if (BGbindSource.Current == null) return;
if (textBox1.Text.Trim() == "")
{
MessageBox.Show("Business Group Code cannot be blank!");
textBox1.Focus();
return;
}
if (textBox2.Text.Trim() == "")
{
MessageBox.Show("Business Group Name cannot be blank!");
textBox2.Focus();
return;
}
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRDBConnection"].ConnectionString);
try
{
conn.Open();
SqlCommand sqlcmd = new SqlCommand("if exists (select 1 from BusinessGroups where BGId=@BGId) "
+ " update BusinessGroups "
+ " set BusinessGroupCode=@BGCode, "
+ " BusinessGroupName=@BGName, "
+ " BusinessGroupDescription=@BGDesc, "
+ " UpdateTimeStamp= getdate()"
+ " where BGId=@BGId "
+ " else "
+ " insert into BusinessGroups (BusinessGroupCode,BusinessGroupName,BusinessGroupDescription) "
+ " select @BGCode,@BGName,@BGDesc "
, conn);
//BGCode
SqlParameter param1 = new SqlParameter("@BGCode", SqlDbType.NVarChar, 30 );
param1.Value = textBox1.Text;
sqlcmd.Parameters.Add(param1);
//BGName
SqlParameter param2 = new SqlParameter("@BGName", SqlDbType.NVarChar, 150);
param2.Value = textBox2.Text;
sqlcmd.Parameters.Add(param2);
//BGDesc
SqlParameter param3 = new SqlParameter("@BGDesc", SqlDbType.NVarChar, 1000);
param3.Value = textBox3.Text;
sqlcmd.Parameters.Add(param3);
//BGId
SqlParameter param4 = new SqlParameter("@BGId", SqlDbType.Int);
if (textBox4.Text.Trim() == "")
{
param4.Value = -999;
}
else
{
param4.Value = textBox4.Text;
}
sqlcmd.Parameters.Add(param4);
sqlcmd.ExecuteNonQuery();
Fill_DataSource();
//BGbindSource.ResetBindings(false);
MessageBox.Show("Successfully saved!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Error! Could not save the requested information!",MessageBoxButtons.OK,MessageBoxIcon.Error);
throw;
}
finally
{
if (conn != null) conn.Close();
}
}
To delete row using the default delete button on the
BindingNavigator
, you don't need any code. So just remove your own code inbindingNavigatorDeleteItem_Click
and it should work.