1 2 3 4 5  ... Previous Next 

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


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 EventArgsHandles 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 from Employees where EmployeeID = @1", Con)
                    com.Parameters.AddWithValue("@1", 1)
                    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



Insert an image (picture, blob) in a database in the most simple way.


Often is asked in the forums how to insert an image in the database. We did not have the most simplest way in our website. Therefore this 2012 sample. Be aware it is made with VB10SP1 in VB11 style.
Option Explicit On
Option Strict On
Option Infer On
Imports System.Data.SqlClient
'Sample made with VB10SP1 for earlier versions add Byval while the ID is an integer autoidentifier
Public Class Form1
    Private ImageToUse As Image
    Private Sub Form1_Load(sender As Object, e As EventArgsHandles MyBase.Load
        Button1.Text = "Get Image"
        Button2.Text = "Save Image to DataBase"
        PictureBox1.SizeMode = PictureBoxSizeMode.Zoom
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgsHandles Button2.Click
        'Error catching not implemented in this sample
        Using con As New SqlConnection("Data Source=YourServerName;Initial Catalog=TestDataBase;Integrated Security=True")
            Using com As New SqlCommand("Insert into TestTable(Picture) values (@1)", con)
                Using ms As New IO.MemoryStream
                    ImageToUse.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
                    Dim byteArray = ms.ToArray
                    com.Parameters.AddWithValue("@1", byteArray)
                    con.Open()
                    com.ExecuteNonQuery()
                End Using
            End Using
        End Using
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgsHandles Button1.Click
        Using OFD As New OpenFileDialog With {.Filter = "Jpg (*.jpg)|*.jpg"}
            If OFD.ShowDialog = DialogResult.OK Then
                ImageToUse = Image.FromFile(OFD.FileName)
                PictureBox1.Image = ImageToUse
            End If
        End Using
    End Sub
End Class



DataGridView: Master Child for OleDB with 3 DataGridViews and creating relations for that


It seems easy to use a DataGridView in a relation. And that is true; if you know how to do it. Here is that code below. You need to drag three DataGridViews on your form and past this in. Also you have to change the place of your NorthWind sample file in the code to the way you use.

Untitled 4
Imports System.Data.OleDb
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object,
                  ByVal e As System.EventArgsHandles MyBase.Load
        Dim ds As DataSet
        Using conn = New OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test1\Nwind.mdb")
            ds = New DataSet
            Dim daEmployees = New OleDbDataAdapter("Select * from Employees", conn)
            Dim daOrders = New OleDbDataAdapter("Select * from Orders", conn)
            Dim daOrderDetails = New OleDbDataAdapter("Select * from [Order Details]", conn)
            daEmployees.Fill(ds, "Employee")
            daOrders.Fill(ds, "Orders")
            daOrderDetails.Fill(ds, "OrderDetails")
        End Using
        ds.Relations.Add("EmployeeOrder"ds.Tables("Employee").Columns("EmployeeID"), _
            ds.Tables("Orders").Columns("EmployeeID"))
        ds.Relations.Add("Order2Details"ds.Tables("Orders").Columns("OrderID"), _
            ds.Tables("OrderDetails").Columns("OrderID"))
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "Employee"
        DataGridView2.DataSource = ds
        DataGridView2.DataMember = "Employee.EmployeeOrder"
        DataGridView3.DataSource = ds
        DataGridView3.DataMember = "Employee.EmployeeOrder.Order2Details"
    End Sub
End Class



CurrencyManager a simple sample


Show synchronise data in labels based on the grid or on the buttons It is very simple.
Take a look at this sample. It needs only a Form Project with dragged on that a DataGridView (can be left out), two buttons and four labels .
You can paste the code below into the code view and click the green start button in top (or F5)

(Be aware it is done with VB10SP1 for older you have to add on some places byval and then it goes even in VB2002)
Public Class Form1
    Private dt As New DataTable
    Private cma As CurrencyManager = DirectCast(BindingContext(dt), CurrencyManager)
    Private Sub Form1_Load(sender As Object, e As System.EventArgsHandles MyBase.Load
        Button1.Text = "Forward"
        Button2.Text = "Back"
 
        dt.Columns.Add("Name")
        dt.Columns.Add("State")
        dt.Columns.Add("Nation")
        dt.LoadDataRow(New Object() {"Ken Tucker""Florida""USA"}, True)
        dt.LoadDataRow(New Object() {"Cor Ligthert""Netherlands""EU"}, True)
        dt.LoadDataRow(New Object() {"John Antonny Oliver""England""EU"}, True)
        dt.LoadDataRow(New Object() {"Armin Zignler""Germany""EU"}, True)
        dt.LoadDataRow(New Object() {"Hannes Heslacher""Germany""EU"}, True)
        dt.LoadDataRow(New Object() {"Paul Clement""Illinois""USA"}, True)
        dt.LoadDataRow(New Object() {"Mark Liu""SjangHai""China"}, True)
        dt.LoadDataRow(New Object() {"Mike Feng""SjangHai""China"}, True)
        DataGridView1.DataSource = dt
        Label1.DataBindings.Add("Text", dt, "Name")
        Label2.DataBindings.Add("Text", dt, "State")
        Label3.DataBindings.Add("Text", dt, "Nation")
        Label4.Text = "0"
        AddHandler cma.CurrentChanged, AddressOf CurrentChanged
    End Sub
    Private Sub CurrentChanged(ByVal sender As ObjectByVal e As EventArgs)
        Label4.Text = DirectCast(sender, CurrencyManager).Position.ToString
    End Sub
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgsHandles Button1.Click
        If cma.Position < dt.Rows.Count + 2 Then
            cma.Position += 1
        End If
    End Sub
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgsHandles Button2.Click
        If cma.Position > 0 Then
            cma.Position -= 1
        End If
    End Sub
End Class




DataTable Expression and Simple hide column


DataTable Expression and Simple hide column

This sample needs only a form with a datagridview on it.
Untitled 1
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, _
       ByVal e As System.EventArgsHandles MyBase.Load
        Dim dt As DataTable = CreateTables()
        dt.Columns.Add("US", _
        GetType(System.String), "IIF(USA=True,'Yes','No')")
        dt.Columns("USA").ColumnMapping = MappingType.Hidden
        DataGridView1.DataSource = dt.DefaultView
    End Sub
    'To have a table to use is one created below
    Private Function CreateTables() As DataTable
        Dim dt As New DataTable("Persons")
        dt.Columns.Add("Name")
        dt.Columns.Add("USA"GetType(System.Boolean))
        dt.LoadDataRow(New Object() {"Ken Tucker"True}, True)
        dt.LoadDataRow(New Object() {"Cor Ligthert"False}, True)
        Return dt
    End Function
End Class



1 2 3 4 5  ... Previous Next