TechSapphire Wednesday, 2018-10-17, 12:26 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Main » Files » Programming

    Using excel as database using vb.Net Or excel and VB.Net
    2011-10-09, 8:18 AM

    Using excel as database using vb.Net Or excel and VB.Net

    Solution code:

    Imports System.Data.Oledb

    Public class form1

    Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=c:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

     
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim pram As OleDbParameter
    Dim dr As DataRow
    Dim olecon As OleDbConnection
    Dim olecomm As OleDbCommand
    Dim olecomm1 As OleDbCommand
    Dim oleadpt As OleDbDataAdapter
    Dim ds As DataSet
    Try
        olecon = New OleDbConnection
        olecon.ConnectionString = connstring
        olecomm = New OleDbCommand
        olecomm.CommandText = "Select FirstName, LastName, Age, Phone from [Sheet1$]"
        olecomm.Connection = olecon
        olecomm1 = New OleDbCommand
        olecomm1.CommandText = "Insert into [Sheet1$] " & _
        "(FirstName, LastName, Age, Phone) values (@FName, @LName, @Age, @Phone)"
        olecomm1.Connection = olecon
        pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
        pram.SourceColumn = "FirstName"
        pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
        pram.SourceColumn = "LastName"
        pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
        pram.SourceColumn = "Age"
        pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
        pram.SourceColumn = "Phone"
        oleadpt = New OleDbDataAdapter(olecomm)
        ds = New DataSet
        olecon.Open()
        oleadpt.Fill(ds, "Sheet1")
        If IsNothing(ds) = False Then
            dr = ds.Tables(0).NewRow
            dr("FirstName") = "Yogesh"
            dr("LastName") = "Mehla"
            dr("Age") = 22
            dr("Phone") = 90232625
            ds.Tables(0).Rows.Add(dr)
            oleadpt = New OleDbDataAdapter
            oleadpt.InsertCommand = olecomm1
            Dim i As Integer = oleadpt.Update(ds, "Sheet1")
            MessageBox.Show(i & " row affected")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        olecon.Close()
        olecon = Nothing
        olecomm = Nothing
        oleadpt = Nothing
        ds = Nothing
        dr = Nothing
        pram = Nothing
        End Try
    End Sub

     End Class

    Output:


    Explanation:

    The System.Data.OleDb namespace is the.NET Framework Data Provider for OLE DB.

    The.NET Framework Data Provider for OLE DB describes a collection of classes used to access an OLE DB data source in the managed space. Using the OleDbDataAdapter, you can fill a memory-resident DataSet that you can use to query and update the data source.

    Category: Programming | Added by: Admin | Tags: excel with vb.net, excel database .net, query excel .net, excel database vb.net, excel with .net, excel .net
    Views: 963 | Downloads: 0 | Comments: 5 | Rating: 2.0/1
    Total comments: 0
    Only registered users can add comments.
    [ Registration | Login ]
    Categories
    Programming [3]
    .Net solution,.net project,vb project,vb,c#,.net,c language,c++,projects,project,college project,html template,free template,java
    .Net [0]
    .Net program,vb.net,c#.net,asp.net,projects,.net project,programming solution,free projects
    Mystic Programs [0]
    programming challenges,computer quiz question,secret program
    Security [0]
    Security,window security,web security,web vulnerabilities,virus,virus programming,software security,validation
    Google [0]
    Google search,Google tricks,restrict Google search,Free book search,search Ftp directly,search directory,search direct link rapidshare,search torrent directly
    Project [0]
    College projects,free project,c language project,vb project,vb.net project,.net project,c# project,html project
    Video Tutorial [2]
    Free Video Tutorial
    Database [1]
    SQL Server
    Copyright MyCorp © 2018