Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

View / Edit SQL Table from web page

Status
Not open for further replies.
mobbarley

mobbarley

IS-IT--Management
Mar 4, 2004
22
0
0
AU
Hello.

I want to set up an inventory table in my client database so I can keep track of stock @ client sites. there could be anything from 5 - 500 enteries for each client & i need to be able to view & edit this from a web page easilly.

Can anyone recommend an easy way? I'm using ADO & ASP already. Ideally something like spreadsheet like you see in programs such as enterprise manager which can insert records as well as view would be great.

Regards,
John R.
 
Sort by date Sort by votes
i recently posted a thread on a table updater/viewer
search the forum for it, you plug in a few variables and the form is ready to run. it lists the db contents, and edit buttons, then you do a single record edit.

[thumbsup2]DreX
aKa - Robert
 
Upvote 0 Downvote
hmmm .. seems i cant find that thread i was referring to so here's the code :

please note, this page can be a security hole, so make sure you store it well and put some authentication on it
Code: 
<%
Response.Expires = 0
Response.Buffer = True
' [green]Connection/Table Variables[/green]
Connection = "dsn=blah;"
Tablename="blahtable"
IdField = "ID"

' [green]Data Settings [/green]
NumericTypes = "2,3,4,5,6,17,72,128,131"
TruthTypes = "11"
DataTypes = "128,129,130,200,201,202,203,204,205"
DateTypes = "135"
DateChar = "#"
' [green]DateChar is the Date Qualifyer for your DataSource, SQL uses single quote, MS Access uses #[/green]
%>
<html>
<head>
	<title>Record Editor</title>
</head>

<body>
<%
Set Con = CreateObject("ADODB.Connection")
Con.Open Connection
SQL = "Select * From [" & TableName & "] Order by " & IdField ' [green]this may need to be altered for pagination if you have large tables[/green]
Set RS = Con.Execute(SQL)
If Not RS.EOF Then ' [green]this check is here just for empty tables, you may want to juggle things around a little[/green]
  If Request("Action") = "Save" AND Request("ID") <> "" Then
    Dim SaveSQL(0)
    SaveSQL(0) = "Update [" & TableName & "] Set "
    For each Element in Request.Form
      If Element = "ID" Then
        SqlCondition = " Where [" & IDField & "]=" & Request(Element) ' [green]Leading Space is important, dont remove[/green]
      Else
        FieldName = Mid(Element,4)
        If Left(Element,3) = "Txt" Then
          If Request(element) <> "" Then
            SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]='" & Replace(Request(Element),"'","''") & "', "
          Else
            SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=NULL, "
          End If
        ElseIf Left(Element,3) = "Num" Then
          If Request(element) <> "" Then
            SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & Request(Element) & ", "
          Else
            SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=NULL, "
          End If
        ElseIf Left(Element,3) = "ToF" Then
          SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & Request(Element) & ", "
        ElseIf Left(Element,3) = "Dte" Then
          SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & DateChar & Request(Element) & DateChar & ", "
        End If 
      End If
    Next
    SaveSQL(0) = Left(SaveSQL(0),Len(SaveSQL(0))-2) & SqlCondition ' [green]Chopping off the trailing comma/space and adding the condition to it[/green]
    Set RSUpd = Con.Execute(SaveSQL(0))
    response.write "Record Saved<br>"
  End If
  ' [green]break in conditionals, so that after save, you can still View Record[/green]

  If (Request("Action") = "Edit" OR Request("Action") = "Save") AND (Request("ID") <> "") Then
    Set RS = Con.Execute("Select * From [" & TableName & "] Where [" & IdField & "]=" & Request("ID"))
' [green]outputting Form[/green]
    If Not Rs.EOF then
%>
<form method="post">
<table border=1>
    <tr>
      <td><%=IdField%>:</td><td><%=RS(IDField)%><input type="hidden" name="ID" value="<%=RS(IDField)%>"></td>
    </tr>
<%
      For Each Field in Rs.Fields
        If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then
%>
    <tr>
      <td><%=Field.Name%>:</td>
<%
          If ChkArray(DataTypes,Field.Type) Then ' [green]looking for text type fields[/green]
            If Field.DefinedSize >= 200 then
%>
      <td><textarea name="Txt<%=Server.HTMLEncode(Field.Name)%>"><%=Server.HTMLEncode(RS(Field.Name))%></textarea></td>
<%
            Else
%>
      <td><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>" maxsize="<%=Field.DefinedSize%>"></td>
<%
            End If
          ElseIf ChkArray(NumericTypes,Field.Type) Then ' [green]looking for numeric type fields[/green]
%>
      <td><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>"></td>
<%
          ElseIf ChkArray(TruthTypes,Field.Type) Then ' [green]looking for numeric type fields[/green]
            If Not IsNull(RS(Field.Name)) then
              If RS(Field.Name) = 1 OR RS(Field.Name) = True Then
                OptTrue = " CHECKED"
                OptFalse = ""
              ElseIf RS(Field.Name) = 0 OR RS(Field.Name) = False Then
                OptTrue = ""
                OptFalse = " CHECKED"  
              Else
                OptTrue = ""
                OptFalse = ""  
              End If
            Else
              OptTrue = ""
              OptFalse = ""  
            End If
%>
      <td>
        True <input type="radio" name="ToF<%=Server.HTMLEncode(Field.Name)%>" value="1"<%=OptTrue%>>&nbsp;&nbsp;
        False <input type="radio" name="ToF<%=Server.HTMLEncode(Field.Name)%>" value="0"<%=OptFalse%>>
      </td>
<%
          ElseIf ChkArray(DateTypes,Field.Type) Then ' [green]looking for date type fields[/green]
%>
      <td nowrap><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>"> DateValue, Format as MM/DD/YYYY OR MM/DD/YYYY</td>
<%
          Else ' [green]catching unknown field types[/green]
%>
      <td>Unknown FieldType : <%=Field.Type%></td>
<%
          End If
%>
    </tr>
<%
        End If
      Next
%>
</table>
<input type="submit" value="Save" name="action">
<input type="submit" value="Cancel/Done" name="action">
</form>
<%
    Else
%>
Error Retrieving Record.
<%      
    End If
  Else ' [green]Displays your Table Contents for Editing[/green]
%>
<table border=1>
    <tr>
      <td><b><%=IdField%></b></td>
<%
    For Each Field in Rs.Fields
      If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then ' show all fields except ID since ID is above ( forced first Column )
%>
      <td><b><%=Field.Name%></b></td>
<%
      End If
    Next
%>
      <td>&nbsp;</td>
    </tr>
<%
' [green]outputting Data[/green]
    Do While Not Rs.EOF
%>
    <tr>
      <td><%=Server.HTMLEncode(RS(IdField))%></td>
<%
      For Each Field in Rs.Fields
        If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then ' show all fields except ID since ID is above ( forced first Column )
%>
      <td><%=Server.HTMLEncode(RS(Field.Name))%></td>
<%
        End If
      Next
%>
      <td><table><tr><td><form method="post"><input type="submit" value="Edit" name="Action"><input type="hidden" name="ID" value="<%=RS(IdField)%>"></td></tr><tr><td></form></td></tr></table></td>
    </tr>
<%
      Response.Flush
      RS.MoveNext
    Loop
%>
</table>
<%
  End If
Else
%>
Table Empty, enter at least one record to be able to edit.
<%
End if
Set RS = nothing
con.Close
Set Con = nothing
%>
</body>
</html>

<%
Function ChkArray(Values,Value) ' [green]returns true/false on a comparitive set[/green]
    If IsArray(Values) Then
        ChkArrayArr = Values
    Else
        ChkArrayArr = Split(Values,",")
    End If
    ChkArray = False
    For ChkArrayArrCounter=0 to Ubound(ChkArrayArr)
        If StrComp(ChkArrayArr(ChkArrayArrCounter),Value,vbTextCompare)=0 Then
            ChkArray = True
        End If
    Next
End Function
%>

[thumbsup2]DreX
aKa - Robert
 
Upvote 0 Downvote
aha here's that thread finally found it, blasted search on here isn't overly reliable : thread333-880932

there's some extra explaination stuffs in it, and some detail on how it works.

[thumbsup2]DreX
aKa - Robert
 
Upvote 0 Downvote
Thats some nifty code, but not exactly what im looking for. Mainly I want to ease inserting records. I'm assuming I'll need to use some sort of insertable component maybe? basically I want someone to be able to insert as many records as possible using some kind of growing sheet, where they can click for a new line.

any ideas?

thanks.
 
Upvote 0 Downvote
well it'd be along the same premise of the above code, instead of retrieving a record for display/delete, you'd retrieve _A_ record for the fields in the db, and generate your input form, and then insert from there.

second option is using a little bit of clientside vbs and activex... easiest way to accomodate this would be port some of the data structure to Access, then export to ASP, access has a nifty export to asp interface in it that will allow you to edit via web page pretty quick and easy

[thumbsup2]DreX
aKa - Robert
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
167
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
145
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
115
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
128
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
288
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top