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 "SELECTCOMMAND" and "UPDATECOMMAND" 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.
Expand|Select|Wrap|Line Numbers
- 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