copying Datatable into Access
copying Datatable into Access
copying Datatable into Access
copying Datatable into Access
copying Datatable into Access
copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access
copying Datatable into Access copying Datatable into Access
copying Datatable into Access
Go Back  Xtreme Visual Basic Talk > > > copying Datatable into Access


Reply
 
Thread Tools Display Modes
  #1  
Old 11-06-2013, 04:50 AM
Meridian2000 Meridian2000 is offline
Contributor
 
Join Date: Jul 2003
Location: London/England
Posts: 573
Default copying Datatable into Access


can someone please give me a clue as to why the following code doesn't work, it's been adapted from some code I found on the Web that was originally written in C#, I've made a data table called postcodes and 2 fields or columns called postcode and streets and filled it with Data, I have an Access database with exactly the same structure. I've run the code and although it doesn't error it doesn't populate the database either.

Code:
        Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DB & ";Persist Security Info=False"
        Dim SQL As String = "SELECT * FROM postcodes"
        Dim INSERT As String = "INSERT INTO postcodes(postcode,streets) " & "VALUES (@postcode, @streets)"
        Dim OleConn As New OleDbConnection(ConnString)
        Dim OleAdp As New OleDbDataAdapter(SQL, OleConn)
        OleAdp.InsertCommand = New OleDbCommand(INSERT)
        OleAdp.InsertCommand.Parameters.Add("@postcode", OleDbType.VarChar, 40, "postcode")
        OleAdp.InsertCommand.Parameters.Add("@streets", OleDbType.VarChar, 40, "streets")
        OleAdp.InsertCommand.Connection = OleConn
        OleAdp.InsertCommand.Connection.Open()
        OleAdp.Update(DT)
        OleAdp.InsertCommand.Connection.Close()
__________________
Bumshanka - May the seed of your loins be fruitful in the belly of your woman - The Young Ones
Reply With Quote
  #2  
Old 11-07-2013, 10:46 AM
DrPunk's Avatar
DrPunkcopying Datatable into Access DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Edit :- Looking again, I don't think all the code is posted. I'll leave what I posted anyway but I doubt it applies


-- Original reply ------------

I'm not 100% sure, but I think it doesn't work because an insert command won't work like that through a dataadapter.

I don't use dataadapters a great deal so I could be wrong.

But dataadapters are used on datasets. You can use a dataadapter to fill a dataset from a database (using its selectcommand) and use a dataadapter to update a database from the dataset (with its insert and updatecommands).

Without a dataset there's nothing for a dataadapter to do.

For your code to work, either use a dataset or ditch the dataadapter. This shows the dataadapter ditched.
Code:
        Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DB & ";Persist Security Info=False"
        Dim SQL As String = "SELECT * FROM postcodes"
        Dim INSERT As String = "INSERT INTO postcodes(postcode,streets) " & "VALUES (@postcode, @streets)"
        Dim OleConn As New OleDbConnection(ConnString)
        dim oleCmd as  New OleDbCommand(INSERT, oleConn)
        
        olecmd.Parameters.Add("@postcode", OleDbType.VarChar, 40, "postcode")
        olecmd.Parameters.Add("@streets", OleDbType.VarChar, 40, "streets")

       try
            oleconn.open

            olecmd.executescalar
       catch ex as exception
            messagebox.show(ex.message, "Error inserting to database")
       finally
            oleconn.close
       end try
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
copying Datatable into Access
copying Datatable into Access
copying Datatable into Access copying Datatable into Access
copying Datatable into Access
copying Datatable into Access
copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access copying Datatable into Access
copying Datatable into Access
copying Datatable into Access
 
copying Datatable into Access
copying Datatable into Access
 
-->