How can I use SqlDataSource to read a row?

The new databound controls are great, but somehow doing the simpler things has gotten harder.The code is in a function that is passed pollid. I want to use SqlDataSource to get the smallest value of answerid where pollid = the value passed for pollid. For example:

"Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"

I can set up the SqlDataSource for a data reader to fill a GridView control, but that isn't what I want to do. In fact, I can't find anything on SqlDataSource that doesn't also involve the GridView control.

I have so far:

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myReader As SqlDataReader

myConnection = New SqlConnection()
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("UserPolls").ConnectionString

myCommand = New SqlCommand()
myCommand.CommandText = "Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"
myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection
myCommand.Connection.Open()

Now assuming I have a single row of data, how do I read the value of Answerid? If I have this wrong, what should I have?

Diane

select top 1 AnswerId from..... order by AnswerId.

|||

Already did that - it's in the example I posted. What I don't know is how to read the data from the record. In classic ASP, if I had a recordset called rs, I would do:

Dim newId as integer

newId = rd("AnswerId")

How do I do this with a SqlDataSource?

Diane

|||

I found an article that helped. A less than perfect but working solution is:

Dim userpoll As New UserPolls
SqlDataSource1.SelectCommand = "Select Top 1 [PollId], [AnswerId] from [PollAnswers] where pollid=" & PollId & " order by AnswerId"
Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
For Each dr As DataRow In dv.Table.Rows
AnswerId = dr("AnswerID")
Next

The For..Next is not needed, but since I'm only returning one record, it won't hurt.

|||

Just a quick addition to what yo already have: you should be using Parameterized Queries. Do a search for it in these forums and you will find how/why to use parameterized queries. In brief, to avoid SQL Injection attacks.

|||

A search turned up three results - your post here, another post by you and one more that also recommends using parameterized quieries. Can you direct me to a tutorial or article?

Diane

|||

Sure: Here's a few:

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

http://davidhayden.com/blog/dave/archive/2005/10/24/2528.aspx

If you are wondering how/where I got them ... Goooooooglee.....