Pulling Multiple columns from one sql statement?

[Brad Isaac]

Hi,

I am trying to cut down on the sheer length of some of my SQL commands
-- especially in reference to pulling column data.

For example, I have one table that has columns named Option 1, Option
2, Option 3 etc.

I created a for / next statement that runs through the same number of
iterations as there are Option columns

Here is the code I tried getting the data from all of the option
colums - it fails because the "SqlVar" variable doesn't seem to grab
the SQL data correctly. If I change from the SqlVar string to
"Option1" it does pull data, although it just pulls one column

I've highlighted the trouble spot with '{{{{ }}}} to make it easy to
find


For x = 1 To 9
Rower = "Row2"

Dim ST(x) As String
Dim S(x) As System.Data.SqlServerCe.SqlCeCommand

Dim DTR(x) As System.Data.SqlServerCe.SqlCeDataReader
'Dim steps As String
SqlVar = "Option" & x
S(x) = New System.Data.SqlServerCe.SqlCeCommand("select '"
& SqlVar & "' from masterlist where RowNum = '" & Rower & "'", cn)

DTR(x) = S(x).ExecuteReader
While DTR(x).Read

' {{{{The SqlVar below always fails. If I change this to "Option1"
It pulls the data from the first column!}}}}
ST(x) = DTR(x)(SqlVar)

End While
Next

Does anyone here have any recommendations or a workaround?

Many thanks,
Brad

 

[Alex Feinman [MVP]]

What exactly is wrong with retrieving the whole row and iterating through
SqlCeDataReader items? I mean this (below) can't possibly be more efficent
under any circumstances I can imagine

 

[Brad Isaac]

What exactly is wrong with retrieving the whole row and iterating through
SqlCeDataReader items?

Nothing is wrong with it except I wasn't doing it that way in the
first place - LOL

I mean this (below) can't possibly be more efficent
under any circumstances I can imagine

Oh, you should have seen it before I started chopping it down

I guess I'll hit the books and try again.

Thanks,
Brad

 

[Brad Isaac]

What exactly is wrong with retrieving the whole row and iterating through
SqlCeDataReader items? I mean this (below) can't possibly be more efficent
under any circumstances I can imagine

Ok,

Now I remember why I didn't do it that way. I was having issues
getting the data to list properly. I'll hack at it some more and see
if I can get it working.