Board index » cppbuilder » BCB6 and Microsoft Access

BCB6 and Microsoft Access


2005-11-03 11:57:51 PM
cppbuilder55
Could someone *please* spell out how to UPDATE or INSERT records into a
Microsoft Access database? My SQL is correct as far as SQL standards go,
but I keep getting exceptions saying I have an error in my UPDATE or my
INSERT commands.
My MS Access database has 2 types of fields: Numbers and Text. The Field
Size for the Numbers are either Long Integer or Double, and the Field Size
for the Text type is 50.
Here is the MS Access Table:
______SPNs : Table________
Name_ Type__ Size________
SPN Number Long Integer
Desc Text 50
Len Number Long Integer
Res Number Double
Ofs Number Double
Min Number Double
Max Number Double
Unit Text 10
Here are my values defined in BCB6:
AnsiString strSql;
int spn, len, bit;
char desc[50], unit[7];
double res, ofs, min, max;
This INSERT statement is successful, so you know I am connecting:
strSql.printf("INSERT INTO SPNs (SPN) VALUES (%d)", spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();
This statement produces "error in INSERT command" exception:
strSql.printf("INSERT INTO SPNs (SPN, Len, Bit) VALUES (%d, %d, %d)", spn,
len, bit);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();
After the successful INSERT statement above, this UPDATE statement fails
with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Len = %d, Bit = %d WHERE SPN = %d", len, bit,
spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();
Also, this UPDATE fails with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Res = %.5lf, Ofs = %.5lf, Min = %.5lf, Max =
%.5lf WHERE SPN = %d", res, ofs, min, max, spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();
And this UPDATE fails with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Desc = '%s', Unit = '%s' WHERE SPN = %d",
desc, unit, spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();
Jesus! What's with this? Why is it so hard to input a record into an
Access database with Borland? I can view the strSql data after the printf()
routines, and they all look great! I've tried entering only the Integer
fields, only the Text fields, and only the Double fields, but they each
fail.
Does ANYBODY have a clue what is going on or how to resolve this? I've also
tried using Parameters and RecordSets, but I still get the same errors when
it tries to insert the records.
 
 

Re:BCB6 and Microsoft Access

I think Len and Bit are keywords in Jet SQL. Try:
INSERT INTO [SPNs] ([SPN], [Len], [Bit]) VALUES (%d, %d, %d)
The same for UPDATE. With column names you use I would brace ([...]) every
column and table name, Min and Max are likely keywords too.
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"poojo hackma" <poojo.com/mail>сообщи?сообщила ?новостях следующе?
Quote
Could someone *please* spell out how to UPDATE or INSERT records into a
Microsoft Access database? My SQL is correct as far as SQL standards go,
but I keep getting exceptions saying I have an error in my UPDATE or my
INSERT commands.

My MS Access database has 2 types of fields: Numbers and Text. The Field
Size for the Numbers are either Long Integer or Double, and the Field Size
for the Text type is 50.

Here is the MS Access Table:
______SPNs : Table________
Name_ Type__ Size________
SPN Number Long Integer
Desc Text 50
Len Number Long Integer
Res Number Double
Ofs Number Double
Min Number Double
Max Number Double
Unit Text 10

Here are my values defined in BCB6:
AnsiString strSql;
int spn, len, bit;
char desc[50], unit[7];
double res, ofs, min, max;

This INSERT statement is successful, so you know I am connecting:
strSql.printf("INSERT INTO SPNs (SPN) VALUES (%d)", spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();

This statement produces "error in INSERT command" exception:
strSql.printf("INSERT INTO SPNs (SPN, Len, Bit) VALUES (%d, %d, %d)", spn,
len, bit);

ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();

After the successful INSERT statement above, this UPDATE statement fails
with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Len = %d, Bit = %d WHERE SPN = %d", len,
bit, spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();

Also, this UPDATE fails with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Res = %.5lf, Ofs = %.5lf, Min = %.5lf, Max
= %.5lf WHERE SPN = %d", res, ofs, min, max, spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();

And this UPDATE fails with "error in UPDATE command:"
strSql.printf("UPDATE SPNs SET Desc = '%s', Unit = '%s' WHERE SPN = %d",
desc, unit, spn);
ADOCommand1->CommandText = strSql;
ADOCommand1->Execute();

Jesus! What's with this? Why is it so hard to input a record into an
Access database with Borland? I can view the strSql data after the
printf() routines, and they all look great! I've tried entering only the
Integer fields, only the Text fields, and only the Double fields, but they
each fail.

Does ANYBODY have a clue what is going on or how to resolve this? I've
also tried using Parameters and RecordSets, but I still get the same
errors when it tries to insert the records.



 

Re:BCB6 and Microsoft Access

Son of a gun!
That worked!
Thank You, Vassiliev! Maybe I can get some sleep tonight.
Quote
I think Len and Bit are keywords in Jet SQL. Try:

INSERT INTO [SPNs] ([SPN], [Len], [Bit]) VALUES (%d, %d, %d)

The same for UPDATE. With column names you use I would brace ([...]) every
column and table name, Min and Max are likely keywords too.

//------------------------------------------
Regards,
Vassiliev V. V.
 

{smallsort}