sql server - Updating SQL database with classic ASP -
i have modify old asp page allow users update listed phone numbers, stored in sql 2005 database. code page looks incredibly heavy, apologise density. users enter name form , directed following result page:
<h1>phone directory results detail</h1> <div class="subcontentstyles"> <br /> <% mm_telephone_string = "dsn=telephone;uid=sa;pwd=sapw;" %> <% dim telephone__varname telephone__varname = "%" if (request("fullname") <> "") telephone__varname = request("fullname") %> <% dim telephone__varjob telephone__varjob = "%" if (request("jobtitle") <> "") telephone__varjob = request("jobtitle") %> <% set telephone = server.createobject("adodb.recordset") telephone.activeconnection = mm_telephone_string telephone.source = "select ext, fullname, jobtitle, emailaddress, photo, extras, keywords, dbo.searchdetailscreen.photo, siteadd, department2, sitefax, mobile dbo.searchdetailscreen fullname '" + replace(telephone__varname, "'", "''") + "' , jobtitle '" + replace(telephone__varjob, "'", "''") + "' or fullname '" + replace(telephone__varname, "'", "''") + "' , jobtitle null" telephone.cursortype = 0 telephone.cursorlocation = 2 telephone.locktype = 3 telephone.open() telephone_numrows = 0 %> <% ' *** recordset stats, move record, , go record: declare stats variables ' set record count telephone_total = telephone.recordcount ' set number of rows displayed on page if (telephone_numrows < 0) telephone_numrows = telephone_total elseif (telephone_numrows = 0) telephone_numrows = 1 end if ' set first , last displayed record telephone_first = 1 telephone_last = telephone_first + telephone_numrows - 1 ' if have correct record count, check other stats if (telephone_total <> -1) if (telephone_first > telephone_total) telephone_first = telephone_total if (telephone_last > telephone_total) telephone_last = telephone_total if (telephone_numrows > telephone_total) telephone_numrows = telephone_total end if %> <% ' *** move record , go record: declare variables set mm_rs = telephone mm_rscount = telephone_total mm_size = telephone_numrows mm_uniquecol = "" mm_paramname = "" mm_offset = 0 mm_attotal = false mm_paramisdefined = false if (mm_paramname <> "") mm_paramisdefined = (request.querystring(mm_paramname) <> "") end if %> <% ' *** move specific record: handle detail parameter if (mm_paramisdefined , mm_rscount <> 0) ' value of parameter param = request.querystring(mm_paramname) ' find record unique column value equal parameter value mm_offset = 0 while (not mm_rs.eof) if (cstr(mm_rs.fields.item(mm_uniquecol).value) = param) exit end if mm_offset = mm_offset + 1 mm_rs.movenext loop ' if not found, set snumber of records , reset cursor if (mm_rs.eof) if (mm_rscount < 0) mm_rscount = mm_offset if (mm_size < 0 or mm_size > mm_offset) mm_size = mm_offset mm_offset = 0 ' reset cursor beginning if (mm_rs.cursortype > 0) mm_rs.movefirst else mm_rs.close mm_rs.open end if end if end if %> <% ' *** move record: handle 'index' or 'offset' parameter if (not mm_paramisdefined , mm_rscount <> 0) ' use index parameter if defined, otherwise use offset parameter r = request.querystring("index") if r = "" r = request.querystring("offset") if r <> "" mm_offset = int(r) ' if have record count, check if past end of recordset if (mm_rscount <> -1) if (mm_offset >= mm_rscount or mm_offset = -1) ' past end or move last if ((mm_rscount mod mm_size) > 0) ' last page not full repeat region mm_offset = mm_rscount - (mm_rscount mod mm_size) else mm_offset = mm_rscount - mm_size end if end if end if ' move cursor selected record = 0 while ((not mm_rs.eof) , (i < mm_offset or mm_offset = -1)) mm_rs.movenext = + 1 wend if (mm_rs.eof) mm_offset = ' set mm_offset last possible record end if %> <% ' *** move record: if dont know record count, check display range if (mm_rscount = -1) ' walk end of display range page = mm_offset while (not mm_rs.eof , (mm_size < 0 or < mm_offset + mm_size)) mm_rs.movenext = + 1 wend ' if walked off end of recordset, set mm_rscount , mm_size if (mm_rs.eof) mm_rscount = if (mm_size < 0 or mm_size > mm_rscount) mm_size = mm_rscount end if ' if walked off end, set offset based on page size if (mm_rs.eof , not mm_paramisdefined) if (mm_offset > mm_rscount - mm_size or mm_offset = -1) if ((mm_rscount mod mm_size) > 0) mm_offset = mm_rscount - (mm_rscount mod mm_size) else mm_offset = mm_rscount - mm_size end if end if end if ' reset cursor beginning if (mm_rs.cursortype > 0) mm_rs.movefirst else mm_rs.requery end if ' move cursor selected record = 0 while (not mm_rs.eof , < mm_offset) mm_rs.movenext = + 1 wend end if %> <% ' *** move record: update recordset stats ' set first , last displayed record telephone_first = mm_offset + 1 telephone_last = mm_offset + mm_size if (mm_rscount <> -1) if (telephone_first > mm_rscount) telephone_first = mm_rscount if (telephone_last > mm_rscount) telephone_last = mm_rscount end if ' set boolean used hide region check if on last record mm_attotal = (mm_rscount <> -1 , mm_offset + mm_size >= mm_rscount) %> <% ' *** go record , move record: create strings maintaining url , form parameters ' create list of parameters should not maintained mm_removelist = "&index=" if (mm_paramname <> "") mm_removelist = mm_removelist & "&" & mm_paramname & "=" mm_keepurl="":mm_keepform="":mm_keepboth="":mm_keepnone="" ' add url parameters mm_keepurl string each item in request.querystring nextitem = "&" & item & "=" if (instr(1,mm_removelist,nextitem,1) = 0) mm_keepurl = mm_keepurl & nextitem & server.urlencode(request.querystring(item)) end if next ' add form variables mm_keepform string each item in request.form nextitem = "&" & item & "=" if (instr(1,mm_removelist,nextitem,1) = 0) mm_keepform = mm_keepform & nextitem & server.urlencode(request.form(item)) end if next ' create form + url string , remove intial '&' each of strings mm_keepboth = mm_keepurl & mm_keepform if (mm_keepboth <> "") mm_keepboth = right(mm_keepboth, len(mm_keepboth) - 1) if (mm_keepurl <> "") mm_keepurl = right(mm_keepurl, len(mm_keepurl) - 1) if (mm_keepform <> "") mm_keepform = right(mm_keepform, len(mm_keepform) - 1) ' utility function used adding additional parameters these strings function mm_joinchar(firstitem) if (firstitem <> "") mm_joinchar = "&" else mm_joinchar = "" end if end function %> <% ' *** move record: set strings first, last, next, , previous links mm_keepmove = mm_keepboth mm_moveparam = "index" ' if page has repeated region, remove 'offset' maintained parameters if (mm_size > 0) mm_moveparam = "offset" if (mm_keepmove <> "") params = split(mm_keepmove, "&") mm_keepmove = "" = 0 ubound(params) nextitem = left(params(i), instr(params(i),"=") - 1) if (strcomp(nextitem,mm_moveparam,1) <> 0) mm_keepmove = mm_keepmove & "&" & params(i) end if next if (mm_keepmove <> "") mm_keepmove = right(mm_keepmove, len(mm_keepmove) - 1) end if end if end if ' set strings move links if (mm_keepmove <> "") mm_keepmove = mm_keepmove & "&" urlstr = request.servervariables("url") & "?" & mm_keepmove & mm_moveparam & "=" mm_movefirst = urlstr & "0" mm_movelast = urlstr & "-1" mm_movenext = urlstr & cstr(mm_offset + mm_size) prev = mm_offset - mm_size if (prev < 0) prev = 0 mm_moveprev = urlstr & cstr(prev) %> <table id="phone"> <tr> <td colspan="3" id="phonedetailhead">need change extension? <a href="#" data-reveal-id="mymodal">click here</a></td> </tr> <tr> <td id="phonedetailhead">full name </td> <td id="phoneresults"><%=(telephone.fields.item("fullname").value)%></td> <td rowspan="8" id="phoneresults"> <img src="<%=(telephone.fields.item("photo").value)%>" alt="<%=(telephone.fields.item("fullname").value)%>" /> </td> </tr> <tr> <td id="phonedetailhead">ext </td> <td id="phoneresults"><%=(telephone.fields.item("ext").value)%> <%=(telephone.fields.item("extras").value)%></td> </tr> <tr> <td id="phonedetailhead">mobile </td> <td id="phoneresults"><%=(telephone.fields.item("mobile").value)%></td> </tr> <tr> <td id="phonedetailhead"> email address </td> <td id="phoneresults"> <% strmail = "mailto:" & telephone.fields.item("emailaddress").value %> <a href="<%=strmail%>" ><%=(telephone.fields.item("emailaddress").value)%></a></td> </tr> <tr> <td id="phonedetailhead">job title </td> <td id="phoneresults"><%=(telephone.fields.item("jobtitle").value)%></td> </tr> <tr> <td id="phonedetailhead">department </td> <td id="phoneresults"><%=(telephone.fields.item("department2").value)%></td> </tr> <tr> <td id="phonedetailhead">site </td> <td id="phoneresults"><%=(telephone.fields.item("siteadd").value)%></td> </tr> <tr> <td id="phonedetailhead">fax </td> <td id="phoneresults"><%=(telephone.fields.item("sitefax").value)%></td> </tr> <tr id="phoneresults"> <td colspan="3"><a href="http://intranet/phone/phoneform.asp" title="new search">new search</a></td> </tr> </table> <% telephone.close() %> how go allowing users update field 'ext'?
originally, made modal window simple two-field form. can't seem paste code here without losing of it. fields given labels , ids 'fullname' , 'ext'.
the form used method send captured info next page, action=phoneresults.asp, same page. on page had following update query:
<% dim fullname, ext dim sconnstring, connection, ssql fullname = request.form("fullname") ext = request.form("ext") ssql = "insert dbo.telephone.staffdetails (fullname, ext) values ('" & fullname & "', '" & ext & "')" %> there's no necessity modal window. first way of updating popped (up?) mind. having looked around online quite time, i've begun wonder if it's simplest way it; over-complicating things?
my update query presumably monumentally off, , i'm guessing so, too, way i'm trying give information needs. apologise it. searching around online, seems code i'm working on incredibly old.
you wrote using form method.
in case, have use request.querystring("fullname") value. request.form(...) used http method post.
you can use request("fullname") if don't care method data coming in.
i have add, should sanitize values before putting them in sql statement prevent sql injection, know this... :)
Comments
Post a Comment