recordset help!

[wealoon]

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

[DDD]

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.

[DDD]

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") <> "" 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

%>

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???

[DDD]

<%
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

%>

[DDD]

I cant figure it out....well email me and i'lll email you my code

[lostinbeta]

You never closed your PHP VB code tag.

[wealoon]

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