Problems with checkbox value stored in database

microsoft.public.inetserver.asp.general

24 Feb 2005 11:43 PM

Jack

Hi, I have a checkbox the value which goes to a database via a asp page that
builds the sql string.

In the front end asp page, the checkbox code is written as follows:

<input type="checkbox" name="chk_Complete" value="<%Response.Write
l_IsChecked%>"<%if cbool(l_IsChecked) then Response.Write " checked"%>>

The code to captures the checkbox value in the asp page that builds the sql
string is follows

l_f_IsChecked = Request.Form("chk_Complete")

With this, when the database is already checked (and the checkbox shows
checked) unchecking and saving makes
the value of the checkbox value in database unchecked. At this point when
the asp front-end page is refreshed
it shows the checkbox in unchecked state(should be so). Now if I want to
check the checkbox and save in the
database, the checkbox value in the database does not get changed to checked.

I am wondering, where I am going wrong. Thanks for any help in advance.

24 Feb 2005 11:58 PM

Bob Barrows [MVP]

Jack wrote:
Show quote

> Hi, I have a checkbox the value which goes to a database via a asp
> page that builds the sql string.
>
> In the front end asp page, the checkbox code is written as follows:
>
> <input type="checkbox" name="chk_Complete" value="<%Response.Write
> l_IsChecked%>"<%if cbool(l_IsChecked) then Response.Write "
> checked"%>>
>
> The code to captures the checkbox value in the asp page that builds
> the sql string is follows
>
> l_f_IsChecked = Request.Form("chk_Complete")
>
> With this, when the database is already checked (and the checkbox
> shows checked) unchecking and saving makes
> the value of the checkbox value in database unchecked. At this point
> when the asp front-end page is refreshed
> it shows the checkbox in unchecked state(should be so). Now if I
> want to check the checkbox and save in the
> database, the checkbox value in the database does not get changed to
> checked.
>
> I am wondering, where I am going wrong. Thanks for any help in
> advance.

I think your symptom is that you make a change to the checkbox, submit the
form, but the change does not get written to the database. Is that correct?
If so, how can we tell you what is wrong without seeing the code that
processes the checkbox value and writes the result to the database?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

25 Feb 2005 6:05 PM

Jack

Bob,
I apologize for the late response. The actual problem is that when the
checkbox is checked in the front-end and it is unchecked and saved, the
Request.Form("chk_Complete") value is not false, instead it is a null value,
I am attaching the sql string that is built here:
START CHECK COMPLETE

THE END
StartCheckBox

EndCheckbox

UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$550.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$100.50' ,
tblGMISExpenditures_Quarter.MBCCShare= '449.5' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$250.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$35.35' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$100.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$200.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$30.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$40.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$20.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '4568.39' ,
tblGMISExpenditures_Quarter.Remarks= 'Testing GWIS' ,
tblGMISExpenditures_Quarter.Name= 'Jack Jones' ,
tblGMISExpenditures_Quarter.Title= 'Manager' ,
tblGMISExpenditures_Quarter.AreaCode= '111' ,
tblGMISExpenditures_Quarter.Phone1= '111' ,
tblGMISExpenditures_Quarter.Phone2= '8762' ,
tblGMISExpenditures_Quarter.Date= '1/11/2005' ,
tblGMISExpenditures_Quarter.IsChecked= where
tblGMISExpenditures_Quarter.SubgrantIntID = 4836;

The CODE for database entry is as follows:
<%@ Language=VBScript %>
<%
'The following line is to prevent this page coming from history.
'We need a new page from the server each time so that all the
'session vairables are reset
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

 
<%

'myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; "
'myDSN=myDSN & "DBQ=C:\_______GWISBRANDNEWREADY5\GMISDATA.mdb"

myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\_______GWISBRANDNEWREADY5\GMISDATA.mdb"

set CN=server.createobject("ADODB.Connection")
CN.Open myDSN
'ecRS stands for recordset corresponding to the current expense
set ecRS=server.createobject("ADODB.Recordset")
'recRS stands for recordset corresponding to the recordcount of the current
expense
set recRS = server.CreateObject ("ADODB.Recordset")
'detecRS stands for the detailed recordset corresponding to the current
expense
set detecRS = server.CreateObject("ADODB.Recordset")

recRS.ActiveConnection = CN
ecRS.ActiveConnection = CN
detecRs.ActiveConnection = CN

'GET GRANT ID FROM SESSION ID
GrantID = Session("sess_GrantID")

SQL = "SELECT COUNT(*) AS reccount FROM tblGMISExpenditures_Quarter WHERE
SubgrantIntID = " & GrantID & ";"

 recRS.Open SQL
'If there is no record in the expense table corresponding to the current
subgrantid number then
'insert the subgrantid number in the tblGMISExpenditures_Quarter table.
 if recRS("reccount") < 1 then
 recRS.Close

 strSQL = "INSERT INTO tblGMISExpenditures_Quarter(SubgrantIntID)
VALUES ("& GrantID &")"

 ecRS.Open strSQL
 'else
 end if
' In the event there is already the subgrantintid in the above table, we
just need to update
' the table with the input values of the main form.

'Define local variables to store the values obtained from input form(either
text or calculated fields)

Dim l_CurrentOutlay
Dim l_CurrentLocalShareOutlay
Dim l_c_MBCCShareOfOutlay 'c represents calculated
Dim l_CurrentUnpaidObligations
Dim l_CurrentLocalShareUnpaidObligations
Dim l_CurrentOtherProjectIncomeReceived
Dim l_CurrentForfeitureIncomeReceived
Dim l_CurrentOtherProjectIncomeExpenditures
Dim l_CurrentForfeitureIncomeExpenditures
Dim l_CurrentInterestIncomeReceivedOnMBCCFunds
Dim l_p_c_TotalOutlay ' c represents calculated
Dim l_f_remarks ' Note that f stands for final remark to distinguish between
variable in the main form
'Similary, the following would be the variables for other contact fields
Dim l_f_name
Dim l_f_personstitle
Dim l_f_PhoneAreaCode
Dim l_f_Phone1
Dim l_f_Phone2
Dim l_f_Date
'The following variable is being added in order to add the value of checkbox
in the database
Dim l_f_IsChecked

'Now store the values collected from the main form to the local variables
 l_CurrentOutlay = Request.Form("txt_CurrentOutlay")
 l_CurrentLocalShareOutlay = Request.Form("txt_CurrentLocalShareOutlay")
 'THIS IS THE CODE INTRODUCED HERE TO FORCE THE SESSION VALUE TO 0
 If (Session("l_cu_c_MBCCShareOfOutlays"))= "" Then
 Session("l_cu_c_MBCCShareOfOutlays") = 0
 End If
 'END OF THE CODE INTRODUCED WITH SESSION VALUE SETTING
 l_c_MBCCShareOfOutlay = Session("l_cu_c_MBCCShareOfOutlays")
 l_CurrentUnpaidObligations = Request.Form("txt_CurrentUnpaidObligations")
 l_CurrentLocalShareUnpaidObligations =
Request.Form("txt_CurrentLocalShareUnpaidObligations")
 l_CurrentOtherProjectIncomeReceived =
Request.Form("txt_CurrentOtherProjectIncomeReceived")
 l_CurrentForfeitureIncomeReceived =
Request.Form("txt_CurrentForfeitureIncomeReceived")
 l_CurrentOtherProjectIncomeExpenditures =
Request.Form("txt_CurrentOtherProjectIncomeExpenditures")
 l_CurrentForfeitureIncomeExpenditures =
Request.Form("txt_CurrentForfeitureIncomeExpenditures")
 l_CurrentInterestIncomeReceivedOnMBCCFunds =
Request.Form("txt_CurrentInterestIncomeReceivedOnMBCCFunds")
 If Session("l_p_TotalOutlay")= "" Then
 Session("l_p_TotalOutlay") = 0
 End If

 l_p_c_TotalOutlay = Session("l_p_TotalOutlay")
 l_f_Remarks = trim(Request.Form("txt_Remarks"))

 l_f_name = trim(Request.Form("txt_Name"))
 l_f_personstitle = trim(Request.Form("txt_Title"))
 l_f_PhoneAreaCode = trim(Request.Form("txt_PhoneAreaCode"))
 l_f_Phone1 = trim(Request.Form("txt_Phone1"))
 l_f_Phone2 = trim(Request.Form("txt_Phone2"))
 l_f_Date = Request.Form("txt_Date")
 'If (Request.Form("chk_Complete")) = "" then
 ' Request.Form("chk_Complete") = "false"
 'End If
 l_f_IsChecked = Request.Form("chk_Complete")

' Response.Write "Start_Check_l_f_IsChecked" & " "

' Response.Write l_f_IsChecked & " "

' Response.Write "End_Check_l_f_IsChecked" & " "

 Response.Write "START CHECK COMPLETE" & " "
 Response.Write Request.Form("chk_Complete") & " "
 Response.Write "THE END" & " "

'---------START OF DEBUGGING TOOLS FOR EACH FIELD RETRIEVED FROM AND SAVED
IN DATABASE-------

'Response.Write "StartCurrentOutlay" & " "

'Response.Write l_CurrentOutlay & " "

'Response.Write "EndCurrentOutlay" & " "'

'Response.Write "StartCurrentLocalShareOutlay" & " "

'Response.Write l_CurrentLocalShareOutlay & " "

'Response.Write "EndCurrentLocalShareOutlay" & " "

'Response.Write "Testing MBCC Share Of Outlay" & " "

'Response.Write l_c_MBCCShareOfOutlay & " "

'Response.Write Session("l_cu_c_MBCCShareOfOutlays") & " "
'Response.Write "EndCurrentMBCCShareOfOutlay" & " "

'Response.Write "StartCurrentUnpaidObligations" & " "

'Response.Write l_CurrentUnpaidObligations & " "

'Response.Write "EndCurrentUnpaidObligations" & " "

'Response.Write "StartCurrentLocalShareUnpaidObligations" & " "

'Response.Write l_CurrentLocalShareUnpaidObligations & " "

'Response.Write "EndCurrentLocalShareUnpaidObligations" & " "

'Response.Write "StartCurrentOtherProjectIncomeReceived" & " "

'Response.Write l_CurrentOtherProjectIncomeReceived & " "

'Response.Write "EndCurrentOtherProjectIncomeReceived" & " "

'Response.Write "StartCurrentForfeitureIncomeReceived" & " "

'Response.Write l_CurrentForfeitureIncomeReceived & " "

'Response.Write "EndCurrentForfeitureIncomeReceived" & " "

'Response.Write "StartCurrentOtherProjectIncomeExpenditures" & " "

'Response.Write l_CurrentOtherProjectIncomeExpenditures & " "

'Response.Write "EndCurrentOtherProjectIncomeExpenditures" & " "

'Response.Write "StartCurrentForfeitureIncomeExpenditures" & " "

'Response.Write l_CurrentForfeitureIncomeExpenditures & " "

'Response.Write "EndCurrentForfeitureIncomeExpenditures" & " "

'Response.Write "StartPeiodicOutlay" & " "

'Response.Write l_p_c_TotalOutlay & " "

'Response.Write "EndPeriodicOutlay" & " "

'Response.Write "StartRemarks" & " "

'Response.Write l_f_Remarks & " "

'Response.Write "EndRemarks" & " "

'Response.Write "StartName" & " "

'Response.Write l_f_Name & " "

'Response.Write "EndName" & " "

'Response.Write "StartPersonTitle" & " "

'Response.Write l_f_personstitle & " "

'Response.Write "EndPersonTitle" & " "

'Response.Write "StartPhoneAreaCode" & " "

'Response.Write l_f_PhoneAreaCode & " "

'Response.Write "EndPhoneAreaCode" & " "

'Response.Write "StartPhone1" & " "

'Response.Write l_f_Phone1 & " "

'Response.Write "EndPhone1" & " "

'Response.Write "StartPhone2" & " "

'Response.Write l_f_Phone2 & " "

'Response.Write "EndPhone2" & " "

'If l_f_IsChecked = "" Then
' l_f_Checked = False
'End If

Response.Write "StartCheckBox" & " "

Response.Write l_f_IsChecked & " "

Response.Write "EndCheckbox" & " "

'---------END OF DEBUGGING TOOLS FOR EACH FIELD RETRIEVED FROM AND SAVED IN
DATABASE-------

 'NOW START BUILDING THE SQL STATEMENT TO UPDATE THE CURRENTQUARTERVALUES
'Response.Write " "
'Response.Write "Hello"
 strSQL1 = "UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '" & l_CurrentOutlay & "' " & _
 ", tblGMISExpenditures_Quarter.LocalShare= '" &
l_CurrentLocalShareOutlay & "' " & _
 ", tblGMISExpenditures_Quarter.MBCCShare= '" &
l_c_MBCCShareOfOutlay & "' " & _
 ", tblGMISExpenditures_Quarter.UnpaidOblig= '" &
l_CurrentUnpaidObligations & "' " & _
 ", tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '" &
l_CurrentLocalShareUnpaidObligations & "' " & _
 ", tblGMISExpenditures_Quarter.ProjectIncome= '" &
l_CurrentOtherProjectIncomeReceived & "' " & _
 ", tblGMISExpenditures_Quarter.ForfeitureIncome= '" &
l_CurrentForfeitureIncomeReceived & "' " & _
 ", tblGMISExpenditures_Quarter.OtherExpense= '" &
l_CurrentOtherProjectIncomeExpenditures & "' " & _
 ", tblGMISExpenditures_Quarter.ForfeitureExpense= '" &
l_CurrentForfeitureIncomeExpenditures & "' " & _
 ", tblGMISExpenditures_Quarter.InterestReceived= '" &
l_CurrentInterestIncomeReceivedOnMBCCFunds & "' " & _
 ", tblGMISExpenditures_Quarter.TotalPeriodOutlay= '" &
l_p_c_TotalOutlay & "' " & _
 ", tblGMISExpenditures_Quarter.Remarks= '" & l_f_Remarks & "' " & _
 ", tblGMISExpenditures_Quarter.Name= '" & l_f_Name & "' " & _
 ", tblGMISExpenditures_Quarter.Title= '" & l_f_personstitle & "' "
& _
 ", tblGMISExpenditures_Quarter.AreaCode= '" & l_f_PhoneAreaCode &
"' " & _
 ", tblGMISExpenditures_Quarter.Phone1= '" & l_f_Phone1 & "' " & _
 ", tblGMISExpenditures_Quarter.Phone2= '" & l_f_Phone2 & "' " & _
 ", tblGMISExpenditures_Quarter.Date= '" & l_f_Date & "' " & _
 ", tblGMISExpenditures_Quarter.IsChecked= " & l_f_IsChecked & " "
& _
 " where " & _
 " tblGMISExpenditures_Quarter.SubgrantIntID = " & GrantID & ";"

Response.Write " "
Response.Write strSQL1
Response.Write " "
'Now open the recordset to update the current record'
detecRs.Open strSQL1

Hope this helps

Show quote

"Bob Barrows [MVP]" wrote:

> Jack wrote:
> > Hi, I have a checkbox the value which goes to a database via a asp
> > page that builds the sql string.
> >
> > In the front end asp page, the checkbox code is written as follows:
> >
> > <input type="checkbox" name="chk_Complete" value="<%Response.Write
> > l_IsChecked%>"<%if cbool(l_IsChecked) then Response.Write "
> > checked"%>>
> >
> > The code to captures the checkbox value in the asp page that builds
> > the sql string is follows
> >
> > l_f_IsChecked = Request.Form("chk_Complete")
> >
> > With this, when the database is already checked (and the checkbox
> > shows checked) unchecking and saving makes
> > the value of the checkbox value in database unchecked. At this point
> > when the asp front-end page is refreshed
> > it shows the checkbox in unchecked state(should be so). Now if I
> > want to check the checkbox and save in the
> > database, the checkbox value in the database does not get changed to
> > checked.
> >
> > I am wondering, where I am going wrong. Thanks for any help in
> > advance.
>
> I think your symptom is that you make a change to the checkbox, submit the
> form, but the change does not get written to the database. Is that correct?
> If so, how can we tell you what is wrong without seeing the code that
> processes the checkbox value and writes the result to the database?
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

25 Feb 2005 6:50 PM

Bob Barrows [MVP]

Jack wrote:

> Bob,
> I apologize for the late response. The actual problem is that when the
> checkbox is checked in the front-end and it is unchecked and saved,
> the Request.Form("chk_Complete") value is not false, instead it is a
> null value, I am attaching the sql string that is built here:

So you are passing the value directly? That will not do, as you have found
out. Use an If statement to generate the correct value to pass to your
update query.

The following cannot work:
'If (Request.Form("chk_Complete")) = "" then
' Request.Form("chk_Complete") = "false"
'End If
You cannot change the value of a variable in the Form collection: it's read
only. Simply do this:

Dim sChecked
sChecked = "true"
If len(Request.Form("chk_Complete")) = 0 then sChecked="false"

Then use the sChecked variable to build your sql statement. Better yet, read
one of my posts from this week about using parameters.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

25 Feb 2005 7:13 PM

Jack

Thanks Bob for your generous help. Your answer was a new revelation regarding
read-only Form Collection variable. I tested the code and it works great.
Thanks a ton. About using the parameter I have checked one of your article.
It has not sunk in yet. May be it will soon, I hope. Then I will not have to
build the complicated sql strings. Regards.
Show quote

"Bob Barrows [MVP]" wrote:

> Jack wrote:
> > Bob,
> > I apologize for the late response. The actual problem is that when the
> > checkbox is checked in the front-end and it is unchecked and saved,
> > the Request.Form("chk_Complete") value is not false, instead it is a
> > null value, I am attaching the sql string that is built here:
>
> So you are passing the value directly? That will not do, as you have found
> out. Use an If statement to generate the correct value to pass to your
> update query.
>
> The following cannot work:
> 'If (Request.Form("chk_Complete")) = "" then
> ' Request.Form("chk_Complete") = "false"
> 'End If
> You cannot change the value of a variable in the Form collection: it's read
> only. Simply do this:
>
> Dim sChecked
> sChecked = "true"
> If len(Request.Form("chk_Complete")) = 0 then sChecked="false"
>
> Then use the sChecked variable to build your sql statement. Better yet, read
> one of my posts from this week about using parameters.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>