Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 17, 1999

How do you do THAT with SQL?

By DatabaseJournal.com Staff

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. Open Access and create a new, blank database
  2. On the "Tables" tab, select "New"
  3. Choose "Link Table" and click OK
  4. In the "Files of type:" list, change it to "ODBC databases()" - when you do this, a new dialog will pop up
  5. Choose your DSN from the list on the "Machine Data Source" tab and click OK
  6. Enter your username and password (or trusted connection if appropriate) and click OK
  7. Choose the table(s) you want to view/manage, and click OK.
  8. 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"))
      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
          totalpages = rc \ pgSize + 1
        end if
        totalpages = 1
      end if
      text = "<p>Page:&nbsp;"
      ahref = "<a href=index.asp?pg="
      if pg > 1 then
        text = text & ahref & pg-1
        text = text & ">&lt;</a>&nbsp;"
      end if
      for i = 1 to totalpages
        if i = pg then
          text = text & "<b>&nbsp;" & i
          text = text & "&nbsp;</b>&nbsp;"
          text = text & ahref & i & ">" & i
          text = text & "</a>&nbsp;"
        end if
      if pg < totalpages then
        text = text & ahref & pg+1
        text = text & ">&gt;</a>"
      end if
      response.write(text & "<p>")
      do while not rs.eof and x > 0
        response.write(rs("field") & "<p>")
        x = x - 1

      response.write("<p>" & text)
      response.write("Sorry, no matches.")
    end if
    set rs = nothing
    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 (<BR>).

    ' ...
    set rs = conn.execute(sql)
    do while not rs.eof
       txt = rs("memoField")
       txt = replace(txt,chr(10),"&nbsp;<br>")
    ' ...

The &nbsp; is added to the replace() function so that consecutive linefeeds are not ignored (some browsers, I won't name names, ignore multiple <BR> 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")
      strName = replace(strName,"'","''")
      sql = "insert into table(nameField) "
      sql = sql & "values('" & strName & "')"
    ' ...

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.


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM