Recordset help!

1

hi.
i want to ask about updating and deleting records from database using recordset method instead of sql method. I using asp and flash.

am i correct to write like this for update?

struserID = Request("currentUserID")
strconID = Request("currentContactID")
strFname = Request("newFname")
strLname = Request("newLname")
strphone = Request("newphone")
stremail = Request("newemail")
straddress = Request("newaddress")
strfax = Request("newfax")
strcompany = Request("newcompany")
strnotes = Request("newnotes")


'create the database connection
Set oConn = Server.CreateObject("ADODB.Connection")
'open the database
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/0search001/database02.mdb") 

'open the recordset
Set oRs = Server.CreateObject("ADODB.Recordset")

strSql = "Select * From Contacts Where UserID= " & struserID & " AND ContactID = "strconID" "

		oRs.Open strSql,oConn,1,3

	If oRs.EOF Then

			Response.write ("updatego=false")
		else 

			oRs("FirstName") = strFname 
			oRs("LastName") = strLname 
			oRs("PhoneNum") = strphone
			oRs("Email") = stremail 
			oRs("Address") = straddress 
			oRs("FaxNum") = strfax 
			oRs("Company") = strcompany 
			oRs("Notes") = strnotes 
			oRs.Update
			Response.write ("updatego=true")
	end if 
	
 oRs.Close
 oConn.Close

and this for delete?

strconid = Request(“currentContactID”)
strFname = Request(“newFname”)
strLname = Request(“newLname”)
struserID = Request(“currentUserID”)

	'create the database connection
Set oConn = Server.CreateObject("ADODB.Connection")
'open the database
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/0calendar001/database02.mdb") 

'open the recordset
Set oRs = Server.CreateObject("ADODB.Recordset")

strSql = "Select * From Contacts Where FirstName='"& strFname & "' " & " And LastName= '" & strLname & "' And UserID= " & struserID & " AND ContactID = "strconID" "

		oRs.Open strSql,oConn,1,3

	If oRs.EOF Then

			Response.write ("deletego=false")
		else 
			oRs.Delete
			Response.write ("deletego=true")
	end if 
	
oRs.Close
oConn.Close
2

Not familiar with this
Response.write (“updatego=false”)

it seems like it should work. I would use request.form for your request instead request.

Also close your db connection and record sets with
Set objRS = nothing

That frees up memory

Actually somethin is not right let me try to run it on my server in a lil bit.

3

this is rather differnet from yours but it does the similar…there is a faster way to do what you are doing… Try this.

<%
Response.Expires = 0
classRepl = Replace(Request.Form(“class”),"’","’’")
classDescRepl = Replace(Request.Form(“class_description”), “’”,"’’")
locationRepl = Replace(Request.Form(“location”), “’”, “’’”)
sqlInsert = “INSERT INTO tblClasses (class, class_description, location, dateSubmit) VALUES (’” & classRepl & “’,’” & classDescRepl & “’,’” & locationRepl & “’,’” & FormatDateTime(Request.Form(“dateSubmit”),2) & “’)”
Set objConn = Server.CreateObject(“ADODB.Connection”)
Set objRS = Server.CreateObject(“ADODB.Recordset”)

strCxn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("./bfc.mdb") & ";"
objConn.Open strCxn

If Request.Form("class") &lt;&gt; "" and Request.Form("class_description") &lt;&gt; "" and Request.Form("location") &lt;&gt; "" and Request.Form("dateSubmit") &lt;&gt; "" and Request.Form("action") = "" Then
	sqlCheckExist = "SELECT * FROM tblClasses WHERE class='" & classRepl & "' AND class_description='" & classDescRepl & "' AND location='" & locationRepl & "' AND dateSubmit ='" & FormatDateTime(Request.Form("dateSubmit"),2) & "'"
	objRS.Open sqlCheckExist, objConn
	If objRS.EOF = False Then
		Response.Write "&lt;font color=red&gt;This record already exists&lt;/font&gt;"
	Else
		objConn.Execute sqlInsert
		Response.Write "&lt;font color=red&gt;Record inserted&lt;/font&gt;"
	End If
	objRS.Close
ElseIf Request.Form("action") = "modify" and classRepl &lt;&gt; "" and classDescRepl &lt;&gt; "" and locationRepl &lt;&gt; "" and Request.Form("dateSubmit") &lt;&gt; "" Then
	sqlUpdate = "DELETE * FROM tblClasses WHERE ID=" & Request.Form("id")
	objConn.Execute sqlUpdate
	objConn.Execute sqlInsert
	Response.Write "&lt;font color=red&gt;Record Updated&lt;/font&gt;"
ElseIf Request.QueryString("action") = "delete" Then
	sqlDelete = "DELETE * FROM tblClasses WHERE ID = " & Request.QueryString("id")
	objConn.Execute sqlDelete
	Response.Write "&lt;font color=red&gt;Record Deleted&lt;/font&gt;"
End If

If Request.QueryString(“action”) = “modify” Then
sqlModify = "SELECT * FROM tblClasses WHERE ID = " & Request.QueryString(“id”)
objRS.Open sqlModify, objConn

classVal = objRS("class")
classDescrVal = objRS("class_description")
locationVal = objRS("location")
dateVal = objRS("dateSubmit")	
objRS.Close

End If

%>

This is from a sample I posted before…It modifies the record using record set. Both modify and update are on the same page. Let me know if you need further explanation…I wrote it rather messy. sorry

why wont my code display???

4 

<%
Response.Expires = 0
	classRepl = Replace(Request.Form("class"),"'","''")
	classDescRepl = Replace(Request.Form("class_description"), "'","''")
	locationRepl = Replace(Request.Form("location"), "'", "''")
	sqlInsert = "INSERT INTO tblClasses (class, class_description, location, dateSubmit) VALUES ('" & classRepl & "','" & classDescRepl & "','" & locationRepl & "','" & FormatDateTime(Request.Form("dateSubmit"),2) & "')" 
	Set objConn = Server.CreateObject("ADODB.Connection")
	Set objRS = Server.CreateObject("ADODB.Recordset")
	
	strCxn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("./bfc.mdb") & ";"
	objConn.Open strCxn
	
	If Request.Form("class") <> "" and Request.Form("class_description") <> "" and Request.Form("location") <> "" and Request.Form("dateSubmit") <> "" and Request.Form("action") = "" Then
		sqlCheckExist = "SELECT * FROM tblClasses WHERE class='" & classRepl & "' AND class_description='" & classDescRepl & "' AND location='" & locationRepl & "' AND dateSubmit ='" & FormatDateTime(Request.Form("dateSubmit"),2) & "'"
		objRS.Open sqlCheckExist, objConn
		If objRS.EOF = False Then
			Response.Write "<font color=red>This record already exists</font>"
		Else
			objConn.Execute sqlInsert
			Response.Write "<font color=red>Record inserted</font>"
		End If
		objRS.Close
	ElseIf Request.Form("action") = "modify" and classRepl <> "" and classDescRepl <> "" and locationRepl <> "" and Request.Form("dateSubmit") <> "" Then
		sqlUpdate = "DELETE * FROM tblClasses WHERE ID=" & Request.Form("id")
		objConn.Execute sqlUpdate
		objConn.Execute sqlInsert
		Response.Write "<font color=red>Record Updated</font>"
	ElseIf Request.QueryString("action") = "delete" Then
		sqlDelete = "DELETE * FROM tblClasses WHERE ID = " & Request.QueryString("id")
		objConn.Execute sqlDelete
		Response.Write "<font color=red>Record Deleted</font>"
	End If

If Request.QueryString("action") = "modify" Then
	sqlModify = "SELECT * FROM tblClasses WHERE ID = " & Request.QueryString("id")
	objRS.Open sqlModify, objConn

	classVal = objRS("class")
	classDescrVal = objRS("class_description")
	locationVal = objRS("location")
	dateVal = objRS("dateSubmit")	
	objRS.Close
End If

%>
5

I cant figure it out…well email me and i’lll email you my code

6

You never closed your PHP VB code tag.

7

hi… thank for all yoru help … i managed to get it working !