One SP with two recordsets

[Guest]

I'm able to get one recordset from my SP that has two select statements. How
do I get the second recordset? How do I access the second recordset to
populate the tag of my controls?

Here is my code so far. I'm getting an error 'System.Data.DataTable' does
not contain a definition for 'Col' on ds.Tables[1].Col = 0;

string sConnString =
System.Configuration.ConfigurationSettings.AppSettings["dsn"];
string sProc = "prGet_HIP";
using (SqlConnection oCn = new SqlConnection(sConnString))
{
using (SqlCommand oCmd = new SqlCommand(sProc, oCn))
{
oCn.Open();
oCmd.CommandType = CommandType.StoredProcedure;

oCmd.Parameters.Add("@sLicenseYear", SqlDbType.SmallInt,
2);
oCmd.Parameters["@sLicenseYear"].Value = intLicYear;

SqlDataAdapter oDa = new SqlDataAdapter();
oDa.SelectCommand = oCmd;
DataSet ds = new DataSet();
oDa.Fill(ds);

cboRails.DataSource = ds.Tables[0];
ds.Tables[1].Col = 0;
ds.Tables[1].Row = 0;
chkRailsNo.Tag = ds.Tables[1];
chkRailYes.Tag = ds.Tables[1];

cboWoodcook.DataSource = ds.Tables[0];
ds.Tables[1].Col = 0;
ds.Tables[1].Row = 1;
chkWoodcockYes.Tag = ds.Tables[1];
chkWoodcockNo.Tag = ds.Tables[1];




}
}

 

[Kevin Yu [MSFT]]

Hi Cadel,

When the select command contains two select statements, one resultset will
be filled to the first table in the DataSet, while the second resultset
will be filled to the second table. Use ds.Table[1] to get the seconod one.

You're getting this error message, because you the DataTable class doesn't
contain a property named Col and Row. Please check the following link for
more information about the members of DataTable class.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatatablememberstopic.asp

If you're intending to do a databinding to the combobox, I suggest you
check the following link.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbtskdatabindingcomboboxcheckedlistboxorlistboxcontrol.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

I read through the articles and changed my code. Now I'm getting a new error,
"Array was not a two-dimensional array." occurs on chkRailsNo.Tag =
foundRows.GetValue(0, 0);

Here is my code so far.

string sConnString =
System.Configuration.ConfigurationSettings.AppSettings["dsn"];
string sProc = "prGet_HIP";
using (SqlConnection oCn = new SqlConnection(sConnString))
{
using (SqlCommand oCmd = new SqlCommand(sProc, oCn))
{
oCn.Open();
oCmd.CommandType = CommandType.StoredProcedure;

oCmd.Parameters.Add("@sLicenseYear", SqlDbType.SmallInt,
2);
oCmd.Parameters["@sLicenseYear"].Value = intLicYear;

SqlDataAdapter oDa = new SqlDataAdapter();
oDa.SelectCommand = oCmd;
DataSet ds = new DataSet();
oDa.Fill(ds);
DataRow[] foundRows = ds.Tables[0].Select();

chkRailsNo.Tag = foundRows.GetValue(0, 0);
chkRailYes.Tag = foundRows.GetValue(0, 0);
cboRails.DataSource = ds.Tables[1];
cboRails.DisplayMember = "Description";

chkWoodcockYes.Tag = foundRows.GetValue(1, 0);
chkWoodcockNo.Tag = foundRows.GetValue(1, 0);
cboWoodcook.DataSource = ds.Tables[1];
cboWoodcook.DisplayMember = "Description";


}
}

 

[Kevin Yu [MSFT]]

Hi Cadel,

You're getting this error, because the foundRows is a one dimention array.
With this array, you can only get the DataRow with FoundRows.GetValue(0).
Or you can just use the index to get the DataRow directly. It returns a
DataRow object and then you get what you need from the DataRow. Change to
the following:

DataRow dr = foundRows[0];
chkRailsNo.Tag = dr[0];

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

That worked, but I see another problem. My combo boxes are all linked
together, so when the user changes a value in a combo box, all the combo
boxes get changed. How do I populate the collection of each combo box and
have each combo box indepentant?

Management didn't want the checkboxes, so now I'm only working with the
combo boxes.

string sProc = "prGet_HIP";
using (SqlConnection oCn = new SqlConnection(sConnString))
{
using (SqlCommand oCmd = new SqlCommand(sProc, oCn))
{
oCn.Open();
oCmd.CommandType = CommandType.StoredProcedure;

oCmd.Parameters.Add("@sLicenseYear", SqlDbType.SmallInt,
2);
oCmd.Parameters["@sLicenseYear"].Value = intLicYear;

SqlDataAdapter oDa = new SqlDataAdapter();
oDa.SelectCommand = oCmd;
DataSet ds = new DataSet();
oDa.Fill(ds);
DataRow[] foundRows = ds.Tables[0].Select();

DataRow dr = foundRows[0];
cboRails.Tag = dr[0];
cboRails.DataSource = ds.Tables[1];
cboRails.DisplayMember = "Description";

cboWoodcock.Tag = dr[0];
cboWoodcock.DataSource = ds.Tables[1];
cboWoodcock.DisplayMember = "Description";


}
}

 

[Kevin Yu [MSFT]]

Hi Cadel,

The comboboxes are linked because their values are binding to the same
datasource. Bind to different data sources so that they won't change
together.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."