Syntax error in INSERT statement?

[gopikrish]

Whats wrong with the following statement because i am getting Run-time error saying "Syntax error in INSERT statement"

cn.Execute "Insert Into News (sno,subject,topic,user,date) values (" & count & ",'" & Trim(txtSubject.Text) & "','" & Trim(txtMessage.Text) & "','" & frmLogin.gstrUsername & "','" & strDate & "' )"

[Graff]

Depends. If your date field is a datetime type then you don't need the quotes around it. A little more info would be useful (ie. what are the values of the variable you are trying to enter).

[gopikrish]

Dim count As Integer
Dim strDate As String
strDate = Day(Date) & "/" & Month(Date) & "/" & Year(Date)
sql = "select * from News"
count = 1
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
count = count + 1
rs.MoveNext
Loop
cn.Execute "Insert Into News (sno,subject,topic,user,date) values (" & count & ",'" & Trim(txtSubject.Text) & "','" & Trim(txtMessage.Text) & "','" & frmLogin.gstrUsername & "','" & strDate & "' )"
rs.Close

Everything looks perfect and I dont know why that error is occuring

[Graff]

Can you post the structure of your News Table

[gopikrish]

Field Name            Data Type
--------------        -----------------
ID                    AutoNumber
 
sno                   Number
 
subject               Text
 
topic                 Text
 
user                  Text
 
date                  Text

[leinad31]

Date is usually a reserved word.

Is this an access database? Then try [Date] or rename the field

[gopikrish]

Amazing!!! I renamed it and still it says error. Actually that is not a problem because in another form I used "date" as a column name and there was no problem. Yes it is MS Access database.

[Graff]

You don't have a ' in any of your (subject,topic,user) variable values do you?

[gopikrish]

There is no ' (apostrophe)
Please help me, I am stuck with this and unless this is resolved I cant go with further coding

[Graff]

I'm grasping straws now but try removing the space between news and the ( and the same with values.

[szlamany]

Just a suggestion - build a complete string - with the INSERT statement...

.EXECUTE this string.

If this was MS SQL 2000, I would take a misbehaving INSERT and DEBUG.PRINT the STRING and cut/paste the INSERT from the IMMEDIATE window into QUERY ANALYZER - a tool for executing SQL queries on-line. Not sure if ACCESS has a tool like that - but maybe seeing the INSERT string as one piece might help you out...

[gopikrish]

Hey I removed (sno,subject,topic,user,date) from
Insert Into News(sno,subject,topic,user,date) values .........

And now there is no error. Strange why that caused an error but thanks to god

[szlamany]

It is probably because there are several reserved keywords in that list of fieldname that are not permitted...

If you change it to:

Insert Into News([[sno],[subject],[topic],[user],[date]) values

it would probably work - wrapping reserved keywords in [] brackets gets past the problem.

But you really should not use reserved keywords - they will burn you eventually.

Here is a list of reserved keywords from an old mainframe BASIC program we have:

200	DATA ABSOLUTE,ACTION,ADA,ADD,ADMIN,AFTER,AGGREGATE,ALIAS,ALL,ALLOCATE &
	,ALTER,AND,ANY,ARE,ARRAY,AS,ASC,ASSERTION,AT,AUTHORIZATION,AVG	&
	,BACKUP,BEFORE,BEGIN,BETWEEN,BINARY,BIT,BIT_LENGTH,BLOB,BOOLEAN	&
	,BOTH,BREADTH,BREAK,BROWSE,BULK,BY,CALL,CASCADE,CASCADED,CASE	&
	,CAST,CATALOG,CHAR,CHARACTER,CHARACTER_LENGTH,CHAR_LENGTH,CHECK	&
	,CHECKPOINT,CLASS,CLOB,CLOSE,CLUSTERED,COALESCE,COLLATE,COLLATION &
	,COLUMN,COMMIT,COMPLETION,COMPUTE,CONNECT,CONNECTION,CONSTRAINT	&
	,CONSTRAINTS,CONSTRUCTOR,CONTAINS,CONTAINSTABLE,CONTINUE,CONVERT &
	,CORRESPONDING,COUNT,CREATE,CROSS,CUBE,CURRENT,CURRENT_DATE,CURRENT_PATH &
	,CURRENT_ROLE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR &
	,CYCLE,DATA,DATABASE,DATE,DAY,DBCC,DEALLOCATE,DEC,DECIMAL,DECLARE &
	,DEFAULT,DEFERRABLE,DEFERRED,DELETE,DENY,DEPTH,DEREF,DESC,DESCRIBE &
	,DESCRIPTOR,DESTROY,DESTRUCTOR,DETERMINISTIC,DIAGNOSTICS,DICTIONARY &
	,DISCONNECT,DISK,DISTINCT,DISTRIBUTED,DOMAIN,DOUBLE,DROP,DUMMY,DUMP &
	,DYNAMIC,EACH,ELSE,END,END-EXEC,EQUALS,ERRLVL,ESCAPE,EVERY,EXCEPT &
	,EXCEPTION,EXEC,EXECUTE,EXISTS,EXIT,EXTERNAL,EXTRACT,FALSE,FETCH &
	,FILE,FILLFACTOR,FIRST,FLOAT,FOR,FOREIGN,FORTRAN,FOUND,FREE,FREETEXT &
	,FREETEXTTABLE,FROM,FULL,FUNCTION,GENERAL,GET,GLOBAL,GO,GOTO,GRANT &
	,GROUP,GROUPING,HAVING,HOLDLOCK,HOST,HOUR,IDENTITY,IDENTITYCOL	&
	,IDENTITY_INSERT,IF,IGNORE,IMMEDIATE,IN,INCLUDE,INDEX,INDICATOR	&
	,INITIALIZE,INITIALLY,INNER,INOUT,INPUT,INSENSITIVE,INSERT,INT	&
	,INTEGER,INTERSECT,INTERVAL,INTO,IS,ISOLATION,ITERATE,JOIN,KEY	&
	,KILL,LANGUAGE,LARGE,LAST,LATERAL,LEADING,LEFT,LESS,LEVEL,LIKE	&
	,LIMIT,LINENO,LOAD,LOCAL,LOCALTIME,LOCALTIMESTAMP,LOCATOR,LOWER	&
	,MAP,MATCH,MAX,MIN,MINUTE,MODIFIES,MODIFY,MODULE,MONTH,NAMES,NATIONAL &
	,NATURAL,NCHAR,NCLOB,NEW,NEXT,NO,NOCHECK,NONCLUSTERED,NONE,NOT,NULL &
	,NULLIF,NUMERIC,OBJECT,OCTET_LENGTH,OF,OFF,OFFSETS,OLD,ON,ONLY,OPEN &
	,OPENDATASOURCE,OPENQUERY,OPENROWSET,OPENXML,OPERATION,OPTION,OR &
	,ORDER,ORDINALITY,OUT,OUTER,OUTPUT,OVER,OVERLAPS,PAD,PARAMETER	&
	,PARAMETERS,PARTIAL,PASCAL,PATH,PERCENT,PLAN,POSITION,POSTFIX	&
	,PRECISION,PREFIX,PREORDER,PREPARE,PRESERVE,PRIMARY,PRINT,PRIOR	&
	,PRIVILEGES,PROC,PROCEDURE,PUBLIC,RAISERROR,READ,READS,READTEXT	&
	,REAL,RECONFIGURE,RECURSIVE,REF,REFERENCES,REFERENCING,RELATIVE	&
	,REPLICATION,RESTORE,RESTRICT,RESULT,RETURN,RETURNS,REVOKE,RIGHT &
	,ROLE,ROLLBACK,ROLLUP,ROUTINE,ROW,ROWCOUNT,ROWGUIDCOL,ROWS,RULE	&
	,SAVE,SAVEPOINT,SCHEMA,SCOPE,SCROLL,SEARCH,SECOND,SECTION,SELECT &
	,SEQUENCE,SESSION,SESSION_USER,SET,SETS,SETUSER,SHUTDOWN,SIZE 	&
	,SMALLINT,SOME,SPACE,SPECIFIC,SPECIFICTYPE,SQL,SQLCA,SQLCODE	&
	,SQLERROR,SQLEXCEPTION,SQLSTATE,SQLWARNING,START,STATE,STATEMENT &
	,STATIC,STATISTICS,STRUCTURE,SUBSTRING,SUM,SYSTEM_USER,TABLE	&
	,TEMPORARY,TERMINATE,TEXTSIZE,THAN,THEN,TIME,TIMESTAMP,TIMEZONE_HOUR &
	,TIMEZONE_MINUTE,TO,TOP,TRAILING,TRAN,TRANSACTION,TRANSLATE	&
	,TRANSLATION,TREAT,TRIGGER,TRIM,TRUE,TRUNCATE,TSEQUAL,UNDER	&
	,UNION,UNIQUE,UNKNOWN,UNNEST,UPDATE,UPDATETEXT,UPPER,USAGE	&
	,USE,USER,USING,VALUE,VALUES,VARCHAR,VARIABLE,VARYING,VIEW	&
	,WAITFOR,WHEN,WHENEVER,WHERE,WHILE,WITH,WITHOUT,WORK,WRITE	&
	,WRITETEXT,YEAR,ZONE