VB-Tips

This is Tip is meant to show the use of the keyword “Using”, the Try and Catch, the ExecuteScalar and how to concatenate first name and last name to a full name.

To test it you need only a new project with one button and a textbox on a windows form.
And to set your Server name in the connectionstring.

Imports System.Data.SqlClient
'Code for versions starting with VB10SP1 for earlier versions add the Byval's in the method
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Using Con As New SqlConnection("Data Source=YourServer;Initial Catalog=Northwind;Integrated Security=True")
                Con.Open()
                Using com As New SqlCommand("Select FirstName + ' ' + Lastname Picture from Employees where EmployeeID =1", Con)
                    Dim textObj = com.ExecuteScalar
                    If Not textObj Is Nothing AndAlso Not textObj Is DBNull.Value Then
                        TextBox1.Text = CStr(textObj)
                    End If
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

'set a reference to system drawing
Class MainWindow
    Private Sub Button1_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles Button1.Click
        Using memst As New IO.MemoryStream
            Dim bmpImage As New BitmapImage
            My.Resources.Edj345.Save(memst, System.Drawing.Imaging.ImageFormat.Jpeg)
            bmpImage.BeginInit()
            bmpImage.StreamSource = New IO.MemoryStream(memst.ToArray)
            bmpImage.EndInit()
            Image1.Source = bmpImage
        End Using
    End Sub
End Class

Public Class Form1
    Private WithEvents tim As New Timer
    Private Event StopRunning()
    Private Sub Form1_Activated(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles Me.Load
        ProgressBar1.Maximum = 10
        ProgressBar1.Step = 1
        ProgressBar1.Minimum = 0
        tim.Enabled = True
        tim.Interval = 1000
        Opacity = 0
    End Sub
 
    Private Sub tim_Tick(sender As Object, e As System.EventArgs) Handles tim.Tick
        Static op As Double = 1
        ProgressBar1.PerformStep()
        op -= 0.1
        Me.Opacity = op
        If op < 0 Then RaiseEvent StopRunning()
    End Sub
    Private Sub Form1_StopRunning() Handles Me.StopRunning
        Close()
    End Sub
End Class

'Set a reference to Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access.Dao
Module Exercise
    Sub Main()
        Try
            Dim AccessDatabaseEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
            Dim AccessDatabase As Microsoft.Office.Interop.Access.Dao.Database
            AccessDatabase = AccessDatabaseEngine.CreateDatabase("C:\Test\NewDatabase.accdb", LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbVersion120)
            AccessDatabase.Close()
        Catch ex As Exception
            Console.Write(ex.Message)
            Console.ReadLine()
        End Try
       
       
        Using conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "  Data Source=C:\Test\NewDatabase.accdb;Persist Security Info=False;")
            Using cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
              "AutoId int identity ," & _
              "Id int NOT NULL," & _
              "Name NVarchar(50)," & _
                "BirthDate datetime," & _
               "IdCountry int," & _
                  "CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
                conn.Open()
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    Console.Write(ex.Message)
                    Console.ReadKey()
                End Try
            End Using
        End Using
    End Sub
End Module

Often is asked in the forums how to handle an MS Access 2003 file and an MS Access 2007 file.

There is a very simple way to convert those. Be aware it is possible that methods from one are not available in the other and are therefore then not always converted.

Option Strict On
'Set a reference to Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access
Module Module1
    Sub Main()
        Dim theAccessObject As New Application
        theAccessObject.ConvertAccessProject("C:\Test1\nwind.mdb", "C:\Test1\nwindAcc.accdb", AcFileFormat.acFileFormatAccess2007)
    End Sub
End Module