My eventual goal is to be able to pull up table data by using a form. I was going to allow the user to enter the table name by an input box, but I figured it would be easier just to put the table names in an option box so that the user can just select the table name, select what statement(action) they are trying to execute[select,update,delete,insert), and what information they want to view. Right now I am just working myself up to all these things, but I have gotten stumped already. I only want the user to be able to view all records from either of the three tables I have for them to select from (Customers, Orders, Employees) for RIGHT NOW, I'll go back, as I progress, and start adding the other capabilities.
So here's what's happening, I got my code and the web browser compiler keeps telling me I am missing an expected statement! I have searched and searched and I can't find anything I could possibly be missing or anything I may have entered wrong. I am new to ASP pages and I know the syntax for some things may have to be slightly altered in order for them to work properly in ASP.
I just need for someone to see where I could have possibly been going wrong and see if you have any advice to what I may be lacking in my code. The code is posted below, thank you!
By the way, I'm only really focused on the forms right now, the HTML I know there are things down there I need to fix, but right now that's not stopping my script from running.
Once I get the script to run I want to be able to output all the data through a table on the page
<% SCRIPT LANGUAGE="VBSCRIPT"%>
<%
<!--
Sub Validate
Dim DateTimeForm
Dim TableForm
Set DateTimeForm = Document.forms("DateTime")
Set TableForm = Document.forms("Query")
If TableForm.Submit.Value = "Customers" Then
If TableForm.Submit.Value <> "SELECT"Then
If TableForm.Submit.Value <> "*" Then
MsgBox "You can only view all records in Customers Table at this time."
End If
End If
End If
If TableForm.Submit.Value = "Employees" Then
If TableForm.Submit.Value <> "*" Then
If TableForm.Submit.Value <> "SELECT" Then
MsgBox "We apologize for the inconvenience but that query is not allowed at this time."
End If
End If
End If
If TableForm.Submit.Value = "Orders" Then
If TableForm.Submit.Value <> "*" Then
If TableForm.Submit.Value <> "SELECT" Then
MsgBox "Sorry for the inconvenience, that query is not allowed at this time."
End If
End If
End If
End Sub -->
</SCRIPT>
<FORM ID="Query" onsubmit="Validate(); return false;" language="jscript">
Select Table:
<SELECT NAME="Table">
<OPTION VALUE="Customers">Customers
<OPTION VALUE="Employees">Employees
<OPTION VALUE="Orders">Orders
</SELECT>
Select Action:
<SELECT NAME="Action">
<OPTION VALUE="SELECT">SELECT
<OPTION VALUE="UPDATE">UPDATE
<OPTION VALUE="INSERT">INSERT
<OPTION VALUE="DELETE">DELETE
</SELECT>
Select SubAction:
<SELECT NAME="SubAction">
<OPTION VALUE="*">ALL
<OPTION VALUE="*=">LEFT-OJ
<OPTION VALUE="=*">RIGHT-OJ
<OPTION VALUE="*=*">FULL-OJ
</SELECT>
Select Field:
<SELECT NAME="Field">
<OPTION VALUE="CustomerID">CustomerID
<OPTION VALUE="EmployeeID">EmployeeID
<OPTION VALUE="OrderID">OrderID
</SELECT>
<P><INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="SELECT">
</form>
%>
Function GetEmployeeTableRows
''''Declaration section
Dim rs
Dim ConnectionString
Dim out
Set rs = CreateObject("ADODB.RecordSet")
ConnectionString = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=Northwind;Data Source=localhost"
rs.open " SELECT COUNT(CUSTOMERS.CustomerID), ORDERS.EmployeeID, Country, ShipCountry" & _
" FROM <%Request.QueryString("Table")%> " & _
" WHERE Country *= ShipCountry " & _
" AND ORDERS.EmployeeID IN ('5')" & _
"GROUP BY ORDERS.EmployeeID" _
,ConnectionString
out = ""
''' this will contain all the HTML that we build
''' Create the table header from the table column names...
If Not rs.EOF Then
out = out & "<TR>" & vbCrLf
For i = 0 To rs.Fields.Count - 1
out = out & "<TD class=""header"">" & _
rs.Fields(i).name & _
"</TD>" & vbCrLf
Next
out = out & "</TR>" & vbCrLf
End If
'''Now process all the received data rows into HTML table rows...
Dim val
While Not rs.EOF
out = out & "<TR>" & vbCrLf
For i = 0 To rs.Fields.Count - 1
val = rs.Fields(i).value
''''Validation check to see if field data contains an array
If IsNull(val) Then
val = "no info"
ElseIf IsArray(val) Then
val = "(array)"
End If
out = out & "<TD " & _
"style=""vertical-align: top"" " & _
">" & _
val & _
"</TD>" & vbCrLf
Next
out = out & "</TR>" & vbCrLf
rs.MoveNext
Wend
rs.close
GetEmployeeTableRows = out
End Function
%>
<HTML>
<HEAD>
<TITLE>Upload Table</TITLE>
</HEAD>
<style>
.header {
font: bold 12pt sans-serif;
color: purple;
}
td {
font: 10pt sans-serif;
color: blue;
}
</style>
</head>
<body>
<table border="1">
<%= GetEmployeeTableRows() %>
</table>
</BODY>
</HTML>
VBScript/Forms
