Single Field Value from SQL Database -> To Hashtable

I'm not sure this is the right place for this question, but I couldnt decide which forum to put it in. Here's a summary of my issue. I have an attached relational SQL database with two related tables looking like this

Table 1

Product_ID (PK, AutoIncrement)

ProductName

...details

Table 2

Product_ID (FK, Integer)

...details

What I'm trying to do, is through an SQL query, return 1 result...which I am able to do, but then I would like to get a specific field from that result and use it's value to load into my hashtable. Here's an example of what I'm upto.

ProtectedFunction GetPackagePrice(ByVal SelectionAsString)AsString

Dim SQLAsString

SQL ="SELECT product.product_id, product.product FROM product INNER JOIN Pprices ON product.Product_ID = Pprices.Product_Id WHERE (product.Product = 'Value') AND (Pprices.Users = 'Value')"

Dim cnnAsNew SqlConnection("Data Source=(removed)")

Dim SelectCommandAs SqlCommand =New SqlCommand(SQL, cnn)

EndFunction

This example correctly retuns a single row, which works great. What I need to do with that single row is grab a field value and load it into a hashtable using the string return of my function as the value for my hashtable key's value. Here's an example of what I'm doing with my hashtable.

Dim FormValuesAs Hashtable

FormValues =New Hashtable

FormValues.Add("Package", DropDownList1.SelectedValue.ToString)

FormValues.Add("Users", DropDownList2.SelectedValue.ToString)

FormValues.Add("Void", Calendar1.SelectedDate.ToString)

FormValues.Add("SalesPerson", DropDownList3.SelectedValue.ToString)

FormValues.Add("Company", TextBox1.Text)

FormValues.Add("Contact", TextBox2.Text)

FormValues.Add("Fax", TextBox3.Text)

Everything is working fine at this point, what I dont know how to do, is refer to the single field value in my returned SQL row to return it in my function. If someone could help me out with figuring out how to get the SQL returned results into a readable form where I can reference the fields with some type of bind, that would be excellent.

Thanks.

What is your data being returned into? DataTable, DataSet ?

NC...

Right now, it's not being returned into anything. I wasnt sure which way to go with it. I'm leaning towards using a datatable, that way I can reference it by column().row() but that didnt seem like an elegant solution to me. Any advice or sample code for getting a single value?

Freakyuno,

Sorry it took me so long to reply. I've been very busy the last few days finishing up a critical project.

But here goes. I've always just created a DataTable and returned the value in that situation, since my data layer originally only returned DataTables and DataSets, and I like to keep the code to a minimum.

Here's a method that I have in my data access layer that does just what you're looking for (I think).

C#
--

public string ReadValue(string sqlStatement, string columnName)
{
string returnValue = string.Empty;

try
{
DataTable dataTable = this.CreateDataSource(
"TempDataTable", sqlStatement);

if ( dataTable.Rows.Count > 0 )
{
DataRow dataRow = dataTable.Rows[0];

returnValue = dataRow[columnName].ToString().Trim();
}
}
catch
{
throw;
}

return returnValue;
}

public DataTable CreateDataSource(string tableName, string sqlStatement)
{
DataTable returnValue = new DataTable(tableName);

SqlDataReader dataReader = null;
SqlConnection dbConnection = null;
SqlCommand dbCommand = null;

try
{
// Open the Connection object...
dbConnection.ConnectionString = "connection string goes here";
dbConnection.Open();

// Set up the Command object...
dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandText = sqlStatement;
dbCommand.CommandType = CommandType.Text;

// Instantiate the DataReader...
dataReader = dbCommand.ExecuteReader();

// Add the column names to the DataTable...
for (int i=0; i<dataReader.FieldCount; i++)
{
DataColumn newDataColumn = new DataColumn(dataReader.GetName(i), typeof(string));

returnValue.Columns.Add(newDataColumn);
}

// Add the column values to the DataTable...
while ( dataReader.Read() )
{
// Create a new row...
DataRow newDataRow = returnValue.NewRow();

// Add the column values...
for (int i=0; i<dataReader.FieldCount; i++)
{
// Add all columns as strings...
newDataRow[i] = dataReader[i].ToString();
}

// Add the row to the DataTable...
returnValue.Rows.Add(newDataRow);
}
}
catch
{
throw;
}
finally
{
if ( dbCommand != null )
dbCommand.Dispose();

if ( dataReader != null )
dataReader.Close();

if ( dbConnection != null )
dbConnection.Close();
}

return returnValue;
}

VB
--

Public Function ReadValue(sqlStatement As String, columnName As String) As String
Dim returnValue As String = String.Empty

Try
Dim dataTable As DataTable = Me.CreateDataSource("TempDataTable", sqlStatement)

If dataTable.Rows.Count > 0 Then
Dim dataRow As DataRow = dataTable.Rows(0)

returnValue = dataRow(columnName).ToString().Trim()
End If
Catch
End Try

Return returnValue
End Function

Public Function CreateDataSource(tableName As String, sqlStatement As String) As DataTable
Dim returnValue As New DataTable(tableName)

Dim dataReader As SqlDataReader = Nothing
Dim dbConnection As SqlConnection = Nothing
Dim dbCommand As SqlCommand = Nothing

Try
' Open the Connection object...
dbConnection.ConnectionString = "connection string goes here"
dbConnection.Open()

' Set up the Command object...
dbCommand = New SqlCommand()
dbCommand.Connection = dbConnection
dbCommand.CommandText = sqlStatement
dbCommand.CommandType = CommandType.Text

' Instantiate the DataReader...
dataReader = dbCommand.ExecuteReader()

' Add the column names to the DataTable...
Dim i As Integer
For i = 0 To dataReader.FieldCount - 1
Dim newDataColumn As New DataColumn(dataReader.GetName(i), GetType(String))

returnValue.Columns.Add(newDataColumn)
Next i

' Add the column values to the DataTable...
While dataReader.Read()
' Create a new row...
Dim newDataRow As DataRow = returnValue.NewRow()

' Add the column values...
Dim i As Integer
For i = 0 To dataReader.FieldCount - 1
' Add all columns as strings...
newDataRow(i) = dataReader(i).ToString()
Next i

' Add the row to the DataTable...
returnValue.Rows.Add(newDataRow)
End While
Catch
Finally
If Not (dbCommand Is Nothing) Then
dbCommand.Dispose()
End If
If Not (dataReader Is Nothing) Then
dataReader.Close()
End If
If Not (dbConnection Is Nothing) Then
dbConnection.Close()
End If
End Try

Return returnValue
End Function

I believe that you could possibly use ExecuteScalar to return a single value. According to the book, it returns the first column of the first row of the first results set, never really tried it though except on SELECT COUNT statements. In C# it would be:

returnValue = (string)dbCommand.ExecuteScalar();

HTH,
NC...

NC01,

Thats absolutly perfect. Thanks for the help on this one. I modified your code slightly to include the connection string from my web config, and worked a little bit with the Scalar example, which I wasnt as pleased with.

Great work. :)

Freakyuno,

Glad to be of help. Like I said, I've only used the ExecuteScalar to return the value from a SELECT COUNT SQL statement. The method uses a DataReader anyways, turning it into a DataTable, which should be just about as fast.

I know that you'll reciprocate when I get into a jam!

NC...