• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

How do I use tableAdapter to save back into Table in Access DB (using vb.net 2005)

I have a routine which opens a connection to a csv file, and then reads it into a dataset. Next I want to save the dataset back to the db. Altho the following code is not producing an error, and /is/ filling the dataset, it is /not/ saving back into the db.

What am I doing wrong?

Thanks Experts~Michael

  Public Sub LoadCSVIntoDataTable(ByVal csvPath$, ByVal tableName$)
        myOleDBConnection = New OleDb.OleDbConnection(My.Settings.myConnectionString)      ' Setup connection
        myOleDBConnection.Open()
        myDB.doQueryCmdUsingConnection("Delete * From AffInfo;")
        Stop
        '
        Dim conn As System.Data.Odbc.OdbcConnection
        Dim strConnstr$ = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvPath + ";"
        conn = New Odbc.OdbcConnection(strConnstr)

        Dim da As New System.Data.Odbc.OdbcDataAdapter("Select * From AffInfo_Update.csv", strConnstr)
        da.Fill(myDS, "AffInfo")
        'myOLEDBAdapter.Fill(myDS, "Output")

        'myfuncs.debugPrintDataTable(myDS.Tables("CSV"))

        da.Update(myDS, "AffInfo")
        myDB.CloseOleDBConnection()
    End Sub
0
JeffreyDurham
Asked:
JeffreyDurham
  • 4
  • 4
  • 2
1 Solution
 
JeffreyDurhamAuthor Commented:
I think I somehow need to associate it with the table I'm trying to save it into..
0
 
AWestEngCommented:
Public Sub BatchUpdate(ByVal dt As DataTable)
        Try
            Dim MyConString As String = _
                       "Persist Security Info=False;" & _
                       "DATABASE=test;" & _
                       "PORT=3308;" & _
                       "SERVER=127.0.0.1;" & _
                       "Connect Timeout=30;" & _
                       "user id=test;" & _
                       "pwd=1234;"

            'Create a SQLConnection, passing in the proper connection string
            Dim myConnection As New MySqlConnection(MyConString)
            myConnection.Open()
            'Create a SQLDataAdapter
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()

            da.ContinueUpdateOnError = True
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
            da.AcceptChangesDuringFill = True

            'Create and configure a SQLCommand for the DataAdapter
            da.InsertCommand = New MySqlCommand()

            With da.InsertCommand

                'Set the connection
                .Connection = myConnection

                'Must be set to none
                .UpdatedRowSource = UpdateRowSource.None

                'Set the CommandType to Text
                .CommandType = CommandType.Text

                ''Set the SQL Text, including parameters
                .CommandText = _
                    "INSERT INTO test" & _
                    "(ImportDate, FileDate, FileType) " & _
                    "VALUES (?ImportDate, ?FileDate, ?FileType) " & _
                    "ON DUPLICATE KEY UPDATE DuplicateInsert = 1"


                'Add the parameters.  
                '    Make sure to map the source colum in our table to the parameter
                With .Parameters
                    .Add("?ImportDate", MySqlDbType.Date, 10, "ImportDate")
                    .Add("?FileDate", MySqlDbType.Date, 10, "FileDate")
                    .Add("?FileType", MySqlDbType.VarChar, 15, "FileType")
                End With

            End With


            'Perform the insert to the SQL table.
            da.Update(dt)

        Catch ex As Exception
       messagebox.show(ex.message)
        End Try
    End Sub
0
 
planoczCommented:
Hi JeffreyDurham,
Here is a sample  of how you can save data back to the database.
When you move your data around  you will need to go thru the DataAdapter, datatable and the dataset then to the database.

'FORM 1
Imports System.Data.OleDb
Public Class frmDataRowADD
    Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents btnClose As System.Windows.Forms.Button
    Friend WithEvents btnAdd As System.Windows.Forms.Button
    Friend WithEvents txtZip As System.Windows.Forms.TextBox
    Friend WithEvents txtState As System.Windows.Forms.TextBox
    Friend WithEvents txtCity As System.Windows.Forms.TextBox
    Friend WithEvents txtAddr As System.Windows.Forms.TextBox
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents lblZip As System.Windows.Forms.Label
    Friend WithEvents lblState As System.Windows.Forms.Label
    Friend WithEvents lblCity As System.Windows.Forms.Label
    Friend WithEvents lblAddr As System.Windows.Forms.Label
    Friend WithEvents lblName As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.btnClose = New System.Windows.Forms.Button
        Me.btnAdd = New System.Windows.Forms.Button
        Me.txtZip = New System.Windows.Forms.TextBox
        Me.txtState = New System.Windows.Forms.TextBox
        Me.txtCity = New System.Windows.Forms.TextBox
        Me.txtAddr = New System.Windows.Forms.TextBox
        Me.txtName = New System.Windows.Forms.TextBox
        Me.lblZip = New System.Windows.Forms.Label
        Me.lblState = New System.Windows.Forms.Label
        Me.lblCity = New System.Windows.Forms.Label
        Me.lblAddr = New System.Windows.Forms.Label
        Me.lblName = New System.Windows.Forms.Label
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(210, 140)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(64, 28)
        Me.DataGrid1.TabIndex = 25
        '
        'btnClose
        '
        Me.btnClose.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnClose.Location = New System.Drawing.Point(118, 136)
        Me.btnClose.Name = "btnClose"
        Me.btnClose.Size = New System.Drawing.Size(60, 28)
        Me.btnClose.TabIndex = 24
        Me.btnClose.Text = "&Close"
        '
        'btnAdd
        '
        Me.btnAdd.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnAdd.Location = New System.Drawing.Point(26, 136)
        Me.btnAdd.Name = "btnAdd"
        Me.btnAdd.Size = New System.Drawing.Size(60, 28)
        Me.btnAdd.TabIndex = 23
        Me.btnAdd.Text = "&Add"
        '
        'txtZip
        '
        Me.txtZip.Location = New System.Drawing.Point(182, 100)
        Me.txtZip.Name = "txtZip"
        Me.txtZip.Size = New System.Drawing.Size(84, 20)
        Me.txtZip.TabIndex = 22
        Me.txtZip.Text = "75124"
        '
        'txtState
        '
        Me.txtState.Location = New System.Drawing.Point(78, 100)
        Me.txtState.Name = "txtState"
        Me.txtState.Size = New System.Drawing.Size(38, 20)
        Me.txtState.TabIndex = 21
        Me.txtState.Text = "TX"
        '
        'txtCity
        '
        Me.txtCity.Location = New System.Drawing.Point(82, 72)
        Me.txtCity.Name = "txtCity"
        Me.txtCity.Size = New System.Drawing.Size(184, 20)
        Me.txtCity.TabIndex = 20
        Me.txtCity.Text = "Dallas"
        '
        'txtAddr
        '
        Me.txtAddr.Location = New System.Drawing.Point(82, 44)
        Me.txtAddr.Name = "txtAddr"
        Me.txtAddr.Size = New System.Drawing.Size(184, 20)
        Me.txtAddr.TabIndex = 19
        Me.txtAddr.Text = "10380 Highway St"
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(82, 16)
        Me.txtName.Name = "txtName"
        Me.txtName.Size = New System.Drawing.Size(184, 20)
        Me.txtName.TabIndex = 18
        Me.txtName.Text = "John Doe"
        '
        'lblZip
        '
        Me.lblZip.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblZip.Location = New System.Drawing.Point(142, 104)
        Me.lblZip.Name = "lblZip"
        Me.lblZip.Size = New System.Drawing.Size(32, 16)
        Me.lblZip.TabIndex = 17
        Me.lblZip.Text = "Zip:"
        '
        'lblState
        '
        Me.lblState.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblState.Location = New System.Drawing.Point(18, 100)
        Me.lblState.Name = "lblState"
        Me.lblState.Size = New System.Drawing.Size(52, 16)
        Me.lblState.TabIndex = 16
        Me.lblState.Text = "State:"
        '
        'lblCity
        '
        Me.lblCity.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblCity.Location = New System.Drawing.Point(18, 72)
        Me.lblCity.Name = "lblCity"
        Me.lblCity.Size = New System.Drawing.Size(52, 16)
        Me.lblCity.TabIndex = 15
        Me.lblCity.Text = "City:"
        '
        'lblAddr
        '
        Me.lblAddr.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblAddr.Location = New System.Drawing.Point(22, 44)
        Me.lblAddr.Name = "lblAddr"
        Me.lblAddr.Size = New System.Drawing.Size(52, 16)
        Me.lblAddr.TabIndex = 14
        Me.lblAddr.Text = "Address:"
        '
        'lblName
        '
        Me.lblName.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblName.Location = New System.Drawing.Point(22, 16)
        Me.lblName.Name = "lblName"
        Me.lblName.Size = New System.Drawing.Size(52, 16)
        Me.lblName.TabIndex = 13
        Me.lblName.Text = "Name:"
        '
        'Form2
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 186)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.btnClose)
        Me.Controls.Add(Me.btnAdd)
        Me.Controls.Add(Me.txtZip)
        Me.Controls.Add(Me.txtState)
        Me.Controls.Add(Me.txtCity)
        Me.Controls.Add(Me.txtAddr)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.lblZip)
        Me.Controls.Add(Me.lblState)
        Me.Controls.Add(Me.lblCity)
        Me.Controls.Add(Me.lblAddr)
        Me.Controls.Add(Me.lblName)
        Me.Name = "Form2"
        Me.Text = "Form2"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim myOleDbDataAdapter As OleDbDataAdapter
    Dim myDataSet As DataSet = New DataSet
    Dim myDataTable As DataTable = New DataTable
    Dim myOleDbConnection As OleDbConnection
    Dim myCommand As OleDbCommandBuilder
    Dim artKeySave As Integer
    Private Sub frmDataRowADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        myOleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "User ID=Admin;Data Source=" & sADODBName)
        myCommand = New OleDbCommandBuilder(myOleDbDataAdapter)

        Try
            myOleDbDataAdapter = New OleDbDataAdapter("Select * from " & sADOTableName, myOleDbConnection)
            myOleDbDataAdapter.Fill(myDataTable)
        Catch ex As Exception
            Console.WriteLine("Error Opening {0}", myOleDbConnection.DataSource)
        End Try
        myCommand = New OleDbCommandBuilder(myOleDbDataAdapter)
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            myOleDbDataAdapter.Fill(myDataSet)
            myDataTable = myDataSet.Tables(0)

            Dim rowVistaDB As DataRow = myDataTable.NewRow()

            rowVistaDB("Name") = txtName.Text
            rowVistaDB("Addr") = txtAddr.Text
            rowVistaDB("City") = txtCity.Text
            rowVistaDB("State") = txtState.Text
            rowVistaDB("Zip") = txtZip.Text

            myDataTable.Rows.Add(rowVistaDB)
            myOleDbDataAdapter.Update(myDataSet)

            MsgBox("Record Saved")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Application.Exit()
    End Sub
End Class
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AWestEngCommented:
This fucntion will take a datatable and insert that table to the database.

1:Fiill the datatable with your data.
2: Modify the data you have in you datattable
3: Insert that table to the database again.

You should not use the da.InsertCommand test using  da.UpdateCommand  if the records already exists
0
 
AWestEngCommented:
and tou need to change the Mysql.data to odbc when you use access
0
 
JeffreyDurhamAuthor Commented:
AWestEng:

I am most interested in your solution as it looks to me like it will do a batch update, as opposed to a per record update, which is what I really want. I've converted over the code you provided me partially, but I'm having trouble figuring out how to set the parameters

In access, these are my field names/types:
Autonumber - Long Integer, Autonumber
RecNum - (KEY!) Text (fieldSize=15)
Names - Memo
DocCode - Text (50)
DocCodeID - Number, Long Integer
RecDate - Date/Time

Here is the routine you've given me, as far as I've got it:

Public Sub BatchUpdate(ByVal dt As DataTable)
        Try
            Dim MyConString As String = My.Settings.myConnectionString

            'Create a SQLConnection, passing in the proper connection string
            Dim myConnection As New OleDb.OleDbConnection(MyConString)
            myConnection.Open()
            'Create a SQLDataAdapter
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()

            da.ContinueUpdateOnError = True
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
            da.AcceptChangesDuringFill = True

            'Create and configure a SQLCommand for the DataAdapter
            da.InsertCommand = New OleDb.OleDbCommand()

            With da.InsertCommand
                .Connection = myConnection                   'Set the connection
                .UpdatedRowSource = UpdateRowSource.None     'Must be set to none
                .CommandType = CommandType.Text              'Set the CommandType to Text

                ''Set the SQL Text, including parameters
                '"Autonumber","Names","RecNum","RecDate","DocCode","DocCodeID","Derogatory","origTimeStamp"
                .CommandText = _
                    "INSERT INTO AffInfo" & _
                    "(Autonumber, Names, RecNum, RecDate, DocCode, DocCodeID, Derogatory, origTimeStamp) " & _
                    "VALUES (?Autonumber, ?Names, ?RecNum, ?RecDate, ?DocCode, ?DocCodeID, ?Derogatory, ?origTimeStamp) " & _
                    "ON DUPLICATE KEY UPDATE DuplicateInsert = 1"

                'Add the parameters.  
                '    Make sure to map the source colum in our table to the parameter
                With .Parameters
                    '.Add("?Autonumber", System.Data.SqlDbType.Int, , "ImportDate")
                    '.Add("?Names", System.Data.SqlDbType.VarChar, 10, "FileDate")
                    '.Add("?FileType", System.Data.SqlDbType.VarChar, 15, "FileType")
                End With
            End With
            'Perform the insert to the SQL table.
            da.Update(dt)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

So how would I finish this? Should I change everything to a sql client instead of oledb, does that matter?

Thanks~Michael

0
 
JeffreyDurhamAuthor Commented:
I was also curious what this sql statement does? It's part of the statement you gave me..
ON DUPLICATE KEY UPDATE DuplicateInsert = 1
0
 
planoczCommented:
Autonumber is that a date or a counter?
If a counter by the database I don't tink you will be able to use it as one of the Parameter fileds,
since it would do it automatily.
0
 
AWestEngCommented:
Do you still need some help?
0
 
JeffreyDurhamAuthor Commented:
Sorry AWestEng. I forgot about the question. I ended up using your solution. It was awesome. Thanks again! ~Michael
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now