Solved

Auto-build UpdateCommand Statement

Posted on 2008-06-10
9
553 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
  • 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 300 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now