Member Avatar for Nebil

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.

  • 2 Contributors
  • 2 Replies
  • 282 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Nebil
Member Avatar for Reverend Jim

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 by Reverend Jim
Member Avatar for Nebil

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.