How do you do THAT with SQL?

by Aaron Bertrand

Browsing a remote SQL database

People often ask how to browse or edit a SQL database
without actually sitting at the SQL machine. Well, you could
use Enterprise Manager installed on a local NT Server / SQL
Server. Or you could use remote control software like Carbon
Copy or Remotely Possible. However, most providers won’t let
you do those things for obvious security reasons. Well guess
what? Do you have a system DSN for your SQL database? Do you
have Access 97 or Access 2000 installed? If you answered yes
to both of these questions, then you CAN browse your remote
SQL database, by following these steps:

1. 
   
2. Open Access and create a new, blank database
3. On the “Tables” tab, select “New”
4. Choose “Link Table” and click OK
5. In the “Files of type:” list, change it to “ODBC
   databases()” – when you do this, a new dialog will pop up
6. Choose your DSN from the list on the “Machine Data
   Source” tab and click OK
7. Enter your username and password (or trusted connection
   if appropriate) and click OK
8. Choose the table(s) you want to view/manage, and click
   OK.
9. Add records, view data, change things to your heart’s
   content (but don’t go crazy… you’re playing with live data
   there!)

* NOTE: There are some limitations with this method (for
example, you will have problems adding or editing tables since
Access and SQL Server don’t share completely interchangeable
datatypes). But it sure beats writing an ASP page from scratch
to see your data in a certain way! I’ve tested this with SQL
Server 7.0 but haven’t had a chance to test with 6.5. If you
found this useful, have any other suggestions, or simply can’t
get it to work, I want to
know about it!

Paging through a recordset

Here’s one that is asked all the time: “How do I create a
system like AltaVista, where the user can browse through all
the records, 10 records per page?” Here’s how AltaVista does
it:

• 
  
• <%
• 
  
• pgSize
• 
  
• = 10
• 
  
• dsnName = “dsnName”
• 
  
• sql = “select field from
• 
  
• table”
• 
  
•  
• 
  
• if request(“pg”)<>””
• 
  
• then
• 
  
•   pg =
• 
  
• cint(request(“pg”))
• 
  
• else
• 
  
•   pg = 1
• 
  
• end
• 
  
• if
• 
  
•  
• 
  
• set conn =
• 
  
• createobject(“adodb.connection”)
• 
  
• conn.open dsnName
• 
  
• set
• 
  
• rs = createobject(“adodb.recordset”)
• 
  
• rs.open
• 
  
• sql,conn,1,1
• 
  
•  
• 
  
• if not rs.eof
• 
  
• then
• 
  
•  
• 
  
•   rs.AbsolutePage =
• 
  
• pg
• 
  
•   rs.PageSize = pgSize
• 
  
•   x =
• 
  
• pgSize
• 
  
•   rc = rs.recordCount
• 
  
•   if
• 
  
• rc > pgSize then
• 
  
•     if rc mod
• 
  
• pgSize = 0
• 
  
• then
• 
  
•       totalpages = rc
• 
  
• /
• 
  
• pgSize
• 
  
•     else
• 
  
•       totalpages
• 
  
• = rc pgSize + 1
• 
  
•     end
• 
  
• if
• 
  
•   else
• 
  
•     totalpages
• 
  
• = 1
• 
  
•   end if
• 
  
•  
• 
  
•   text =
• 
  
• “

  Page: ”

• 
  
•   ahref = “
• 
  
• href=index.asp?pg=”
• 
  
•   if pg > 1
• 
  
• then
• 
  
•     text = text & ahref
• 
  
• & pg-1
• 
  
•     text = text &
• 
  
• “>< ”
• 
  
•   end
• 
  
• if
• 
  
•   for i = 1 to
• 
  
• totalpages
• 
  
•     if i = pg
• 
  
• then
• 
  
•       text = text
• 
  
• & “ ” &
• 
  
• i
• 
  
•       text = text &
• 
  
• “  ”
• 
  
•     else
• 
  
•       text
• 
  
• = text & ahref & i & “>” &
• 
  
• i
• 
  
•       text = text &
• 
  
• “ ”
• 
  
•     end
• 
  
• if
• 
  
•   next
• 
  
•   if pg < totalpages
• 
  
• then
• 
  
•     text = text & ahref
• 
  
• & pg+1
• 
  
•     text = text &
• 
  
• “>>”
• 
  
•   end
• 
  
• if
• 
  
•  
• 
  
•   response.write(text &
• 
  
• “

  ”)

• 
  
•  
• 
  
•   do while not rs.eof
• 
  
• and x >
• 
  
• 0
• 
  
•     response.write(rs(“field”)
• 
  
• & “

  ”)

• 
  
•     x = x –
• 
  
• 1
• 
  
•     rs.movenext
• 
  
•   loop
• 
  
•   response.write(“

  ” &
  text)
   
  else
    response.write(“Sorry,
  no matches.”)
  end if
  rs.close
  set rs =
  nothing
  conn.close
  set conn =
  nothing
  %>
  

NOTE: This script doesn’t behave too well if you change the
pageSize on the fly.

Maintaining line feeds from memo/text fields

Your users entered separate paragraphs in a textarea. This
data was entered into your database. Now when you insert it
into HTML, those paragraphs are gone… why? HTML doesn’t
understand line feeds (that’s why you can put lots of space
between HTML tags; most whitespace is ignored). So what you
have to do is replace the “hidden” linefeeds with HTML
linefeeds (
).

• <%
• 
  
• ‘
• 
  
• …
• 
  
• set rs = conn.execute(sql)
• 
  
• do while not
• 
  
• rs.eof
• 
  
•    txt = rs(“memoField”)
• 
  
•  
• 
  
•  txt =
• 
  
• replace(txt,chr(10),” 
  ”)
• 
  
•  
• 
  
•  response.write(txt)
• 
  
•  
• 
  
•  rs.movenext
• 
  
• loop
• 
  
• ‘ …
• 
  
• %>

The   is added to the replace() function so
that consecutive linefeeds are not ignored (some browsers, I
won’t name names, ignore multiple
tags).

Preventing the dreaded apostrophe error

Have you come across SQL syntax errors because your user
entered a single quote (‘)? Do you want to know an easy
way to prevent that? Simply replace it with two single quotes
before you do your insert/update… the first acts like an
escape character, letting SQL know that you do NOT want to use
the second single quote as an end of string marker. Here’s an
example:

• <%
• 
  
• ‘
• 
  
• …
• 
  
• strName = request.form(“strName”)
• 
  
• ‘ FIX NAMES LIKE
• 
  
• O’SHEA:
• 
  
•   strName =
• 
  
• replace(strName,”‘”,”””)
• 
  
•   sql = “insert into
• 
  
• table(nameField) “
• 
  
•   sql = sql & “values(‘”
• 
  
• & strName & “‘)”
• 
  
• conn.execute(sql)
• 
  
• ‘
• 
  
• …
• 
  
• %>

The replace line, for clarity, reads: “replace”, open
parenthesis, “strName”, comma, double quote, single quote,
double quote, comma, double quote, single quote, single quote,
double quote, close parenthesis.

🙂