Add ADO record via code

[bob on whidbey]

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:

// 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;

[filippo.toso]

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

[Clive29]

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.

[waynera]

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

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

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.