SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question basic instructions

    Hi guys, sorry bothering with such a basic doubt but I'm a new asp programmer and I'm having some problems to perform select on sql statements just cause I'm really confuse on how to refer some variable I got by request.querystring or request.form or even inside form variables...sometimes just using "var" others using "&var&" others '%"&var&"%'...
    Does anyone have an article link or some tips regarding this kind of issue?
    Thanks in advance!

  2. #2
    SitePoint Enthusiast afgmail's Avatar
    Join Date
    Jan 2005
    Location
    Houston, TX
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are you trying to achieve?
    Scan completed....
    Brain not found.
    filecabin.com : Notepad2

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Example using Request.Form(POST) and Request.QueryString(GET)

    try to get something out of this asp page i created...i tried to provide comments throughout to show you how request.form and request.querystring are used...

    see the code in action here:http://k.domaindlx.com/bslintx/users_example.asp


    Code:
    <html>
      <head>
     </head>
    <body>
    
    
    <!--   a form needs 2 attributes 1. action -where to send the data
             in this case it will be to itself, so put in the name of the
             file here. 2. method can be POST or GET. POST no data
             is viewable in a string in browser while GET does. The object
             of this form is to add a last name and a last name and a 
             first name to a database with 3 fields. 1. UserID(Primary Key)
             2. LastName 3. FirstName -->
    
    <form action="users_example.asp" method="POST"> 
    
    <!-- whether using get or post it is MANDATORY to have a name for 
           the form input fields if request data from them. this is how asp
           retrieves data by the Request method. Try to keep the names
           similar to what the data contains for easier readibility-->
     
     Last Name: <input name="lname" size="20" maxlength="20">
     First Name: <input name="fname" size="20" maxlength="20">
                 <input type="submit" name="postsubmit" value="Submit">
    
    <%
         ' connect to database. this is an access db
     
      Set objConn = Server.CreateObject("ADODB.Connection")
      Set objRS = Server.CreateObject("ADODB.Recordset") 
      FilePath = Server.MapPath("users.mdb")
      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
      objConn.Open strConn   
      
        ' retrieve the form input values using Request.Form since it is using POST
        ' assign a variable to the requested data 
         
         lname = Request.Form("lname")
         fname = Request.Form("fname")
         postsubmit = Request.Form("postsubmit")  
    
        ' If form method was GET then do this
        ' Notice no Request.Querystring("lname")
        ' this is a shortcut...not necessary in qerystring
        ' but post needs .Form
    
        ' lname = Request("lname")
        ' fname = Request("fname")
        ' postsubmit = Request("postsubmit")
        
         id = Request("id")
         all = Request("all")
         delrec = Request("delrec")
    
        ' Use condition statement to see if the form was submitted
        ' The <> means not equal. the "" means empty string
        ' So if postsubmit is not equal to an empty string then 
        ' run code proceeding If clause. It is known that the
        ' postsubmit will not be empty because its "value" is 
        ' filled in with a string. This is why it is used instead 
        ' of lname or fname. They could be sent empty since
        ' there is no error checking but you can not trigger the
        ' if statement by ensuring all input fields have something
        ' in them else don't do anything
    
        If postsubmit <> ""  AND lname <> "" AND fname <> "" Then
           strSQL ="SELECT UserID, LastName, FirstName FROM tblUsers;"
           objRS.Open strSQL, objConn, 3, 3
    
        ' Most prefered method is sql update set but this way we can 
        ' retrieve the ID of the record just submitted
    
        ' Requested form variables to database fields
          
          objRS.AddNew   
          objRS("LastName") = lname
          objRS("FirstName") = fname
          objRS.Update ' add to dabase
    
        ' Now retrieve the UserID from the just inserted record
     
          userid = objRS("UserID")
          objRS.Update
          objRS.Close
         
          Response.Write "<p>Thanks " & fname & ",<br>" & vbCrlf & _
                         "Your record has been successfully entered into the database</p>" 
          
        'Now you can see how the Request.QueryString works with a hyperlink
        'Use the hyperlink to store db data and trigger an if condition to 
        'search db for the record or records in db. Look in browser address
        'bar and look how you can see the values. Great troubleshooting
        'technique if .Form isn't working
    %>
         <p><a href="users_example.asp?id=<%=userid%>">View just inserted record</a>
         <a href="users_example.asp?all=true">View all records</a>
    
    <%                
       End If
    
       If id <> "" Then
          strSQL="SELECT UserID, LastName, FirstName FROM tblUsers WHERE UserID=" & id & ";"
          objRS.Open strSQL, objConn
    %>
    
        <p><a href="users_example.asp?all=true">View all records</a></p>
    
    <%    
          Response.Write "<p>User ID: " & objRS("UserID") & "<br>" & vbCrlf & _
                         "Name: " & objRS("FirstName") & " " & objRS("LastName") & "</p>"
    
         objRS.Close
       End If
    
       If all <> "" Then
          strSQL="SELECT UserID, LastName, FirstName FROM tblUsers;"
          objRS.Open strSQL, objConn  
    
          If objRS.EOF Then
    	Response.Write "<p>No records</p>"
       
          Else
             i = 0
            Do While Not objRS.EOF
              i = i + 1
        
              Response.Write "<p><b>" & i & ".</b> User ID: " & objRS("UserID") & "&nbsp;"
    
    %>
    
          <a href="users_example.asp?delrec=<%=objRS("UserID")%>">Delete</a><br>
    
    <%
             Response.Write "&nbsp;&nbsp;&nbsp <u>Name: " & objRS("FirstName") & " " & objRS("LastName") & "</u></p>" 
    
            objRS.MoveNext
            Loop
            objRS.Close
          End If
       End If
    
       If delrec <> "" Then
          strSQL="DELETE * FROM tblUsers WHERE UserID=" & delrec & ";"
          objRS.Open strSQL, objConn
       
          Response.Write "<p>Record " & delrec & " has been removed from database<br>"
    %>
    
        <a href="users_example.asp?all=true">View all records</a></p>
      
    <%
       End If                  
    %>
    
    </body>
    </html>

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    round 2

    sql for a var string: notice the single quotes, used for TEXT strings

    sql="SELECT * FROM table WHERE text='" & text & "';"

    sql for a var number: no single quotes

    sql="SELECT * FROM table WHERE number=" & number & ";"

    sql for a date (in access db): needs #

    sql="SELECT * from table where date =#" & date & "#;"

    looks like you were tring to use the wildcard %
    this is used in the LIKE statement to query something that matches a pattern searched for

    this looks for a string match STARTING w/ Hew
    sql="SELECT * FROM table WHERE name LIKE 'Hew%'; "

    this one is a match for chars anywhere within a string
    sql="SELECT * FROM table WHERE name LIKE '%it%'; "

    good luck


Bookmarks

Posting Permissions

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