Dynamic SQL statement in aspx page

I've written a dynamic sql statement in my web applications code behind.  The select statement is created using a while loop.  Everything works fine until I try to edit a line on my aspx page.  I know this is because my records being generated in the code behind statement need to match my records being generated in the aspx page.  Is there a way to make my sql statement in the aspx page match what I have in my code behind?
'Code Behind:
 
SqlCmd = "SELECT * FROM [salesprogresstest] WHERE "
        
SqlCmd2 = "[quotenum] LIKE '%" + vArray(i) + "%' OR "
i = i + 1
 
While i <> vArray.Length -1
     SqlCmd2 = SqlCmd2 + "[quotenum] LIKE '%" + vArray(i) + "%' OR "
     i = i + 1
End While
 
SqlCmd2 = SqlCmd2 + "[quotenum] LIKE '%" + vArray(i) + "%' ORDER BY quotenum"
 
SqlCmd3 = SqlCmd + SqlCmd2
 
 
'aspx page:
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DRSDataConnectionString %>" 
            SelectCommand="SELECT [QuoteNum] FROM [salesprogresstest] WHERE ([QuoteNum] LIKE '%' + @QuoteNum + '%')">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox12" Name="QuoteNum" PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

Open in new window

brettawvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bane83Commented:
SqlDataSource1.SelectCommand = SqlCmd
MyGrid.DataBind()
0
brettawvAuthor Commented:
I figured it out.  All I needed to do was clear out my parameters for the select statement in the aspx page and eliminate the WHERE portion.  
SelectCommand="SELECT [QuoteNum] FROM [salesprogresstest]"

Then in my codebehind page I duplicated the code used for my search button and put it in the SQLDatasource_Selecting Subroutine.  (See Code Snippet)
The important part of this is the

 If TextBox16.Text <> "" Then
            SqlDataSource1.SelectCommand = SqlCmd3
End If

This replaces the Select Command in the aspx page.

All is good.
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
        Dim i As Integer
        i = 0
        Dim watot As Integer
        Dim wqtot As Integer
        Dim SqlCmd As String
        Dim SqlCmd2 As String
 
        watot = 0
        wqtot = 0
 
        Dim vArray As Object
        vArray = Split(TextBox16.Text)
 
        GridView6.Visible = False
        GridView7.Visible = True
        Button2.Visible = False
 
        'Response.Write(vArray.Length.ToString)
 
        SqlCmd = "SELECT * FROM [salesprogresstest] WHERE "
        SqlCmd2 = "[quotenum] LIKE '%" + vArray(i) + "%' OR "
 
        i = i + 1
 
        While i <> vArray.Length
            SqlCmd2 = SqlCmd2 + "[quotenum] LIKE '%" + vArray(i) + "%' OR "
            'Response.Write(vArray(i).ToString)
            'Response.Write("<br />")
            i = i + 1
        End While
 
        i = 0
 
        While i <> vArray.Length - 1
            SqlCmd2 = SqlCmd2 + "[jobnumber] LIKE '%" + vArray(i) + "%' OR "
            'Response.Write(vArray(i).ToString)
            'Response.Write("<br />")
            i = i + 1
        End While
 
        SqlCmd2 = SqlCmd2 + "[jobnumber] LIKE '%" + vArray(i) + "%' ORDER BY quotenum"
 
        SqlCmd3 = SqlCmd + SqlCmd2
 
        'TextBox12.Text = SqlCmd2
        'Response.Write(SqlCmd3)
 
        If TextBox16.Text <> "" Then
            SqlDataSource1.SelectCommand = SqlCmd3
        End If
 
        'SqlDataSource1.DataBind()
        'GridView7.DataBind()
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.