How do I create an update form, in a search.asp page.
I have a search.asp page which when submitted transfers to the results.asp page. In the search I would like to also create an insert statement. The search.asp page has the following.

Form: searchForm (Post)
Text Field: KeywordSearch
List Menu: CategoryTable
List Menu: Location
Button: Search

When a client enters data in the search.asp page, I would like for the data, to be submitted to a database at the same time it searches the database. This using an insert statement. The reason for this is so that we can see what everyone is searching for.

The database/table we created has the following.

Table: tblSearches
Field: idSearch (Auto Number)
Field: location (Text)
Field: category (Text)
Field: user (Text)
Field: keyWord (Text)

At the moment I cant create an insert form because of the form that already exists in the search.asp page.

Can anyone suggest what to do.

Ideally it would be good to be able to combine two forms into one, but I am told that this is not possible.

TA

Mally.

Right, so you want to do this with a Dreamweaver behavior?

I think it's much easier to fire your own insert statement, and drop the behavior (for inserting that is; for searching you can still use the existing behavior).

Just look at the code that Dreamweaver generates and copy the relevant parts of the connection code. Then build up a SQL statement using something like Request.Form("KeywordSearch") and other fields, and manually send the SQL statement to the database by using the connection's Execute method.

For more details: http://msdn.microsoft.com/library/de...cnnexecute.asp

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Imar, I want to confirm that I do this all in the results.asp page and not the seasrech.asp page.

Thanks for your reply.

Mally.

Is that a question or a statement? If it's a question: yes that would work fine. The search page submits all the details to the results page which then inserts the search term in the database and displays the results.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

That was the questions. Thanks again Imar.

Mally.

Can you see something wrong with this insert statement?

<%
        if(Request.Form("Keyword") <> "") then Command1__MMColParam = Request.Form("Keyword")

        if(Request.Form("CategoryTable") <> "") then Command1__MMColParam2 = Request.Form("CategoryTable")

        if(Request.Form("Location") <> "") then Command1__MMColParam3 = Request.Form("Location")

        if(Session("MM_UserName") <> "") then Command1__MMColParam4 = Session("MM_UserName")
%>

        <%
        set Command1 = Server.CreateObject("ADODB.Command")
        Command1.ActiveConnection = MM_connSeek_STRING
        Command1.CommandText = "INSERT INTO tblSearchResults (result, category, location, user) VALUES (( Keyword, CategoryTable, Location, MM_UserName) WHERE result = '%" + Replace(Command1__MMColParam, "'", "''") + "%' AND category = '%" + Replace(Command1__MMColParam2, "'", "''") + "%' AND location = '%" + Replace(Command1__MMColParam3, "'", "''") + "%' AND user = '%" + Replace(Command1__MMColParam4, "'", "''") + "%') "
        Command1.CommandType = 1
        Command1.CommandTimeout = 0
        Command1.Prepared = true
        Command1.Execute() <--- Line 48
        %>

Component details are...

search.asp page
Form: searchForm (POST)
Text Field: Keyword
Text Field: CategoryTable
Text Field: Location
Session: MM_Username

database properties
Table: tblSearchResults
Number: idSearch (AutoNumber)
Text: result (Which will be inserted by the "Keyword" text field)
Text: category (Which will be inserted by the "CategoryTable" text field)
Text: location (Which will be inserted by the "Location" text field)
Text: user (Which will be inserted by the Session "MM_Username")

Keeps giving me this...

INSERT INTO tblSearchResults (result, category, location, user) VALUES (( Keyword, CategoryTable, Location, MM_UserName) WHERE result = '%spotted gum%' AND category = '%%%' AND location = '%%%' AND user = '%malhyp%')

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/html/results.asp, line 48

Hi there,

If I were you, I'd base your custom INSERT statement on another INSERT statement that Dreamweaver has generated, and not on an SELECT statement. That way, you can see how the syntax for INSERT works and it'll be easier to modify it to make it a valid statement.

Right now, you list column names while you should be listing the values in the VALUES section and you have a WHERE clause that doesn't make sense.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Ok, so id say that something like this would be better.

<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSearchResults (result, category, location, user) " _
& " VALUES('" & Keyword & "', '" & CategoryTable & "', '" & Location & "', '" & MM_UserName & "')"
conn.Execute SQL
%>

But the values I have are the text field and drop down names from the page that sends the information being the search.asp page. Is this correct? The username I have as the session variable...

Mally

Adding this to the code, Response.Write "DEBUG SQL: " & SQL & "".

Gives me a result of this.

DEBUG SQL: INSERT INTO tblSearchResults (result, category, location, user) VALUES('', '', '', '')
--------------------------------------------------------------------------------

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/html/results.asp, line 34

So, the values are empty. Before you execute the SQL statement, make sure that the variables Keyword and CategoryTable etc have a valid value...

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Hmmm, I changed the code to read this... Less the MM_UserName.

<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSearchResults (result, category, location) " _
& " VALUES('" & Keyword & "', '" & CategoryTable & "', '" & Location & "', ')"
conn.Execute SQL
%>

And there was no error message. What it did do was enter a new blank line in the database. So I did 4 searches on the database and it entered 4 blank lines with no values....

?