Sintax error when adding a record

[Maranza]

Hello, i've got a problem with adding records to a database.

Here's the code for adding a record:

set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=c:\documenti\forum.mdb"
conn.Open
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Threads", conn, , adLockOptimistic, adCmdTable
'You must imagine a form first
rs.addnew
rs("title")=request("strtitle")
rs("from")=request.cookies("forum")("user")
rs("text")=request("strtext")
rs.update
when the script calls rs.update, it's a sintax error for INSERT INTO

why? It's because i'm adding wrong type of data in the db fields? The title is text, the from is text and the title is memo (long text)
Or is because the table has pre-defined fields? ID is a counter and time and date have a default value of time() and date()

Where's the mistake?

[Shafee]

Isn't it Request.Form("Field") ?

[Maranza]

if you request something without specyfing the collection, asp automatically search for a querystring, if not present, it searchs for a form, then for a cookie and so on... that's not the mistake
Please help!

[Shafee]

What is the error you are getting?

[monte96]

My money is on the datatypes. Use conversion functions to convert to the proper datatype.

[monte96]

By the way, the best way to insert a record is to use an Insert statement instead of .Addnew. It is less prone to errors and forgotten update method calls.

[Maranza]

i can't use insert into because i don't know sql that good. How do i convert date() or time() in a real date or time?

[Maranza]

is this sql statement correct?
where login is the cookie ("user") and the others come from a form
--------
sqladd="INSERT INTO Threads (title,from,text) VALUES ('" & request("strtitle") & "', '" & login & "', '" & request("strtext") & "')"

[tonyenkiducx]

Looks right to me dude

[Maranza]

same error
is this because it's an asp 3.0 feature? I'm using PWS and it doesn't support asp3.0
anyway, i'll attach the form and the asp for you to check

[tonyenkiducx]

Ok, this will work, and its how I would do it.

It may not be the fastest, but it will work.

Rather than using objects to build up a command all the time, you should look more at the conn object, and use it to execute sql code, rather than using all the SQL in-builts, there a little flaky, and not the easiest to use.

Also consider sticking your connection string in an include file, then you can use the same "conn" object on every page.



Tony@Work.

[Maranza]

what's chr(13)?

[tonyenkiducx]

A carriage return, when you print code from strings to the browser, there are no carriage returns. So while the front end looks good, the html code is all on one line. So stick in carriage returns to make your HTML nice and readable.

Does the code for the insert work?

[monte96]

Post the error message verbatim.

You may still need to convert the form data to the appropriate data types. Using an insert statement will not change that.

[Maranza]

it's italian, hope you understand it
---------
Microsoft OLE DB Provider for ODBC Drivers errore "80040e14'

[Microsoft][Driver ODBC Microsoft Access] Errore di sintassi nell'istruzione INSERT INTO.

/forum/post.asp, riga 17
---------
errore di sintassi means syntax error

how do i convert all the values?
from and user are text and 'text' is memo (long text)

[absalom]

It looks to me like you do not have an SQL interpreter and thats why the error is comming up maybe try and install SQL and it will work. Correct if I am wrong that does say syntax error in INSERT INTO right, italian is not my strongest language. And here is converting code:

To convert the date and time to something the database can interperet is the cdate and ctime functions this is how it looks

rs("timeorwhatever") = ctime(Time)
rs("dateplace") = cdate(now)

and the database can interperet that and you should have no problem with that part

[Maranza]

an sql interpreter?
isn't installed on PWS? Where i can get one?

[absalom]

I bought version 7 but you can download 2000 from microsoft and can then get the crack on thecrack.net

[Maranza]

you mean sql server! But it's used to create stored procedures, isn't it?
With sql server can i run dinamic stored procedures?
E.g. "SELECT * FROM tab WHERE price > " & request.form("iprice")
Can i run it under win98?

[monte96]

SQL Server is more than 'just' Stored Procedures.

It holds the data itself as well as provides security for the data.

As far as stored procedures go, your example is not a stored procedure, it is a SQL statement. A Stored Procedure is a group of SQL statements that is precompiled on the server and thus runs much faster. To do 'dynamic' SQL statements within a S.P., you pass in parameters that are used in the SQL statement(s) Where conditions.