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?
 
brettawvConnect With a Mentor Author 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
 
Bane83Commented:
SqlDataSource1.SelectCommand = SqlCmd
MyGrid.DataBind()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.