How to get a single value out of a dataset

[JEFF]

There is most likely a better way to do what I am trying to do here so any
advice on a better solution would be greatly appreciated. I have a table of
events in an Access db. I want to get the eventid of record to feed to an
aspx page via QueryString. Here is what I am trying:

Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()
cn = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\MyApp\App_Data\spsi.mdb")
cmd = New System.Data.OleDb.OleDbDataAdapter("SELECT Events.ID
FROM(Events)WHERE (((Events.Title)="""" & MyTitle /", cn)
cn.Open()
Try
cmd.Fill(ds)
Catch
End Try
cn.Close()

So, now I have this dataset that has 1 row and one column that contains an
integer number. How can I reference that as a string?

Also, how do I encapsulate my MyTitle in quotes?

Thanks in advance!
Jeff

 

[Alec MacLean]

Using indexes in your dataset object hierarchy:

ds.tables(0).rows(0).item(0).tostring

Or, using known table and column names on the dataset object hierarchy:

ds.tables("Events").rows(0).item("ID").tostring

I haven't used Access for years now, so any comment by me on this would be
pure guesswork only.
SQL Server supports stored procedures, which I think you can emulate in
Access as saved queries with parameters.

If that is the case, you could make a reference to a stored procedure in
code, which handles adding parameter values in the correct format without
having to use visually messy escape sequences for quotes, etc.

E.g. This snippet is using the DAAB (free data library fom Microsoft, but
may be a bit OTT for your needs), but there are similar processes in normal
ADO.NET;
'Create a database and command object. Use the connection's default data
connection for "db" (set in application config file).

Private db As Database = DatabaseFactory.CreateDatabase()

Private dbCmd As DbCommand

'Specify the stored proc (query) to use.

dbCmd = db.GetStoredProcCommand("usp_GetIDFromTitle")

'Add parameter

db.AddInParameter(dbCmd, "Title", DbType.String, MyTitle)

'Run command and get the data - loads the result rows into dataset "ds",
using table name "Events".

db.LoadDataSet(dbCmd, ds, "Events")

Do you really need to use Access as your datasource? MS provide FREE
editions of SQL Server 2005 for desktop installs.
http://www.microsoft.com/sql/editions/express/default.mspx

Use of the quoted SQL statement is now considered to be very insecure, as it
opens you up to potential SQL attacks that could purge or modify all your
data. Use of stored procedures helps mitigate against this risk.

Alec

 

[Miha Markic]

You might consider using OleDbCommand.ExecuteScalar method instead.

 

[Jesús López]

You should use a parametrized query to improve performance and avoid sql
injection attacks.

To get a single value form a query, ExecuteScalar() is appropriate.

Dim EventID As String

Using cn As New OleDbConnection("<the connection string>"), _
cmd As New OleDbCommand("SELECT ID FROM Events WHERE Title = ?",
cn)

cmd.Parameters.AddWithValue("Title", MyTitle)

EventID = cmd.ExecuteScalar().ToString()
End Using

 

[Cor Ligthert[MVP]]

alec,

ds.tables(0).rows(0).item(0).tostring

In my idea is this the only applicable answer.

Let us not make it more difficult on a simple question than it is.

Cor