Database Update Help

Dev Shed Forums Sponsor:

September 23rd, 2003, 03:14 AM

Moses912

Junior Member

Join Date: Sep 2003

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Database Update Help

Please Help!!!

I've been trying to figure this out, but no mater what I do it does not work. In the code below the update script does not work, I have a comment in Bold Letters where it's supposed to update. can anyone give me any suggestions

<html>
<head>
<%language="javascript" %>
<%
UserThreadName=Request.Form("UserThreadName")
UserThreadContent=Request.Form("UserThreadContent")

Session("ThreadName")=UserThreadName
Session("ThreadContent")=UserThreadContent
%>
<% language="VBscript" %>
<%'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddThread 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
Dim strSQL1 'Holds the SQL query to query the database
Dim strSQL2 'Holds the SQL query to query the database
Dim strSQL3 'Holds the SQL query to query the database

'If Session("LoggedIn")=0 Then
' Response.Redirect
'"http://localhost/site/ForumTopics.asp?ForumTopic='"&Session("FTopic")&"'"
'End If

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &

Server.MapPath("site.mdb")

'Create an ADO recordset object
Set rsAddThread = Server.CreateObject("ADODB.Recordset")
Set rsAddThread1 = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM tblForumThread;"

'Updating so that no 2 threads in the same forum the same user have the 'same name

strSQL1 = "SELECT * FROM tblForumThread WHERE ThreadName='"&Session("ThreadName")&"'

AND ForumName='"&Session("FTopic")&"';"

strSQL2 = "SELECT * FROM tblForumThread WHERE ForumName='"&Session("FTopic")&"';"

strSQL3 = "UPDATE tblForumThread SET ThreadNameURL='"&ThreadURL&"' WHERE

ThreadID="&Session("ThreadID")&""

rsAddThread.Open strSQL1, adoCon 'Check For Existing ThreadName

If rsAddThread.eof <> true then
rsAddThread.Close
Set rsAddThread = Nothing
Set adoCon = Nothing
Session("ThreadNameExists")="1"
Response.Redirect "http://localhost/site/AddNewThread.asp"
End If
rsAddThread.Close

Set rsAddThread = Server.CreateObject("ADODB.Recordset")
'Set the cursor type we are using so we can navigate through the recordset
rsAddThread.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddThread.LockType = 3
'Open the recordset with the SQL query
rsAddThread.Open strSQL, adoCon
'61
'Tell the recordset we are adding a new record to it
rsAddThread.AddNew
'Add a new record to the recordset
rsAddThread.Fields("UserID") = Session("UserID")
rsAddThread.Fields("ThreadName") = Request.Form("UserThreadName")
rsAddThread.Fields("ForumName") = Session("FTopic")
rsAddThread.Fields("DatePosted") = Date
rsAddThread.Fields("ThreadContent") = Request.Form("UserThreadContent")
'Write the updated recordset to the database
rsAddThread.Update
rsAddThread.Close

rsAddThread.Open strSQL1, adoCon
Session("ThreadID")=rsAddThread.Fields("ThreadID")
rsAddThread.Close

Response.Write Session("ThreadID")
ThreadURL="Prime"

'THIS IS WHAT DOES NOT WORK

rsAddThread.Open strSQL3, adoCon, 3, 3

rsAddThread.Open strSQL, adoCon

%>

<table border="1" width="100%" bgcolor="#fff5ee">
<tr>
<%for each x in rsAddThread.Fields
response.write("<th align='left' bgcolor='#b0c4de'>" & x.name & "</th>")
next%>
</tr>
<%do until rsAddThread.EOF%>
<tr>
<%for each x in rsAddThread.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rsAddThread.MoveNext%>
</tr>
<%loop
rsAddThread.close
adoCon.close
%>
</table>
</body>
</html>

Thanks

September 23rd, 2003, 02:41 PM

nopoints

Contributing User

Join Date: Jul 2003

Location: Windsor ON, Canada

Posts: 459

Time spent in forums: 13 h 44 m 22 sec
Reputation Power: 8

you do not open an update statement. you execute it with the connection object.

Code:

adoCon.Execute strSQL3

also choose better names for your variables than strSQL1, strSQL2 and strSQL3. lastly, the comment is not in bold

__________________
Programmer's Corner

September 24th, 2003, 09:37 AM

davegerard

Registered User

Join Date: Sep 2003

Posts: 15

Time spent in forums: 35 m 5 sec
Reputation Power: 0

Try this for starters.

rsAddThread.Open strSQL1, adoCon, 3, 3

Now let me look at the rest of the code.

September 24th, 2003, 10:24 AM

davegerard

Registered User

Join Date: Sep 2003

Posts: 15

Time spent in forums: 35 m 5 sec
Reputation Power: 0

Okay Moses91, it would help if you included the error that you're getting.

It looks as though you are trying to add a new record if it does not exist. But if it does exist? It looks like you are trying to open a recordset that is already open if it exists. I could have missed something and be wrong. Either way I think there is a way easier way to do this.

Are you trying to add a new record if it doesn't exist?

September 24th, 2003, 10:42 AM

davegerard

Registered User

Join Date: Sep 2003

Posts: 15

Time spent in forums: 35 m 5 sec
Reputation Power: 0

I wish they had a bigger box for this. A preview feature would be nice too.

Moses try something like this...

<%
db = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=whatever.mdb"

MyDate = Date()
UserThreadName = request("UserThreadName")
UserThreadContent = request("UserThreadContent")

set rs = server.createobject("adodb.recordset")
sql = "select * from tblForum where ThreadName = '" & Session("ThreadName") & "'"
rs.open sql, db, 3, 3
if rs.eof then
rs.addnew
rs.Fields("UserID") = Session("UserID")
rs.Fields("ThreadName") = UserThreadName
rs.Fields("ForumName") = Session("FTopic")
rs.Fields("DatePosted") = MyDate
rs.Fields("ThreadContent") = UserThreadContent
rs.update
end if
rs.close
set rs = nothing
%>

I'd also consider querying by TopicId or something numeric rather than the ForumTopic string. If the name of the topic happens to have an ampersand (&) or an apostrophe (") it could hose up your whole query.

September 24th, 2003, 11:00 AM

davegerard

Registered User

Join Date: Sep 2003

Posts: 15

Time spent in forums: 35 m 5 sec
Reputation Power: 0

Sorry I hosed something up. Replace the following

<%
rs.addnew
rs.Fields("UserID") = Session("UserID")
rs.Fields("ThreadName") = UserThreadName
rs.Fields("ForumName") = Session("FTopic")
rs.Fields("DatePosted") = MyDate
rs.Fields("ThreadContent") = UserThreadContent
rs.update
%>

with this

<%
rs.addnew
rs("UserID") = Session("UserID")
rs("ThreadName") = UserThreadName
rs("ForumName") = Session("FTopic")
rs("DatePosted") = MyDate
rs("ThreadContent") = UserThreadContent
rs.update
%>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: