Syntax error in INSERT statement?

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

VB Code:

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

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

VB Code:

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 :(

Can you post the structure of your News Table

VB Code:

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

Date is usually a reserved word.

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

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.

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

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

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

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

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

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

If you change it to:

Code:

---

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:

Code:

---

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

---