SQL controlled checkbox?

[adam2804]

Hello,

I hope you can help me as this thing is doing my head in!

I’ve been asked to create a page that shows a checkbox based on a certain SQL condition, if this condition is true the checkbox is shown and checked by default, however the user can choose to uncheck it.

When the user submits the form the checkbox should control an insert query that multiplies a quantity figure by the ‘upload figure’.

The condition that the checkbox is shown is
Request(“WorkType”) = ‘P1’
AND
SELECT Customers.UpliftIndex
FROM Customers INNER JOIN
Jobs ON Customers.CustomerID = Jobs.CustomerID
WHERE (Customers.UpliftIndex = 1) AND (Jobs.JobNumber = ::JobNumber::)

For the upload multiplier this is how the figure is retrieved from the DB:

(SELECT TB_Site.UpliftMultiplier
FROM TB_Site INNER JOIN Customers ON TB_Site.ID = Customers.SiteID INNER JOIN Jobs ON Customers.CustomerID = Jobs.CustomerID
WHERE (Jobs.JobNumber = ::JobNumber::)))

And the insert query works like this:

INSERT INTO WorkDetails ( JobNumber, PayrollNumber, WorkDate, StartTime, EndTime, WorkType, Quantity, Hours, MachineID, Description, BoxNumber ) VALUES ( '::JobNumber::', '::EmployeeID::', '::WorkDate::', '::StartTime2::', DATEADD(hh, ::Hours::, '::StartTime2::'), '::WorkType::', (::Quantity:: * ::MultiplierIndex::), ::Hours::, '::ScannerID::', 'Added by Add Work Item Intranet Page', ::BoxID:: )

Now the problems I am having:

- I can’t get the checkbox to show on the SQL condition
- I can’t assign a value to a variable so that I can retrieve it for the INSERT query (MultiplierIndex) – I can’t use a subquery by the way

Any help would be gratefully received, any more information that you need please ask and I can provide.

Thanks

Adam

[bud]

Write a simple page with a DB Region to view the database. Include the check box value from the DB.

You will see that the Value returned in the Region will be a YES or NO value.

So you need to convert the checkbox to either YES or NO

Yes it's checked, NO it's not.

dim worktype

If Request(“WorkType”) = CHECKED Then
worktype = "YES"
else
worktype = "NO"
End If

dim insStr
insStr = "INSERT INTO WorkDetails ( JobNumber, PayrollNumber, WorkDate, StartTime, EndTime, WorkType, Quantity, Hours, MachineID, Description, BoxNumber ) VALUES ( '::JobNumber::', '::EmployeeID::', '::WorkDate::', '::StartTime2::', DATEADD(hh, ::Hours::, '::StartTime2::'), '::" & worktype & "::', (::Quantity:: * ::MultiplierIndex::), ::Hours::, '::ScannerID::', 'Added by Add Work Item Intranet Page', ::BoxID:: )"

When reading the values do the same thing in reverse.

<input type="checkbox" name="C1" value="ON" <% If rs("WorkType") = "YES" Then %> checked<% end if %>>