How to load data into DataGridView from SQL Server

How to load data into DataGridView from SQL Server

 Many times in a project, we need to load data from sql server database table into datagridview. Each time we need to populate, you can use the following source code.

First you need to create Function in any Module as follows

  Public Function SQLLoadGridData(ByVal SQLStr As String) As DataTable  
     Dim TBD As New DataTable  
     Dim SqlConn As New SqlClient.SqlConnection  
     Try  
       SqlConn.ConnectionString = ConnectionStrinG  
       SqlConn.Open()  
       Dim Adapter As New SqlClient.SqlDataAdapter  
       Adapter.SelectCommand = New SqlClient.SqlCommand(SQLStr, SqlConn)  
       TBD.Locale = System.Globalization.CultureInfo.InvariantCulture  
       Adapter.Fill(TBD)  
       Adapter.Dispose()  
     Catch ex As Exception  
       MsgBox(ex.Message)  
     Finally  
       SqlConn.Close()  
       SqlConn.Dispose()  
     End Try  
     Return TBD  
   End Function  

The above function returns the datatable for SQL Select Query (SqlStr is the Select Query)

Then, How to use the code for Data Grid View, here is the example (with Filter Also)

 Dim SqlStr as String  
 SqlStr="SELECT FIRSTNAME,LASTNAME,AGE,DATEOFBIRTH FROM STUDETNS"  
 Dim BS As New BindingSource  
     With Me.TxtList  
        BS.DataSource = SQLLoadGridData(SqlStr)  
       .AutoGenerateColumns = True  
        BS.Filter = "VoucherName <>'SQ' AND VoucherName<>'SO' "  
       .DataSource = BS  
     End With