Uploading ASP pages written in Dreamweaver MX

I have built a site in DMX with a login page and a register page. I have tested it all locally and it works fine but when I publish it, the register page does not work. The log in page is fine and interacts with the Access Database as it should do but the register page results in the error message below when submitted.

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/register.asp, line 141


I think the problem is that when I try to change my DSN to a DSN free connection, the register form loses the connection with Access table and therefore cannot reach it to add information. I have just started with ASP and am not very technical so I have had problems sorting this. If anyone can look at the attached code and tell me where the problem is I would be very grateful.

 

if it wasn't connecting to the database then it wouldn't give you a SQL error like that. It would tell you the database could not be found or it couldn't create the connection.

something is funny with your INSERT statement.

MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

the table, field names and values being passed to the query are concatenated in a string. Man, DMX makes HORRIBLE code.. blech, and I thought Front Page was bad.


right after the insert statement (show above) and before this statement

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If


put these two lines

response.write MM_EditQuery
response.end

then try the form out. Note that the insert statement will NOT execute. What this will do is show you what is actually passed to the entire query. Copy and paste what it prints out to the screen . This will help us determine what the problem may be.

 

Thanks for the reply

Thanks for getting back to me,

I added the code as you suggested and I got the following error:

insert into tbLogin (Firstname,Surname,UserID,Password) values ('sdfds','sdfds','sgfg','hh')

Any ideas? -

Cheers

 

ok, is the userid field a text data type or a number data type. ?

 

UserID

The userID field is a text type field.

Jack

 

I stared at this for a while. I couldn't see anything wrong with the insert statement. The one thing I could think of was maybe this line was causing a problem

MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"

But I don't know why it would. It's just a guess. This line basically says make ' into this '' so it can escape the single quotes. If you don' t have, then text like " blah's " would cause an error.

The only other thing I could think of was that DMX was assembling the query code wrong. If it is, I can't find where.