asp database browser omlægning fra access til sql
jeg bruger nedenstående kode til en access database, men vil gerne gøre det samme til en sql database - kan jeg beuge det og hvor skal der kodes om?<% Option Explicit %>
<!-- #include file="adovbs.inc" -->
<%
Dim dbc, rsData, rsFieldNames
'--- PROMPT FOR DESIRED .MDB FILE (IF USER HAS NOT YET SPECIFIED) ---
If Request.Form("mdb")="" Then
Dim objFSO, objFolder, objItem
'--- CREATE PULL-DOWN LIST WITH NAMES OF .MDB FILES IN DEFAULT DIRECTORY ---
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Server.MapPath("."))
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Response.Write "Please select desired Access database: "
Response.Write "<SELECT name='mdb'>"
For Each objItem In objFolder.Files
If InStr(UCase(objItem.Name), ".MDB") > 0 Then Response.Write "<OPTION value=" & """" & objItem.Name & """" & "</OPTION>" & UCase(objItem.Name)
Next
Response.Write "</SELECT>"
Response.Write "<Input type='submit' value=' Next --> '>"
Response.Write "</FORM>"
Set objFSO = Nothing
Set objFolder = Nothing
Set objItem = Nothing
'--- PROMPT FOR DESIRED TABLE IN .MDB FILE (IF USER HAS NOT YET SPECIFIED) ---
ElseIf Request.Form("table")="" Then
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CREATE PULL-DOWN LIST OF ALL TABLES IN SELECTED .MDB FILE ---
Set rsFieldNames = dbc.OpenSchema(adSchemaTables)
rsFieldNames.Filter = "TABLE_TYPE='TABLE'"
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Response.Write "Please select desired table: "
Response.Write "<SELECT name=table>"
Do While Not rsFieldNames.EOF
Response.Write "<OPTION value=" & """" & rsFieldNames("TABLE_NAME") & """" & "</OPTION>" & rsFieldNames("TABLE_NAME")
rsFieldNames.MoveNext
Loop
Response.Write "</SELECT>"
Response.Write "<input name='mdb' type=hidden value='" & Request.Form("mdb") & "'>"
Response.Write "<Input type='submit' value=' Next --> '>"
Response.Write "</FORM>"
rsFieldNames.Close
set rsFieldNames = Nothing
dbc.Close
set dbc = Nothing
'--- PROMPT FOR DESIRED FIELDNAMES IN SELECTED TABLE (IF USER HAS NOT YET SPECIFIED) ---
ElseIf Request.Form("selectfields")="" Then
Dim table
table = Request.Form("table")
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CREATE RADIO-BUTTON SELECTION OF AVAILABLE FIELDS IN SELECTED TABLE ---
Set rsFieldNames = dbc.OpenSchema(4, Array(empty, empty, table, empty) )
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Do While Not(rsFieldNames.EOF)
Response.Write "<INPUT TYPE=checkbox NAME='selectfields' VALUE=" & """" & rsFieldNames(3) & """" & ">" & rsFieldNames(3) & "<BR>"
rsFieldNames.MoveNext
Loop
Response.Write "<INPUT TYPE='hidden' NAME='mdb' VALUE='" & Request.Form("mdb") & "'>"
Response.Write "<INPUT TYPE='hidden' NAME='table' VALUE='" & Request.Form("table") & "'><BR>"
Response.Write "<Input type='submit' value=' Next --> '>"
rsFieldNames.Close
set rsFieldNames = Nothing
dbc.Close
set dbc = Nothing
'--- IF ALL NECESSARY INFORMATION HAS BEEN PROVIDED, DISPLAY DATA FROM SELECTED TABLE ---
Else
'--- ESTABLISH CONNECTION TO DESIRED .MDB FILE ---
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CONSTRUCT SQL QUERY STRING ---
Dim sql
sql = "SELECT "
Dim i
For i = 1 to Request.Form("selectfields").Count
sql = sql & "[" & Request.Form("selectfields")(i) & "]"
If i<Request.Form("selectfields").Count Then sql = sql & ", "
Next
sql = sql & " FROM [" & Request.Form("table") & "];"
'--- CREATE RECORDSET BASED ON QUERY STRING
Set rsData = dbc.Execute(sql)
'--- CREATE RECORDSET OF DATABASE SCHEMA INFORMATION ---
Set rsFieldNames = dbc.OpenSchema(adSchemaColumns)
'--- WRITE DATA AND SCHEMA INFO ON CHOSEN FIELDS TO HTML TABLE
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<b>Database:</b> " & Request.Form("mdb") & ", <b>Table: </b> " & Request.Form("table") & "<br>"
Response.Write "<b>Query: </b> " & sql & "<br><br>"
Do While Not(rsData.EOF)
Response.Write "<TABLE WIDTH=100% BORDER=1 ALIGN=center>"
Response.Write "<TR><TD width='25%'><font face='verdana,arial' size=2><B>Field Name</b></TD><TD width='30%'><font face='verdana,arial' size=2><B>Description</b></TD><TD width='45%'><font face='verdana,arial' size=2><B>Value</b></TD></TR>"
For i = 1 to Request.Form("selectfields").Count
rsFieldNames.filter = "TABLE_NAME='" & Request.Form("table") & "'" & " AND COLUMN_NAME='" & Request.Form("selectfields")(i) & "'"
Response.Write "<TR>"
Response.Write " <TD width='25%'><font face='verdana,arial' size=2>" & Request.Form("selectfields")(i) & " </TD>"
Response.Write " <TD width='30%'><font face='verdana,arial' size=2>" & rsFieldNames("DESCRIPTION") & " </TD>"
Response.Write " <TD width='45%'><font face='verdana,arial' size=2>" & rsData(Request.Form("selectfields")(i)) & " </TD>"
Response.Write "</TR>"
Next
Response.Write "</TABLE><BR>"
rsData.MoveNext
Loop
'--- CLEAN UP ---
rsData.Close
rsFieldNames.Close
dbc.Close
set dbc = Nothing
End If
%>
