?
Solved

Auto-build UpdateCommand Statement

Posted on 2008-06-10
9
Medium Priority
?
572 Views
Last Modified: 2012-08-14
Hello:

I am writing an application that changes the datagrid dynamically when a dropdown value is changed.

Works fine - but I'd like for it to auto-generate the updatecommand for me when the selectcommand is changed.

Using this code:
 SqlDataSource1.SelectCommand = "select * from tableA"
   
In this case; you can edit the datagrid; the updatecommand is built for me at compile.

When the dropdown is changed using
    SqlDataSource1.SelectCommand = "select * from " & DropDownList1.Text

The datagrid updates fine, but the updatecommand is still using the previous sql.

I can build an updatecommand statement with parameters for every possible dropdown table (25 diff tables); but If there is an easy way to force it to 'update' the updatecommand based on a new selectcommand; that would be optimal.

Thanks for any help.


       
0
Comment
Question by:sny23vpb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 2

Expert Comment

by:gurutodd
ID: 21759598
I'm doing something very similar...mySQLString = "select * from " & ComboBox1.SelectedItem.ToString & " where invoicetype = 0"

I use a button to update mySQLString and repopulate the grid, but you should have the new selected value on ComboBox1_SelectedIndexChanged if you wanted to fire the update then.  Try selecteditem and see what that does...
0
 

Author Comment

by:sny23vpb
ID: 21762213
Thanks. I tried that; when I click the auto-generated update button on the grid; it throws the error :  Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.

Is the update command re-building for you based on the table you choose in the dropdown ?
0
 
LVL 2

Expert Comment

by:gurutodd
ID: 21762476
I don't use any wizards, my grid is populated via code.  

Dim myDataSet As DataSet
myDataSet = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(My.Settings.DBString2, CommandType.Text, mySQLString)
DataGridView1.DataSource = myDataSet
DataGridView1.DataMember = myDataSet.Tables(0).ToString
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sny23vpb
ID: 21762736
Mine is not completely code driven; I have a gridview and sql data source objects defined and then I just populate the selectcommand for the data source dynamically; I can change mine to code similar to what you have above if you think that would auto generate the updatecommand="update table1 set field1=@field1"  etc for me.

When I do work with your code; it tells me applicationblocks are not a part of microsoft. I'm running VS 2005 ; do I need some specific references / ?
thanks
0
 
LVL 2

Expert Comment

by:gurutodd
ID: 21762885
I use application blocks, but you don't need it.  I find it easy to work with data using it.  http://www.microsoft.com/downloads/details.aspx?familyid=F63D1F0A-9877-4A7B-88EC-0426B48DF275&displaylang=en
I think you can make it work, I update rows with that grid as well...
0
 

Author Comment

by:sny23vpb
ID: 21763506
Thanks. I apologize : I'm new to .net.  I downloaded this and was expecting to see a reference to check when I restarted VS 2005 ;  Not sure how to implement it after running it.

Likewise I'm not seeing a datagridview ; I only have a gridview object.

Perhaps once I reference it properly I'll have use of that object, but how should  I do that.

I'm increasing points for your trouble.
0
 
LVL 2

Expert Comment

by:gurutodd
ID: 21768692
Lets skip the application blocks right now...Try this...

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        update_grid()
    End Sub
    Public Sub update_grid()
        Dim SQL_String As String = "select top 10 * from " & DropDownList1.SelectedValue
        Dim connection As Data.SqlClient.SqlConnection
        connection = New Data.SqlClient.SqlConnection("server=A0116-DBS0133-S;Integrated Security=SSPI;Initial Catalog=APAY")
        connection.Open()
        Dim cmd As New Data.SqlClient.SqlCommand
        cmd.CommandTimeout = 30
        Dim ds As New Data.DataSet
        Dim dataAdatpter As Data.SqlClient.SqlDataAdapter
        cmd.Connection = connection
        cmd.CommandText = SQL_String
        cmd.CommandType = Data.CommandType.Text
        Try
            ' Create the DataAdapter & DataSet
            dataAdatpter = New Data.SqlClient.SqlDataAdapter(cmd)

            ' Fill the DataSet using default values for DataTable names, etc
            dataAdatpter.Fill(ds)

            ' Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear()
        Finally
            If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
        End Try
        connection.Close()
        If Not connection Is Nothing Then connection.Dispose()
        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        update_grid()
    End Sub
0
 

Author Comment

by:sny23vpb
ID: 21771581
ok. The code works great. I created a gridview2 and adjusted the code accordingly. Pulls the table based on the drop down selection. I also added autogenerateeditbutton so you could make edits in the data grid. (see code below) ; but when I click edit I get the following error message instantly.  Are you not getting this or are we handling the edit process differently ?

Thanks again.

-----------------------------------  


<asp:GridView ID="GridView2" runat="server" AutoGenerateEditButton="True" AutoGenerateDeleteButton="True">
        </asp:GridView>

--------------------------------------------------------------------------------
Server Error in '/Control_data_maint_app' Application.


The GridView 'GridView2' fired event RowEditing which wasn't handled.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The GridView 'GridView2' fired event RowEditing which wasn't handled.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
0
 
LVL 2

Accepted Solution

by:
gurutodd earned 1200 total points
ID: 21779084
i'm still looking at this...mine was in a winform.  I have half of it.   I can't seem to get any values on the update overload...

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        AddHandler GridView1.RowEditing, AddressOf GridView_RowEditing

        AddHandler GridView1.RowUpdating, AddressOf GridView_RowUpdating

        update_grid()
    End Sub


Sub GridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)

        GridView1.EditIndex = e.NewEditIndex
        GridView1.DataBind()


    End Sub

    Sub GridView_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

     
    End Sub

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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