First, think of everything you need to do as accessing *collections*. The
sample code below retrieves a free-standing DataTable (which might most
closely resemble - conceptually - the old RecordSet from legacy ADO or DAO;
but it's still quite different). Note that the DataTable is a collection of
objects... long gone is the concept of "current row". The DataTable is made
up of a *collection* of DataRow objects - which in turn has a collection of
columns. Your job is to navigate this hierarchy of collections to get to the
piece of data you are trying to locate. Because all this involves
collections of generic object types, you will need to convert the final
value you want to the correct data type (int, string, or whatever) from the
generic object type.
Note that while I only have C# example, but you could easily find similar
examples on the Web that use VB.
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
DataTable myDataTable = new DataTable();
SqlCommand mySqlCommand = new SqlCommand("MyStoredProcedureName");
mySqlCommand.CommandType = CommandType.StoredProcedure;
//Open a connection to DB (assuming local development machine; definitely
use a different/more secure connection string value for production use)
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = "Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MyDatabaseName;Data Source=(local)";;
myConnection.Open();
mySqlCommand.Connection = myConnection;
//Set a parameter (repeat this logic for additional parameters)
SqlParameter p = new SqlParameter("myParameterName", SqlDbType.Int, 4);
p.Value = 37; //or whatever int value you need
mySqlCommand.Parameters.Add(p);
myDataAdapter.SelectCommand = mySqlCommand;
//Fill a free-standing DataTable
myDataAdapter.Fill(myDataTable);
//Loop through it and retrieve specific values.
if (myDataTable.Rows.Count > 0) {
DataRow myCurrentDataRow;
int loopCounter;
for (loopCounter = 0; loopCounter < myDataTable.Rows.Count; loopCounter++)
{
myCurrentDataRow = myDataTable.Rows[loopCounter];
//Now populate your variables
//NOTE: You must convert to correct DataType because the DataRow otherwise
returns a generic 'object'
myIntVariable = Convert.ToInt32(myCurrentDataRow["SomeIntegerFromDB"]);
myStringVariable = Convert.ToString(myCurrentDataRow["SomeStringFromDB"]);
}
}
Good Luck
"Robin" <ro*******@hotmail.com> wrote in message
news:uA**************@TK2MSFTNGP11.phx.gbl...
In VB.Net I have returned a dataset. How do I loop through a named table
for all fields for each row returned?