visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: Delphi :: sql easy
By: VB guy Canada  Date: 29/06/2003 00:00:00  English  Points: 300 Status: Answered
Quality : Excellent
What is the right sql queery syntax to add (insert):
comboboxtext,datetimepicker1(date),datetimepicker2(time)
to my database table1??

And to see the data in grid refreshed?
By: VGR Date: 29/06/2003 04:47:00 English  Type : Comment
INSERT INTO table1 SET thefield='comboboxtext',datefield='datetimepicker1',timefield='datetimepicker2';

with a table defined liek this, of course :

CREATE TABLE table1 (thefield CHAR(x),datefield DATE, timefield TIME);
By: VB guy Date: 29/06/2003 06:52:00 English  Type : Comment
I can not compile this :
INSERT INTO table1 SET xxxx='combobox1.text',xxxx='datetimepicker1',xxxx='datetimepicker2';
By: VGR Date: 29/06/2003 07:22:00 English  Type : Assist
of course not 8-)

'INSERT INTO table1 SET xxxx=''+combobox1.text+'',xxxx=''+datetimepicker1+'',xxxx=''+datetimepicker2+''';
By: VGR Date: 29/06/2003 07:22:00 English  Type : Comment
you asked for the SQL query, not for the Delphi-Pascal-way to correctly write a string containing single (SQL) quotes :D
By: esoftbg Date: 29/06/2003 07:46:00 English  Type : Assist
var
T: string;
begin
T := ''
+ 'INSERT INTO table1 SET xxxx = '
+ QuotedStr(ComboBox1.Text) + ', '
+ 'xxxy = '
+ QuotedStr(FormatDateTime(ShortDateFormat, DateTimePicker1.Date)) + ', '
+ 'xxyy = '
+ QuotedStr(FormatDateTime(ShortTimeFormat, DateTimePicker2.Time)) + ', '
+ '';
Query1.Active := False;
Query1.SQL.Text := T;
Query1.Open;
end;

emil
By: esoftbg Date: 29/06/2003 08:00:00 English  Type : Comment
use
Query1.ExecSQL;
instead
Query1.Open;
please

emil
By: VB guy Date: 29/06/2003 16:50:00 English  Type : Comment

VGR
SQL Queery and delphi way basically do the same thing.
just do on button click ExecSQL ...

I am having trouble with insert statement...

Do I insert or append?Not clear on this one,since my db is empty (just learning).
This insert stuff works not.
Is it because I have ID (autonumber field) before everything mentioned?
But i cant insert autonumber because it is self generated...
Anyway i use acess as db.


emil
havent tried yours jet but i think i will get compiler error on 'qoutedstr'
anyway tell me what am i doing wrong here...
By: VB guy Date: 29/06/2003 16:58:00 English  Type : Comment
Would it not be easier to use parameters???
By: udosoft Date: 30/06/2003 05:48:00 English  Type : Assist
Why do you use delphi fieldbyvalues, not sql?

for example:
afterinsert:
Query1.edit;
Query1['combotextfield']:=ComboBox1.Text;
Query1['datefieldname']:=datetimepickerdate.date;
Query1['timefiledname']:=datetimepickertime.time;
Query1.post;



Put all seperate in onchange evens too.
for example

in Datetimepickerdate onchange

Query1.edit;
Query1['datefieldname']:=datetimepickerdate.date;
Query1.post;

The query contains select * from tablename;
(when using bde the requestlive must be set true);

the datasource of the dbgrid should be connected to this query, and so it will be updated!
By: kretzschmar Date: 30/06/2003 18:53:00 English  Type : Comment
??? never learned sql, he


insert into (fieldlist) values (valuelist)

in this case i would use a statment like


const sql = 'insert into DatabaseTableNameHere (TheField, DateField, TileField) '+
Values(:TheField,:DateField,:TimeFiled);

begin
query1.sql.text := sql;
query1.parambyname('TheField').AsString := combobox1.text;
query1.parambyname('DateField').AsDateTime := Trunc(datetimepicker1.Date);
query1.parambyname('TimeField').AsDateTime := Frac(DateTimePicker2.trime);
query1.execSQL;
end;

just my two cents

meikl ;-)
By: dext Date: 30/06/2003 18:57:00 English  Type : Comment
Use SQL ,I think , is more Fast!

strSQL:= 'INSERT INTO table1(thefield,datefield,timefield) VALUES' +
Format('("%s",%s,"%s")',
['comboboxtext', DateToStr(datetimepicker1.Date),TimeToStr(datetimepicker2.Time)])
By: udosoft Date: 30/06/2003 19:27:00 English  Type : Comment
re: dext
but when you need to refresh the dbgrid too connected to another dataset component you loose more time!
By: udosoft Date: 30/06/2003 19:33:00 English  Type : Comment
to make it faster can use Query.AppendRecord() too.
By: udosoft Date: 30/06/2003 20:09:00 English  Type : Comment
But to make my code 2 way you should give a code to Query afterscroll too

begin
if Query1.State=dsbrowse then
begin
Combobox1.text:=Query1['combotextfield'];
if (Query1['timefieldname'] <> null) then datetimepickertime.time:=Query1['timefieldname'];
if (Query1['datefieldname'] <> null) then datetimepickertime.date:=Query1['datefieldname'];

end;
end;


or


begin
if (Query1['timefieldname'] <> null) then Combobox1.text:=Query1['combotextfield'] else Combobox1.text:='';
if (Query1['timefieldname'] <> null) then datetimepickertime.time:=Query1['timefiledname'] ;
if (Query1['datefieldname'] <> null) then datetimepickertime.date:=Query1['datefieldname'];

end;




and so your Combobox, datetimepickers will show the value of the current row (except of the new lines)

You can append a record with dbgrid or with a button and Query1.append; in the onclick.
By: esoftbg Date: 30/06/2003 20:43:00 English  Type : Comment
may be your version of Delphi is earlier than 6 and doesn't support function QuotedStr(.....
var
T: string;
begin
T := ''
+ 'INSERT INTO table1 SET xxxx = '
+ '''' + ComboBox1.Text + '''' + ', '
+ 'xxxy = '
+ '''' + FormatDateTime(ShortDateFormat, DateTimePicker1.Date) + '''' + ', '
+ 'xxyy = '
+ '''' + FormatDateTime(ShortTimeFormat, DateTimePicker2.Time) + '''' + ', '
+ '';
Query1.Active := False;
Query1.SQL.Text := T;
Query1.ExecSQL;
end;

emil
By: kretzschmar Date: 30/06/2003 20:45:00 English  Type : Comment
emil


insert into ... set blabla =

isn't a correct sql-statement

meikl ;-)
By: dext Date: 30/06/2003 20:46:00 English  Type : Comment
Use Params and SQL!
By: VGR Date: 30/06/2003 20:50:00 English  Type : Comment
Sure meikl :D

But it works :D

mysql> describe youjohnny;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| User | char(10) | YES | MUL | NULL | |
| points | int(11) | YES | MUL | NULL | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.08 sec)

mysql> insert into youjohnny set User='test',points=0;
Query OK, 1 row affected (0.02 sec)

mysql> select * from youjohnny;
+----+--------+--------+
| id | User | points |
+----+--------+--------+
| 8 | test | 0 |
+----+--------+--------+
8 rows in set (0.00 sec)

<A HREF="http://www.idera.com/support/documentation/SQL92__SQL_2__Syntax_Reference.htm">http://www.idera.com/support/documentation/SQL92__SQL_2__Syntax_Reference.htm</a>

VGR ;-)
By: kretzschmar Date: 30/06/2003 21:09:00 English  Type : Comment
? seems to be mysql specific
By: esoftbg Date: 30/06/2003 21:48:00 English  Type : Comment
i hope this time it is correct sql:
var
T: string;
begin
T := ''
+ 'INSERT INTO table1 VALUES (xxxx = '
+ '''' + ComboBox1.Text + '''' + ', '
+ 'xxxy = '
+ '''' + FormatDateTime(ShortDateFormat, DateTimePicker1.Date) + '''' + ', '
+ 'xxyy = '
+ '''' + FormatDateTime(ShortTimeFormat, DateTimePicker2.Time) + '''' + ')'
+ '';
Query1.Active := False;
Query1.SQL.Text := T;
Query1.ExecSQL;
end;

emil
By: esoftbg Date: 03/07/2003 05:58:00 English  Type : Assist
the syntax could be:

Query1.Active := False;
Query1.SQL.Text := ''
+ 'INSERT INTO table1 (xxxx, xxxy, xxyy)'
+ ' VALUES ('
+ ''''
+ ComboBox1.Text
+ ''''
+ ', '
+ ''''
+ FormatDateTime(ShortDateFormat, DateTimePicker1.Date)
+ ''''
+ ', '
+ ''''
+ FormatDateTime(ShortTimeFormat, DateTimePicker2.Time)
+ '''' + ')'
+ '';
Query1.ExecSQL;

emil
By: VB guy Date: 03/07/2003 08:16:00 English  Type : Comment
well emil,the last one worked!
Previous one did not.I got 'XXXX has no default value' (combobox1).
Now i have the problem with seeing data updated in the dbgrid bellow.

I use adotable to view data bellow.
Calling adotable1.refresh after exec queery does not do the trick.
Can i see data updated after i run the queery?
I would not like to do table close then open...

Meikl i could not compile yours (?)
forgot what the error was....

How am i to see updated data at once?
By: esoftbg Date: 03/07/2003 16:45:00 English  Type : Comment
hi VBguy,
my problem is that i am not use ADO. I don't know how to start and then commit transaction.
using InterBase it would be (Query1 and Table1 must be linked to the same TTransaction component):

var
B: Boolean;
begin
B := True;
try
try
if not Query1.Transaction.Active then
Query1.Transaction.StartTransaction;
Query1.Active := False;
Query1.SQL.Text := ''
+ 'INSERT INTO table1 (xxxx, xxxy, xxyy)'
+ ' VALUES ('
+ ''''
+ ComboBox1.Text
+ ''''
+ ', '
+ ''''
+ FormatDateTime(ShortDateFormat, DateTimePicker1.Date)
+ ''''
+ ', '
+ ''''
+ FormatDateTime(ShortTimeFormat, DateTimePicker2.Time)
+ '''' + ')'
+ '';
Query1.ExecSQL;
except
B := False;
Query1.Transaction.Rollback;
end;
finally
if B then
begin
Query1.Transaction.CommitRetaining;
Table1.Refresh;
end;
end;
end;

i know this works only in INTERBASE, but it is just an example ;-))
you need to use some similar code for ADO ....

emil
By: esoftbg Date: 03/07/2003 16:54:00 English  Type : Comment
may be Commitment of your Transactions is in a StoredProcedure into the DataBAse, just call this StoredProcedure and then call:
ADOTable1.Refresh;

emil
By: kretzschmar Date: 03/07/2003 16:54:00 English  Type : Comment
>Meikl i could not compile yours (?)
>forgot what the error was....

well, my sample was for bde,
for ado it is a bit different, but also possible

but emil does a good advice,
so that i guess a sample for ado is not needed

meikl ;-)
By: kretzschmar Date: 04/07/2003 05:17:00 English  Type : Answer
ADOTable1.ReQuery;
works sometimes better than
ADOTable1.Refresh;

meikl ;-)
By: VB guy Date: 04/07/2003 07:54:00 English  Type : Comment
you are right michael,it does work better...
By: VB guy Date: 04/07/2003 07:59:00 English  Type : Comment
This spilt pts is weird...
By: senad Date: 05/07/2003 17:23:00 English  Type : Comment
I think none of the above will work correctly with ado.
... :-(
By: VB guy Date: 06/07/2003 18:45:00 English  Type : Comment
Why is it that I get result of datetimepicker(Time) in grid:
30.12.1899 20:15:12

and in access the time is ok?????
By: VB guy Date: 07/07/2003 08:29:00 English  Type : Comment
....think i will repost this....

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 417.208910 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page