ASP - Updating Database Field 3

[SamHale]

Hi, thanks for any help in advanced.

I'm trying to create a simple (or atleast what seems simple) form using ASP VB.

It will appear at the bottom of an article, and act much like a rating system.

There we go two option, a Good checkbox, and a Bad checkbox.
(this can change to just two submit buttons if it makes what I want to
achieve easier)

So, I have a database linking to the page with a Table called Rating, and three columns, ID (to link with article), Good and Bad.

So when someone uses the form below the article, say they submit 'Good', I wish for a 1 to appear in the database under good. Then someone else submits 'Good', that 1 increases to 2, then someone else submits 'Good', that 2 increases to 3, etc. and vice versa for the Bad rating.

Can anyone help me?

Many thanks,
Sam.

 

[hithere]

I'm not sure how detailed I need to be for you (what your experience is) so I hope this covers it. I program in VBscript which is similar to VB so this will be similar to what you need but may not be exact.

Create a call to the database to establish what the value of "Good" and "Bad" are. i.e.
Select * from tbldata

Assign the values to variants.
good = rs(1)
bad = rs(2)

get the value from the form.
formgood = request("good")
formbad = request("bad")

test for value being non-zero and increment.
if formgood = 1 then
good = good +1
else
bad = bad +1
end if

then update your database with the new value.
update tbldata set tblgood = " & good

this is pretty basic but should get the job done for ya'
mb

"Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian

 

[steven290]

this might be a little easier

create 2 checkboxes

<input type="radio" name="rating" value="good">
<input type="radio" name="rating" value="bad">

then do the actual asp

if request("rating")="good" then
sql="update tblname set goodrating=goodrating + 1 where somefield='somevalue'"
else
sql="update tblname set badrating=badrating + 1 where somefield='somevalue'"
end if

execute your query here

 

[ChrisHirst]

The code above has a logic error that would cause a problem should the form be submitted with no radio button selected.
(Yes of course I realise that nobody will ever do that!)
But should it happen the article will always be given a bad rating.

it could be recoded as

if request.form("rating") <> "" then
    if lcase(request.form("rating")) = "good" then 
       ' do the process for good here
    elseif lcase(request.form("rating")) = "bad" then 
       ' do the process for bad here
    end if
end if

or just for the sake of efficiency

if request.form("rating") <> "" then
select case lcase(request.form("rating"))
    case "good"
       ' do the process for good here
    case "bad"
       ' do the process for bad here
    case else
       ' do something else here
end select
end if

the second example being easier to update should more options need adding later.


Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[SamHale]

Good morning,

Thanks for all your help.

Chris, with regards to the second coding you posted.

if request.form("rating") <> "" then
select case lcase(request.form("rating"))
case "good"
sql="update rating set good=good + 1 where somefield='somevalue'"
case "bad"
sql="update rating set bad=bad + 1 where somefield='somevalue'"
end select
end if

Is this correct? Sorry, I'm only just starting out with ASP, and knowing my luck probably got it wrong.

Thanks again for everyones help,
Sam.

 

[ChrisHirst]

That should be ok if all you need to do is set the SQL string. However you should have a case else fallback to set the SQL string into a state that can be checked before running the command.

or have a function to run the query that can be called in the select case construct.
something like;

function RunSQL(strSQLQuery)
'used for insert or update. does not return a recordset
dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strSQLConnString 
objConn.Execute strSQLQuery 
objConn.close
set objConn = Nothing
end function

then it would be

    case "good"
       sql="update rating set good=good + 1 where somefield='somevalue'"
        RunSQL(sql)
' rest of case statements

where strSQLConnString is defined elsewhere as a global constant.

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[SamHale]

Ok, I'll have a try when I get home from work.

This may be really stupid, but what is - somefield='somevalue' ...

Really apprechiate your help.

Sam.

 

[ChrisHirst]

it's what you need to identify the record(s) to be updated. Could be the ID number in a database.

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[SamHale]

Hey,

Thanks again for the reply.

I've given it a go at home but with no success.
The form is working, as I was getting a code error, but managed to fix this, but the data isn't getting entered into the database.

Below is my code:

<%
function RunSQL(strSQLQuery)
'used for insert or update. does not return a recordset
dim objConn, Connect
Connect="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../db/userman.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strSQLConnString 
objConn.Execute strSQLQuery 
objConn.close
set objConn = Nothing
end function
%>
<%
if request.form("rating") <> "" then
select case lcase(request.form("rating"))
    case "good"
               sql="update rating set good=good + 1 where ID=1"
   RunSQL(sql)
    case "bad"
               sql="update rating set bad=bad + 1 where ID=1"
         RunSQL(sql)
end select
end if
%>

<html><head> etc. etc.

<form ACTION="thanks.asp" METHOD="POST" name="rating">
good
<input type="radio" name="rating" value="good"> 
bad
<input type="radio" name="rating" value="bad">
<input type="submit" name="Submit" value="Submit">
</form>

</body> etc. etc.

What am I doing incorrect?

I currently have 1 row of data in my database:

- ID - good - bad -
- 1 - 0 - 0 -

The table for my database is called 'rating'.

Thanks for any help.

Many thanks,
Sam.

 

[SamHale]

Bump

Thanks.

 

[ChrisHirst]

this

objConn.Open strSQLConnString

should be changed to this;

objConn.Open Connect

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[SamHale]

Hey,

Thanks Chris, Still no luck..
The form is still submitted but nothing is being entered into the database.

Could it be anything to do with the thanks.asp page?

Have you got any other ideas/sugguestions?

Thanks for all your help,
Sam.

 

[steven290]

thats the problem your code is on the wrong page

 

[steven290]

place this in your thanks.asp

<%
function RunSQL(strSQLQuery)
'used for insert or update. does not return a recordset
dim objConn, Connect
Connect="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../db/userman.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strSQLConnString
objConn.Execute strSQLQuery
objConn.close
set objConn = Nothing
end function
%>
<%
if request.form("rating") <> "" then
select case lcase(request.form("rating"))
case "good"
sql="update rating set good=good + 1 where ID=1"
RunSQL(sql)
response.write("thank you for the good rating")
case "bad"
sql="update rating set bad=bad + 1 where ID=1"
RunSQL(sql)
response.write("thank you for the bad rating")
end select
end if
%>

 

[steven290]

place the form in a another page called rate.asp (or something)

 

[SamHale]

Thanks you two,
I'm just about to post my next problem *sigh*
So if you have time, please take a look.

Stars for you both.

 

[SamHale]

Hi again,

Ok, so now i've made it so each user has their own id which is added to the end of the profile.asp (profile.asp?ID=1) - I now need to incorporate this with the above rating system. So instead of it using Id=1, it needs to use the ID # associated with the username being viewed.

sql="update Users set good=good+ 1 WHERE username = '" & user & "' AND ID = '" & ID & "'"

The form is submitted fine, but now a 1 isn't appearing for any user.

I'm guessing I need something similar to:

<%
Dim user
user		=	LCASE(Request.Form("username"))
%>

But can't work out what this is, and the ID field isn't in the previous form.

Can anybody help?

Many thanks,
Sam.

 

[steven290]

show previous form:

i don't think you need to check against the user since the id's are unique.

set it like this

sql="update Users set good=good+ 1 WHERE ID = " & request("id")

also don't use "'" around numeric fields

 

[steven290]

also this is called a querystring (after the question mark)

profile.asp?id=1

you get the number using request.querystring("id")

 

[SamHale]

Hi Steven,

Thanks for trying to help again.

Unfortunately this is not working, below is the code i've got :

<%
Dim ID
ID			=	request.querystring("ID")
%>

(run sql etc. here)

<%
if request.form("rating") <> "" then
select case lcase(request.form("rating"))
    case "good"
               sql="update Users set good=good + 1 WHERE ID = " & request("ID")
   RunSQL(sql)
    case "bad"
              sql="update Users set bad=bad + 1 WHERE ID = " & request("id")
         RunSQL(sql)
end select
end if
%>

Any idea why this isn't working? All names (forms, fields, etc.) are correct.

Many thanks,
Sam.