VB-Tips

The fastest way to put one text from a database in a Textbox

Wednesday, January 11, 2012

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

WPF: Image from resource in Image Box.

Monday, December 05, 2011

It took me a long time when WPF was new to be able to show an image from the resources in an imagebox. Because it was asked in the Express forum I've placed here a simple version of that.

'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

Close a form from a timer by raising an event

Monday, November 28, 2011

Sometimes people ask how to close a program from another place. This also shows how to make a slowly 10 seconds showing form and then closes Be aware the used code is VB2011 style but fits with a byval in every version before that.

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

Access: How to create an Accdb databasefile with columns

Monday, November 21, 2011

With thanks to Paul Clement we were able to create this tip how to create from scratch a Accdb database file in code. Be aware there has to be set a reference (extensions) to the Access Interop

'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

Convert MS Access DataBase to another version format (also to 2007) in Visual Basic Code

Friday, November 18, 2011

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