losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

Loops to create a page!

Hi,

Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,
basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.

Get all the table names from a database
Loop through each of the results to build a statement
Nest a 2nd loop to split the returned data from the correct table
Build a listbox for each table returned

This is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new
code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please note
I cut this down to just show 2 result but there is about 30 odd.

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")2'Dim DBDataAdapter As SqlDataAdapter3 DBDataAdapter =New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @.ddlTitle", DBConn)4 DBDataAdapter.SelectCommand.Parameters.Add("@.ddlTitle", SqlDbType.NVarChar)5 DBDataAdapter.SelectCommand.Parameters("@.ddlTitle").Value = TitleDropDown.SelectedValue6 DBDataAdapter.Fill(DSPageData,"Courses")78'Loop through each record, split at + and place in ListBoxs910 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")11Dim VarArrayAs String() = VarDS.Split("+")12Dim iAs Integer13 For i = 0To VarArray.Length - 11415Dim liAs New ListItem()16 li.Text = VarArray(i).ToString()17 li.Value = VarArray(i).ToString()18Me.txtAOE.Items.Add(li)19Next i2021 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")22 VarArray =Nothing23 VarArray = VarDS.Split("+")24 i =Nothing25 For i = 0To VarArray.Length - 12627Dim liAs New ListItem()28 li.Text = VarArray(i).ToString()29 li.Value = VarArray(i).ToString()30Me.txtADC.Items.Add(li)31Next i

Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,
I know the split bit works, so at the minute I am just trying to get the loop to get all my courses

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"All")12'declare all loop vars13Dim XAs Integer14 Dim YAs Integer15 Dim iAs Integer16'Loops through all the tables17Dim DSArrayAs String() = DSPageData.Tables("All").Items()18For Y = 0To Count19Dim VarDSAs String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))20Dim SplitArrayAs String() = VarDS.Split("+")212223For i = 0To SplitArray.Length - 124Dim LiAs New ListItem()25 Li.Text = SplitArray(i).ToString()26 Li.Value = SplitArray(i).ToString()27Me.txt & DSArray(Y) &.Items.Add(Li)28Next i2930Next Y

I have just realised, in each loop for each course I will need to add the select statement so I think I need to add

DBDataAdapter = New SqlDataAdapter("SELECT " & Dr & "FROM TBL_" & DSArray(Y), DBConn)
DBDataAdapter.Fill(DSPageData, "Courses")

Into it

|||

ok think I am making some head way and I think I have logically what I want.

For every row of column TName in TBL_ALL, assign to var Dim TName
For every row of column PKey in TBL_ALL, assign to var Dim Pkey
For every row create statement "SELECT '" & Pkey & "' FROM '" & TName " "'")

Once I have made the loop that creates all of them select statements, I can run a loop to execute them,
and then a loop to assign the values to listboxes, but first step at a time.

Here is what I have so far, it doesn't work and I know its not right, but maybe someone can see where I am comming from
point my in a better direction. Please also note, the response.write bits are to check when my code i have working is working
but they are not needed

Thanks
Chris

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;PWD=xx;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT TName FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"TName")12 DBDataAdapter =New SqlDataAdapter("SELECT PKey FROM TBL_All", DBConn)13 DBDataAdapter.Fill(DSPageData,"PKey")14'declare all loop vars1516Dim DrAs DataRow17Dim DcAs DataColumn18'Loops through all the tables1920 'For Each Dr In DSPageData.Tables("All").Rows21 'For Each Dc In DSPageData.Tables("All").Columns22 'Dim Column As DataColumnCollection = DSPageData.Tables("All").Columns23 'Dim Table As String = (Dr(Dc.ColumnName.ToString))24 'Response.Write(Table)25 'DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;UID=wgw;PWD=wgwsql")26 'DBSelect.Connection = DBConn27 'DBSelect.Connection.Open()28 'DBSelect.CommandText = "SELECT '" & Dr(Dc.ColumnName.ToString) & "' FROM ('" & Table & "')"29 'DBSelect.ExecuteNonQuery()30 'Next Dc31 'Next Dr3233Dim Search()As SqlDataAdapter34Dim PKeyAs String35 Dim TNameAs String36 Dim ResultAs String()37For Each DrIn DSPageData.Tables("TName").Rows38For Each DcIn DSPageData.Tables("TName").Columns39'For i = 0 To Count40 PKey = (Dr(Dc.ColumnName.ToString))41 Response.Write(PKey)42 TName = (Dr(Dc.ColumnName.ToString))43 Response.Write(TName)44'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"45 'DBResult = DBSelect.ExecuteNonQuery46 'Result(i) = DBResult47 'Next48Next49 Next50 For Each DrIn DSPageData.Tables("PKey").Rows51For Each DcIn DSPageData.Tables("PKey").Columns52'For i = 0 To Count53 PKey = (Dr(Dc.ColumnName.ToString))54 Response.Write(PKey)55 TName = (Dr(Dc.ColumnName.ToString))56 Response.Write(TName)57'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"58 'DBResult = DBSelect.ExecuteNonQuery59 'Result(i) = DBResult60 'Next61Next62 Next63 End Sub

|||

maybe I am totally confusing everyone lol? I am so lost :(

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