dataset to mdb

[spooke2k]

hi im really struggling to get a one button import routine running i import a xls into a data grid this bit works great, i then want it to insert into a database table "mdb" on the fly. i found some code online but this errors. what im looking for a way to drop all data from the grid into a table

thanks for any help given

An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dllAdditional information: Keyword not supported: 'provider'.


Dim da As SqlDataAdapter
Dim cb As SqlCommandBuilder
Dim ds1 As New DataSet
Dim ds2 As New DataSet


Dim dbPath1 As String
dbPath1 = VB6.GetPath & "\" & "core.mdb"


Dim conn As New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & dbPath1 & " ';")
'This line fails


' conn = New SqlConnection("Data Source=Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & dbPath1 & "'; Jet OLEDB:database password=123")



Dim sel As String = "SELECT * FROM Table1"
da = New SqlDataAdapter(sel, conn)
cb = New SqlCommandBuilder(da)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds2, "Table1")


Me.DataGridView1.DataSource = ds2
Me.DataGridView1.DataMember = "Table1":miserable:

 

[Herman]

Well you are using a SqlConnection object with an OLEDB connection string for one... Use an OleDbConnection, an OleDbDataAdapter, and an OleDbCommandBuilder.

 

[spooke2k]

Well you are using a SqlConnection object with an OLEDB connection string for one... Use an OleDbConnection, an OleDbDataAdapter, and an OleDbCommandBuilder.

Thanks for help ill have a read its been driving me nuts

 

[jmcilhinney]

The SqlClient namespace is only for SQL Server. As suggested, if you're using an OLE DB provider to connect to a data source then you use the OleDb namespace. You should check out ConnectionStrings.com for all your connection string needs, including which ADO.NET provider to use to connect to each data source with each connection string.

Also, you should get rid of that VB6.GetPath immediately. What are you actually trying to achieve there? Whatever it is, there's a proper VB.NET way to do it. If your MDB file is in the same folder as the EXE then the proper way to create a String containing its path would be like this:

Dim filePath = IO.Path.Combine(Application.StartupPath, "core.mdb")

The thing is, you don't need to do that for a connection string. It should just look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source =|DataDirectory|\core.mdb;

ADO.NET will resolve that place-holder to the correct folder path based on the type of application at run time.

If you're determining the path at run time then you should be using the connection string builder for your provider, e.g.

Using dialogue As New OpenFileDialog
    dialogue.Filter = "Microsoft Access 2003 and earlier data files (*.mdb)|*.mdb"

    If dialogue.ShowDialog = DialogResult.OK Then
        Dim builder As New OleDbConnectionStringBuilder

        builder.Provider = "Microsoft.Jet.OLEDB.4.0"
        builder.DataSource = dialogue.FileName

        Using connection As New OleDbConnection(builder.ConnectionString)
            '...
        End Using
    End If
End Using

 

[spooke2k]

hi thank for the replys, i really appeciate you taking time to look at it, i get whats being said but every time im trying to use the insert into command now its erroring not valid.

Ill keep reading and corrected my path setter too

 

[jmcilhinney]

The original error message was because Provider is an OLE DB thing and you were using SqlClient. If you have not switched to using OleDb then you need to do so. If you have and you have new issues then by all means show us your new code and tell us what and where the errors are.

 

[spooke2k]

im sorry to be a pain i used to program in vb6 5 years ago and basically ive started off a new project idea,which is a one stop button to send data populated in a datagrid to a table in a mdb. Im literally just trying to find a simple solution i found this online which gave a simple explanation but when i try run it goes wrong.

basically the data in dataviewgrid is there i just need to force it into a table on the mdb. I really appeciate everyones times

Private Sub CopyToAccess(source As DataTable)
        Try


            Dim filepath As String
            filepath = "C:\test.mdb"




            Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & filepath & "'; Jet OLEDB:database password=123"
            Dim accConnection As New OleDb.OleDbConnection(connString)
            Dim selectCommand As String = "SELECT Test1, Test2 FROM table1"
            Dim accDataAdapter As New OleDb.OleDbDataAdapter(selectCommand, accConnection)
            Dim accCommandBuilder As New OleDb.OleDbCommandBuilder()
            accDataAdapter.InsertCommand = accCommandBuilder.GetInsertCommand()
            accDataAdapter.UpdateCommand = accCommandBuilder.GetUpdateCommand()
            Dim accDataTable As DataTable = source.Copy()
           
            ''Just to make sure, set the RowState to added to make sure an Insert is performed'
            ' For Each row As DataRow In accDataTable.Rows '
            'If row.RowState = RowState.Added Or RowSate.UnChanged Then '
            'row.SetAdded() '
            'End If '
            'Next '

            accDataAdapter.Update(accDataTable)
        Catch ex As Exception
        End Try
    End Sub

 

[jmcilhinney]

I've edited your post to format your code for readability. Please do so for us in future.

As for the issue, please don't just tell us that something doesn't work without telling us what actually happens. If we know the symptoms then it's much easier to find the cause. For a start, I can see an empty Catch block there so you're simply ignoring it if an exception is thrown. The exception tells you specifically what went wrong and where so you had best be examining it for that information. If you want us to diagnose the issue then you should be passing that information on to us.