1 2 3 4 5  ... Previous Next 

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



DataTable: Compute


This tip computes how many rows with dates 2 months before today in a datatable

This sample needs only a datagridview and a label on a form

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgsHandles MyBase.Load
        Dim dt As New DataTable
        dt.Columns.Add("Count"GetType(System.Int32))
        dt.Columns.Add("Date"GetType(System.DateTime))
        For i As Integer = 0 To 5
            Dim dtime As DateTime = Now.AddMonths(-i + 3)
            dt.LoadDataRow(New Object() _
            {DateTime.DaysInMonth(dtime.Year, dtime.Month), dtime}, True)
        Next
        DataGridView1.DataSource = dt
        ' This is the sample
        Dim ThisDate As String = Now.ToString("#MM/dd/yy#")
        ThisDate = ThisDate.Replace("-""/")
        Label1.Text = dt.Compute("Sum(Count)""Date < " _
               & ThisDate).ToString
    End Sub
End Class



1 2 3 4 5  ... Previous Next