ASP update

Winters · 31-05-2002 6:03pm #1

I am having lots of problem with an ASP update form. all i need to do is update: Subject and User from tblPosts. they need to be updated with strSubject and strUsername. Can anyone give me the code as i have tried about 5 differant ones and it still doesnt verk :mad:

DJB · 31-05-2002 6:15pm

Once you have your connection string defined either DSN or DSN-less then you can use the following code:

set UpdatePosts = Server.CreateObject("ADODB.Command")
UpdatePosts.ActiveConnection = ConnectionString
UpdatePosts.CommandText = "UPDATE tblPosts SET subject = " & strSubject & ", username = " & strUsername & " WHERE id = 1"
UpdatePosts.CommandType = 1
UpdatePosts.CommandTimeout = 0
UpdatePosts.Prepared = true
UpdatePosts.Execute()
UpdatePosts.Close()

You have to change the id = 1 to id = whatever the record is you want to change. subject is the field called subject and the same for username being that field. ConnectionString is your dsn or dsn-less connection.

Try that... if it doesn't work throw up your code that you have got done already.

Regards,

Dave

Winters · 31-05-2002 7:49pm

Nope... Still doesnt verk! :mad: :mad: :mad:

Dim AddadoCon
Dim AddComments
Dim AddstrSQL

Set AddadoCon = Server.CreateObject("ADODB.Connection")
AddadoCon.Open "DRIVER=Microsoft Access Driver (*.mdb); DBQ=" & Server.MapPath("/bishlit/db/Boards.mdb")

Set AddComments = Server.CreateObject("ADODB.Recordset")

AddstrSQL = "Insert into tblPosts (Subject, Post) WHERE ThreadID = " & &" Values ('"& strSubject &"' , '"& strMessage &"')"

AddComments.Execute

AddComments.Close
Set AddComments = Nothing
Set AddadoCon = Nothing

Response.Redirect("../boards/view.asp?id="& strThreadID &"")

and the one you gave me:

Dim UpdatePosts

set UpdatePosts = Server.CreateObject("ADODB.Command")
UpdatePosts.ActiveConnection = "DRIVER=Microsoft Access Driver (*.mdb); DBQ=" & Server.MapPath("/bishlit/db/Boards.mdb")
UpdatePosts.CommandText = "UPDATE tblPosts SET subject = " & strSubject & ", username = " & strUsername & " WHERE id = "& Request.Form("ThreadID") &""
UpdatePosts.CommandType = 1
UpdatePosts.CommandTimeout = 0
UpdatePosts.Prepared = true
UpdatePosts.Execute()
UpdatePosts.Close()

If anyone can get em verking you will be my god

DJB · 31-05-2002 8:04pm

Try the code below but two things first. Have you defined the values strUsername and strSubject? Where are they coming from - form, session variable, cookie? What error message are you getting? If I know that I might be able to solve the problem quicker.

dbasepath = "/bishlit/db/Boards.mdb"
dbasepassword = "password"
dsnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.mappath(dbasepath)&";Jet OLEDB:Database Password=" & dbasepassword
cursortype = 3
Dim connectionstring
connectionstring = dsnstring

set UpdatePosts = Server.CreateObject("ADODB.Command")
UpdatePosts.ActiveConnection = connectionstring
UpdatePosts.CommandText = "UPDATE tblPosts SET subject = " & strSubject & ", username = " & strUsername & " WHERE id = " & Request.Form("ThreadID")
UpdatePosts.CommandType = 1
UpdatePosts.CommandTimeout = 0
UpdatePosts.Prepared = true
UpdatePosts.Execute()
UpdatePosts.Close()

Regards,

Dave

Winters · 31-05-2002 8:08pm

Still doesnt verk

Ill clear my Temp files and try again.. Heres the error i get:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'Test Message 6'.
/bishlit/scripts/editpost.asp, line 129

DJB · 31-05-2002 8:20pm

What is the name of the of the field that has the individual record id in it? It seems to not recognise what record to update!

Where are you getting the values from?

DJB · 31-05-2002 8:22pm

Are you trying to insert a new record or update an existing one?

Winters · 31-05-2002 8:25pm

I have a form with the following values:
PostNum, Subject, Message and Username.

At the start it checks the posts Username with the Username submitted it then goes on...

The PostNum is the ID from the tblBoards table. It (should) filler out the "Subject" and "Post" values and update it to the strSubject and strMessage ones.... but it doesnt :mad:

Ive been verking for 3hours on this one!

DJB · 31-05-2002 8:31pm

Is this an update of any existing post or the insert of a new one?

Why are you requesting "ThreadID" if it is PostNum?

What has tblBoards got in it?

I getting confused with some of the things you are saying.

If you want to mail me some of the files I will be able to debug them quicker. If you want to... you can send them to david@site-manager.com and I will look at them before I leave the office.

Regards

Winters · 31-05-2002 8:36pm

tblPosts has the following columes:

id = (Unique ID .. using for Filter)
ThreadID = (Not Changing)
Subject = (Updating to strSubject)
Post = (Updating to strMessage)
User = (Not Changing)
Date = (AutoDate)

I am looking to update the Subject with strSubject and Post with strMessage WERE id = Request.Form("PostNum")

Simple .... so simple is doesnt work!

DJB · 31-05-2002 8:51pm

Examing the code... lets try this...

dbasepath = "/bishlit/db/Boards.mdb"
dbasepassword = "password"
dsnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.mappath(dbasepath)&";Jet OLEDB:Database Password=" & dbasepassword
cursortype = 3
Dim connectionstring
connectionstring = dsnstring

Form_Post_ID = Request.Form("PostNum")
Form_Subject = Request.Form("strSubject")
Form_Post = Request.Form("strPost")

set UpdatePosts = Server.CreateObject("ADODB.Command")
UpdatePosts.ActiveConnection = connectionstring
UpdatePosts.CommandText = "UPDATE tblPosts SET Subject = " & Form_Subject & ", Post = " & Form_Post & " WHERE id = " & Form_Post_ID
UpdatePosts.CommandType = 1
UpdatePosts.CommandTimeout = 0
UpdatePosts.Prepared = true
UpdatePosts.Execute()
UpdatePosts.Close()

Try this and if doesn't work tell me the error and whats on the line the error occurred.

Regards

Winters · 31-05-2002 8:55pm

Ive added the editpost.asp file to this page. Heres the error i STILL get :mad:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'Test Thread'.
/bishlit/scripts/editpost.asp, line 133

'Test Thread' is the name of the strSubject

DJB · 31-05-2002 9:09pm

So simple... I couldn't see it. I should picked this up straight away!

Use this line for line 133

UpdatePosts.CommandText = "UPDATE tblPosts SET Subject = '" & Form_Subject & "', Post = '" & Form_Post & "' WHERE id = " & Form_Post_ID

Forgot to include the ' ' around the text string!!

If that doesn't work I'll go mad. That looks like the problem alright.

Regards,

Winters · 31-05-2002 9:13pm

You will not believe this:

Error Type:
Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'UpdatePosts.Close'
/bishlit/scripts/editpost.asp, line 134

:mad: :mad: :mad:

/me kicks his win2000 server and curses at Gates

DJB · 31-05-2002 9:16pm

Just delete that line

Winters · 31-05-2002 9:16pm

It does update the database though

Winters · 31-05-2002 9:18pm

Thank you..... you are my GOD!

the site will be viewable from http://www.bishpacking.da.ru tomorrow!

Its avelable online atm but 90% complete.. tell me what you think

(sorry..wrong URL there

)

DJB · 31-05-2002 9:24pm

Well good luck. I will look at my work on it tomorrow

For now I am off to the pub for a well deserved pint.

Cheeurs

Dave

DJB · 31-05-2002 9:26pm

Before I go... you have this error on your smelly.asp page

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Unrecognized database format '\\genfs2\www23\bishlit\db\Members.mdb'.

/bishlit/smelly.asp, line 31

And I am going to the pub...

Winters · 31-05-2002 9:42pm

I was uploading the database at the time... you scared the cr*p out of me though!