Trouble Inserting Strings Into A Table

[Jason Teagle]

I have a simple test table (called test_table) which has two fields: Number and String are the field names.

If I try to use the following SQL command:

INSERT INTO test_table (Number, String) VALUES(13, 'Goodbye')

it fails; I am told that the INSERT INTO syntax is invalid.

If, however, I have a table (simple_table) which has one field: Number and use this SQL command:

INSERT INTO simple_table (Number) VALUES(75)

it works. I have to conclude, therefore, that there is some problem inserting a string, even a short one which is way under the limit for the field (the limit is the default of 50 characters).

I tried (") instead of (') for the string, no change.

I am using an Access (.mdb) database, and using VBScript (although what I am using to do it should be irrelevant!)

Can anyone give me a hint as to what I am doing wrong?

Thanks in advance.

[Kyle Burns]

A good thing to try when you are debugging SQL is to paste the statement that you think may be a problem into the SQL view of a new query. The DBMS will often be a little better environment for debugging your SQL.

Number is a datatype in Access I don't know if that is the problem but I bet if you change your column name to number1 or something it will work.

[Jason Teagle]

I'm sorry, you misunderstood. If I have a numeric field called Number and no other fields, it DOES work - only if I have a second field of string type does it go wrong.

I set up a database with one table called test_table and it had 2 fields number and string.
when I ran your sql statement it failed as you said but if I changed the name of the field from number to number1 it worked
If I set up a simpl_table with just number as a field I still got the error.
Try changing your field name.

[Jason Teagle]

OK, I stand corrected. I had originally used a table with just a numeric field called NextPostID, which had worked; when I tried the fields Number and String, I had assumed that it failed because of the string; however, on renaming the fields, you are correct, it works.

So my problem becomes this: why doesn't the following SQL command work:

INSERT INTO message_board
(PostID, ParentPostID, RootPostID, Subject, Category, Replies, Author, Timestamp, MessageBody)
VALUES(53, 0, 0, 'ddddd', 'Miscellaneous', 0, 'Mr Nobody', '16/02/00 9:31:31 PM', 'ddddd')

The database DOES contain those fields, no more, no less. I can happily make SELECT queries on that database, so the fields must be valid.

The data types shown above ARE correctly matched to the database fields (numbers and strings).

Is there a limit on the length of an SQL command string? That's the only thing I can think of that might cause it to fail.

[Kyle Burns]

Assuming you're using MS Access and TimeStamp is a Date datatype (I know that's assuming a lot),
'16/02/00 9:31:31 PM' Should Be #16/02/00 9:31:31 PM#

[Johnny101]

This is just a shot in the dark, but does the PostID field happen to be an autonumber field in the access db? If it is, then you dont' reference it in the query and (obviously) you dont give it a value as well.

Stretching,
John


John Pirkey
MCSD
www.ShallowWaterSystems.com

[Jason Teagle]

Please disregard any further posts from me - I had intended the date and time to be a string, but I had not noticed that Access had taken it upon itself to make it a date / time format.

Thankyou for your help.

[Jason Teagle]

Sorry, PostID is not. However, I have now found the cause of the problem thanks to another poster - I had intended the date and time to be a string, but I had not noticed that Access had taken it upon itself to make it a date / time format.

Thankyou for your help.

[Jason Teagle]

No, it was not assuming a lot - you are absolutely right; the irony is, I had intended the date and time to be a string, but I had not noticed that Access had taken it upon itself to make it a date / time format.

Thankyou for your help.

[Jason Teagle]

Out of interest, how do I do that? All I could get in Access was a table-like thing for performing queries based on selecting things from lists and the like - I couldn't find a way to just enter the literal SQL command INSERT INTO...

[Jason Teagle]

I know one of my other responses says ignore further posts, but unfortunately the problem isn't solved yet after all. I am now going to give as much information as I can, perhaps you would be kind enough to try and point out my remaining error?

The Access db contains a table named "visual_c". This table contains the following field names, with the shown data types and values for the Index property:

PostID Number No
ParentPostID Number Yes (duplicates OK)
RootPostID Number No
Subject String No
Category String No
Replies Number No
Author String No
Timestamp String (corrected from date/time) No
MessageBody String No

Yet, I STILL can't get the following SQL command to work:

INSERT INTO visual_c
(PostID, ParentPostID, RootPostID, Subject, Category, Replies, Author, Timestamp, MessageBody)
VALUES(15, 0, 0, 'Hellooooo', 'Miscellaneous', 0, 'Mr Nobody', 'XXXXX', 'Does this work')

(the command is all in one string)

As you can see, I even set the timestamp to 'XXXXX' in case it was still trying to interpret it as a date and time against my wishes.

Can ANYONE see why it is failing (MS Access, .mdb db, VBScript in an ASP page being used to perform the query.

Please note that the same .mdb contains another table called NextPostID which I can successfully read, modify and even insert new records into. I can also quite happily read the fields from the visual_c table.

If anyone can help me I would be grateful, I knew I hated databases and this is why.

[Jason Teagle]

Unfortunately the problem isn't solved yet after all. I am now going to give as much information as I can, perhaps you would be kind enough to try and point out my remaining error?

The Access db contains a table named "visual_c". This table contains the following field names, with the shown data types and values for the Index property:

PostID Number No
ParentPostID Number Yes (duplicates OK)
RootPostID Number No
Subject String No
Category String No
Replies Number No
Author String No
Timestamp String (corrected from date/time) No
MessageBody String No

Yet, I STILL can't get the following SQL command to work:

INSERT INTO visual_c
(PostID, ParentPostID, RootPostID, Subject, Category, Replies, Author, Timestamp, MessageBody)
VALUES(15, 0, 0, 'Hellooooo', 'Miscellaneous', 0, 'Mr Nobody', 'XXXXX', 'Does this work')

(the command is all in one string)

As you can see, I even set the timestamp to 'XXXXX' in case it was still trying to interpret it as a date and time against my wishes.

Can ANYONE see why it is failing (MS Access, .mdb db, VBScript in an ASP page being used to perform the query.

Please note that the same .mdb contains another table called NextPostID which I can successfully read, modify and even insert new records into. I can also quite happily read the fields from the visual_c table.

If anyone can help me I would be grateful, I knew I hated databases and this is why.

[Jason Teagle]

Unfortunately the problem isn't solved yet after all. I am now going to give as much information as I can, perhaps you would be kind enough to try and point out my remaining error?

The Access db contains a table named "visual_c". This table contains the following field names, with the shown data types and values for the Index property:

PostID Number No
ParentPostID Number Yes (duplicates OK)
RootPostID Number No
Subject String No
Category String No
Replies Number No
Author String No
Timestamp String (corrected from date/time) No
MessageBody String No

Yet, I STILL can't get the following SQL command to work:

INSERT INTO visual_c
(PostID, ParentPostID, RootPostID, Subject, Category, Replies, Author, Timestamp, MessageBody)
VALUES(15, 0, 0, 'Hellooooo', 'Miscellaneous', 0, 'Mr Nobody', 'XXXXX', 'Does this work')

(the command is all in one string)

As you can see, I even set the timestamp to 'XXXXX' in case it was still trying to interpret it as a date and time against my wishes.

Can ANYONE see why it is failing (MS Access, .mdb db, VBScript in an ASP page being used to perform the query.

Please note that the same .mdb contains another table called NextPostID which I can successfully read, modify and even insert new records into. I can also quite happily read the fields from the visual_c table.

If anyone can help me I would be grateful, I knew I hated databases and this is why.

[Kyle Burns]

If you look at the lefthand side of you toolbar while in the QBE grid (the table-like thing), you'll see a button with a picture of a table in it and a down arrow next to it. Pressing the button will take you to a "table view" (execute the query and display the results). Pressing the down arrow will activate a dropdown list of options. You want to choose the little icon with "SQL" in it. This will bring you to a giant text box that you can type your query into. You can also make complex queries in the QBE grid and switch to SQL view to copy the query text to the clipboard and use it in your code.

[Jason Teagle]

Thanks for that, it was very helpful to be able to apply the query directly within Access.

It seems that, although it had no problem read the table with the 'Timestamp' field, it didn't like INSERT INTO with that field. I couldn't find anything on it, as I would have expected for a reserved word, but that is what was highlighted when I tried to apply the query in Access.

It is now working, thankyou for your help.

(If you could suggest something for this I would be grateful, but it doesn't matter if you can't: is there any way to put strings greater than 255 characters into the database?)

[Kyle Burns]

Use the memo data type