Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    May 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using check boxes to link to database

    Please excuse my ignorance but I am very new to ASP and have tried lots of tutorials including using snippets of sample code etc but I am having trouble with my code for a checkbox, any help with code would be very much appreciated. I have managed to work with standard text but cannot code a check box.
    I have two files where the problem with the code appears to be (I also have an access database with fields to hold firstname, surname and subscribe. The first file - html is as follows:

    [CODE]
    <html>
    <head>
    <title>Guestbook Form</title>
    </head>
    <h1>Newsletter request Form</h1>

    <center>
    <form name = "form" method = "post" action = "add_to_register.asp">

    <table cellpadding= 5px style= "background-color:#b0d8ff;border:1px solid navy;">
    <tr><td width = "50%">First Name</td><td><input name = 'firstname' type = "text" size = "40" maxlength = "200"/></td></tr>
    <tr><td width = "50%">Surname</td><td><input name = 'surname' type = "text" size = "40" maxlength = "200"/></td></tr>
    <tr><td width = "50%">Do you wish to receive our newsletter?</td><td style = 'text-align: center'><input type = 'checkbox'name= 'subscribe' /></td></tr>
    </table>
    <br />
    <button type = "submit">Submit</button>
    </form>
    </center>
    </body>
    </html>
    [ICODE]


    The second file:

    [CODE]
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added to the database
    Dim strSQL 'Holds the SQL query for the database

    'Create an ADO connection odject
    Set adoCon = Server.CreateObject("ADODB.Connection")

    'Set an active connection to the Connection object using a DSN-less connection
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Newsletter.mdb")

    'Set an active connection to the Connection object using DSN connection
    'adoCon.Open "DSN=guestbook"

    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT tbldetails.firstname, tbldetails.surname, tbldetails.subscribe, FROM tblComments;"

    'Set the cursor type we are using so we can navigate through the recordset
    rsAddComments.CursorType = 2

    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3

    'Open the tblComments table using the SQL query held in the strSQL varaiable
    rsAddComments.Open strSQL, adoCon

    'Tell the recordset we are adding a new record to it
    rsAddComments.AddNew

    'Add a new record to the recordset
    rsAddComments.Fields("Firstname") = Request.Form("firstname")
    rsAddComments.Fields("Surname") = Request.Form("surname")
    rsAddComments.Fields("Register") = Request.Form("register")

    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    Set adoCon = Nothing


    'Redirect
    Response.Redirect "complete_message.html"
    %>
    [ICODE]

    I also have a small html file complete_message to complete the process

    It works without the checkbox but not with

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    A few problems.

    First of all, what is the DATA TYPE of the field subscribe in your table?

    Is it YES/NO? Or TEXT? Or a number???

    I am going to *assume* it is YES/NO. If not, tell us and it's an easy change.

    Big problem: Your SQL query is totally bogus.
    Code:
    SELECT tbldetails.firstname, tbldetails.surname, tbldetails.subscribe, FROM tblComments
    How can you SELECT fields from the table tbldetails when the only table you mention in the FROM clause is tblcomments???

    Also, you have a bogus extra comma after the last field name there, before the FROM.

    HOWEVER...

    If you are going to use ADDNEW with a recordset, it's almost always a better idea to use *ONLY* the table name and *NOT* do a SELECT. (If you must do a SELECT, then this is one case it should be SELECT *.)

    So:
    Code:
    <%
    'Dimension variables
    Dim adoCon 
    Dim rsAddComments 
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    ' the Access Driver is very very old and very buggy...you really should use JET OLEDB
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("Newsletter.mdb")
    
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    
    ' when you use ADDNEW, you should use the whole table, not just SELECT some fields
    ' so no SQL query
    
    ' cursor type 2 is not properly usable with ASP code...type 3 is much better
    ' and no reason to set cursor type and lock type ahead of time...
    
    ' open the recordset using just the name of the table; set cursor type and lock type at same time:
    rsAddComments.Open "tblComments", adoCon, 3, 3
    
    'Tell the recordset we are adding a new record to it
    rsAddComments.AddNew
    
    'Add a new record to the recordset
    rsAddComments.Fields("Firstname") = Request.Form("firstname")
    rsAddComments.Fields("Surname") = Request.Form("surname")
    
    ' this assumes the Register field ind DB is a boolean (YES/NO) field:
    ' the comparison of <> "" will only only be true if the field was checked
    rsAddComments.Fields("Register") = ( TRIM(Request.Form("register")) <> "" )
    
    rsAddComments.Update
    rsAddComments.Close
    adoCon.Close
    
    ' instead of redirecting, just include the message here...faster by a long shot!
    %>
    <!-- #include file="complete_message.html" -->
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    May 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up Thank you thank you thank you

    I really do appreciate your help and your explanations. All working now - thank you - you are my hero


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •