Help with DataSet return values

[Rob]

Hi,
I've been going at this for a few days and can't see the problem. Does
anyone see a problem with this code?
I have a call to a function that returns a dataset and when I assign
values to session variables, the values are the actual column names and
not the database value. I've tried using ordinal values and get the same
results.

--here's the code

--sql is defined here

Dim ds = New DataSet

ds = tools.GetDataSet(sql)

If ds.Tables(0).Columns("contact_id").ToString <> "" Then
Session("ContactID") = ds.Tables(0).Columns("contact_id").ToString
Session("CompanyID") = ds.Tables(0).Columns("company_id").ToString
GetContactData(Session("ContactID"), Session("CompanyID"))
ShowPanel(pnlStep1)
Else
lblMessage.Text = "User information not found"
End If

--My function looks like this

Public Shared Function GetDataSet(ByVal SQL As String) As DataSet
Dim cn As String
Dim adapter As SqlDataAdapter
Dim connection As SqlConnection

cn = ConfigurationSettings.AppSettings("CONN_STRING")

Try
connection = New SqlConnection(cn)
connection.Open()

adapter = New SqlDataAdapter(SQL, connection)
Dim myData As New DataSet

adapter.Fill(myData)
Return myData
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
'clean up
adapter.Dispose()
connection.Close()
End Try

End Function

when I refer to ds.tables(0).Columns("contact_id").tostring or
ds.tables(0).columns(9).toString()...the value returned is "contact_id"
rather than 936. I've used this function before and it works fine. Does
anyone see a problem?

Thanks
Rob

 

[Karl Seguin]

you are ToStringing a DataColumn value.... you aren't even specifying a row!

ds.Tables(0).Rows(0)("contact_id")
ds.Tables(0).Rows(10000)("contact_id")

Also

(a) why dim ds = new DataSet ? your GetDataSet is creating a new dataset
(b) turn option strict on it'll save you some headaches
(c) don't needlessly catch exceptions ala:
Catch ex As Exception
Throw New Exception(ex.Message)
that's just repackaging the exception with no added benefit. just remove
the catch and keep your try/finally

Karl

 

[CsaaGuy]

I dont' use the sntax you do but with a datatable off the rows propety
is an item property with i use as in:
Dt.Rows(0).Item("FinalACV").ToString

Hope this helps

 

[Rob]

Thanks for the advice Karl, I've turned option explicit on and fixed a
bunch of other errors as well. As far as declaring a dataset...shouldn't
I be declaring a dataset to accept the dataset that is being passed?
What would I use to reference it's contents if I don't. Also don't I
need a Catch...how can I evaluate the error from the calling function if
it doesn't throw back the error?

Thanks
Rob

 

[Karl Seguin]

I take it the original error was fixed?

You need to declare your DataSet, but not instantiated it..
Dim ds = New DataSet
ds = tools.GetDataSet(sql)

should be

dim ds as DataSet
ds = Tools.GetdataSet(sql)

no need to create a "new" dataset, since GetDataSet is already doing that.
Apologies for not being clear.

Uncaught examples automatically bubble up. By doing:
Catch ex As Exception
Throw New Exception(ex.Message)

you aren't really adding any value. AS a matter of fact, you are making it
worse by emdedding the actual exception (the one with the useful error
information) inside a fictional one. If you don't "catch" it'll bubble up
the original exception - which is what you want.

Karl