Hi

I have  a really simple question (hoppefully), I have created a details page that uses a dataset.  So the dataset is only ever filled with one row of data.  I want to put the dataset values into variouse labels on the  page.  But Im not sure of the syntaxt to display a record from a dataset.  Here is a piece of my code:

Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("connBSS"))
Conn.Open()
Dim dsDetails As New DataSet()
Dim strSQL As String = "SELECT SvpsName, SvpsID FROM Services"
Dim objAdapter As New SqlDataAdapter(strSQL, Conn)
objAdapter.Fill(dsDetails, "Details")
Dim objDataView As New DataView(dsDetails.Tables("Details"))

I thought I could display display a value by doing something like:

lblSvpsName.text = DataBinder.Eval(objDataView, "SvpsName")

But it didnt work, as you can tell I am new to this ASP.NET, thanks in advance

Hi BigMeat,

You don't need to open your connection or even close it.
The Fill method retrieves rows from the data source using the SELECT
statement specified by an associated SelectCommand property.
The connection object associated with the SELECT statement must be valid,
but it does not need to be open. If the connection is closed before Fill
is called, it is opened to retrieve data, then closed. If the connection
is open before Fill is called, it remains open.

Also, I don't think you need any DataView. To get only the first row use this:

dsDetails.Tables("Details").Rows(0).Item("SvpsName")

Something like this:

Dim dsDetails As New DataSet()
Dim Conn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connBSS"))
Dim objAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT SvpsName, SvpsID FROM Services", Conn)

objAdapter.Fill(dsDetails, "Details")

lblSvpsName.Text = dsDetails.Tables("Details").Rows(0).Item("SvpsName")
lblSvpsID.Text = dsDetails.Tables("Details").Rows(0).Item("SvpsID")

i dont even know why you would use a dataset in that situation...

could just use a SQLDataReader as reference it like school recordset-like.. one less object to create :-)

Whenever you have a one-time hit of data from a database, use a DataReader,
it's much faster than a DataSet. Dataset uses a DataReader to get its data anyway.

If you are requesting data that is going to be used or accessed multiple times,
use the DataSet. It grabs the data from the table, puts it in memory and then
closes the connection.

DataReader can't as it is read-only, forward only so if you need same data twice,
with DataReader you'd have recreate the DataReader every time data is reused,
with DataSet you have the data to be used as many times you like.

Cheers Otaku

You are a star.... i really appreciate all your help

The reason I used a dataset is because the above example does not show the full sql statement I used, the real one pulls values from two tables.  Which I dont think is possible form a datareader (dont hold me to that as I new to all of this)

But I might aswell ask.... can I join two tables and still use the read method?

Thanks in advance

Im really sorry to be a pain, but I have just come accross another problem with my first application.  The website works perfectly fine on my development machine.  However if any body else tries to view it from there machine they get an error saying:

"An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine."

However I have noticed that this error only occurs on pages that are datadriven from a database.  

I have the following security settings:

<add key="connBSS" value="Password=BSS_WClient;Persist Security Info=False;User ID=BSS_WClient;Initial Catalog=BSS;Data Source=bsswk1" />

<identity impersonate="true"/>
<customErrors mode="RemoteOnly"/>
<authentication mode="Windows"/> tried None aswell but didnt work
<allow users="*"/>

I cant really understand why this happens?  Is it a sql server problem?  Sorry to be a pain... but I will get use to this ASP.NET eventually

try this web.config

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>

<appSettings>
<add key="connBSS" value="Password=BSS_WClient;Persist Security Info=False;User ID=BSS_WClient;Initial Catalog=BSS;Data Source=bsswk1" />
</appSettings>

<system.web>
<customErrors mode="Off"/>
</system.web>

</configuration>

Thanks guys.... you have been a great help.....

Im so glad i joined this forum

Hi

Im try to get my head round datasets being typed and untyped and  had a query.  Am i write in saying that a typed dataset has to have a schema which comes .xsd file.  An untyped dataset does not have a schema and just contains tables, row and columns. 

So im just wondering how do I create a strongly typed dataset programmatically, i dont know how to create a .xsd file, however I notice I can write the dataset out to an xml file (does this make it strongly typed).  I notice that Visual Studio creates the .xsd file for  you easily enough but im trying to stay away from the GUI side of things and doing it all by hand code first.

So for example how would i make the below example typed?  And do I need to make sure every dataset I create is typed. 

Dim dsDetails As New DataSet()
Dim Conn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connBSS"))
Dim objAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT SvpsName, SvpsID FROM Services", Conn)

objAdapter.Fill(dsDetails, "Details")

lblSvpsName.Text = dsDetails.Tables("Details").Rows(0).Item("SvpsName")
lblSvpsID.Text = dsDetails.Tables("Details").Rows(0).Item("SvpsID")

Thanks in advance