One data source for multiple tables - dynamically changing

[webmasterguy]

Hello,

On my page, I have only one SQL data source and one GridView. I would like to use My Data Source is for different tables I have in the database. SELECT statement works fine. However, when I try to update the data in any other table except the first/default table of datasource then my page for some reason shows the rows of first/default table.

In the 2nd scenario if I remove the "SELECTCOMM AND" and "UPDATECOMM AND" from SQLDataSource on the "aspx" page code view, then I click the Edit button for the first time it doesnt show the gridview. But when for the second time, I select another table from the drop down then it shows that table in the edit mode. So when I try to update the row, I get the following error.

“Updating is not supported by data source 'myDataSource' unless UpdateCommand is specified.”

If you see my code, I am updating the UpdateCommand dynamically.

I am just curious if one data source works for one table specifically. What’s the best approach for doing what I described above.

Protected Sub ddlTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTable.SelectedIndexChanged
        Dim strSelect, strColumns As String

        strColumns = ""
        
        Select Case ddlTable.SelectedValue
            Case "businesslistings"
                strColumns = "*"
                myDataSource.UpdateCommand = "UPDATE businesslistings SET Title=@Title, Address=@Address,Phone=@Phone, Description=@Description WHERE ID=@ID"
                myDataSource.DeleteCommand = "Delete businesslistings WHERE ID=@ID"
            Case "jobs"
                strColumns = "*"
                myDataSource.UpdateCommand = "UPDATE jobs SET Title=@Title, Location=@Location,Compensation=@Compensation, Description=@Description,Phone=@Phone WHERE ID=@ID"
                myDataSource.DeleteCommand = "Delete jobs WHERE ID=@ID"
            Case "rentals"
                strColumns = "*"
                myDataSource.UpdateCommand = "UPDATE rentals SET Name=@Name, Address=@Address,Phone=@Phone, Email=@Email,Rent=@Rent, Description=@Description ID=@ID"
                myDataSource.DeleteCommand = "Delete rentals WHERE ID=@ID"
            Case "sales"
                strColumns = "*"
                myDataSource.UpdateCommand = "UPDATE sales SET Name=@Name, Item=@Item,Phone=@Phone, Email=@Email,Price=@Price, Description=@Description WHERE ID=@ID"
                myDataSource.DeleteCommand = "Delete sales WHERE ID=@ID"
        End Select


        strSelect = "SELECT " & strColumns & " FROM " + ddlTable.SelectedValue
        myDataSource.SelectCommand = strSelect

        lblMessage.Text = myDataSource.SelectCommand & "<br>" & myDataSource.UpdateCommand & "<br>" & myDataSource.DeleteCommand
        'Response.Write(strSelect)
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("loginSuccessful") = False Then
            Response.Redirect("login.aspx")
        End If

        If Not IsPostBack Then
            myDataSource.SelectCommand = "SELECT Title,Address,Phone,Description FROM businesslistings"
            myDataSource.UpdateCommand = "UPDATE businesslistings SET Title=@Title, Address=@Address,Phone=@Phone, Description=@Description WHERE ID=@ID"
            myDataSource.DeleteCommand = "Delete businesslistings WHERE ID=@ID"
            lblMessage.Text = "Postback " & IsPostBack & "<br>" & myDataSource.SelectCommand & "<br>" & myDataSource.UpdateCommand & "<br>" & myDataSource.DeleteCommand
        End If

    End Sub