Ad:

Pascal and Delphi Discussion Thread
Unsolved
Views: 3529

Jun 25th, 2008

Add ADO record via code

Expand Post »

I'm using D2007 and trying to add a new, blank record to my Access database with code. The DB has a 'day' field and a 'event' field. All works fine when a record exists for the given day. I need to learn how to add a record to the file with day=NewKey when that record doesn't exist in the file.

Here's what I've got so far:

Pascal and Delphi Syntax (Toggle Plain Text)
// e.g. NewKey = '"2008-06-18"';
 
  SQL := 'SELECT Event FROM Events ' + ' WHERE Day = ' + NewKey;
 
  rs := CoRecordset.Create;
  rs.CursorLocation := adUseClient;
  rs.Open(SQL, cnn, adOpenDynamic, adLockPessimistic, adCmdTableDirect);
 
  if not rs.EOF then
    DayEvent  := VarToStr(rs.Fields.Item['Event'].Value)
  else begin
      ??????
 
 end;
// e.g. NewKey = '"2008-06-18"';

  SQL := 'SELECT Event FROM Events ' + ' WHERE Day = ' + NewKey;

  rs := CoRecordset.Create;
  rs.CursorLocation := adUseClient;
  rs.Open(SQL, cnn, adOpenDynamic, adLockPessimistic, adCmdTableDirect);

  if not rs.EOF then
    DayEvent  := VarToStr(rs.Fields.Item['Event'].Value)
  else begin
      ??????

 end;

Similar Threads

trojans...now nothing opens and I get a paint can't open error in Viruses, Spyware and other Nasties
Master:Detail deletions in C#
Generate Primary Key in VB / Delete Record Status in Visual Basic 4 / 5 / 6
Beginner: how to add new record into database??? in VB.NET
Read Me:Access to any type of DB's possible in Visual Basic 4 / 5 / 6

bob on whidbey

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Oct 2007

Permalink

Jun 26th, 2008

Re: Add ADO record via code

You can use an INSERT statement and the cnn.Execute() method.

filippo.toso

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jun 2008

Permalink

Jun 28th, 2008

Re: Add ADO record via code

hi
if you want to be able to add a new record, i suggest that you draw a DBGrid on your form. since DBGrid has delete, add, assign, update, edit and goto. so you dont need a code for that, all you have to do is to link your DBGrid with your ADO thingy.

Clive29

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since May 2008

Permalink

Jul 13th, 2008

Re: Add ADO record via code

I use the following code to do this:
This is the SQL TStrings fotr my ADQuery.

Pascal and Delphi Syntax (Toggle Plain Text)
 
INSERT INTO Users (UserName,[Password],[First Name],Surname,eMail,Country,Program,Joined,[First Ip],Updates,Premium,Http,TimesViewed,AveLoginTime,AveConnectTime,TimesOptomized) VALUES ( :xUserName,:xPassword,:xFirstName,:xSurname,:xEmail,:xCountry,:xProgram,:xJoined,:xIP,:xUpdates,:xPremium,:xHttp,:xTimesViewed,:xAveLoginTime,:xAveConnectTime,:xTimesOptomized);
 
INSERT INTO Users (UserName,[Password],[First Name],Surname,eMail,Country,Program,Joined,[First Ip],Updates,Premium,Http,TimesViewed,AveLoginTime,AveConnectTime,TimesOptomized) VALUES ( :xUserName,:xPassword,:xFirstName,:xSurname,:xEmail,:xCountry,:xProgram,:xJoined,:xIP,:xUpdates,:xPremium,:xHttp,:xTimesViewed,:xAveLoginTime,:xAveConnectTime,:xTimesOptomized);

To run the Query

Pascal and Delphi Syntax (Toggle Plain Text)
with ADOQuery2 do
begin
    Close;  
	// Make sure the Query is Closed before trying to set the Parameters.
	// Add the Data to the Parameter List.
 
	Parameters.ParamByName('xUserName').Value := Peer[CurPeer].UserName;
	Parameters.ParamByName('xPassword').Value := Peer[CurPeer].Password;
	Parameters.ParamByName('xEmail').Value := Peer[CurPeer].Email;
	Parameters.ParamByName('xCountry').Value := Peer[CurPeer].Country;
	Parameters.ParamByName('xIp').Value := Peer[CurPeer].IP;
	Parameters.ParamByName('xJoined').Value := DateTimeToStr(Now);
	if(Peer[CurPeer].Premium)then
	begin
		Parameters.ParamByName('xPremium').Value := 'Yes';
	end
	else
	begin
		Parameters.ParamByName('xPremium').Value := 'No';
	end;
	.........etc
 
    ExecSQL; 
// This is very important and often overlooked. You need to use ExecSQL not Open for an update , Insert etc.
with ADOQuery2 do
begin
    Close;  
	// Make sure the Query is Closed before trying to set the Parameters.
	// Add the Data to the Parameter List.
  
	Parameters.ParamByName('xUserName').Value := Peer[CurPeer].UserName;
	Parameters.ParamByName('xPassword').Value := Peer[CurPeer].Password;
	Parameters.ParamByName('xEmail').Value := Peer[CurPeer].Email;
	Parameters.ParamByName('xCountry').Value := Peer[CurPeer].Country;
	Parameters.ParamByName('xIp').Value := Peer[CurPeer].IP;
	Parameters.ParamByName('xJoined').Value := DateTimeToStr(Now);
	if(Peer[CurPeer].Premium)then
	begin
		Parameters.ParamByName('xPremium').Value := 'Yes';
	end
	else
	begin
		Parameters.ParamByName('xPremium').Value := 'No';
	end;
	.........etc
                  
    ExecSQL; 
// This is very important and often overlooked. You need to use ExecSQL not Open for an update , Insert etc.

Please see how to declare the variables ....... use :VarName;
Also use ExecSQL not Open. Open is for SELECT Queries only.

Also I save the DateTime to a Text var. and convert back when I extract the data.
It works all the time. It is somewhat buggy trying to attach the DateTime formats for differing Databases.

Hope this helps.

Waynera.

waynera

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Pascal and Delphi Forum Timeline: Need examples of manipulating 2d arrays

Next Thread in Pascal and Delphi Forum Timeline: Help with cursor!!!!

DaniWeb Message

Cancel Changes

User Name
Password