Updating DataSet

[dauphian]

Greetings,

I have a dataSet where I return an ID, I now need to associate that ID
with the description, which is in another database. I think I am on
the right track, but am not sure:

DataSet myData = new DataSet();
myAdapter.Fill(myData);

foreach (DataRow dRow in myData.Tables[0].Rows)
{
int prodID = (int)dRow["prod_id"];

string sel = "SELECT prod_desc FROM product_master WHERE
prod_id = " + prodID;
SqlCommand cmd =
new SqlCommand(sel, new
SqlConnection(GetConnectionString(siteCode)));
cmd.Connection.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;

}

return myData;

I am not sure how to update the dataSet???

Thanks in advance!

[sri_san]

http://msdn.microsoft.com/library/de...terdataset.asp

FYI.. Dataset can return multiple tables(with relation). Instead of
hitting the database in a loop, the foreign key relationship can be
processed in the code.

HTH,
Sam.

da******@gmail.com wrote:

Greetings,

I have a dataSet where I return an ID, I now need to associate that ID
with the description, which is in another database. I think I am on
the right track, but am not sure:

DataSet myData = new DataSet();
myAdapter.Fill(myData);

foreach (DataRow dRow in myData.Tables[0].Rows)
{
int prodID = (int)dRow["prod_id"];

string sel = "SELECT prod_desc FROM product_master WHERE
prod_id = " + prodID;
SqlCommand cmd =
new SqlCommand(sel, new
SqlConnection(GetConnectionString(siteCode)));
cmd.Connection.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;

}

return myData;

I am not sure how to update the dataSet???

Thanks in advance!

[Gnic]

Do you want to update the prod_desc? or the first dataset you get (about the
ID's),
If you want to modify the prod_desc, use a dataset instead of dataReader,
data Reader is read only.

To update the dataset, one ways is to go to the field, (like what you did in
the sameple "myData.Tables[0].Rows"), change the value and then call
ds.AcceptChanges() after you've made all the changes.

Gasnic
http://gasnicdev.blogspot.com/

<da******@gmail.com> wrote in message
news:11*********************@f6g2000cwb.googlegrou ps.com...

Greetings,

I have a dataSet where I return an ID, I now need to associate that ID
with the description, which is in another database. I think I am on
the right track, but am not sure:

DataSet myData = new DataSet();
myAdapter.Fill(myData);

foreach (DataRow dRow in myData.Tables[0].Rows)
{
int prodID = (int)dRow["prod_id"];

string sel = "SELECT prod_desc FROM product_master WHERE
prod_id = " + prodID;
SqlCommand cmd =
new SqlCommand(sel, new
SqlConnection(GetConnectionString(siteCode)));
cmd.Connection.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;

}

return myData;

I am not sure how to update the dataSet???

Thanks in advance!

[dauphian]

Hi,

I need to update the first DataSet, basically replace the ID with the
description from the other database.

I am not sure how you update or change a field in the dataSet, but I am
thankful for the advice. Guess I can research that aspect of it.

-Kevyn

[dauphian]

I am actually very close now, as it does return the value I need, but
only for the first entry:

SqlConnection myConn = new
SqlConnection(ConfigurationManager.ConnectionStrin gs["CRM_SystemConnection"].ConnectionString);
SqlDataAdapter myAdapter = new SqlDataAdapter(query, myConn);

DataSet myData = new DataSet();
myAdapter.Fill(myData);

DataColumn dc = new DataColumn();
myData.Tables[0].Columns.Add(dc);

foreach (DataRow dRow in myData.Tables[0].Rows)
{
int prodID = (int)dRow["prod_id"];

string sel = "SELECT prod_desc FROM product_master WHERE
prod_id = " + prodID;
SqlCommand cmd =
new SqlCommand(sel, new
SqlConnection(GetConnectionString(siteCode)));
cmd.Connection.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;

while (dr.Read())
{
string someValue = dr["prod_desc"].ToString();

myData.Tables[0].Rows[0][dc] = someValue;

myData.AcceptChanges();
}
}

It only works for first row, and doesn't seem to want to work for
others in the dataSet. Any thoughts?