Visual Studio Express Tools are the limited but free versions of the Microsoft IDE. One of their limitations is the lack of database server tools. Whereas the paid versions of these tools let you connect to SQL Server this tool only intrinsically connects to Access databases, SQL Server files or SQL Server Compact files.
SQL Server Compact is the small footprint database that ports to anything from enterprise applications down to tablet and smartphone Apps. Its limitation includes the lack of programmability, i. e., stored procedures. Also with the Visual Basic Express – SQL Server Compact combination you can’t script queries and transfer data via SQL Management Console like you may be used to.
Microsoft says the advantage of stored procedures (sprocs) has diminished over time as SQL Server performance has improved to the point that precompiled queries may not be faster. In fact, since sprocs are optimized at the point of creation conditions may have changed to the point that the sproc performance may be now slower than pragmatically sending queries through code. And Compact Edition will certainly suffice if all you want to do is update table information with a data entry form or such.
There is a work around, however, if you want to access SQL Server in Visual Basic Express 2010. Visual Web Developer 2010 Express does allow connecting to SQL Server/Express.
That will give you the connection string and other parameters that you can copy to your visual basic project.
The Project
I created a table of winning PA Lottery daily numbers in a database in SQL Server Express called LotteryHelperDB. In VB 2010 Express I started a new project. I created a Windows Application project with a form in it. On the form I added a datagridview, some text boxes and labels. (The screen shot is below.)
Then I programmatically set up the connection to SQL Server Express instead of using the data connection tool in VB 2010 Express.
How to set up a connection in Visual Web developer and use it in visual basic 2010 express
In Visual Basic 2010 Express you should be able to choose the SQL Server Database file option to get the right kind of database (the system.data.SqlClient provider), and then manually correct the connection string to point to your db. (Don’t worry if you actually can’t connect to an .mdf file. Just let the wizard set up the connection.
I did that and the IDE added the following to my App.config file:
<connectionStrings>
<add name=”SQLServerExpressConnectionExample.My.MySettings.LotteryHelperDbFileConnectionString”
connectionString=”Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Mark\Documents\Visual Studio 2008\Projects\LotteryHelper\LotteryHelper\LotteryHelperDbFile.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
In Visual Web Developer 2010 Express I opened a new project and connected to the SQL Server Express. That IDE created the following in the Web.config file:
<connectionStrings>
<add name=”ApplicationServices” connectionString=”data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true”
providerName=”System.Data.SqlClient” />
<add name=”LotteryHelperDBConnectionString” connectionString=”Data Source=MARK-LAPTOP\SQLEXPRESS;Initial Catalog=LotteryHelperDB;Integrated Security=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
In VB.Net 2010 I created a form with the datagridview, textboxes and labels as shown below. I called the form “PADailyNumberForm”.
I added a data connection to the .mdf file to let the IDE configure for a data connection. Then I used the connection string created in Visual Web Developer 2010 Express to replace the one generated in Visual Basic 2010 Express.
After I was done the code looked like this for my PADailyNumberForm:
Imports System.Configuration
Imports System.Data.Sql ‘ For SQL Server express
Imports System.Data.SqlClient
Imports System.Collections
Public Class PADailyNumberForm
Dim SQL_Conn As New SqlConnection ‘SqlConnection for SQLServerExpress
Dim DataAdapterDN As SqlDataAdapter ‘SqlDataAdapter for SQLServerExpress
Dim CommandBuilderDN As SqlCommandBuilder ‘SqlCommandBuilder for SQLServerExpress
Dim DataTableDN As New DataTable
Dim ds As New DataSet()
Dim DataRowDN As DataRow
Dim RowPostionDN As Integer = 0
Dim BindingSource1 As New BindingSource()
Private Sub PADailyNumberForm_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
SQL_Conn.Close()
SQL_Conn.Dispose()
End Sub
Private Sub PADailyNumberForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
SQL_Conn.ConnectionString = ConfigurationManager.ConnectionStrings(“SQLServerExpressConnectionExample.My.MySettings.LotteryHelperDbFileConnectionString”).ConnectionString
SQL_Conn.Open()
‘Use SqlDataAdapter for SQLServerExpress
DataAdapterDN = New SqlDataAdapter(“SELECT * FROM DailyNumberMidday Order by DNMiddayID”, SQL_Conn)
CommandBuilderDN = New SqlCommandBuilder(DataAdapterDN)
DataAdapterDN.Fill(DataTableDN)
Me.DisPlayCurrentRecord()
With DataGridView1
‘ Automatically generate the DataGridView columns.
.AutoGenerateColumns = True
‘ Set up the data source.
bindingSource1.DataSource = DataTableDN
.DataSource = bindingSource1
End With
Catch ex As SqlException ‘SqlException for SQlServerExpress
MessageBox.Show(“Error loading form: ” & Err.ToString, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
System.Threading.Thread.CurrentThread.Abort()
End Try
End Sub
Private Sub DisplayCurrentRecord()
If DataTableDN.Rows.Count = 0 Then
lblRecordCount.Text = “0”
lblRecordNumber.Text = “0”
Else
lblRecordCount.Text = DataTableDN.Rows.Count.ToString
lblRecordNumber.Text = CStr(RowPostionDN + 1) ‘ DataTableDN.Rows.Count.ToString
TextBox1.Text = DataTableDN.Rows(RowPostionDN)(“DNMiddayID”).ToString
TextBox2.Text = Format(DataTableDN.Rows(RowPostionDN)(“DNDrawdate”).ToString, “Short Date”)
TextBox3.Text = DataTableDN.Rows(RowPostionDN)(“DNP1”).ToString
TextBox4.Text = DataTableDN.Rows(RowPostionDN)(“DNP2”).ToString
TextBox5.Text = DataTableDN.Rows(RowPostionDN)(“DNP3”).ToString
‘This sychronizes the Datagrid to the navigation bar
‘DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
End If
End Sub
Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
‘Move to the first row and update display
RowPostionDN = 0
Me.DisPlayCurrentRecord()
DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
End Sub
Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
‘Move to the next row and update display
If RowPostionDN < DataTableDN.Rows.Count – 1 Then
RowPostionDN = RowPostionDN + 1
Me.DisPlayCurrentRecord()
‘DataGridView1.Rows(RowPostionDN).Selected = True
‘The DataGridView should only contain a single CurrentCell –
‘this is the cell that is currently active (being edited for example).
‘Set the CurrentCell property to a non-hidden non-disabled, non-header cell
‘and that will move the black arrow to the row that contains that cell.
‘This row can be checked with the CurrentRow (read-only) property of the DataGridView.
DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
End If
End Sub
Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
‘Move to the previouos row and update display
If RowPostionDN > 0 Then
RowPostionDN = RowPostionDN – 1
Me.DisPlayCurrentRecord()
DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
End If
End Sub
Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
‘Move to the last row and update display
MoveToLastRecord()
End Sub
Private Sub MoveToLastRecord()
‘Placed in separate sub so that it can be called in subs below without an event argument
If DataTableDN.Rows.Count > 0 Then
RowPostionDN = DataTableDN.Rows.Count – 1
Me.DisplayCurrentRecord()
DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
End If
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
RowPostionDN = DataGridView1.CurrentCell.RowIndex
Me.DisPlayCurrentRecord()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
‘ Hide command buttons and navigation buttons so that record cannot be moved
ChangeDisplayMode(False)
‘Get the next DrawID and putthat value into Textbox1
TextBox1.Text = “”
‘Blank the other textboxes
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”
TextBox5.Text = “”
End Sub
Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
‘ Show command buttons and navigation buttons so that record manipulation can return to normal
ChangeDisplayMode(True)
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
‘Add a Row to te datatable using the values entered in the textboxes
Dim NewRowDN As DataRow = DataTableDN.NewRow
NewRowDN(“DNMiddayID”) = CInt(TextBox1.Text)
NewRowDN(“DNDrawdate”) = CDate(TextBox2.Text)
NewRowDN(“DNP1”) = CInt(TextBox3.Text)
NewRowDN(“DNP2”) = CInt(TextBox4.Text)
NewRowDN(“DNP3”) = CInt(TextBox5.Text)
DataTableDN.Rows.Add(NewRowDN)
DataAdapterDN.Update(DataTableDN)
‘Update the grid and navigation bar to show the newly created record
RowPostionDN = DataTableDN.Rows.Count – 1
DataGridView1.CurrentCell = DataGridView1.Rows(RowPostionDN).Cells(0)
Me.DisplayCurrentRecord()
ChangeDisplayMode(True)
End Sub
Private Sub ChangeDisplayMode(ByVal bSwitch As Boolean)
GroupBox1.Visible = bSwitch
btnUpdate.Visible = bSwitch
btnAdd.Visible = bSwitch
btnDelete.Visible = bSwitch
btnCancel.Visible = Not bSwitch
btnSave.Visible = Not bSwitch
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If (MessageBox.Show(“Are you sure you want to delete this record? This cannot be undone.”, “Confirm Record Deletion”, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes) Then
If DataTableDN.Rows.Count <> 0 Then
DataTableDN.Rows(RowPostionDN).Delete()
DataAdapterDN.Update(DataTableDN)
RowPostionDN = 0
End If
Else
Exit Sub
End If
MoveToLastRecord()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
If DataTableDN.Rows.Count <> 0 Then
DataTableDN.Rows(RowPostionDN)(“DNDrawdate”) = CDate(TextBox2.Text)
DataTableDN.Rows(RowPostionDN)(“DNP1”) = CInt(TextBox3.Text)
DataTableDN.Rows(RowPostionDN)(“DNP2”) = CInt(TextBox4.Text)
DataTableDN.Rows(RowPostionDN)(“DNP3”) = CInt(TextBox5.Text)
End If
End Sub
Private Sub GetNewDrawID()
End Sub
Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
End Sub
End Class
The database is just a table with the daily number from the Pennsylvania lottery. In actuality a simple table like this could be ideally handled by SQL Server Compact unless you are planning on making it fancier by calculating possible winning numbers based on what has been drawn previously, calculating wins and losses and so forth. Even that could be done in the application and stored in the table in SQL Server Compact. But, for our purposes it illustrates how to get VB 2010 Express to connect to SQL Server Express.
Here is a screen shot of the little application:

Note: the only build error I had was the configurationmanager returned an error. To correct it I added a reference to the configuration component:
Menu Bar -> Project -> Add Reference… -> .NET (Tab) -> System.Configuration -> OK
After that everything worked.
Happy programming.