SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2005
    0 Post(s)
    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
    Houston, TX
    0 Post(s)
    0 Thread(s)
    What are you trying to achieve?
    Scan completed....
    Brain not found. : Notepad2

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    0 Post(s)
    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:

    <!--   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("LastName") = lname
          objRS("FirstName") = fname
          objRS.Update ' add to dabase
        ' Now retrieve the UserID from the just inserted record
          userid = objRS("UserID")
          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>"
       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>"
             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>" 
          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                  

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2005
    0 Post(s)
    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


Posting Permissions

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