By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,943 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,943 IT Pros & Developers. It's quick & easy.

Inserting checkbox into MS Access using ASP

P: 85
Hi Mark et All,

I understand that you created a very professional document and a similar issue forum out there regarding to Inserting the checkbox value into MS Access using ASP however; I am so new to this stuff and have tried couple times but no luck. Sorry to waste your time again, but would appreciate if you can help me out.

I have a form that has a checkbox name of ExpeditedRequest. I can't send the unchecked or checked value to the MS Access using ASP. By the way, everything on the form is inserted into the MS Access OK without the checkbox.

Below is the information that I have.

Tform.asp (the form that has a checkbox):

<INPUT TYPE="Checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest">

MS Access Database:

Fieldname: ExpeditedRequest DataType: Yes/No Default Value (under General): 0 and Required: No

AddProject.asp (this file will take the information from the Tform.asp and send to the MS Access). There should be more other fields but I just want to cut and paste the portion for the ExpeditedRequest only.
Expand|Select|Wrap|Line Numbers
  1. strExpeditedRequest    = trim(Request.Form("ExpeditedRequest"))
  2.  
  3. Set adocon = Server.CreateObject("ADODB.Connection")
  4. Set RS = Server.CreateObject("ADODB.Recordset")
  5.  
  6. adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb") 
  7.  
  8. If Request.Form("ExpeditedRequest") = "on" Then
  9.     RS("ExpeditedRequest") = -1
  10. Else
  11.     RS("ExpeditedRequest") = 0
  12. End If
  13.  
  14. strSQL = "INSERT INTO PTSProjects (ExpeditedRequest) VALUES('"& strExpeditedRequest &"')"
  15. RS.Open strsql, adocon
  16.  
  17. 'Etc...
Please let me know if you see anything wrong with the code a above. Thanks once again for your outstanding support.
Jan 7 '08 #1
Share this Question
Share on Google+
18 Replies


jhardman
Expert 2.5K+
P: 3,405
Your code looks good to me. Really quickly try this:
Expand|Select|Wrap|Line Numbers
  1. 'list everything sent from the form, good way to verify that everything
  2. ' you think you sent was really sent.  It also verifies spelling.
  3. for each x in request.form
  4.    response.write x & ": " & request.form(x) & "<br>" & vbNewLine
  5. next
  6.  
You could also try setting the field to "yes" or "YES", but I believe Mark is right about setting the value to -1.

Jared
Jan 8 '08 #2

P: 85
Hi Jared,

Thanks for your help. I spent couple hours after I received your replied. Made some progress and it looks like I might need your ASP expertise here.

1. I changed the IF statement in the AddProject.asp file to the following. I
used "-1" and it does not work.

If Request.Form("ExpeditedRequest") = "On" Then
strExpeditedRequest = "1"
Else
strExpeditedRequest = "0"
End If

2. I changed the MS Access format for the ExpeditedRequest field to Format: On/Off. So this field now should be ExpeditedRequest DataType: Yes/No Default Value (under General): 0 and Format: On/Off.

I can now add the entry to the MS Access database OK whether the checkbox is checked or not. HOWEVER; if the checkbox is CHECKED, the ExpeditedRequest field in the MS Access still show not checked.

Do I need to modify my SQL INSERT statement for the ExpeditedRequest or something to make it checked on the database if the checkbox is CHECKED?

This is really a tough one and I would very much appreciate if you can help me out here.
Jan 9 '08 #3

jhardman
Expert 2.5K+
P: 3,405
show a little more of your code, specifically after you open the recordset until you are finished using the db. I think you might have things out of order.

Jared
Jan 9 '08 #4

P: 85
Hi Jared,

Thanks for your replying again. Below is the code needed.

1. Trform.asp form. This is a form that has the checkbox and other items for
user to fillout.

<INPUT TYPE="Checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest">

2. MS Access setup for the ExpeditedRequest field.

Field Name: ExpeditedRequest
Data Type: Yes/No
General tab: Format: On/Off
Default Value: 0
Required: No

3. AddProject.asp (this is a script that take the information from the Trform.asp
and send to the MS Access database). It has a lot of other stuff however, I
just send you the important info only. If you give me your email, I can send
you a whole file.

<%
Dim strExpeditedRequest

Dim adocon
Dim strSQL
Dim RS

strExpeditedRequest = trim(Request.Form("ExpeditedRequest"))
Set adocon = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")

If Request.Form("ExpeditedRequest") = "On" Then
strExpeditedRequest = "1"
Else
strExpeditedRequest = "0"
End If

strSQL = "INSERT INTO PTSProjects (ExpeditedRequest) VALUES('"& strExpeditedRequest &"')"
RS.Open strsql, adocon

adocon.Close
set adocon = nothing
set RS = Nothing

%>

Thanks once again for your outstanding support.
Jan 9 '08 #5

jhardman
Expert 2.5K+
P: 3,405
Try it like this:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim adocon
  3. Dim strSQL
  4. Dim RS
  5.  
  6. Set adocon = Server.CreateObject("ADODB.Connection")
  7. Set RS = Server.CreateObject("ADODB.Recordset")
  8.  
  9. adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb") 
  10.  
  11. strSQL = "SELECT * FROM PTSProjects"
  12.  
  13. RS.Open strsql, adocon
  14.  
  15. RS.addNew
  16.  
  17. If Request.Form("ExpeditedRequest") = "on" Then
  18.    RS("ExpeditedRequest") = -1
  19. End If
  20.  
  21. RS.update
  22.  
  23. adocon.Close
  24. set adocon = nothing
  25. set RS = Nothing
  26.  
  27. %>
Instead of using the Insert command on the sql, I am using the addNew command on the recordset. I think this simplifies the code, but I can understand why some wouldn't like it.
Jan 9 '08 #6

Expert 100+
P: 164
Hotflash, you need to put the VALUE attribute in your HTML for the checkbox to pass any value to the Request object. Like this:

Expand|Select|Wrap|Line Numbers
  1. <INPUT TYPE="Checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest" value="On">
If the user checks the box and submits the form, then:
Expand|Select|Wrap|Line Numbers
  1. request.form("ExpeditedRequest") = "On" (a string)
If the user does not check the box and submits the form, then:
Expand|Select|Wrap|Line Numbers
  1. request.form("ExpeditedRequest") = "" (an empty string)

The following code should then work just fine:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim strExpeditedRequest
  3.  
  4. Dim adocon
  5. Dim strSQL
  6. Dim RS
  7.  
  8. strExpeditedRequest = trim(Request.Form("ExpeditedRequest"))
  9. Set adocon = Server.CreateObject("ADODB.Connection")
  10. Set RS = Server.CreateObject("ADODB.Recordset")
  11.  
  12. adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")
  13.  
  14. If Request.Form("ExpeditedRequest") = "On" Then
  15. strExpeditedRequest = "1"
  16. Else
  17. strExpeditedRequest = "0"
  18. End If
  19.  
  20. strSQL = "INSERT INTO PTSProjects (ExpeditedRequest) VALUES('"& strExpeditedRequest &"')"
  21. RS.Open strsql, adocon
  22.  
  23. adocon.Close
  24. set adocon = nothing
  25. set RS = Nothing
  26.  
  27. %>
Jan 9 '08 #7

P: 85
Hi All,

Thanks for your outstanding suppport. I set the value="on" as advised by Nicole and everything WORKS FINE. I have another question regarding to the checkbox and would appreciate if you can give me a quick advise here. I have another file called ViewProject.asp. This file will display the record (project) information. It displays everything OK EXCEPT the checkbox. I thought if the box is checked then when I view the project, it will also show up as a well however; it does not show anything. Is the INPUT below looks correct to you?

Like I mentioned above, RS(X, Y, Z) something display OK EXCEPT the checkbox. Please advise. Thanks once again for your support.

<INPUT TYPE="Checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest" VALUE="<%=RS("ExpeditedRequest")%>">
Jan 9 '08 #8

Expert 100+
P: 164
Are you trying to get it to show up as checked if the user saved it as "On"?

If so:

Expand|Select|Wrap|Line Numbers
  1. <input type="checkbox" name="ExpeditedRequest" id="ExpeditedRequest" value="<% if RS("ExpeditedRequest") = "1" then response.write "checked=""checked""" %>" />
Jan 9 '08 #9

P: 85
Hi Nicole,

Thanks for your reply. Yes, I want the "checked" to show up in the checkbox if there is a checked for that specific record. I have tried the command line you recommended however; it still does not show up even though there is a checked in the database for that record. Let's say record 21 for example.

Please help. Thanks.
Jan 10 '08 #10

Expert 100+
P: 164
You need to compare the value of the ExpeditedRequest field to the "on" value you have chosen. If they are the same, put "checked='checked'" in the HTML for your checkbox.

For example:

Expand|Select|Wrap|Line Numbers
  1. ValueFromDatabase = rs("ExpeditedRequest")
  2.  
  3. if ValueFromDatabase = "1" then
  4. '... write out your INPUT element with checked="checked" in the tag.
  5. end if
Jan 11 '08 #11

P: 85
Hi Nicole,

Thanks once again for your help. I am kinda new to this stuff so please patience with me here. I have tried again but still can't get the "checked" to display if there is a checked in the database. Here is the design of my ViewProject.asp. This file has a mix of ASP code and HTML. It starts with the ASP then HTML. The HTML portion has other fields as well however; I just want to show you the portion for the Expedited Request only. Like I mentioned earlier, other values display OK EXCEPT the checkbox. I have tried couple ways

1. Put the code you provided in the ASP portion an it does not call anything.
2. Put in the HTML portion right above the INPUT line but getting file error.

You are so good with the ASP stuff and wonder if you can advise here. Thanks once again for your patience.

<%
Dim ProjectID
Dim adocon
Dim adorst
Dim strSQL
Dim RS

ProjectID = Request.QueryString("ProjectID")

Set adocon = Server.CreateObject("ADODB.Connection")
adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM PTSProjects WHERE ProjectID =" & ProjectID
RS.Open strSQL, adocon
%>

Next is the HTML portion of it.

<HTML>
<BODY>
Etc......

<TABLE>
<TR>
<TD ALIGN="LEFT"><FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2">&nbsp;&nbsp; Expedited Request</FONT></TD>
<TD>
<INPUT TYPE="checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest" VALUE="<%RS("ExpeditedRequest")%>">
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
Jan 11 '08 #12

Expert 100+
P: 164
[quote=hotflash]Hi Nicole,

Thanks once again for your help. I am kinda new to this stuff so please patience with me here. I have tried again but still can't get the "checked" to display if there is a checked in the database. Here is the design of my ViewProject.asp. This file has a mix of ASP code and HTML. It starts with the ASP then HTML. The HTML portion has other fields as well however; I just want to show you the portion for the Expedited Request only. Like I mentioned earlier, other values display OK EXCEPT the checkbox. I have tried couple ways

1. Put the code you provided in the ASP portion an it does not call anything.
2. Put in the HTML portion right above the INPUT line but getting file error.

You are so good with the ASP stuff and wonder if you can advise here. Thanks once again for your patience.

<%
Dim ProjectID
Dim adocon
Dim adorst
Dim strSQL
Dim RS

ProjectID = Request.QueryString("ProjectID")

Set adocon = Server.CreateObject("ADODB.Connection")
adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM PTSProjects WHERE ProjectID =" & ProjectID
RS.Open strSQL, adocon
%>


Just to clarify, what value do you get when you put this into your code:

response.write "recordset value: "& RS("ExpeditedRequest") &"; type: "& typename(RS("ExpeditedRequest").value)
Jan 11 '08 #13

P: 85
Hi Nicole,

Thanks for your reply. See my answer below and please let me know if you need me to try something else.

For a record that has a "CHECKED", below is what I got
recordset value: True; type: Boolean

For a record that does not have a "CHECKED", below is what I got.
recordset value: False; type: Boolean
Jan 11 '08 #14

P: 85
Hi Nicole,

Anything else that you would like me to try please? Thanks once again for your help.
Jan 13 '08 #15

Expert 100+
P: 164
Use this:

Expand|Select|Wrap|Line Numbers
  1. <INPUT TYPE="checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest" VALUE="<%RS("ExpeditedRequest")%>" <% if RS("ExpeditedRequest") then response.write "checked=""checked"""%> />
Jan 18 '08 #16

jhardman
Expert 2.5K+
P: 3,405
Use this:

Expand|Select|Wrap|Line Numbers
  1. <INPUT TYPE="checkbox" NAME="ExpeditedRequest" ID="ExpeditedRequest" VALUE="<%RS("ExpeditedRequest")%>" <% if RS("ExpeditedRequest") then response.write "checked=""checked"""%> />
I thought you were supposed to write 'checked="True"' or just 'Checked'

Your name isn't really Nicole, is it?

Jared
Jan 18 '08 #17

P: 85
Thanks for you guys outstanding support on this subject. It works fine.
Jan 20 '08 #18

Expert 100+
P: 164
I thought you were supposed to write 'checked="True"' or just 'Checked'

Your name isn't really Nicole, is it?

Jared

Haha. No, it's not. For whatever reason hotflash decided my name was Nicole. How on Earth that was derived from Nicodemas, I have no idea. Whatever, though. I let him run with it.

2nd, it'll work with "true", but it won't validate. You must use "checked" to get it valid in XHTML 1.0 Transitional
Jan 21 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.