Solved

ODBC/NT/MSACCESS

Posted on 1998-10-16
3
319 Views
Last Modified: 2009-12-16
I am writing this question in order to get quick knowledge from someone who has more than likely spent hours researching the answers.... But look at the amount of points I am offering, I kinda hope for a good answer.

I have access to and NT server. Lets say I have a database made in MS-access 97 (an .mdb file) and say it contained a table called "address_book" and 3 fields in the table called (name, phone, email).

I upload the mdb file on the net.

how can I (and what library/modules do i need) ....ODBC??
1. Search for a record with "joe Bloggs" in the name field. and print out his email address
2. Delete "anne smith" from the database along with her records
3. Add "john brown" to the database with his email and phone number
4. change "geof daniels" phone number to 333 333 222

thanks..i know the answer may be lengthy. please, I need this answer really quickly....

thanks people.
0
Comment
Question by:n1875621
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
tfabian earned 390 total points
Comment Utility
you've got the classic decision to make about web based scripting..  (I had to make it too)..

one the one hand, you can use your NT server, and MS IIS 3.0 or above, ODBC, your existing databases, and a combination of ASP code and HTML..

or on the other hand, you can find an UNIX based server, APACHE or HTTPD, SQL, your existing databases, and Perl scripts plus the HTML...

whichever you choose depends on your levels of comfort with either solution..

since you brought it up, lets talk about the first solution..

ASP code is a programming language built into your HTML pages..  an example might look something like this:

in this example, we're selecting records from a database of yeark 2000 vendors, and displaying a web page back to the user listing the vendors that meet the selection criteria..  note the imbedded SQL calls within the HTML code..   the web page url might be something like

  http://yourserver.somewhere.com/test.asp

the .ASP extention is important in this case.. it tells the server that the file needs to be executed before sending it out... (the web server area in which it resides must also have execute priv turned on..)


---start example asp code within html-------------------

mydir="Next"
session("myproduct")=request.querystring("product")
session("myvendor")=request.querystring("vendor")
myconditions=request.querystring("condition")
mycomment=request.querystring("comment")
mytested=request.querystring("tested")
mynottested=request.querystring("nottested")
mycompliant=request.querystring("compliant")
mynotcompliant=request.querystring("notcompliant")
mymisscrit=request.querystring("misscrit")
mynotmisscrit=request.querystring("notmisscrit")
myvuln=request.querystring("vuln")
mynotvuln=request.querystring("notvuln")
myvendcomp=request.querystring("vendcomp")
mynotvendcomp=request.querystring("notvendcomp")
myobsolete=request.querystring("obsolete")
mynotobsolete=request.querystring("notobsolete")

mydate=date
mytime=time
mydisp=mydate & "    -     " & mytime
myuser=ucase(request.servervariables("logon_user"))
myhost=request.servervariables("remote_host")
mydisp1=myuser & "      -    " & myhost

'
' Set any variables that are needed
'
if len(trim(session("myproduct")))= 0 then
   session("myproduct")="%"
end if

if len(trim(session("myvendor"))) = 0 or session("myvendor")="All Vendors" then
   session("myvendor")="%"
end if

if len(trim(mycomment)) = 0 then
   mycomment="%"
end if

if len(trim(myconditions)) = 0 then
   myconditions="%"
end if



'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'sql = Request("sql")



SQL="SELECT DISTINCTROW id, vendor.vendor, vendor.vaddress1, vendor.vaddress2, vendor.vcity, vendor.vstate, vendor.vzip, vendor.vfax, vendor.vphone, product, version, comptest, datetested, testcenter, compliant, compconditions, datecompliant, comments, missioncrit, obsolete, vulnerable, vendcomp  FROM Y2000, vendor where "
'
' Begin creation of dynamic SQL...based on entry made by user
'
' Check for entry of Product
'
if len(trim(session("myproduct")))=0 then
  sql=sql & "(product like '%" & session("myproduct") & "%' or isnull(product)) and "
else
  sql=sql & "product like '%" & session("myproduct") & "%' and "
end if
'
'
'
' Check for entry of Vendor...if so make adjustments to SQL
'
if trim(session("myvendor"))="%" then
  sql=sql & "(y2000.vendor like '" & session("myvendor") & "' or isnull(y2000.vendor)) and "
else
  sql=sql & "y2000.vendor = '" & session("myvendor") & "' and "
end if

'
' Check for entry of Comment...if so make adjustments to SQL
'
if trim(mycomment)="%" then
  sql=sql & "(comments like '" & mycomment & "' or isnull(comments)) and "
else
  sql=sql & "comments like '%" & mycomment & "%' and "
end if
'
'
' Check for entry of Conditions...if so make adjustments to SQL
'
if trim(myconditions)="%" then
  sql=sql & "(compconditions like '" & myconditions & "' or isnull(compconditions)) "
else
  sql=sql & "compconditions like '%" & myconditions & "%' "
end if

'
' Check for Tested
'
if mytested = "on" then
   sql=sql & " and (comptest= Yes)"
end if
'
' Check for Not Tested
'
if mynottested = "on" then
   sql=sql & " and (comptest= No)"
end if
'
' Check for Mission Critical
'
if mymisscrit = "on" then
   sql=sql & " and (Missioncrit= Yes)"
end if
'
' Check for Not Mission Critical
'
if mynotmisscrit = "on" then
   sql=sql & " and (Missioncrit= No)"
end if
'
' Check for Compliant
'
if mycompliant = "on" then
   sql=sql & " and (compliant= Yes)"
end if
'
' Check for Not Compliant
'
if mynotcompliant = "on" then
   sql=sql & " and (compliant= No)"
end if
'
' Check for Vulnerable
'
if myvuln = "on" then
   sql=sql & " and (vulnerable= Yes)"
end if
'
' Check for Not Vulnerable
'
if mynotvuln = "on" then
   sql=sql & " and (vulnerable= No)"
end if
'
' Check for Vendor Compliant
'
if myvendcomp = "on" then
   sql=sql & " and (vendcomp= Yes)"
end if
'
' Check for Not Vendor Compliant
'
if mynotvendcomp = "on" then
   sql=sql & " and (vendcomp= No)"
end if
'
' Check for Obsolete
'
if myobsolete = "on" then
   sql=sql & " and (obsolete= Yes)"
end if
'
' Check for Not Obsolete
'
if mynotobsolete = "on" then
   sql=sql & " and (obsolete= No)"
end if

sql=sql & " and (vendor.vendor = y2000.vendor) order by vendor.vendor, product, version" %>

<!--<%=sql%>-->

<%

If sql = "" Then
      Response.Redirect("Query.asp")
End If
Set Conn = Server.CreateObject("ADODB.Connection")
Session("ConnectionString")="dsn=y2000;uid=admin;pwd="
Set RS = Server.CreateObject("ADODB.RecordSet")
Conn.Open Session("ConnectionString")
RS.Open sql, Conn, adOpenKeyset,adLockReadOnly

RS.PageSize = 12 ' Number of rows per page
mypage=rs.pagesize
TRECS=RS.RECORDCOUNT
if Request("Action") = "" Then
      FormAction = "test.asp"
else
      FormAction = "test.asp"
     'Response.Redirect("Query.asp?sql=" & Server.URLEncode(sql))
end if
ScrollAction = Request("ScrollAction")
if ScrollAction <> "" Then
      PageNo = mid(ScrollAction, 5)
      if PageNo <  1 Then
           PageNo = 1
      end if
else
      PageNo = 1
end if
recs=1
if trecs > 1 then
   RS.AbsolutePage = PageNo
end if
pages=int(trecs/mypage)
if pages = 0 then
   pages=1
end if
%>
<HTML>
<HEAD><title>Year 2000 - COTS Software Compliance - Query Results</title></head>
<BODY BACKGROUND="/yr2000/pict/white.gif">
<center>
<table><tr><td align=center><a href="/yr2000/default.asp"><img src="/yr2000/pict/2000.gif" height=50 alt="Year 2000" border=0></a></td>
<td align=center><font size=4 color=black>Year 2000 COTS Compliance - Query Results <br>
<b>Page # <font color=red><%=PageNo%> <font color=black>of <font color=red><%=pages%><font color=black> - Records <font color=red><%=trecs%></font></td>
<td><a href="/yr2000/help.asp?screen=YR2000-Query"><img src="/yr2000/pict/help.gif" alt="Help" border=0></a></td></tr></table>

<%  Do while not (RS is nothing) %>
      <TABLE BORDER=1>
      <table cellspacing=2 border=0>
      <tr>
      <td></td>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Vendor:</FONT></TD>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Product:</FONT></TD>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Version:</FONT></TD>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Tested:</FONT></TD>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Compliant:</FONT></TD>
      <TD ALIGN=CENTER BGCOLOR="cyan"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2>Critical:</FONT></TD>
      </tr>
      <TR>
     
      <% if not rs.eof then %>
         <TD ALIGN=center BGCOLOR="white"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><a href="detail.asp?id=<%=rs("id")%>"><img src="/yr2000/pict/show.gif" border=0 alt="Show Me"></a></FONT></TD>
         <TD ALIGN=left BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("vendor")%><br>
         <TD ALIGN=left BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("product")%></FONT></TD>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("version")%></FONT></TD>
         <% if rs("comptest") = 0 then %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
         <% else  %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
         <% end if %>

         <% if rs("compliant") = 0 then %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
         <% else  %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
         <% end if %>
 
         <% if rs("missioncrit") = 0 then %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
         <% else  %>
            <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
         <% end if %>
         </tr>
         <%  RowCount = rs.PageSize
             recs=recs+1
             rs.movenext
      end if
      Do While Not RS.EOF and rowcount > 0
      recs=recs+1
      %>
      <TR>
      <TD ALIGN=center BGCOLOR="white"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><a href="detail.asp?id=<%=rs("id")%>"><img src="/yr2000/pict/show.gif" border=0 alt="Show Me"></a></FONT></TD>
      <TD ALIGN=left BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("vendor")%><br>
      <TD ALIGN=left BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("product")%></FONT></TD>
      <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=2><%=rs("version")%></FONT></TD>
      <% if rs("comptest") = 0 then %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
      <% else  %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
      <% end if %>

      <% if rs("compliant") = 0 then %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
      <% else  %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
      <% end if %>
 
      <% if rs("missioncrit") = 0 then %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/uncheck.gif"></FONT></TD>
      <% else  %>
         <TD ALIGN=center BGCOLOR="f7efde"><FONT STYLE="Verdana, Arial, Helvetica"  SIZE=3><img src="/yr2000/pict/check.gif"></FONT></TD>
      <% end if %>
      </tr>
      <%
      RowCount = RowCount - 1
      RS.MoveNext
      Loop  %>
      </TABLE>
     <table><tr>
     <%
      ' Message for none found
      if recs=1 then %>
        <td align=center colspan=5>No matching records found</td>
     <% end if  %>
     </table>
      <P>
      <%
      set RS = RS.NextRecordSet
Loop


%>

<tr><td>
<FORM METHOD=GET ACTION="<%=FormAction%>">
<INPUT TYPE="HIDDEN" NAME="product" VALUE="<%=session("myproduct")%>"><INPUT TYPE="HIDDEN" NAME="vendor" VALUE="<%=session("myvendor")%>"><INPUT TYPE="HIDDEN" NAME="condition" VALUE="<%=myconditions%>"><INPUT TYPE="HIDDEN" NAME="comment" VALUE="<%=mycomment%>"><INPUT TYPE="HIDDEN" NAME="tested" VALUE="<%=mytested%>"><INPUT TYPE="HIDDEN" NAME="nottested" VALUE="<%=mynottested%>"><INPUT TYPE="HIDDEN" NAME="compliant" VALUE="<%=mycompliant%>">
<INPUT TYPE="HIDDEN" NAME="notcompliant" VALUE="<%=mynotcompliant%>">
<INPUT TYPE="HIDDEN" NAME="misscrit" VALUE="<%=mymisscrit%>">
<INPUT TYPE="HIDDEN" NAME="notmisscrit" VALUE="<%=mynotmisscrit%>">


<%  if PageNo > 1 Then %>
       <INPUT TYPE="HIDDEN" NAME="direction" VALUE="Prior">
       <INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo-1%>">
<%  end if %>
<%  if RowCount = 0 and recs > 1 Then %>
       <INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo+1%>">
<%  end if %></td><td><img src="/yr2000/pict/space.gif" width=25></td><td colspan=3.5 align=center><a href="addrec.asp"><img align=bottom src="/yr2000/pict/add.gif" alt="Add" border=0></a></td><td colspan=3.5 align=center></td></tr>
<tr><td><br></td></tr>
</form>

<%
Conn.Close
set rs = nothing
set Conn = nothing  %>

<tr><td align=middle colspan=<%=mycol%>>For Assistance with this form contact: </td></tr>
</TABLE>


</BODY>
</HTML>





---end example asp code within html-------------------


as to where you can get the required pieces and parts...

make sure your NT server has NT version 4.0 Service Pack 3 installed.. (although SP 4 came out yesterday)..

see url   http://www.microsoft.com/ntserver/nts/default.asp   for details


grab the ODBC program modules from the Microsoft web site at url

   http://www.microsoft.com/data/odbc/

also I beleive you need a specific ASP module, but I can't find it now.. I remeber getting it from the Microsoft website too..


for more details on asp see url

  http://www.microsoft.com/iis/partners/samples/components/asp2htm/docs/default.htm


and for IIS, see url


   http://www.microsoft.com/ntserver/nts/web/default.asp


as for ease of setup.. when we wanted to put our databases on the web, it took some initial investigation, a short talk with another guy who'ld done it, and about three days worth of coding..  today our guys build ASP pages without much effort in a real short time..


regarding the Perl solution.. it's not as complicated, but you need to pass your data to/from the database using SQL (instead of ODBC) and you've got to write/learn Perl to manage and /or display the data..  it's a trade off, but if you've already got the NT server, using it is 90% of the battle / decision...


good luck



0
 

Author Comment

by:n1875621
Comment Utility
Thanks for the help. It has been a while since I asked that question...I went on programming in perl with a text-file based database. It actually works quite well, its not real slow and I find it reliable....not to mention EASY!! So I will keep the info you have given me for future reference....just a query though, is ASP browser dependant? or will it run on any browser, making it server dependant?

thanks again....john
john@ActiveGS.com.au

0
 
LVL 5

Expert Comment

by:tfabian
Comment Utility
the ASP stuff will typically run in any browser.. it puts it's compute / processing load on the server which is hosting the page..the browser has no clue that it's displaying ASP data.. when you ask for a "show source" of an ASP page, it'll show you the full html minus the ASP code.. the only clue that you have that the page is dependant on ASP calls is that it's URL ends in .ASP


good luck


0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now