Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic SQL statement in aspx page

Posted on 2008-11-10
2
Medium Priority
?
601 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:brettawv
2 Comments
 
LVL 10

Expert Comment

by:Bane83
ID: 22924737
SqlDataSource1.SelectCommand = SqlCmd
MyGrid.DataBind()
0
 

Accepted Solution

by:
brettawv earned 0 total points
ID: 22924845
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question