SQL question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
parallon
Forum Newbie
Posts: 6
Joined: Fri Jan 07, 2005 9:06 am

SQL question

Post by parallon »

Hello all. I am having a problem with the following statement. Basically, if a checkbox is true, then I want it to run the first command, but if it is not, I want it to run the second one. The second part works fine by itself, but once I added the IF/ELSE section, I am getting errors. I am sure it is in the UPDATE statement, but I am not sure what I am doing wrong. Any suggestions?

Here is the code:

Code: Select all

If toActuals = "-1" then
  sSQL = "INSERT INTO WOLabour (Employee,TransDate,WoNum,LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,ModifyDate,Estimate,Actual,Craft,ChargeBack,TaskNum,ChargeBackAmount) SELECT Employee," _
         & nextissue3 & ",'" & wo & "',LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,'" & curDate & "',1,Craft,ChargeBack,TaskNum,ChargeBackAmount FROM PMLabor WHERE PmNum = '" & pm & "'"
  sSQL = "UPDATE WOlabour SET Actual = '-1' WHERE PmNum = '" & pm & "'"

 
  else
  
  sSQL = "INSERT INTO WOLabour (Employee,TransDate,WoNum,LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,ModifyDate,Estimate,Craft,ChargeBack,TaskNum,ChargeBackAmount) SELECT Employee," _
         & nextissue3 & ",'" & wo & "',LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,'" & curDate & "',1,Craft,ChargeBack,TaskNum,ChargeBackAmount FROM PMLabor WHERE PmNum = '" & pm & "'"
  conn.Execute sSQL, , 128
  end if
Thanks in advance,

Parallon
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

where are you using this code?
parallon
Forum Newbie
Posts: 6
Joined: Fri Jan 07, 2005 9:06 am

Post by parallon »

Tahnk you for your response. Actually, I have come a long ways since then, and now I am stuck on what I think is the last part.

Ok, here is the situation, the following statement populates the table WOLabour and the primary key is an AutoNumber (WOLabour.Counter). Well, now that the data is in there, I need to store the value of Counter in a variable called Counter because the next SQL statement uses that number to populate a field called RefNum in the same table. It seems weird that two lines will have VERY similar information with a few changes, but it needs to be. If it is not necessary to use a variable, is there another way to do this?

Code: Select all

sSQL = "INSERT INTO WOLabour (Employee,TransDate,WoNum,LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,ModifyDate,Estimate,Craft,ChargeBackAmount,TaskNum) SELECT Employee," _
         & nextissue3 & ",'" & wo & "',LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,'" & curDate & "',1,Craft,ChargeBackAmount,TaskNum FROM PMLabor WHERE PmNum = '" & pm & "'"
  conn.Execute sSQL, , 128
Here is the following statement:

Code: Select all

sSQL = "INSERT INTO WOLabour (Employee,TransDate,WoNum,LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,ModifyDate,Estimate,RefNum,Craft,ChargeBackAmount,TaskNum) " & _
         "SELECT Employee,'" & txdate & "',WoNum,LaborType,Scale,Hours,Rate,Tax1,Tax2,AddCost,TotalCost,Account,Description,'" & curDate & "',0,Counter,Craft,ChargeBackAmount,TaskNum FROM WOLabour WHERE Counter IN (" & counters & ")"
  conn.Execute sSQL, , 128
I hope this makes sense...

Parallon
Post Reply