mas2 sekalian minta script import data dari excel ke sql server table yg sudah ada  di VB 2005 dunk....

tolong pencerahannya.....

Ini saya ambilkan dari forum tetangga, semoga membantu

klik disini

guphfrontz:

Ini saya ambilkan dari forum tetangga, semoga membantu

klik disini

bukan pakai yg wizard mas tpi yg full script VB 2005

Full Script in VB2005 : 

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class GEEKS

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        simpan("C:\data.xls")
    End Sub
    Public Sub simpan(ByVal fileexcel As String)
        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=Lab")
        con.Open()
        Dim cmd As New SqlCommand("Select * from Table1", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ods As New DataSet("newtable")
        da.FillSchema(ods, SchemaType.Source, "Table1")
        Dim cb As New SqlCommandBuilder(da)
        Dim dr As DataRow
        Dim getrw As DataRow
        For Each getrw In exceldata(fileexcel).tables(0).Rows
            dr = ods.Tables(0).NewRow()
            dr(0) = getrw(0)
            dr(1) = getrw(1)
            dr(2) = getrw(2)
            ods.Tables(0).Rows.Add(dr)
        Next
        da.Update(ods, ods.Tables(0).TableName)
        MessageBox.Show("Done!")
    End Sub

    Public Function exceldata(ByVal filelocation As String) As DataSet
        Dim ds As New DataSet()
        Dim excelCommand As New OleDbCommand()
        Dim excelDataAdapter As New OleDbDataAdapter()
        Dim excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filelocation & "; Extended Properties =Excel 8.0;"
        Dim excelConn As New OleDbConnection(excelConnStr)
        excelConn.Open()
        Dim dtPatterns As New DataTable()
        excelCommand = New OleDbCommand("SELECT [NO], NAMA, ALAMAT FROM [Sheet1$]", excelConn)
        excelDataAdapter.SelectCommand = excelCommand
        excelDataAdapter.Fill(dtPatterns)
        dtPatterns.TableName = "Sheet1"
        ds.Tables.Add(dtPatterns)
        Return ds
    End Function
End Class

Field pada file excel dan di SSE : | NO | Nama | Alamat |

Semoga Membantu.

Blacx:

Full Script in VB2005 : 

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class GEEKS

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        simpan("C:\data.xls")
    End Sub
    Public Sub simpan(ByVal fileexcel As String)
        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=Lab")
        con.Open()
        Dim cmd As New SqlCommand("Select * from Table1", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ods As New DataSet("newtable")
        da.FillSchema(ods, SchemaType.Source, "Table1")
        Dim cb As New SqlCommandBuilder(da)
        Dim dr As DataRow
        Dim getrw As DataRow
        For Each getrw In exceldata(fileexcel).tables(0).Rows
            dr = ods.Tables(0).NewRow()
            dr(0) = getrw(0)
            dr(1) = getrw(1)
            dr(2) = getrw(2)
            ods.Tables(0).Rows.Add(dr)
        Next
        da.Update(ods, ods.Tables(0).TableName)
        MessageBox.Show("Done!")
    End Sub

    Public Function exceldata(ByVal filelocation As String) As DataSet
        Dim ds As New DataSet()
        Dim excelCommand As New OleDbCommand()
        Dim excelDataAdapter As New OleDbDataAdapter()
        Dim excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filelocation & "; Extended Properties =Excel 8.0;"
        Dim excelConn As New OleDbConnection(excelConnStr)
        excelConn.Open()
        Dim dtPatterns As New DataTable()
        excelCommand = New OleDbCommand("SELECT [NO], NAMA, ALAMAT FROM [Sheet1$]", excelConn)
        excelDataAdapter.SelectCommand = excelCommand
        excelDataAdapter.Fill(dtPatterns)
        dtPatterns.TableName = "Sheet1"
        ds.Tables.Add(dtPatterns)
        Return ds
    End Function
End Class

Field pada file excel dan di SSE : | NO | Nama | Alamat |

 

Semoga Membantu.

maaf mas, klo yg ini bukannya menyimpannya dalam tabel baru....

 Sudah bisa mas.....

script saya hanya seperti ini....

Sub Import(ByVal Filename As String)

strSQL = "insert into mastcust SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=" & Filename & ";Extended Properties=Excel 8.0')...[Sheet1$]"

        Myconnection.Open()
        objCommand.CommandText = strSQL
        Try

            objCommand.Connection = Mycon
            objCommand.ExecuteNonQuery()
        Catch When Err.Number <> 0
            MsgBox(objCommand.CommandText & vbCrLf & Err.Description)
        End Try
        MessageBox.Show("File Imported sucessfully.", "Importing done", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)
        Myconnection.Close()

    End Sub