It seems the book I’m using is behind the Visual Studio Devlopment cycle…
I’m trying to follow the example in the book but Visual Studio isn’t helping. The end result on this web form is to have a text box, a data grid, a button and a SQL connection. The SQL Connection connects to Northwind and when you click the button, the SQL query you typed in the textbox get executed against the database. The results are shown in the datagrid.
I can’t figure out how to make the SQL DataSource read the text in txtQuery texbox.
When I add a SQL Connection object, it ends up being a SQLDataSource object. After I try configure the SqlDataSource (To Northwind.mdb) I get prompted to design a query, which is not what I want to do.
First off, is this a web page or a windows application?
Either way, here’s what you need to do. I’m doing this from memory, so some of the the object/method names may be incorrect.
All you need set in the SQLDataSource is the connection string. You can go through the wizard and set the Command text if you want - it’s sometimes helpful to have some data to work with in the designer. It doesn’t matter, because you’ll overwrite it at runtime anyway.
In the button’s OnClick event, set the SQLDataSource.Command (SQLCommand? something like that) to the text in the textbox, then do a SQLDataSource.Select to actually perform the query.
Your Grid should have its data source property set to the SQLDataSource object.
You might have to do a grid.Databind() to update the grid itself.
If this is a web page, there might be more mumbo jumbo to get it all to work right, but that’s the basics.
You don’t even really need the SQLDataSource object on the page - you can create one in the OnClick event, set the connection string, and set the grid.datasource to the newly created object then follow the above directions.
I’m only familiar with VS 2005. If that’s not what you’re using your mileage may vary.
Feel free to ask more questions. I just got done with a project with about 50 different screens with a minimum of one data grid on each one. I know more about the GridView and SQLDataSource objects than anyone ever would want to know.
After reinstalling everything, I think I’m close but I am having trouble with a new object/method (for me).
Here’s the code:
Protected Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
'process query when form is posted back
If IsPostBack Then
'create a sqlcommand to represent the query
Dim cmd As SqlCommand = SqlDataSource1.createcommand
cmd.CommandType = CommandType.Text
cmd.CommandText = txtQuery.Text
'create sqladapter to talk to database
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
'create dataset to hold results
Dim ds As DataSet = New DataSet
'fill dataset
da.Fill(ds, "Results")
dgResults.DataSource = ds
dgResults.DataMember = "Results"
dgResults.DataBind()
End If
End Sub
End Class
I’m getting an error telling me CreateCommand is not a member of the sql.datasource namespace. I tried to look in the member list and if I use InsertCommand, I get an error:
'Value of type ‘String’ cannot be converted to ‘System.Data.SqlClient.SqlCommand’.
You don’t need any of that createcommand stuff. Not sure where you got that, but the compiler is right, there is no method on the SQLDataSource object called “createcommand.”
Question: is dgResults a GridView or a DataGrid? I’ve been assuming it’s a GridView. If it’s a DataGrid, I’m not sure if the following code will work or not. I haven’t used DataGrids in forever. You probably want to switch to a GridView anyway - it’s the replacement for DataGrid and it’s much easier to use and more powerful.
I looked it up this time. What you want to do is set SQLDataSource1.SelectCommand to your text string. After that, as long as the SQLDataSource is set as the Datasource for dgResults, all you should have to do is call DataBind():
If IsPostBack Then
SqlDataSource1.SelectCommand = txtQuery.Text
dgResults.DataBind()
End If
You don’t actually have to perform the select in this code. DataBind() will take care of it.
>>I tried to look in the member list and if I use InsertCommand, I get an error
The SQLDataSource object has four members that are SQL query strings: SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand. These work closely with the GridView object’s buttons. For example, if you insert a new row using the GridView’s “Insert” button, what really happens is the code calls SQLDataSource.InsertCommand. If you delete a row, SQLDataSource.DeleteCommand is called. Very handy.
I think that’s your problem then. Though they kind of look the same, they are VERY different.
And yes, if your book was for ASP.NET 1.xxx and you’re using 2.xxx, you might as well throw it away. FYI, there’s a lot of good tutorials on the web if you go searching for them.
Wouldn’t you know that ALL that code in the book can be boiled down to:
Protected Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
'process query when form is posted back
If IsPostBack Then
'create a sqlcommand to represent the query
SqlDataSource1.SelectCommand = txtQuery.Text
dgResults.DataBind()
End If
End Sub
That makes more sense to me than the previous code. I think that indeed, my book is using ASP .NET 1.xxx and I will glance over the code in it vs trying it out.
AND
I only got it to work when I changed my connection string to refer to the MS Access database. It seems there’s a bug in SQLExpress that causes the other error. Whatever, I’m moving on.
