hi, i am trying to copy all the data from two columns from two different tables to another table. the problem is when i use insert statement for the second time it does not copy the data from the two columns. i was able to insert two columns from loanTable2 (that is my table 1) to another table tempTbl (that's the target table) but the second insert statement where in i would copy two columns from loanTable3 (that is my table 2) and put the data in tempTbl, it didn't go through.. no errors.. but it doesn't copy anything.. the columns in tempTbl, have no primary key set to it.. and it is the exact data type coming from the two tables.. by the way i'm using vb.net 2005 and ms access 2007 as my database. so basically my problem is, i want my second insert statement to work.. if i'm doing it wrong please guide me.. thanks..

Imports System.Data.OleDb

    Dim conString As String
    Dim cn As OleDbConnection
    Dim oledbAdapter As OleDbDataAdapter
    Dim firstSql, secondSql As String
    Dim tempSql1, tempSql2, tempSql3, delTempTbl As String
    Dim ds As New DataSet

        conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SampleDB.accdb;Persist Security Info=False;"
        firstSql = "select * from loanTable2"
        secondSql = "select * from loanTable3"
        tempSql1 = "select * from tempTbl"

'the exact order of columns in tempTbl is IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef

        tempSql2 = "INSERT INTO tempTbl (IDPrev, DaysPastDuePrev) SELECT ID, DaysPastDue FROM loanTable2"
        tempSql3 = "INSERT INTO tempTbl (IDRef, DaysPastDueRef) SELECT ID, DaysPastDue FROM loanTable3"

        delTempTbl = "delete from tempTbl"
        cn = New OleDbConnection(conString)


    'this one deletes the data in tempTbl before inserting anything
        Try
            cn.Open()
            oledbAdapter = New OleDbDataAdapter
            oledbAdapter.DeleteCommand = cn.CreateCommand
            oledbAdapter.DeleteCommand.CommandText = delTempTbl
            oledbAdapter.DeleteCommand.ExecuteNonQuery()
            oledbAdapter.Dispose()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    'this one works, it inserts data from two columns from loanTable2 in tempTbl
        Try
            cn.Open()
            oledbAdapter = New OleDbDataAdapter
            oledbAdapter.InsertCommand = New OleDbCommand(tempSql2, cn)
            oledbAdapter.InsertCommand.ExecuteNonQuery()
            oledbAdapter.Dispose()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    'this one doesn't work
    Try
            cn.Open()
            oledbAdapter = New OleDbDataAdapter
            oledbAdapter.InsertCommand = New OleDbCommand(tempSql3, cn)
            oledbAdapter.InsertCommand.ExecuteNonQuery()
            oledbAdapter.Dispose()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    'this views the results in datagridviews, also works...
        Try
            cn.Open()
            oledbAdapter = New OleDbDataAdapter(firstSql, cn)
            oledbAdapter.Fill(ds, "lnTbl2")
            oledbAdapter.SelectCommand.CommandText = secondSql
            oledbAdapter.Fill(ds, "lnTbl3")
            oledbAdapter.SelectCommand.CommandText = tempSql1
            oledbAdapter.Fill(ds, "tempTbl")

            oledbAdapter.Dispose()
            cn.Close()
            DataGridView1.DataSource = ds.Tables(0)
            DataGridView2.DataSource = ds.Tables(1)
            DataGridView3.DataSource = ds.Tables(2)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

I had a look at your two sql statements and found some discrepancies -

tempSql2 = "INSERT INTO tempTbl (IDPrev, DaysPastDuePrev) SELECT ID, DaysPastDue FROM loanTable2"
tempSql3 = "INSERT INTO tempTbl (IDRef, DaysPastDueRef) SELECT ID, DaysPastDue FROM loanTable3"

Shouldn't "IDRef" in tempsql3 be "IDPrev" and so on for all the other fields? Unless of course you have more fields in tempTbl...:)

My first question for you is why not use a SQL statement to select all data from both tables then JOIN the selected information into another table (temp or not). With single statement you can manipulate one table to the datagrid without handling three or more to do the same...
example..

tempSQL = "INSERT INTO tempTbl(IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef) SELECT loanTable2.IDPrev, loanTable3.IDRef, loanTable2.DaysPastDuePrev, loanTable3. DaysPastDueRef FROM loanTable2, loanTable3 WHERE (loanTable2.ID = ?) AND (loanTable3.ID = ?)"

This will select all information, insert it to the table and then you can manipulate the information that you need. It also removes the additional queries that are in your code, and makes one table. From here proceed to verify your data.

Edited 5 Years Ago by zinnqu: n/a

tempSQL = "INSERT INTO tempTbl(IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef) SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2, loanTable3 WHERE (loanTable2.ID = ?) AND (loanTable3.ID = ?)"

This is just a corrected form of what you gave as code... Just needed it to reflect your IDs from above...

I had a look at your two sql statements and found some discrepancies -

Shouldn't "IDRef" in tempsql3 be "IDPrev" and so on for all the other fields? Unless of course you have more fields in tempTbl...:)

AndreRet, nothing's wrong with that.. it is..as it is.. i have four columns in tempTbl, they are IDPrev, IDRef, DaysPastDuePrev and DaysPastDueRef. and i want the IDPrev and DaysPastDuePrev columns to have data coming from loanTable2's columns which are ID and DaysPastDue. and i want IDRef and DaysPastDueRef columns to have data coming from loanTable3's columns which are ID and DaysPastDue.

"INSERT INTO tempTbl(IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef) SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2, loanTable3

sorry to tell you zinnqu, this one doesn't work and it's ruining my database.. after i used your query, i can't use the database (the tables inside the database) for inserting anymore.. i can only read files.. like displaying the table in the datagridview but i can't run the insert query anymore it says that oledbexception - the database can not be recognized or the file is corrupt. it happened twice. i made another database to make sure.. first i ran my insert query and it worked just fine.. after that, i ran yours.. it gave me that error message.. and so, i ran again my own insert statement.. it can't anymore and it gives the same error message.. what the did you do...? did you try the code yourself..?

guys, what i resorted to is this..

"SELECT loanTable2.ID as IDPrev, loanTable3.ID as IDRef, loanTable2.DaysPastDue as DaysPastDuePrev, loanTable3.DaysPastDue as DaysPastDueRef FROM loanTable2 LEFT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID UNION SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2 RIGHT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID"

since there's no full join in ms access... this one works fine when i use this for the dataset.. and that dataset will be the datasource for the datagridview.. but when i use this query for the insert statement, like this one..

"INSERT INTO tempTable (IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef) SELECT loanTable2.ID as IDPrev, loanTable3.ID as IDRef, loanTable2.DaysPastDue as DaysPastDuePrev, loanTable3.DaysPastDue as DaysPastDueRef FROM loanTable2 LEFT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID UNION SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2 RIGHT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID"

it doesn't work it says in oledbexception that syntax error (missing operator) in query expression 'loanTable2.ID = loanTable3.ID UNION SELECT loanTable2.ID, loanTable3.I'
guys do you have any suggestion or any better than what i'm trying to do..? thanks in advance..

let me correct the second code snippet, it should be this one:

"INSERT INTO tempTable (IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef) SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2 LEFT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID UNION SELECT loanTable2.ID, loanTable3.ID, loanTable2.DaysPastDue, loanTable3.DaysPastDue FROM loanTable2 RIGHT JOIN loanTable3 ON loanTable2.ID = loanTable3.ID"

The anwer to your question is yes, I did run it, as I have a database that is similar to yours. Mine is used for IT equopment. The query is the same just diffrent values. Mine works fine as it is used daily.

"INSERT INTO tmptbl ( IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef )
SELECT tmptbl1.ID, tmptbl2.ID, tmptbl2.DaysPastDue, tmptbl1.dayspastdue
FROM tmptbl2, tmptbl1
WHERE (((tmptbl1.ID)=[tmptbl2].[ID]));
"

this is a good query that I just built tested and have the DB ready if you would like to see it. There is no need for all of the Join statements. If you have any questions PM me any I'll give you the info required

"INSERT INTO tmptbl ( IDPrev, IDRef, DaysPastDuePrev, DaysPastDueRef )
SELECT tmptbl1.ID, tmptbl2.ID, tmptbl2.DaysPastDue, tmptbl1.dayspastdue
FROM tmptbl2, tmptbl1
WHERE (((tmptbl1.ID)=[tmptbl2].[ID]));
"

this is a good query that I just built tested and have the DB ready if you would like to see it. There is no need for all of the Join statements. If you have any questions PM me any I'll give you the info required

zinnqu.. it really doesn't work with me.. this code.. maybe this is not applicable to ms access 2007.. or vb.net 2005 calling ms access 2007... is there any other work around that you can suggest.. :-(

This article has been dead for over six months. Start a new discussion instead.