Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

insert statement problem

Discussion in 'Information Technology' started by jonathandrott@gmail.com, Jul 28, 2006.

  1. jonathandrott@gmail.com

    jonathandrott@gmail.com
    Expand Collapse
    Guest

    i'm trying to move items from one database to another using the insert
    statement. some colums have the same names. can someone help me with
    the syntax here?

    Dim dbILSA1Adapter As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(
    "Insert into PDAReorder (PLU_NUM, Qty, EST_COST, Description) Select
    PLU_NUM, VENDOR_ID, VEND_PUR_COST, VEND_DESC from Driver={Microsoft
    Access Driver (*.mdb)};Dbq=" & Me.TextBox1.Text & ";", connILSADB)
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Simpler with two access db's

    Currentdb.execute "Insert into PDAReorder (PLU_NUM, Qty, EST_COST,
    Description) Select
    PLU_NUM, VENDOR_ID, VEND_PUR_COST, VEND_DESC from [" & Me.TextBox1.Value &
    "] IN 'c:\temp\fromdb.mdb'"

    hth

    Pieter

    <jonathandrott@gmail.com> wrote in message
    news:1148394821.353883.53410@g10g2000cwb.googlegroups.com...
    > i'm trying to move items from one database to another using the insert
    > statement. some colums have the same names. can someone help me with
    > the syntax here?
    >
    > Dim dbILSA1Adapter As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(
    > "Insert into PDAReorder (PLU_NUM, Qty, EST_COST, Description) Select
    > PLU_NUM, VENDOR_ID, VEND_PUR_COST, VEND_DESC from Driver={Microsoft
    > Access Driver (*.mdb)};Dbq=" & Me.TextBox1.Text & ";", connILSADB)
    >
     
  4. jonathandrott@gmail.com

    jonathandrott@gmail.com
    Expand Collapse
    Guest

    thanks for the quick response. do i add this in below the code or
    change the code? sorry, not really a good programmer.
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    change the code

    Pieter

    <jonathandrott@gmail.com> wrote in message
    news:1148396588.976438.287850@i39g2000cwa.googlegroups.com...
    > thanks for the quick response. do i add this in below the code or
    > change the code? sorry, not really a good programmer.
    >
     
  6. jonathandrott@gmail.com

    jonathandrott@gmail.com
    Expand Collapse
    Guest

    still having problems. here's all the code for the btn click event:

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Button5.Click
    'opening connection to db with file dialog
    Dim connDB As Odbc.OdbcConnection = New Odbc.OdbcConnection
    Try
    MsgBox("Select 'Ilsatemp.mdb' file.")
    OpenFileDialog1.ShowDialog()
    TextBox1.Text = OpenFileDialog1.FileName

    connDB.ConnectionString = "Driver={Microsoft Access Driver
    (*.mdb)};Dbq=" & Me.TextBox1.Text & ";"

    Dim dbAdapter As Odbc.OdbcDataAdapter = New
    Odbc.OdbcDataAdapter("select * from PDAReorder_Pending", connDB)

    Dim dbTable As New DataTable
    dbAdapter.Fill(dbTable)
    Me.DataGridView1.DataSource = dbTable

    Dim intCounter As Integer
    intCounter = DataGridView1.Rows.Count
    Label2.Text = intCounter - 1
    ' opening another database to transfer fields from temp to
    ilsa
    Dim connIlsaDB As Odbc.OdbcConnection = New
    Odbc.OdbcConnection

    Try
    MsgBox("Select the 'Ilsa.mdb' file to open.")
    OpenFileDialog1.ShowDialog()
    Label6.Visible = True
    Label6.Text = "Database: " & OpenFileDialog1.FileName &
    " is open"
    connIlsaDB.ConnectionString = "Driver={Microsoft Access
    Driver (*.mdb)};Dbq=" & Me.TextBox1.Text & ";"

    'Below i'm copy the data from the temp table to the
    ilsa table
    Dim dbILSA1Adapter As Odbc.OdbcDataAdapter = New
    Odbc.OdbcDataAdapter("Insert into POrdData (VEND_PART_NUM, PLU_NUM,
    QTY, EST_COST) Values (VendorPart, PLU_NUM, Qty, EST_COST) from
    PDAReorder_Pending", connIlsaDB)

    'Ending the Copy table functions
    Catch ex As Exception
    MsgBox("Error: " & ex.Message)

    Finally
    If connIlsaDB.State <> ConnectionState.Closed Then
    connIlsaDB.Close()
    End If
    End Try

    Catch ex As Exception
    MsgBox("Error: " & ex.Message)

    Finally
    If connDB.State <> ConnectionState.Closed Then
    connDB.Close()
    End If
    End Try
    End Sub
     

Share This Page