Can I say that this is the correct way to increase the value of a cell by 1, everytime the statement is exected?

<%
User = Replace( Trim("" & Request.QueryString("SupplierName")), "'", "''" )
%>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSuppiers ([Hits]) " _ & " VALUES('" & 1 & "')"
conn.Execute SQL
%>

Hmm, Id say its not. Just got this...

Microsoft JET Database Engineerror '80040e37'

Could not find output table 'tblSuppiers'. /html/product.asp, line 76

UPDATE table SET column = column + 1 WHERE criteria

The WHERE clause im not 100% sure about.

I did this...

UPDATE tblSuppliers
SET Hits = Hits + 1
WHERE SupplierName = SupplierName

Which updates all the suppliers by 1.

"UPDATE tblSuppliers SET Hits = Hits + 1 WHERE ?

The page transfers from one page to the next a URL Parameter "SupplierName".

Not 100% sure on what I should have there...

the criteria should isolate the one row to update

so SupplierName = request.querystring("SupplierName")

although you should do some checks rather than use the querystring directly

UPDATE tblSuppliers
SET Hits = Hits + 1
WHERE SupplierName = request.querystring("SupplierName")

This look oky?

givin me this...




Microsoft VBScript compilation error '800a0401'
Expected end of statement
/html/productDescription.asp, line 67 Command1.CommandText = "UPDATE tblSuppliers SET Hits = Hits + 1 WHERE SupplierName = request.querystring("SupplierName")"---------------------------------------------------------------------------------------------------------------------------^

you should add in the contatenation

Sorry Chris, what is a contatenation?

might have helped if I'd have typed it correctly

concatenation

WHERE SupplierName = " & request.querystring("SupplierName")

I don't always type the exact code, sometimes you have to think. It's all part of the learning process.

Thanks Chris, it worked. I originally had it all included in the code that Dreamweaver creates. As soon as I got rid of all the extras, it worked.

Thanks Heaps.

Also just had a look at your pic. The reseblance is amaizing. Lolz.

Cheers.

Mally.

malhyp: having seen a few of your posts, I think I can give you a piece of advice that will save you a bunch of time.

Don't use DW-created (or for that matter, any other WYSIWYG-generated) code. It's not optimized and can quite often be very buggy, as you're learning. The best thing you can do is to get a colour-coded text editor (Notepad++ for example) that will allow you to hand-code your ASP and still see the different aspects of string concatenation, SQL Query manipulation, etc.

Also, when you get an error code such as 800a0401 , you can often Google it and find your answer that way.

Yeh, thanks for that. I think I learnt that the hard way. It is Dreamwever that confuses me.

Good. Now that you've learned it, get your *** to steppin' and finding a good text editor.

I dont quite understand this.

I have 3 pages.

1) search.asp which is set to POST to the
2) results.asp page. From there if you want to view more detils on a particular subject in the results page, you click on the DETAIL link which is set as a Go To Detail Page function. This directs you to the.
3) productdetail.asp page, which shows all the suppliers details.

When I insert the code below in the 3) productdetail.asp page, it updates the database by 1 each time the result is viewed.

If I insert the code in the 2) results.asp page, it dose nothing?

<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "UPDATE tblSuppliers SET Hits=Hits+1 WHERE SupplierName='" & Replace(Request.QueryString("SupplierName"), "'", "''") & "'"
conn.Execute SQL
%>

To confirm:

search.asp has a form with text fields, set to POST. When submitted this goes to the results.asp page.

results.asp has the following SQL which pulls all the details together.

SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC

In the same page it has a Go To Detail Link which looks something like this...

<a href="productdetail.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "SupplierName=" & rsSearchResult.Fields.Item("SupplierName").Value %>">More Details</a>

Any ideas on where I have gone wrong?

Mally

does Query1 have columns with data such as

MMColParam, MMColParam2, or MMColParam3 ?????

also when you use AND on each criteria they ALL have to equate to true.

malhyp: the code in question can only work in case 3. The point of that code is to let you know the number of times a supplier has been viewed.

Now...what happens is this.

The Go To Detail Link contains a querystring key/value pair (in this case SupplierName = the name corresponding to the record from your database). Anything you see in a URL of the form ?something=value or &somethingelse=anothervalue is a querystring key/value pair.

So...from this, we know that the SupplierName key/value pair is passed via that hyperlink (when you click on it) to the productdetail.asp page. This is where that code should go, since it updates the query based on...you guessed it, the SupplierName querystring key/value pair.

It also is probably retrieving the supplier info based on the supplier details.

Mind you, this isn't the best way to go about doing it. Longer supplier names and supplier names with spaces can cause issues, as well as suppliers with the same name but duplicate addresses (warehouses, for example).

What I always use for these situations is an AutoNumber field (I'm assuming you use Access). AutoNumber fields are fields that are created specifically as incremental counters for a series of database records. Each AutoNumber value is generated by the system, cannot be edited (to avoid any screwups) and will be used once and only once.

So...for example, if I have 3 records in a database, I might have something like this (sorry about the weird formatting...apparently I can't do a table within the board):

AutoNumber Field Other fields in table

1 Other Fields for Record 1
2 Other Fields for Record 2
3 Other Fields for Record 3

Now, let's say I deleted the second record and added a fourth. My new table would look something like this:

AutoNumber Field Other fields in table

1 Other Fields for Record 1
3 Other Fields for Record 3
4 Other Fields for Record 4


As you can see, it did not reuse the 2 value, because it was already used once (even though it was deleted).

Why is this significant?

Because if you use something to the extent of "autonumber=" & RS ("AutoNumberField") as part of your querystring, you will be guaranteed to get no more than one possible record for each hyperlink clicked on.

Hope that makes some sense. If not, mess with it. (BACK UP WHAT YOU'RE DOING FIRST. VERY KEY.)

Query1 dose have....

<%
Dim rsSearchResult__MMColParam
rsSearchResult__MMColParam = "1"
If (Request("Keyword") <> "") Then
rsSearchResult__MMColParam = Request("Keyword")
End If
%>
<%
Dim rsSearchResult__MMColParam2
rsSearchResult__MMColParam2 = "2"
If (Request("CategoryTable") <> "") Then
rsSearchResult__MMColParam2 = Request("CategoryTable")
End If
%>
<%
Dim rsSearchResult__MMColParam3
rsSearchResult__MMColParam3 = "3"
If (Request("Location") <> "") Then
rsSearchResult__MMColParam3 = Request("Location")
End If
%>

WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'

Mally, since when has "MMColParam" been a timber species ??


if your table has values such as "MMColParam" in it you have got something badly wrong somewhere.

HINT: Look back to post #10

As Adam posted, A lot of these issues can be attributed back to use of DW generated gibberish. It is not helping you understand how to build queries from form input.

yeh, I know. The site is riddled with DW statements. Anything that I create new is being written manualy. But the rest is all Dutch to me.

Anyhows back to post # 10.

Mally.