ADOQuery Giving me trouble again....

[memevertical2]

Hi, I'm trying to run a command in SQL, the INSERT INTO command, but it gives the following error: Parameter Object is Improperly defined. Inconsistent or incomplete information was provided.

The SQL text has 50 fields and values. If I use the code, one field at a time, it gives that error in a specific Field, but the funny thing is that the field were it stops, is identical to the other fields, a simple Text Field.

What can be going wrong?

 

[memevertical2]

Ok, here's an example, this code doesnt give error:

ADOQuery2.SQl.Add('Insert into Listings (Length)');
ADOQuery2.SQL.Add('Values('+QuotedStr('1')+')');
ADOQuery2.ExecSQL;

but this one does give an error:

ADOQuery2.SQl.Add('Insert into Listings (Size)');
ADOQuery2.SQL.Add('Values('+QuotedStr('1')+')');
ADOQuery2.ExecSQL;

Now, "Size" and "Length" are both TEXT Fields, they are the same....I dont know one of them gives me error.....I'm lost.

 

[towerbase]

It would help if you specified which DBMS you are using.

Is Size a reserved word? Try renaming that column in your Listings table to something unlikely to be reserved word.

Andrew
Hampshire, UK

 

[memevertical2]

Hi, thanks for writing.

I'm not sure what you mean by DBMS, but I'm using a MDB database file, and using ADO components.

I tried re-naming the field, and It did work. But I know face a different problem. The only way I know how to insert a record is with that command, and I have a procedure that navigates a table with the info I want, and creates a string with all the fields and all the values, and it gives that error. I tried running that command for each field as a test and it goes all right, but ALL fields at once, it gives that error....is there a chance that I can add each field at a time maybe?

Thank

 

[memevertical2]

Ok, here's an update. I see that the 2 fields giving me trouble are DATE fields.

Now here the part that confuses me:

I tried running the SQL Text entered directly, and using the procedure I made. On both cases I used a SHOWMESSAGE just to see the value of the SQL.Text before it executes it, and in both cases the SQL.Text was EXACTLY THE SAME before it got executed, but the one from the procedure gives that error again.....and the one entered directly didn't.....why can that be?

 

[memevertical2]

Man, this is confusing.....ok, so, the FIELD called TITLE, some times has weird characters, but, If I make an insert of just the title, it doesn't give trouble, but if I insert the title and all the rest 50 fields, it does give trouble....and if I insert ALL fields, without that Title, it does work....and I also tried inserting a record with all Fields including a Title without weird characters, and it worked....

What I don't understand is why does a Title with weird characters can be inserted alone, but not along with the rest of the 50 fields.....thats weird....

any ideas?

 

[memevertical2]

OK, FINAL UPDATE, I promise.......So I figured out that when I INSERT just a weird Title its all good, if I Insert just a Date its all Good....but when I INSERT both there's the problem....here's an example of when it gives an error:

ADOQuery2.SQl.Add('INSERT INTO Listings (Title,SaleDate)');
ADOQuery2.SQL.Add('Values('+QuotedStr('Piece 14" 7x10 mm')+','+QuotedStr('1/1/2008 12:00:00 PM')+')');
ShowMessage(ADOQuery2.SQL.Text);
ADOQuery2.ExecSQL;

Like I said, the Title Alone gives me no trouble, and the Date alone doesnt give me any trouble, but together is when it crashes.....is there a way to INSERT field by field?

 

[roo0047]

Your problem is in this line:

ADOQuery2.SQL.Add('Values('+QuotedStr('Piece 14" 7x10 mm')+','+QuotedStr('1/1/2008 12:00:00 PM')+')');

Which results in

Values("Piece 14" 7x10 mm", "1/1/2008 12:00:00 PM")

Do you see the problem?

HINT: Count the double-quotes...


Roo
Delphi Rules!

 

[memevertical2]

Well, the actual SQL.Text reflects like this:

Values('Piece 14" 7x10 mm','1/1/2008 12:00:00 PM')

And like is said, when I INSERT just the title, it works, and if I INSERT just the date, it works....but together they crash.....dont know why.....

 

[roo0047]

You are correct. I incorrectly assumed QuotedStr used double quotes and I always use AnsiQuotedStr after evaluating whether a user included either in their query and toggle "Quote:Char" accordingly.

I've run into this before (INSERT multiple fields) with TQuery and resolved by using TQuery.Perams. I've never used ADOQuery, but perhaps you solution is to use TADOQuery.Parameters.

HTH

Roo
Delphi Rules!

 

[memevertical2]

Thanks for writing. Can you tell me how to do a normal INSERT using the PARAMETERS property????

Thanks a lot.....

 

[memevertical2]

Ok, I found info on it and got it to work.... THANkS!!!

Now, my question would be, can I add a value to field, but instead of refering to the field by name, just refer to the field number..????

 

[roo0047]

Yes. You can use "BPData.BP.Fields[4].AsString" instead of "BPData.BP.FieldByName('URL').AsString" but it is dangerous because if the table's field order ever changes, you will have broken your code.

Roo
Delphi Rules!

 

[memevertical2]

Thanks. Well its not really that dangerous, because I first run through all the names of the fields, and copy their index numbers, and then, with those numbers I intend to do the insert.

But how do I make the "INSERT INTO" with that function you said? I've never used any of those.....

Thanks a lot for your help

 

[memevertical2]

Ok, let me be more clear. I have no control over what fields are created, and the names the user assigns to it.

So, in an example table, there is a field called SIZE....it crashes there, so I assume its a reserved word, so I want to insert values not referring to the name of the field, but with the number.

And using a procedure that extracts the field name by using a number is no solution, because I end up using the INSERT method with a name.....and if there are fields with reserved words, it will crash.....

any ideas?

 

[roo0047]

Delphi reserved words don't matter when they are within the data (text) of any string var. I think Andrew was warning is that it may be a SQL reserved word, depending on what you're using. Also, depending on what DBMS you're using, there is syntax to use a reserved word as literal in SQL fields. I have no examples available at the moment.

 

[memevertical2]

How can I know the DBMS I'm using? and how can I find out the syntax to use a reserved word in a field?

Thanks.

 

[towerbase]

OP says they are using a .MDB database file which I believe is the file extension used by Microsoft Access. I googled for the list of Access reserved words and size does not appear to be one.

What is the actual message error that you get?

Assuming that Access uses a fairly standard dialect of SQL then it should be possible to insert a record without having to list all the column names.

Instead of using

INSERT INTO listings ( a, b, c, d ... ) VALUES ( 'p', 'q', 'r', 's' ... )

try

INSERT INTO listings VALUES ( 'p', 'q', 'r', 's' ... )

but remember to include values for all the columns.

Is it possible that size is an indexed column with unique keys and you are trying to insert an non-unique value?

Andrew
Hampshire, UK

 

[DjangMan]

MS Access - like most MS products are not completely compliant. For Access - surround reserved (or suspected reserved) words in square brackets. Surround dates in with # characters using MM/DD/YYYY format.

 

[memevertical2]

Hi,

Size is a normal, text column.... I tried a single insert for SIZE and it gives error, if I rename the column to SIZES it does work. I'll try to enter value for all fields so that I don't have to name each one.....

I'll let you know how it goes....

Thanks again...