I-BEGO

numpang nanya lagi Om, maklum lagi kejar setoran tugas kuliah

Om saya mau coba bikin tombol import file .xls (excel) ke tabel mysql. saya udah nyari lwt mbah google tp masih bingung cz dengan kodingnya.
ni gambar rencana formnya dan kodingnya:

Code: Select all

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\gammu\'" & Text1.Text & "'.xls;" & _      <<< tempat input file .xls
        "Extended Properties=Excel 8.0"
   
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={MySQL ODBC 5.1 Driver};" & _
        "Server=localhost;Database=server;" & _
        "UID=root;PWD=].mahasiswa" & _
        "FROM [Sheet1$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
       
    cn.Close
    Set cn = Nothing
End Sub

Private Sub FrmMhs_Load()
    buka_koneksi
End Sub


mohon penjelasan dan pencerahannya

mau tanya dulu sebelumnya... ini pake store procedure ga?
kalo pake store procedure mungkin saya bisa bantu dikit.

contoh yang saya buat :

Code: Select all

    Dim connectionString As String = ""
            'Check filenya  xls atau xslx

            If ViewState("fileExtension") = ".xls" Then
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ViewState("fileLocation") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            ElseIf ViewState("fileExtension") = ".xlsx" Then
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ViewState("fileLocation") & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            End If

            'Buat koneksi OleDB dan OleDb Command

            Dim con As New OleDbConnection(connectionString)
            Dim cmd As New OleDbCommand()
            con.Open()
            cmd.CommandType = System.Data.CommandType.Text
            cmd.Connection = con
            Dim dAdapter As New OleDbDataAdapter(cmd)
            Dim dtExcelRecords As New DataTable()

            Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()
            cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
            dAdapter.SelectCommand = cmd
            dAdapter.Fill(dtExcelRecords)
            con.Close()


yang diatas code behind untuk vbnya.
untuk ViewState("fileLocation") saya gunakan untuk mengambil path excel filenya. kenapa saya pake viewstate karna berbeda sub classnya..

Code: Select all

Dim flag As Boolean = True
            Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim fileLocation As String = Server.MapPath("~/Upload/Customer/" & fileName)
            Dim folder = IO.Path.GetDirectoryName(fileLocation)
ViewState("fileExtension") = fileExtension

untuk saving ke databasenya saya collect semuanya ke dalam dataset

Code: Select all

                Dim dataSet As DataSet = New DataSet("dataSet")
                dataSet.Tables.Add(dtData)
                '' Display the DataSet contents as XML.
                Console.WriteLine(dataSet.Tables(0).DataSet.GetXml())
ViewState("dataset") = dataSet

setelah itu saya declare di querynya untuk ngambil file2nya yang kemudia di save ke database

ntu coding yang d atas d buat dalam modul to gmn om gigabyte..??