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
