Tag Archives: Database Connection

How to fill a ComboBox using a DataTable in VB.NET

A Combo box has two properties

 

1.One which displays the items in the combo box called the DISPLAY MEMBER

2. Another one which stores the underlying ID for the Item which is being Displayed.

 

eg your table may have EmployeeId and EmployeeName.

You would like to display the EmployeeName in the ComboBox and when a user selects an employee name you would like to save the EmployeeID.

Function Fillcombo(ByVal SQL As String, ByVal CBO As ComboBox, ByVal DisplayMember As String, ByVal ValueMember As String)
Dim dt As New DataTable                  'Create a New DataTable
Dim dc As New SqlCommand              'Create a New SQLCommand
Dim da As New SqlDataAdapter         ' Create a New SQL Data Adapter
dc.CommandTimeout = 0
dc.CommandText = sql
da.SelectCommand = dc
dc.Connection = cn

da.Fill(dt)    ‘ Use the DataAdapter to fill the DataTable with the records
Try
CBO.DataSource = dt
CBO.DisplayMember = DisplayMember   ‘ This is the item which will be displayed on the combo box  eg Employee Name
CBO.ValueMember = ValueMember       ‘ This is the value which is stored eg. employeeID

Catch
MsgBox(“Unable to Fill the Combobox:” & CBO.Name & ” with values” & “:” & Err.Description)
End Try
End Function

 

 How to use the above function

Fillcombo(“Select EmployeeID,EmployeeName from Employees”, Me.EmployeeCombo, “EmployeeName”, “EmployeeID”)

How to connect to a SQL Server in VB.NET

Connection to a SQL Server database canbe achieved in vb.net quite easily.
The Windows Forms based applications as well as ASP.NET based applications can connect to the SQL Server database in a similar way. But the way the ConnectionStrings are stored is slightly different.
1. Windows Forms Applications – How to make a connection in a Windows Forms based application
I normally use a module in which I have the following two functions – one for opening a connection and one for closing the connection.
Step 1:
At the top of the module write the following line

Imports System.Data.SqlClient

Step 2

Also declare a public string in which you can store the connection string as follows
( well there are different other ways to store the connection string but I will not discuss them here)

Public gsConnString As String = "Data Source=100.200.300.400;Initial Catalog=testdatabase;
User Id=testuser;Password=testpassword;"

Step 3

Function OpenConnection(ByVal cn As SqlConnection) As Boolean
Try
If cn.State <> ConnectionState.Closed Then
cn.Close()
End If
cn.ConnectionString = gsConnString
cn.Open()
Return True ' If the connection is made then it returns true
Catch
MsgBox("Unable to Connect to the database" & Err.Description, vbCritical)
Return False ' If the connection is not made then it returns false
End Try

End Function

Function CloseConnection(ByVal cn As SqlConnection) As Boolean

Try
If cn.State <> ConnectionState.Closed Then
cn.Dispose()
cn.Close()
End If

Return True
Catch
End Try

End Function

Now that you have written the functions and the connection string for your SQL Server . Now you are ready to open the connection in your form or module
Step 1
write the following line at the top of the form or the module

Imports System.Data.SqlClient

Step 2
You can use the openconnection and the close connection in your programs the following way.
This is a useful generic function which you can use to fill and return a datatable. You simply pass the sql statement to it and then you can get a datatable with the results
Once you get the datatable you can use it eg. use it in a datagrid

Function GetDataTable(ByVal sql As String) As DataTable
Dim dt As New DataTable
Dim dc As New SqlCommand
Dim cn As New SqlConnection
dc.CommandTimeout = 0
Dim da As New SqlDataAdapter
OpenConnection(cn)
da.SelectCommand = dc
dc.Connection = cn
dc.CommandText = sql
Try
da.Fill(dt)
Return dt
Catch
MsgBox("Error encountered, please try again:(" & sql & ")" & Err.Description, MsgBoxStyle.Critical)
Finally
dt.Dispose()
dc.Dispose()
CloseConnection(cn)
End Try

End Function

In the following function we are just passing the SQL string to the GetDataTable function which returns the datatable filled with the results. and the Datagrid is being populated with this table.

Private Sub btnFetchData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetchData.Click
Dim sql As String
sql = "SELECT UserID,SectionID, PostID, ThreadID, PostAuthor, PostLevel,PostDate FROM dbo.cs_Posts order by PostID Desc"
Me.DataGridView1.DataSource = GetDataTable(sql)

End Sub

In ASP.NET the Connection string is normally stored in the web.config file

Following example shows a connection string for a sql server 2008 database.



This is how you would read the connection string from the web.config file

Public gsConnString As String = ConfigurationSettings.ConnectionStrings("vbnet_ConnectionString").connectionstring

They you can open the connection using the Openconnection function as described above.