Problems with checkbox value stored in database

Discussion in 'ASP General' started by Jack, Feb 24, 2005.

  1. Jack

    Jack Guest

    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:

    <i><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.
    Jack, Feb 24, 2005
    #1
    1. Advertising

  2. 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:
    >
    > <i><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"
    Bob Barrows [MVP], Feb 24, 2005
    #2
    1. Advertising

  3. Jack

    Jack Guest

    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>

    <P> </P>
    <%

    '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" & "<BR>"
    ' Response.Write l_f_IsChecked & "<br>"
    ' Response.Write "End_Check_l_f_IsChecked" & "<BR>"

    Response.Write "START CHECK COMPLETE" & "<BR>"
    Response.Write Request.Form("chk_Complete") & "<br>"
    Response.Write "THE END" & "<BR>"

    '---------START OF DEBUGGING TOOLS FOR EACH FIELD RETRIEVED FROM AND SAVED
    IN DATABASE-------
    'Response.Write "StartCurrentOutlay" & "<br>"
    'Response.Write l_CurrentOutlay & "<br>"
    'Response.Write "EndCurrentOutlay" & "<br>"'

    'Response.Write "StartCurrentLocalShareOutlay" & "<br>"
    'Response.Write l_CurrentLocalShareOutlay & "<br>"
    'Response.Write "EndCurrentLocalShareOutlay" & "<br>"

    'Response.Write "Testing MBCC Share Of Outlay" & "<br>"
    'Response.Write l_c_MBCCShareOfOutlay & "<br>"
    'Response.Write Session("l_cu_c_MBCCShareOfOutlays") & "<br>"
    'Response.Write "EndCurrentMBCCShareOfOutlay" & "<br>"

    'Response.Write "StartCurrentUnpaidObligations" & "<br>"
    'Response.Write l_CurrentUnpaidObligations & "<br>"
    'Response.Write "EndCurrentUnpaidObligations" & "<br>"

    'Response.Write "StartCurrentLocalShareUnpaidObligations" & "<br>"
    'Response.Write l_CurrentLocalShareUnpaidObligations & "<br>"
    'Response.Write "EndCurrentLocalShareUnpaidObligations" & "<br>"

    'Response.Write "StartCurrentOtherProjectIncomeReceived" & "<br>"
    'Response.Write l_CurrentOtherProjectIncomeReceived & "<br>"
    'Response.Write "EndCurrentOtherProjectIncomeReceived" & "<br>"

    'Response.Write "StartCurrentForfeitureIncomeReceived" & "<br>"
    'Response.Write l_CurrentForfeitureIncomeReceived & "<br>"
    'Response.Write "EndCurrentForfeitureIncomeReceived" & "<br>"

    'Response.Write "StartCurrentOtherProjectIncomeExpenditures" & "<br>"
    'Response.Write l_CurrentOtherProjectIncomeExpenditures & "<br>"
    'Response.Write "EndCurrentOtherProjectIncomeExpenditures" & "<br>"

    'Response.Write "StartCurrentForfeitureIncomeExpenditures" & "<br>"
    'Response.Write l_CurrentForfeitureIncomeExpenditures & "<br>"
    'Response.Write "EndCurrentForfeitureIncomeExpenditures" & "<br>"

    'Response.Write "StartPeiodicOutlay" & "<br>"
    'Response.Write l_p_c_TotalOutlay & "<br>"
    'Response.Write "EndPeriodicOutlay" & "<br>"

    'Response.Write "StartRemarks" & "<br>"
    'Response.Write l_f_Remarks & "<br>"
    'Response.Write "EndRemarks" & "<br>"

    'Response.Write "StartName" & "<br>"
    'Response.Write l_f_Name & "<br>"
    'Response.Write "EndName" & "<br>"

    'Response.Write "StartPersonTitle" & "<br>"
    'Response.Write l_f_personstitle & "<br>"
    'Response.Write "EndPersonTitle" & "<br>"

    'Response.Write "StartPhoneAreaCode" & "<br>"
    'Response.Write l_f_PhoneAreaCode & "<br>"
    'Response.Write "EndPhoneAreaCode" & "<br>"

    'Response.Write "StartPhone1" & "<br>"
    'Response.Write l_f_Phone1 & "<br>"
    'Response.Write "EndPhone1" & "<br>"

    'Response.Write "StartPhone2" & "<br>"
    'Response.Write l_f_Phone2 & "<br>"
    'Response.Write "EndPhone2" & "<br>"

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

    Response.Write "StartCheckBox" & "<br>"
    Response.Write l_f_IsChecked & "<br>"
    Response.Write "EndCheckbox" & "<br>"

    '---------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 "<br>"
    '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 "<br>"
    Response.Write strSQL1
    Response.Write "<br>"
    'Now open the recordset to update the current record'
    detecRs.Open strSQL1

    Hope this helps

    "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:
    > >
    > > <i><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"
    >
    >
    >
    Jack, Feb 25, 2005
    #3
  4. 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.
    Bob Barrows [MVP], Feb 25, 2005
    #4
  5. Jack

    Jack Guest

    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.
    "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.
    >
    >
    >
    Jack, Feb 25, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joey Pang
    Replies:
    4
    Views:
    485
    Joey Pang
    Jun 13, 2005
  2. Imran Aziz

    checkbox value attribute in checkbox control ?

    Imran Aziz, Aug 6, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    217
    Imran Aziz
    Aug 8, 2005
  3. randy
    Replies:
    13
    Views:
    422
    Tad McClellan
    Nov 1, 2003
  4. garima puri
    Replies:
    0
    Views:
    74
    garima puri
    Sep 29, 2005
  5. garima puri
    Replies:
    6
    Views:
    225
    Erwin Moller
    Sep 30, 2005
Loading...

Share This Page