Hey to all, anyone know why I am getting this error message.

INSERT INTO tblSearchResults (result, category, location, user) VALUES (( Keyword, CategoryTable, Location, MM_UserName) WHERE result = '%spotted gum%' AND category = '%Door Frames, Jambs & Sills%' AND location = '%Northern Territory%' AND user = '%malhyp%')


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

I think I have something the wrong way around...

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")

I don't understand that query at all

MM_Username ?? is that a variable or a column name ???

why have you so many wildcards ??

especially why is the username a wildcard match??
That is the one thing that should be an exact match

looking at it I am thinking that you need to do some serious work on normalising your structures

BUT !!!!! and most importantly !!!!


INSERT queries cannot have a WHERE clause for fairly obvious reasons

Chris is right. You've got two different queries rolled into one. You'll need to do one of them first (I'd say the search) and then do the insert query after.

Ok, it was not a good idea to post that code. It was something that I created using Dreamweaver's SQL creator.

Dose this look better? No wildcards, WHERE statement and MM_UserName is the username session variable that is created.


<%
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
%>

Adding this to the code, Response.Write "DEBUG SQL: " & SQL & "<HR>".
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

Any suggestions on how to fix/trouble shoot the error?

The SQL looks ok

does the Db table allow blank fields?

have you tested it with hardcoded data?

Heya there, I have checked the database, it dose allow blank fields. Im not sure what you mean by testing it with hard coded data.

I was just thinking that I probably had one of the properties in the SQL mixed up.

Are the following comments correct?

----------------------------------------------------------------------------------------------

conn.Open MM_connSeek_STRING (This opens the connection)

SQL = "INSERT INTO tblSearchResults (This inserts the result into the table)

result, category, location, user (This directs the data to the field)

VALUES Keyword CategoryTable Location (These are the names of my text fields in the previouse page)

MM_UserName (This is the username session variable name)

conn.Execute SQL (This executes the whole thing)

-----------------------------------------------------------------------------------------------

Mally

not really,

as you can see from the debug print, it is inserting all blank entries (or trying to)

hardcoding means putting actual values in there by setting the variables to some test values

Will give that a try.

Heya, I wasnt assigning the variables. As soon as I did it worked.

End resut.

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

Thanks again.

Mally