Looping through SQLDataSouce in a Function

Hi All,

Thanks for looking at the post. Here is what I am trying to do:

Lets say I have dragged and dropped a new SqlDataSource onto the page and called it SQLDataSource2

Now, in a function that happens afterload:

ProtectedSub Partners_afterLoad(ByVal senderAsObject,ByVal eAs System.EventArgs)

I want to be able to loop through that SQLDataSource2, and pull a value to compare.

In classic ASP it would be something like:

'Move to the first record
SQLDatasource.MoveFirst

'Loop through Recordset
While Not SQLDataSource2.Eof

'Do compare of the Field MyValue
If SQLDataSource2("MyValue")=27 Then Reponse.Write "Hit on # 27"

'Move to Next Record
SQLDatasource.MoveNext

'End the loop
Wend

My questions are as follows:

1) Do I need to define the Datasource in the function, or can I use the one that VWD2005 helped me to define.

2) How can I loop throught that Datasource (Recordset) in a function.

Any and all help appreciated--Code examples will make me your best friend.

Rather than loop through the entire dataset, I'd use the power of the SQL engine to speed things up a little.

There's a property called "filterexpression". I'd filter the dataset to just return a single row if it exists, and no rows if it doesnt, and you can very quickly check for the existance of a row or not.

Here's an article describing how to use the .filterexpression property, you can combine it with .filterparamaters.

You can access the same property from the codebehind using the Sqldatasource2.filterexpression and SqlDatasource2.filterparamaters method.

http://www.aspnetpro.net/newsletterarticle/2003/11/asp200311ss_l/asp200311ss_l.asp

|||

You can do much of the same thing in ASPX, but the sqldatasource control really wasn't designed for that type of thing. Try the sqlconnection, sqlcommand, and the datareader classes, and it'll be darn near what you are used to.

dim conn as new sqlconnection("{Connection string here}")

conn.open

dim cmd as new sqlcommand("SELECT whatever FROM whereever",conn)

dim dr as datareader=cmd.executedatareader

while dr.read

if dr("whatever")=some value then

' Do something here

end if

end while

dr.close

conn.close

|||

You might even be able to do (I have never tried this, so it might not work)

ForEach drAs DataRowInCType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView).ToTable.Rows

if dr("whatever")=some value then

' Do something

end if

Next

|||

Thanks for the reply Freakyuno, unfortunately I do have to loop through the recordset since I am ultimately trying to prepropulate checkboxes from a different table, not just checking to see if the records exists.

Thanks for the link!

|||

Motley!

You are the man! Here was the final code I used:

ForEach drAs Data.DataRowInCType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), Data.DataView).ToTable.Rows

Not sure why I had to do the Data.DataRow thing--but that's what it wanted.

Again, thanks!

|||

Probably because you didn't have something like

imports system.data

at the top of your script, sorry, I normally have that.

|||

Probably because you didn't have something like

imports system.data

at the top of your script, sorry, I normally have that.

There might be a better way of doing it, but that's the first way I came up with. If anyone knows of an easier way, I'd like to hear it too :-)

sql