• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2234
  • Last Modified:

SqlDataSource and GridView from Code-Behind

I maintain several databases in several environments (my local machine, test, production).  I am constantly writing little queries to look at the data in the various databases so I thought it would be handy to build a little page where I can write (and I suppose eventually store) common sql queries and view their results.  

So, in my Web.Config file I set up a bunch of connection strings to all my databases - they all work, I've tested them.  

Here is my .aspx page.  Of note, it contains a drop down to select my desired ConnectionString, a text box to type my query, a SQLDataSource, and a DataGrid to view the results.
            <p>
                <asp:DropDownList ID="ConnectionString" runat="server">
                    <asp:ListItem Value="HSMSConnectionString_local">HSMS_Local</asp:ListItem>
                    <asp:ListItem Value="HSMSConnectionString_preprod">HSMS_PreProd</asp:ListItem>
                    <asp:ListItem Value="HSMSConnectionString">HSMS</asp:ListItem>
                    <asp:ListItem Value="HSEConnectionString_local">HSE_local</asp:ListItem>
                    <asp:ListItem Value="HSEConnectionString_preprod">HSE_PreProd</asp:ListItem>
                    <asp:ListItem Value="HSEConnectionString">HSE</asp:ListItem>
                    <asp:ListItem Value="TrainingWebConnectionString_local">TrainingWeb_local</asp:ListItem>
                    <asp:ListItem Value="TrainingWebConnectionString_preprod">TrainingWeb_preprod</asp:ListItem>
                    <asp:ListItem Value="TrainingWebConnectionString">TrainingWeb</asp:ListItem>
                </asp:DropDownList>
            </p>
            <p>
                <asp:TextBox ID="QueryString" runat="server" Font-Names="Arial" Font-Size="12px"
                    Height="128px" TextMode="MultiLine" Width="328px"></asp:TextBox></p>
           <p>
               <asp:Button ID="ExecuteQuery" runat="server" Text="Execute" />&nbsp;</p>
           <p>
               <asp:SqlDataSource ID="QueryAnalyzer" runat="server"></asp:SqlDataSource>
               &nbsp;</p>
           <p>
            <asp:TextBox runat="server" ID="lblMessage"></asp:TextBox>
            <asp:GridView ID="ResultGrid" runat="server"></asp:GridView>
            </p>


Here is my code-behind:


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If QueryString.Text <> "" Then

            QueryAnalyzer.ConnectionString = ConfigurationManager.AppSettings(ConnectionString.SelectedValue)
            QueryAnalyzer.SelectCommand = QueryString.Text
            QueryAnalyzer.DataBind()
            ResultGrid.DataBind()


        End If
    End Sub

Now, this does not produce any errors, which is good.  But it also doesn't produce any  results!  When I select a connection string from the dropdown, type in a simple query and Execute, the page refreshes and nothing is displayed.  Can someone point to the problem?  I'm missing something!
0
Steve Krile
Asked:
Steve Krile
  • 5
  • 3
1 Solution
 
Bob LearnedCommented:
It sounds like a problem with the timing of things.  The SqlDataSource and the GridView are pretty tightly linked, and maybe it tries to data-bind the GridView to the SqlDataSource before the Page_Load event occurs, so you would be too late.

Bob
0
 
Steve KrileAuthor Commented:
So...how could I do this cleaner?  Maybe don't use the SqlDataSource?
0
 
Bob LearnedCommented:
1) I don't like using the SqlDataSource because of the lack of control.

2) If you create an SqlDataAdapter or SqlCommand/SqlConnection/SqlDataReader, then you have a lot more control.

Bob
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Steve KrileAuthor Commented:
Right.  So, not to be to thick or needy...but can you help me with the syntax of that?  Just a cut/paste from something you've done before would be fine.  Specifically, creating a SqlDataAdapter, setting it's connection and select values, grabbing the data, and binding it to my GridView.
0
 
Steve KrileAuthor Commented:
I've tried this:

        If QueryString.Text <> "" Then
            Dim cn As New SqlConnection(ConfigurationManager.AppSettings(ConnectionString.SelectedValue))

            Try
                Dim cmd As SqlCommand = New SqlCommand(QueryString.Text, cn)
                cmd.CommandType = Data.CommandType.Text
                cmd.Connection.Open()
                ResultGrid.DataSource = cmd.ExecuteReader()
                ResultGrid.DataBind()
                cmd.Connection.Close()
                cmd.Connection.Dispose()
            Catch ex As Exception
                lblMessage.Text = ex.Message
            End Try
        End If

and get this in the lblMessage:

The ConnectionString property has not been initialized.
0
 
Bob LearnedCommented:
It almost sounds like you are pulling the connection strings from the wrong place:

   ConfigurationManager.AppSettings(ConnectionString.SelectedValue)

Usually, it is like this:

  Dim connectionString As String = ConfigurationManager.ConnectionStrings(ConnectionString.SelectedValue).ConnectionString

Bob
0
 
Steve KrileAuthor Commented:
Nice.  Thanks...that did it!
0
 
Steve KrileAuthor Commented:
Here is the Working Code:

        If QueryString.Text <> "" Then
            Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings(ConnectionString.SelectedValue).ConnectionString)

            Try
                Dim cmd As SqlCommand = New SqlCommand(QueryString.Text, cn)
                cmd.CommandType = Data.CommandType.Text
                cmd.Connection.Open()
                ResultGrid.DataSource = cmd.ExecuteReader()
                ResultGrid.DataBind()
                cmd.Connection.Close()
                cmd.Connection.Dispose()
            Catch ex As Exception
                lblMessage.Text = ex.Message
            End Try
        End If
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now