Datagrid - Populate with SQL Select Statement
-
I am trying to populate a datagrid using a SQL select statement. The statement contains a "WHERE fieldname LIKE textbox.text" as below in my code. The problem is, I can't get the code right to display the data in the grid when the button is clicked. Here is my code so far: Public Sub loadDoc() Dim DAdapt As SqlDataAdapter Dim custMap As DataTableMapping = DAdapt.TableMappings.Add("Table", "DocumentList") custMap.ColumnMappings.Add("Title", "Title") custMap.ColumnMappings.Add("Expiration", "Expiration") custMap.ColumnMappings.Add("Keywords", "Keywords") Dim sql2 As String = "Select * FROM DocumentList WHERE Keywords LIKE '" & tbSearch.Text & "'" Dim ds As New DataSet DAdapt = New SqlDataAdapter(sql2, cnn1) Me.cnn1.Open() DAdapt.Fill(ds) Try grdDocumentList.DataSource = ds.DefaultViewManager ds.Clear() ds.Merge(ds) grdDocumentList.SetDataBinding(ds, "DocumentList") Catch eLoadMerge As System.Exception Throw eLoadMerge End Try Me.cnn1.Close() End Sub Thank you, :-D
LWhite
-
I am trying to populate a datagrid using a SQL select statement. The statement contains a "WHERE fieldname LIKE textbox.text" as below in my code. The problem is, I can't get the code right to display the data in the grid when the button is clicked. Here is my code so far: Public Sub loadDoc() Dim DAdapt As SqlDataAdapter Dim custMap As DataTableMapping = DAdapt.TableMappings.Add("Table", "DocumentList") custMap.ColumnMappings.Add("Title", "Title") custMap.ColumnMappings.Add("Expiration", "Expiration") custMap.ColumnMappings.Add("Keywords", "Keywords") Dim sql2 As String = "Select * FROM DocumentList WHERE Keywords LIKE '" & tbSearch.Text & "'" Dim ds As New DataSet DAdapt = New SqlDataAdapter(sql2, cnn1) Me.cnn1.Open() DAdapt.Fill(ds) Try grdDocumentList.DataSource = ds.DefaultViewManager ds.Clear() ds.Merge(ds) grdDocumentList.SetDataBinding(ds, "DocumentList") Catch eLoadMerge As System.Exception Throw eLoadMerge End Try Me.cnn1.Close() End Sub Thank you, :-D
LWhite
Hmmm, I have never done it like that, try something like this Dim DAdapt as new SqlDataAdapter DAdaPt.SelectCommand = new SqlCommand DAdapt.SelectCommand.CommandType = CommandType.Text DAdapt.SelectCommand.Connection = cnn1 DAdapt.SelectCommand.CommandText = "Select * FROM DocumentList WHERE Keywords LIKE '" & search.Text & "'" Dim ds as new Datatable("Display") try DAdapt.fill(ds) Catch ex as SQLException end try grdDocumentList.Datasource = ds
-
Hmmm, I have never done it like that, try something like this Dim DAdapt as new SqlDataAdapter DAdaPt.SelectCommand = new SqlCommand DAdapt.SelectCommand.CommandType = CommandType.Text DAdapt.SelectCommand.Connection = cnn1 DAdapt.SelectCommand.CommandText = "Select * FROM DocumentList WHERE Keywords LIKE '" & search.Text & "'" Dim ds as new Datatable("Display") try DAdapt.fill(ds) Catch ex as SQLException end try grdDocumentList.Datasource = ds
Sweet! Thank you very much, that worked. I appreciate the assistance.:cool:
LWhite
-
Hmmm, I have never done it like that, try something like this Dim DAdapt as new SqlDataAdapter DAdaPt.SelectCommand = new SqlCommand DAdapt.SelectCommand.CommandType = CommandType.Text DAdapt.SelectCommand.Connection = cnn1 DAdapt.SelectCommand.CommandText = "Select * FROM DocumentList WHERE Keywords LIKE '" & search.Text & "'" Dim ds as new Datatable("Display") try DAdapt.fill(ds) Catch ex as SQLException end try grdDocumentList.Datasource = ds
I added another variation to this code that works very nicely to provide a search based on part of the "Keywords" field. Add this to the btnLoad_Click sub: tbSearch.Text = "%" & tbSearch.Text & "%" Again, Thanks to Kevin for the assist! Got the creative juices flowing again, lol.
LWhite