void ConnectVendorsCmbxFill() {
//connection string
string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod; ";
//query
string qry = @"select VendID, VendName from Vendors order by VendName";
//create connection
SqlConnection conn = new SqlConnection(connString);
conn.Open();

//create datatable
try {
//create data adapter
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
//create data table
DataTable dt = new DataTable();
da.Fill(dt);

foreach (DataRow row in dt.Rows) {
cmbxVendName.DisplayMember = "VendName";
cmbxVendName.ValueMember = "VendID";
cmbxVendName.Items.AddRange(new object[] { row["VendName"] });
}
cmbxVendName.Text = "Choose Vendor";
}
catch (Exception ex){MessageBox.Show("Error: " + ex);}
finally {

//close connection
conn.Close();
}
}

Second -- code to insert VendID into Products table

void

ConnectProductInsert() {
//create connection string
string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod;";
//create select qry
string qry = @"select * from products";
//create insert SQL
string ins = @"insert products (ProdName, VendID) values (@ProdName, @VendID)";
//create connection
SqlConnection conn = new SqlConnection(connString);

try {
//create data adapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);

//create and fill data set
DataSet ds = new DataSet();
da.Fill(ds, "Products");

//get table reference
DataTable dt = ds.Tables["Products"];

DataRow newRow = dt.NewRow();
newRow["ProdName"] = txtProdName.Text;
newRow["VendID"] = cmbxVendName.ValueMember;
dt.Rows.Add(newRow);

//create insert command
SqlCommand cmd = new SqlCommand(ins, conn);
//map parameters
cmd.Parameters.Add("@ProdName", SqlDbType.NVarChar, 50, "ProdName");
cmd.Parameters.Add("@VendID", SqlDbType.Int, 25, "VendID");
da.InsertCommand = cmd;
da.Update(ds, "Products");
}
catch (Exception ex) {MessageBox.Show("Error: " + ex);}

finally{
//close connection
conn.Close();
}
}

Any help will be greatly appreciated. Thanks
Bill

Bill,

What you really want to do is use DataBinding of the two DataTables, then use the DataAdapter stuff to put it back in the database. Something like this:

// After filling the DataTable with your DataAdapter,
// Here's how to "fill" the ComboBox
cmbxVendName.ValueMember = "VendID";
cmbxVendName.DisplayMember = "VendName";
cmbxVendName.DataSource = dt;

// Now databind to your Products table
cmbxVendName.DataBindings.Add("SelectedValue", ds.Tables["Products"], "VendID");

Second -- code to insert VendID into Products table

void

ConnectProductInsert() {
//create connection string
string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod;" ;
//create select qry
string qry = @"select * from products" ;
//create insert SQL
string ins = @"insert products (ProdName, VendID) values (@ProdName, @VendID)" ;
//create connection
SqlConnection conn = new SqlConnection (connString);

try {
//create data adapter
SqlDataAdapter da = new SqlDataAdapter ();
da.SelectCommand = new SqlCommand (qry, conn);

//create and fill data set
DataSet ds = new DataSet ();
da.Fill(ds, "Products" );

//get table reference
DataTable dt = ds.Tables[ "Products" ];

DataRow newRow = dt.NewRow();
newRow[ "ProdName" ] = txtProdName.Text;
newRow[ "VendID" ] = cmbxVendName.ValueMember;
dt.Rows.Add(newRow);

//create insert command
SqlCommand cmd = new SqlCommand (ins, conn);
//map parameters
cmd.Parameters.Add( "@ProdName" , SqlDbType .NVarChar, 50, "ProdName" );
cmd.Parameters.Add( "@VendID" , SqlDbType .Int, 25, "VendID" );
da.InsertCommand = cmd;
da.Update(ds, "Products" );
}
catch ( Exception ex) { MessageBox .Show( "Error: " + ex);}

finally {
//close connection
conn.Close();
}
}

Any help will be greatly appreciated. Thanks
Bill

Bill,

What you really want to do is use DataBinding of the two DataTables, then use the DataAdapter stuff to put it back in the database. Something like this:

// After filling the DataTable with your DataAdapter,
// Here's how to "fill" the ComboBox
cmbxVendName.ValueMember = "VendID";
cmbxVendName.DisplayMember = "VendName";
cmbxVendName.DataSource = dt;

// Now databind to your Products table
cmbxVendName.DataBindings.Add("SelectedValue", ds.Tables["Products"], "VendID");

Good enough. Let me know if you still have problems andhave more questions.

Thanks Bonnie, I am still trying. I will respond with details of my lastest version tomorrow morning.

Bill

//create insert SQL
string ins = @"insert products (ProdName, VendID)
values (@ProdName, @VendID)";

//create connection
SqlConnection conn = new SqlConnection(connString);
try {

//create data adapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);

//create and fill data set
DataSet ds = new DataSet();
da.Fill(ds, "Products");

//get table reference
DataTable dt = ds.Tables["Products"];

//add new row
DataRow newRow = dt.NewRow();
newRow["ProdName"] = txtProdName.Text;
//insert converted integer from combo box -- (PART 2 OF PROBLEM 2)
newRow["VendID"] = intSelVal;
dt.Rows.Add(newRow);

//create insert command
SqlCommand cmd = new SqlCommand(ins, conn);
//
//map parameters
cmd.Parameters.Add("@ProdName", SqlDbType.NVarChar, 50, "ProdName");
cmd.Parameters.Add("@VendID", SqlDbType.Int, 25, "VendID");
da.InsertCommand = cmd;
da.Update(ds, "Products");
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
//close connection
conn.Close();
}
}

I can't believe it really works. Thanks for all the help.

Bill

I'm glad you got it working Bill, but you're still going about it the hard way. Sorry.

First, you don't need to spin through the rows of your DataTable when you "fill" your Combo. So this:

//fill combobox
foreach (DataRow row in dt.Rows){
cmbxVendName.DisplayMember = "VendName";
cmbxVendName.ValueMember = "VendID";
//PROBLEM 1 FIXED -- puts both VendName and VendID into combobox
cmbxVendName.DataSource = dt;
}
cmbxVendName.Text = "Choose Vendor";
}
catch (Exception ex) { MessageBox.Show("Error: " + ex); }
finally {
//close connection
conn.Close();
}
}

Should be changed to this (note that I've simply taken out the iteration through the DataRow collection):

//fill combobox
cmbxVendName.DisplayMember = "VendName";
cmbxVendName.ValueMember = "VendID";
//PROBLEM 1 FIXED -- puts both VendName and VendID into combobox
cmbxVendName.DataSource = dt;

cmbxVendName.Text = "Choose Vendor";
}
catch (Exception ex) { MessageBox.Show("Error: " + ex); }
finally {
//close connection
conn.Close();
}
}

Now, secondly, it looks to me like you are retrieiving your entire Product DataTable simply to insert one row. While it's probably perfectly ok to have all your Products in one DataTable (depending on what you're going to do with them), retrieving them all each and every time you insert a new one isn't going to be very effective. Retrieve them all once, one time only. Update or insert data only when you need to. DataBind everything else to the Product table and you won't have to jump through quite so many hoops. This is kind of the point I've been trying to make ... databinding your DataTable to your various UI controls is the proper way to make this all happen.

So, here's some code off the top of my head to show you what I mean:

// member declarations
DataTable dtVendors = new DataTable();
DataTable dtProducts = new DataTable();

private void FillDataAndDataBind()
{
	this.ConnectVendorsCmbxFill();
	this.FillProducts();
	this.DataBind();
}
private void DataBind()
{
	this.cmbxVendName.DataBindings.Add("SelectedValue", this.dtProducts, "VendID");
	this.txtProdName.DataBindings.Add("Text", this.dtProducts, "VendName");
}

private void Save()
{
	this.UpdateProducts();
}
private void FillProducts()
{
	//create connection string
	string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod;"; 

	//create select products qry
	string qry = @"select * from products"; 

	//create connection
	SqlConnection conn = new SqlConnection(connString);
	try { 
		//create data adapter
		SqlDataAdapter da = new SqlDataAdapter();
		da.SelectCommand = new SqlCommand(qry, conn); 

		da.Fill(this.dtProducts); 
	}
	catch (Exception ex)
	{ 
		MessageBox.Show("Error: " + ex);
	}
	finally
	{
		//close connection
		conn.Close();
	}
}

// somewhere on your form, you'll either need a button to add a new row to your dtProducts table, 
// or you simply add it yourself programatically ( I'd probably do it in the DataBind method, but I
// really don't know the total flow of your app, so it may belong elsewhere ... button click sounds better to me).
private void AddNewProduct()
{
	this.dtProducts.Rows.Add(this.dtProducts.NewRow());
	// here's what you have to do to make databinding work nicely
	this.BindingContext[this.dtProducts].Position = this.dtProducts.Rows.Count - 1;
}

// bb - I've left this as you had it (with the exception of removing the iteration through the Rows
//        and using this.dtVendors).
private void ConnectVendorsCmbxFill() {
	//connection string
	string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod; ";
	//query
	string qry = @"select VendID, VendName
	from Vendors
	order by VendName";
	//create connection
	SqlConnection conn = new SqlConnection(connString);
	conn.Open(); 

	//create datatable
	try {
		//create data adapter
		SqlDataAdapter da = new SqlDataAdapter(qry, conn);
		//create data table
		da.Fill(this.dtVendors); 

		//fill combobox
		cmbxVendName.DisplayMember = "VendName";
		cmbxVendName.ValueMember = "VendID";
		//PROBLEM 1 FIXED -- puts both VendName and VendID into combobox
		cmbxVendName.DataSource = this.dtVendors;

		cmbxVendName.Text = "Choose Vendor";
	}
	catch (Exception ex) { MessageBox.Show("Error: " + ex); }
	finally {
		//close connection
		conn.Close();
	}
}

// Change the name of this method to reflect the fact that you're updating the Product table
// Even though I'm not sure your app is updating anything, looks like you just want to add new Products
// private void ConnectProductInsert() {
private void UpdateProducts() {
	// bb - you won't need this anymore because you're databinding
	//cast combobox selected value to integer (PROBLEM 2 FIXED -- PART 1)
	//string strSelVal = cmbxVendName.SelectedValue.ToString();
	//int intSelVal = int.Parse(strSelVal);
	
	// bb - likewise, for the same reason, you won't need this:
	//add new row
	//DataRow newRow = dt.NewRow();
	//newRow["ProdName"] = txtProdName.Text;
	//insert converted integer from combo box -- (PART 2 OF PROBLEM 2)
	//newRow["VendID"] = intSelVal;
	//dt.Rows.Add(newRow); 
	
	//create connection string
	string connString = @"server = .\sqlexpress; integrated security = true; database = vendprod;"; 

	//create insert SQL
	string ins = @"insert products (ProdName, VendID)
	values (@ProdName, @VendID)"; 

	//create update SQL
	string upd = @"update products SET ProdName = @ProdName, 
	                               SET VendID = @VendID";

	//create connection
	SqlConnection conn = new SqlConnection(connString);
	try { 
		//create data adapter
		SqlDataAdapter da = new SqlDataAdapter();
		da.InsertCommand = new SqlCommand(ins, conn); 
		da.UpdateCommand = new SqlCommand(upd, conn); 

		//map parameters
		cmd.Parameters.Add("@ProdName", SqlDbType.NVarChar, 50, "ProdName");
		cmd.Parameters.Add("@VendID", SqlDbType.Int, 25, "VendID");
		da.Update(this.dtProducts);
	}
	catch (Exception ex)
	{ 
		MessageBox.Show("Error: " + ex);
	}
	finally
	{
		//close connection
		conn.Close();
	}
}

I hope this helps you see the bigger picture. =0)

Hi Bill,

Yep, your waywill work ... but wouldn't it be nice to have it all displayed on one form? ;0)

>>Let me tinker with databinding a bit and I'll let you know how it works out (if my brain doesn't explode absorbingit allin the meantime).<<

Sure ... and let me know if you have any other questions about it.

>>How much do I owe you?<<

hahahahaha --- actually, I have a new blog thatI just started last week. I have a PayPal "donate" button on it, if you're so inclined. ;0) Actually, I'm not really being serious here (and I'm sure you weren't either)...no oneexpects any payment for forum participation ...but I *am* unemployed at the moment. ;0)

private void btnInsertProd_Click(object sender, EventArgs e) {
//ConnectProductInsert();
//clear textbox and hide button
FillDataAndDataBind();
AddNewProduct();
UpdateProducts();
gpbxProdInsert.Hide();
btnAddVend.Show();
btnAddProd.Show();
btnCancel.Hide();
}

The FillDataAndDataBind() method is intended to be run only one time, not with every button click.

But, oh wait ... I know what the problem probably is. Sorry I forgot about mentioning it, because it's a question thatcomes up frequently and I have a standard canned response for it. Here it is:

Data in a DataRow has several different versions. First, there's the original version. Then, when it's being edited, it has a Proposed version and once it's done being edited, that becomes the Current version. Sometimes when editing, the row is left in the Proposed state and the Edit needs to be ended programmatically.

Here's a method I *always* call before I attempt to check for .HasChanges() before saving data:

protected virtual void CommitProposedChanges(DataSet ds)
{
	if (ds == null)
		return;

	for (int nTable = 0; nTable < ds.Tables.Count; nTable++)
	{
		for (int nRow = 0; nRow < ds.Tables[nTable].Rows.Count; nRow++)
		{
			if (ds.Tables[nTable].Rows[nRow].HasVersion(DataRowVersion.Proposed))
			{
				ds.Tables[nTable].Rows[nRow].EndEdit();
			}
		}
	}
}

So, add the above method, and in theSave() method, call it before you call the UpdateProducts() method.See if that takes care of it.