Solved

Auto-build UpdateCommand Statement

Posted on 2008-06-10
9
566 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
Independent Software Vendors: 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!

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generate Unique ID in VB.NET 21 128
Data organization issue 7 53
Create a button class for use in all vb.net apps 5 40
vb.net application has warrnings about VB6 calls. 2 40
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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