Help with editing/updating a recordset

ASP Free Forums Sponsor:

AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th-1:00PM EST. Register Today!

May 7th, 2008, 01:47 PM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

Help with editing/updating a recordset

I need help in creating an application that will edit a recordset once I call it using a drop down menu. If someone could point me in the right direction it would be greatly appreciated. Here is my code for my drop down menu and table.

Thanks
colin

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<%
Dim varsearch
dslam = request.form("dslam")


Sub showResults
	formAction = Request.Form("formAction")
	If formAction = "SUBMIT" Then
		' Grab Values From Form
		search = Request.Form("dslam")
		



		' Open Table
		response.write "<table border=""1"">" & vbCrLf	


			' Create Connection to DB 
			Dim myConn
			Set myConn = Server.CreateObject("ADODB.Connection")
			myConn.Open "Provider=SQLOLEDB;Server=xx;Database=xx;UID=xx;PWD  =xx;"
                       

			' Create Recordset
			strSQL = "select location,portnumber,name,phone,ipaddress,username,  password,users,additionalaccounts from dbo.ports where location = '"&dslam&"' order by location "
			 
			Set oRSp = myConn.Execute(strSQL)
%>



<TABLE BORDER="1" CELLPADDING="1" CELLSPACING="3" WIDTH="100%">
<THREAD>
  <TR>
    <TD><STRONG>Location</STRONG></TD>
	<TD><STRONG>Port Number</STRONG></TD>
	<TD><STRONG>Name</STRONG></TD>
	<TD><STRONG>Phone</STRONG></TD>
	<TD><STRONG>IP Address</STRONG></TD>
	<TD><STRONG>Username</STRONG></TD>
	<TD><STRONG>Password</STRONG></TD>
	<TD><STRONG>Users</STRONG></TD>
	<TD><STRONG>Additional Accounts</STRONG></TD>
  </TR>
</THREAD>
<%


			' Loop Through Results
			If Not oRSp.EOF Then
				Do While Not oRSp.EOF
    			    response.write "<TR><TD>" & oRSp("location") & "</TD>" & vbCrLf
    				response.write "<TD>" & oRSp("portnumber") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("name") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("phone") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("ipaddress") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("username") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("password") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("users") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("additionalaccounts") & "&nbsp;</TD>" & vbCrLf
					response.write "<TD>" & oRSp("phone") & "</TD></TR>" & vbCrLf
				oRsp.MoveNext
				Loop
			Else
				response.write "<TR><TD>Sorry, no records found.</TD></TR>" & vbCrLf
			End If
		Else
			response.write "<TR><TD>" & errorList & "</TD></TR>" & vbCrLf
		End If


		' Close Table
		response.write "</table>" & vbcrLf
oRSp.Close
Set oRSp = Nothing
myconn.close
set myconn = nothing
			
End Sub
%>

<FORM 
     ACTION="dslamtest.asp" 
     METHOD="POST">
<SELECT NAME="dslam" value="dslam">
<OPTION VALUE="">Choose a Location

<OPTION VALUE="AT-28 CAMP-1">AT-28 CAMP-1
<OPTION VALUE="AT-28 CAMP-2">AT-28 CAMP-2
<OPTION VALUE="AT-28 CAMP-3">AT-28 CAMP-3
<OPTION VALUE="AT-28 CAMP-4">AT-28 CAMP-4
<OPTION VALUE="AT-28 CAMP-5">AT-28 CAMP-5
<OPTION VALUE="C04 DSLAM-3">C04 DSLAM-3
<OPTION VALUE="C06 DSLAM-1">C06 DSLAM-1
<OPTION VALUE="C09 DSLAM-1">C09 DSLAM-1
<OPTION VALUE="C09 DSLAM-2">C09 DSLAM-2
<OPTION VALUE="C09 DSLAM-3">C09 DSLAM-3
<OPTION VALUE="C09 DSLAM-4">C09 DSLAM-4
<OPTION VALUE="C09 DSLAM-5">C09 DSLAM-5
<OPTION VALUE="C09 DSLAM-6">C09 DSLAM-6
<OPTION VALUE="C09 DSLAM-7">C09 DSLAM-7
<OPTION VALUE="C09 DSLAM-8">C09 DSLAM-8
<OPTION VALUE="C09 DSLAM-9">C09 DSLAM-9
<OPTION VALUE="C09 DSLAM-10">C09 DSLAM-10
<OPTION VALUE="C09 DSLAM-11">C09 DSLAM-11
<OPTION VALUE="C09 DSLAM-12">C09 DSLAM-12
<OPTION VALUE="C09 DSLAM-13">C09 DSLAM-13
<OPTION VALUE="C09 DSLAM-14">C09 DSLAM-14
<OPTION VALUE="C09 DSLAM-15">C09 DSLAM-15
<OPTION VALUE="C09 DSLAM-16">C09 DSLAM-16
<OPTION VALUE="C09 DSLAM-17">C09 DSLAM-17
<OPTION VALUE="C09 DSLAM-18">C09 DSLAM-18
<OPTION VALUE="C09 DSLAM-19">C09 DSLAM-19
<OPTION VALUE="C09 DSLAM-20">C09 DSLAM-20
<OPTION VALUE="C09 DSLAM-21">C09 DSLAM-21
<OPTION VALUE="C09 DSLAM-22">C09 DSLAM-22
<OPTION VALUE="C09 DSLAM-23">C09 DSLAM-23
<OPTION VALUE="C09 DSLAM-24">C09 DSLAM-24
<OPTION VALUE="C09 Multi-1">C09 DSLAM-1
<OPTION VALUE="C09 Multi-2">C09 DSLAM-2
<OPTION VALUE="C09 Multi-3">C09 DSLAM-3
<OPTION VALUE="C09 Multi-4">C09 DSLAM-4
<OPTION VALUE="C09 Multi-5">C09 DSLAM-5
<OPTION VALUE="C09 Multi-6">C09 DSLAM-6
<OPTION VALUE="C09 Multi-7">C09 DSLAM-7
<OPTION VALUE="C09 Multi-8">C09 DSLAM-8
<OPTION VALUE="C09 Multi-9">C09 DSLAM-9
<OPTION VALUE="C09 Multi-10">C09 DSLAM-10
<OPTION VALUE="C09 Multi-11">C09 DSLAM-11
<OPTION VALUE="C09 Multi-12">C09 DSLAM-12
<OPTION VALUE="CBQ DSLAM-1">CBQ DSLAM-1
<OPTION VALUE="CBQ DSLAM-2">CBQ DSLAM-2
<OPTION VALUE="CBQ DSLAM-3">CBQ DSLAM-3
<OPTION VALUE="CBQ DSLAM-4">CBQ DSLAM-4
<OPTION VALUE="Cuzco DSLAM-1">Cuzco DSLAM-1
<OPTION VALUE="Cuzco DSLAM-2">Cuzco DSLAM-2
<OPTION VALUE="Cuzco DSLAM-3">Cuzco DSLAM-3
<OPTION VALUE="Cuzco DSLAM-4">Cuzco DSLAM-4
<OPTION VALUE="Official-1">Official-1

</SELECT>
<input type="hidden" name="formAction" value="SUBMIT">
<input type="submit" value="Submit">
</FORM>

<%
If request.servervariables("CONTENT_LENGTH")>0 then
call showResults
end if
%>
</body>
</html>

May 7th, 2008, 02:51 PM

bla4free

Contributing User

Join Date: Feb 2005

Posts: 143

Time spent in forums: 2 Days 11 h 29 m
Reputation Power: 21

What exactly do you want to do? And where do you want to do it?

May 7th, 2008, 03:01 PM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

Right now I have a drop down menu of locations. When you choose a location a table is made with all of the rows that have that location. I would like to have a functionality where you can edit the table's contents and inturn it will update the database.

colin

May 7th, 2008, 05:16 PM

keep_it_simple

KIS

Join Date: Jul 2007

Location: USA

Posts: 1,031

Time spent in forums: 2 Weeks 2 Days 8 h 43 m 49 sec
Reputation Power: 340

Send a message via Yahoo to keep_it_simple

Quote:

Originally Posted by Cozilla

not an easy task if in bulk (ajax would be handy here-realtime updates vs looping)...is this what you are wanting...or modify one by one?

__________________
Please give respect to those that helped solve an issue by clicking on the reputation icon

May 8th, 2008, 07:13 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

I would like to be able to edit each row individually. As in have an edit button or link in the last column of the table that would allow me to edit some of the fields. Im guessing on click of the edit button for a certain row it would redirect me to an edit page where i can enter in the information like a form, then on submit it updates the database and returns me to the page with the table with the updated information.

colin

May 9th, 2008, 07:42 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

anyone?

May 13th, 2008, 07:54 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

bump

May 13th, 2008, 08:19 AM

keep_it_simple

KIS

Join Date: Jul 2007

Location: USA

Posts: 1,031

Time spent in forums: 2 Weeks 2 Days 8 h 43 m 49 sec
Reputation Power: 340

Quote:

Originally Posted by Cozilla

bump

its the same 'ole song and dance...

no code = no replies...

this is a very common task for a developer...and google has many examples...yet you have no code = no effort

May 13th, 2008, 08:25 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

I'm sorry I am not asking for someone to do it for me, I was just looking for some direction.

I have been searching Google and have not come up with much help on the subject. If you know of any good examples please link me.

Thanks,

Colin

#10

May 13th, 2008, 08:35 AM

keep_it_simple

KIS

Join Date: Jul 2007

Location: USA

Posts: 1,031

Time spent in forums: 2 Weeks 2 Days 8 h 43 m 49 sec
Reputation Power: 340

Quote:

Originally Posted by Cozilla

I'm sorry I am not asking for someone to do it for me, I was just looking for some direction.

Thanks,

Colin

i didn't mean to imply that colin...i was simply pointing out why no replies...

the simplest way is by having a column that has hyperlinks....

while looping thru the recordset you can have the id and the target page in the link...click the link you now have parameters to set up a query

for example: <a href="edit.asp?recid=<%=oRs("recid")%>">Edit</a>

make sure whomever has access to the "edit" page is protected....as memnoch will certainly warn ya about

.....the link contains the rec id....so now you use request.querystring("recid") on the edit page

remember...this is a simple approach.....if wanting a more advanced.....you'll need to supply code...and point us to where you are having trouble

demo links...and yes....google came up on the first hit

http://www.asp101.com/samples/db_edit.asp

http://www.4guysfromrolla.com/webtech/082799-1.shtml

Last edited by keep_it_simple : May 13th, 2008 at 08:41 AM.

#11

May 13th, 2008, 08:45 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

I appreciate the help KIS.

Thanks again

Colin

#12

May 14th, 2008, 08:39 AM

Cozilla

Contributing User

Join Date: Oct 2007

Posts: 48

Time spent in forums: 14 h 8 m 56 sec
Reputation Power: 1

Im having trouble getting my page to come up with the following code. Where I am trying to get the edit link to show up in the table. Any suggestions?

Colin

Code:

response.write "<table border=""1"">" & vbCrLf	


			' Create Connection to DB 
			Dim myConn
			Set myConn = Server.CreateObject("ADODB.Connection")
			myConn.Open "Provider=SQLOLEDB;Server=xx;Database=xx;UID=xx;PWD  =xx;"
                       

			' Create Recordset
			strSQL = "select id,location,portnumber,name,phone,ipaddress,userna  me,password,users,additionalaccounts from dbo.ports where location = '"&dslam&"' order by location "
			 
			Set oRSp = myConn.Execute(strSQL)
%>



<TABLE BORDER="1" CELLPADDING="1" CELLSPACING="3" WIDTH="100%">
<THREAD>
  <TR>
    <TD><STRONG>Location</STRONG></TD>
	<TD><STRONG>Port Number</STRONG></TD>
	<TD><STRONG>Name</STRONG></TD>
	<TD><STRONG>Phone</STRONG></TD>
	<TD><STRONG>IP Address</STRONG></TD>
	<TD><STRONG>Username</STRONG></TD>
	<TD><STRONG>Password</STRONG></TD>
	<TD><STRONG>Users</STRONG></TD>
	<TD><STRONG>Additional Accounts</STRONG></TD>
<TD><STRONG>Edit</STRONG></TD>

  </TR>
</THREAD>
<%


			' Loop Through Results
			If Not oRSp.EOF Then
				Do While Not oRSp.EOF
    			    response.write "<TR><TD>" & oRSp("location") & "</TD>" & vbCrLf
    				response.write "<TD>" & oRSp("portnumber") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("name") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("phone") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("ipaddress") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("username") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("password") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("users") & "</TD>" & vbCrLf
					response.write "<TD>" & oRSp("additionalaccounts") & "&nbsp;</TD>" & vbCrLf
					Response.Write(" <td>"<a href='edit.asp?ID=" & RecordSet.Fields("ID") & "'>Edit</a></td>" & vbCrLf)
					Response.Write("</tr>" & vbCrLf)
				oRsp.MoveNext
				Loop
			Else
				response.write "<TR><TD>Sorry, no records found.</TD></TR>" & vbCrLf
			End If
		Else
			response.write "<TR><TD>" & errorList & "</TD></TR>" & vbCrLf
		End If


		' Close Table
		response.write "</table>" & vbcrLf
oRSp.Close
Set oRSp = Nothing
myconn.close
set myconn = nothing
			
End Sub
%>

#13

May 14th, 2008, 10:09 AM

sync_or_swim

Contributing User

Join Date: Mar 2006

Location: South Wales

Posts: 1,259

Time spent in forums: 3 Weeks 2 Days 23 h 8 m 7 sec
Reputation Power: 796

Try this:

Code:

Response.Write("<td><a href=""edit.asp?ID=" & RecordSet.Fields("ID") & """>Edit</a></td>" & vbCrLf)

#14

May 14th, 2008, 10:23 AM

sync_or_swim

Contributing User

Join Date: Mar 2006

Location: South Wales

Posts: 1,259