Sunday, March 25, 2012

Accessing data from a programmatically created SqlDataSource

Hi

I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example??

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsNew Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

'Create a SqlConnection instance

Using connString

'Specify the SQL query

Const sqlAsString ="SELECT eventID FROM viewEvents WHERE eventID=17"

'Create a SqlCommand instance

Dim myCommandAsNew Data.SqlClient.SqlCommand(sql, connString)

'Get back a DataSet

Dim myDataSetAsNew Data.DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew Data.SqlClient.SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet)

Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -??????

'Close the connection

connString.Close()

EndUsing

EndIf


Thanks for any help
Richard

No, you haven't programmatically created a SqlDataSource. You have used plain ADO.NET code to create and fill a DataSet. The DataAdapter is purely a bridge between the dataset and your data source (the database). It doesn't contain tables. The Dataset does though. It holds them in a zero-based collection:

Label1.Text = MyDataSet.Tables(0).Rows(0)("eventID").ToString()

But if all you want is one value from the database, you are better off using Command.ExecuteScalar():

Dim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Using connString
Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17"
Dim myCommand As New Data.SqlClient.SqlCommand(sql, connString)
Label1.Text = mycommand.ExecuteScalar().ToString()
...etc

No comments:

Post a Comment