Hi guys,
A problem came up when i tried to copy record from the temp. table to the main one.I was copying records to two main tables but one table is empty but the other works fine.Here's the code i used:

    projcode = TextBox4.Text
    projname = TextBox5.Text
    description = TextBox6.Text

    qry = "INSERT INTO [ptempTable] ([project number],[project name],[description]) VALUES ('" & projcode & "','" & projname ,"'" & description & "')"
    qry1 = "INSERT INTO project SELECT * FROM ptempTable"
    qry2 = "INSERT INTO per_diem_accomodation SELECT * FROM pntempTable"
    qry3 = "DROP TABLE ptempTable"
    qry4 = "DROP TABLE pntempTable"
    val = ExecSQL(qry)
    val1 = ExecSQL(qry1)
    val2 = ExecSQL(qry2)
    val3 = ExecSQL(qry3)
    val4 = ExecSQL(qry4)
    If val = True And val1 = True And val2 = True And val3 = True And val4 = True Then
    MsgBox("Project with per diem allocation saved", MsgBoxStyle.Information, "Project Payroll")
    End If

I tried to copy the data from ptemp to the project table,still the ptemp is holding the data but it's empty but for the per_diem table,it works fine though the insert statement is in another place.

Do all of the tables have the same structure? What is that structure? Are you getting any error messages? Try changing

qry = "INSERT INTO [ptempTable] ([project number],[project name],[description]) VALUES ('" & projcode & "','" & projname ,"'" & description & "')"

to

qry = "INSERT INTO [ptempTable] ([project number],[project name]," &
      "[description]) VALUES ('" & projcode & "','" & projname & "','" & 
      description & "')"

It looks like you have a formatting error between projname and description. Are you using OleDB, or SqlDB? If you use parameterized queries you would be less likely to see these types of formatting errors. Please see here for examples of both.

Edited 2 Years Ago by Reverend Jim

I just placed qry another place and it worked.But there was no formatting error,to see the effect i had written it on a single line.I think placing the records into a temp table and then copying doesn't work in the same event.But thanks anyway

This question has already been answered. Start a new discussion instead.