I have a listbox with a union query as datasource :
public partial class FrmCodes : Form
{
SqlConnection Cn = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
SqlDataAdapter da,da2;
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
CurrencyManager Cm;
SqlCommandBuilder Cmdb;
string prm,SlctType;
public FrmCodes()
{
InitializeComponent();
da = new SqlDataAdapter("SELECT Code,Item,Type FROM Items UNION SELECT ProductCode,Product,Family FROM Products", Cn);
da.Fill(dt);
DataView dv = new DataView(dt);
ListItems.DataSource = dv;
ListItems.DisplayMember = "Item";
}
I am trying to bind data to four textboxes I have in this form based on listbox choice , First i tried the union query directly but keep getting error " Column x doesn’t exist .... " So I used the original two queries seperated Like this :
private void ListItems_Click(object sender, EventArgs e)
{
var Slctd = (DataRowView)ListItems.SelectedItem;
if (Slctd != null)
{
prm = Slctd["Code"].ToString();
SlctType = Slctd["Type"].ToString();
// Try to solve two bindings to the same property error
txtcode.DataBindings.Clear();
txtItem.DataBindings.Clear();
txtMaterialType.DataBindings.Clear();
txtFamily.DataBindings.Clear();
if (SlctType == "Material" || SlctType == "Master" || SlctType == "Packing" || SlctType == "Mix")
{
this.txtMaterialType.Visible = true;
this.lblMaterialType.Visible = true;
this.txtFamily.Visible = false;
this.lblFamily.Visible = false;
//this.PgBom.Hide();
try
{
da2 = new SqlDataAdapter("SELECT Code,Item,Type FROM Items WHERE Code LIKE '%prm%' ", Cn);
da2.Fill(dt2);
txtcode.DataBindings.Add("Text", dt2, "Code");
txtItem.DataBindings.Add("Text", dt2, "Item");
txtMaterialType.DataBindings.Add("Text", dt2, "Type");
Cm = (CurrencyManager)this.BindingContext[dt2];
}
catch (SqlException Err)
{
MessageBox.Show("This Error Occured :" + Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
// MessageBox.Show("You Chosed : " + prm + " It Is Product ", "Product");
this.txtFamily.Visible = true;
this.lblFamily.Visible = true;
this.txtMaterialType.Visible = false;
this.lblMaterialType.Visible = false;
try
{
da2 = new SqlDataAdapter("SELECT ProductCode,Product,Family FROM Products WHERE ProductCode LIKE '%prm%'", Cn);
da2.Fill(dt2);
txtcode.DataBindings.Add("Text", dt2, "ProductCode");
txtItem.DataBindings.Add("Text", dt2, "Product");
txtFamily.DataBindings.Add("Text", dt2, "Family");
Cm = (CurrencyManager)this.BindingContext[dt2];
}
catch (SqlException Err)
{
MessageBox.Show("This Error Occured :" + Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
prm = "";
}
}
But nothing happens with the textboxes no data appears in the textboxes .
If the data appeared I need to edit , add , delete using Currency Manager :
private void CmdEdit_Click(object sender, EventArgs e)
{
try
{
Cm.EndCurrentEdit();
Cmdb = new SqlCommandBuilder(da2);
da.Update(dt2);
Cm.Refresh();
MessageBox.Show("Edited Successfuly", "Done :)", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException Err)
{
MessageBox.Show("This Error Occured :" + Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Sorry for the long post , Thanks in advance .
Edit #1 : When i tried to set the criteria of the sql query to fixed value it works fine and the edit of data works too .
da2 = new SqlDataAdapter("SELECT ProductCode,Product,Family FROM Products WHERE ProductCode LIKE '10001'", Cn);
But if the criteria is a variable value or Textbox.Text nothing shows in the textboxes.
Edit #2 : I changed the referring syntax from LIKE '%prm%' to LIKE @prm and added this code :
da2.SelectCommand.Parameters.AddWithValue("@prm", "%" + Slctd["Code"].ToString() + "%");
And it worked perfectly .
After some researching I figured it out , The problem was
da2 = new SqlDataAdapter("SELECT Code,Item,Type FROM Items WHERE Code LIKE '%prm%' ", Cn);
I changed it to :
da2 = new SqlDataAdapter("SELECT Code,Item,Type FROM Items WHERE Code LIKE @prm ", Cn);
da2.SelectCommand.Parameters.AddWithValue("@prm", "%" + Slctd["Code"].ToString() + "%");
Works great now thanks everyone.