Topic: delphi ado database connection

[clarke]

am developing this application for my local church and wanted to connect to an sql server using ado connection.am using a data module  where i have a dataset component,connection and datasource component.
well,am i on the right track?
i want some help in connecting with the database using code which i think is better than just using the properties...
would also like to get help on capturing errors of failure to connect to the server and also navigation and insertion of data.havent used this before so please can someone help me out.....thanx in advance  

[Simon]

Hi  Clarke, and  

I'm sure someone will be able to help you with this soon.  

[clarke]

thank you simon..

[sam]

Clarke,

What is your sql server webbased or locally kept?

Sorry I walso wnted to clarify what software you were using. Does it allow direct SQL code? or do you have to use its?

[clarke]

thank you sam.Am using delphi 7.0 and want to connect to a local ms sql server just within the LAN.well i hope that was the info that might have been missing.....      

[sam]

ok delphi 7.0 - gosh havent used delphi in a long time... umm needs some thought. Does delphi allow you to directly run sql commands or do you have to use your own?

Ok I have racked my brain a little and I think you might want to take a look at this page: http://delphi.about.com/od/database/l/aa050101a.htm - tell me if it is off use. Do you have any experience with sql?

Another interesting tool which might be useful is: http://www-106.ibm.com/developerworks/db2/library/techarticle/0210swart/0210swart.html

[clarke]

well i figured delphi 7.0 does support sql commands.it should work even better than visual basic but am syet to figure it out nowonder i need some assistance.On the other hand maybe i can get someone can tell me the best way to make an application and connect to the database which is of ms sql server.I have used bde engine but the problem is that once the application is installed then you have to install in each machine which i think is cumbersome.i want an application where once am through i jus give it to the user and goes on with using it and found ado is independent so if there is anyway other than ado then please someone help me out.
On sql am familiar with it and thats why i want to use it as my backend.

my biggest problem is connecting to the database....well ave tried using the getconnection() and setconnection() adodb class methods though am geting no errors i still seem not to get connected.i also want to capture the error when my aplication cant establish the connection to the sql server.....then we can address the database records update and deletion :shock:  :shock:  :shock:

[sam]

Ok.

umm I am not a delphi programmer so can't help you too much with the inbuilt classes. I am also not that familiar with ms sql.. only use it when forced.. and that was a one time deal! Sorry.

I may have found a useful site for you. Thinking back to when I used it before I remember looking at: http://www.functionx.com/sqlserver/Lesson07.htm

- down the bottom of the page is a section all about delphi... might have the answer you need.

One thing - surely all you need for your connection string is:

Code: [Select]

Server=hostname;
 Database=databaseName;
 User ID=sqlServerUserid;
 Password=sqlServerPassword

or if you are using an NT

Code: [Select]

Server=hostname;
 Database=databaseName;
 User ID=windowsDomain\windowsUserid;
 Password=windowsPassword;
 Integrated Security=SSPI

[clarke]

well this code thing has got the better of me cant seem to get the connection right.i'll do it without code but the error especially is a nuiscance so someone help...sam thanks alot for the assistance keep it up

[sam]

i am just sorry i cant really help you any more. i will ask around and see if i know anyone who uses this combination.

[clarke]

am having a form on my application that when i click on a command button opens a second form for inserting data.the first form inserts to a different table and the second form to a different table.waht ild want is the second table to use the primarykey ie a field from the first form,copy it to the second form dbedit for insertion into the second table.my problem is that if i place a control eg a dbedit on the second form to read from the firstt table nothing shows and when i place the same control on an empty control it works......waht migth be my problem.....

THIS IS MY CODE FOR THE SECOND FORM

unit married;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, db, Grids, DBGrids, DBCtrls, Mask, Buttons;

type
  TFamilyDetails = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    DBEdit4: TDBEdit;
    DBComboBox1: TDBComboBox;
    GroupBox3: TGroupBox;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    DBEdit1: TDBEdit;
    DBEdit5: TDBEdit;
    DBEdit6: TDBEdit;
    DBComboBox2: TDBComboBox;
    PopupCalBtn: TSpeedButton;
    GroupBox8: TGroupBox;
    AddCommand: TBitBtn;
    CommandCancel: TBitBtn;
    closecommand: TBitBtn;
    procedure AddCommandClick(Sender: TObject);
    procedure CommandCancelClick(Sender: TObject);
    procedure enter;
    procedure PickDate(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    procedure DataSource2StateChange(Sender: TObject);

  public
    { Public declarations }
  end;

var
  FamilyDetails: TFamilyDetails;

implementation

uses DataApp, Registration, DatePicker, Children;

{$R *.dfm}
procedure TFamilyDetails.Enter;
begin
  applications.DataSource2.OnStateChange := DataSource2StateChange;

  try
    applications.ADOConnection1.Open;
    applications.ADODataSet2.Open;
    applications.ADODataSet2.Insert;
    
    showmodal;
    finally
    applications.DataSource2.OnStateChange := nil;
  end;
end;

procedure TFamilyDetails.AddCommandClick(Sender: TObject);
begin
applications.ADODataSet2.Post;
end;

procedure TFamilyDetails.CommandCancelClick(Sender: TObject);
begin
applications.detailAfterCancel(applications.ADODataSet2);
end;

procedure TFamilyDetails.DataSource2StateChange(Sender: TObject);
var ds: string;
  begin
    case applications.ADODataSet2.State of
        dsInactive: ds:='Closed';
        dsBrowse  : ds:='Browsing';
        dsEdit    : ds:='Editing';
        dsInsert  : ds:='New record inserting';
    else
        ds:='Other states'
    end;
      Caption:='The Master detail datasource state: ' + ds;

    end;

procedure TFamilyDetails.PickDate(Sender: TObject);
begin
BrDateForm.Date := applications.ADODataSet2dMarriageDate.Value;    { start with current date }
  if BrDateForm.ShowModal = mrOk then
  begin
    applications.ADODataSet2.Edit;
    applications.ADODataSet2dMarriageDate.Value := BrDateForm.Date;
    dbedit2.SelectAll;
  end;
end;
procedure TFamilyDetails.FormCreate(Sender: TObject);
begin
applications.ADODataSet1.Open;
end;

end.


THIS IS THE CODE FOR FIRST FORM

unit Registration;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, db, StdCtrls, Mask, DBCtrls, ComCtrls, Buttons, Menus, Grids,
  DBGrids;

type
  TMemberReg = class(TForm)
    MainMenu1: TMainMenu;
    File1: TMenuItem;
    AddNewMember1: TMenuItem;
    N1: TMenuItem;
    CancelRegistration1: TMenuItem;
    N2: TMenuItem;
    Exit1: TMenuItem;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    PopupCalBtn: TSpeedButton;
    Label19: TLabel;
    Label13: TLabel;
    GroupBox4: TGroupBox;
    Label6: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label7: TLabel;
    GroupBox5: TGroupBox;
    Label11: TLabel;
    Label12: TLabel;
    Label18: TLabel;
    DBEdit11: TDBEdit;
    DBEdit12: TDBEdit;
    DBEdit18: TDBEdit;
    DBEdit6: TDBEdit;
    DBEdit7: TDBEdit;
    DBEdit8: TDBEdit;
    DBEdit9: TDBEdit;
    DBEdit10: TDBEdit;
    GroupBox6: TGroupBox;
    Label14: TLabel;
    Label15: TLabel;
    GroupBox7: TGroupBox;
    Label16: TLabel;
    Label17: TLabel;
    DBEdit13: TDBEdit;
    DBEdit14: TDBEdit;
    DBEdit16: TDBEdit;
    DBEdit17: TDBEdit;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    DBEdit4: TDBEdit;
    DBEdit5: TDBEdit;
    DBComboBox1: TDBComboBox;
    DBComboBox2: TDBComboBox;
    GroupBox8: TGroupBox;
    AddCommand: TBitBtn;
    CommandCancel: TBitBtn;
    closecommand: TBitBtn;
    StatusBar1: TStatusBar;
    DBEdit15: TDBEdit;
    procedure AddCommandClick(Sender: TObject);
    procedure CommandCancelClick(Sender: TObject);
    procedure closecommandClick(Sender: TObject);
    procedure PickDate(Sender: TObject);
    procedure AddNewMember1Click(Sender: TObject);
    procedure CancelRegistration1Click(Sender: TObject);
    procedure Exit1Click(Sender: TObject);
    procedure enter;
    procedure DBEdit7KeyPress(Sender: TObject; var Key: Char);
    procedure DBEdit8KeyPress(Sender: TObject; var Key: Char);
    procedure DBEdit9KeyPress(Sender: TObject; var Key: Char);
    procedure DBEdit14KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
    procedure DataSource1StateChange(Sender: TObject);
  public
    { Public declarations }
  end;

var
  MemberReg: TMemberReg;

implementation

{$R *.dfm}
uses dataapp, DatePicker, married, marrieds;

procedure Tmemberreg.Enter;
begin
  applications.DataSource1.OnStateChange := DataSource1StateChange;
  try
    applications.ADOConnection1.Open;
    applications.ADODataSet1.Open;
    applications.ADODataSet1.Insert;
    ShowModal;
  finally
    applications.DataSource1.OnStateChange := nil;
  end;
end;

procedure TMemberReg.AddCommandClick(Sender: TObject);
begin
  if dbedit15.field.AsString = 'married' then
  applications.ADODataSet1.Post;
  familydetails.enter;
end;

procedure TMemberReg.CommandCancelClick(Sender: TObject);
begin
applications.detailsAfterCancel(applications.ADODataSet1);
end;

procedure TMemberReg.closecommandClick(Sender: TObject);
begin
close;
end;

procedure TMemberReg.PickDate(Sender: TObject);
begin
BrDateForm.Date := applications.ADODataSet1dDOB.Value;    { start with current date }
  if BrDateForm.ShowModal = mrOk then
  begin
    applications.ADODataSet1.Edit;
    applications.ADODataSet1dDOB.Value := BrDateForm.Date;
    dbedit4.SelectAll;
  end;
end;

procedure TMemberReg.AddNewMember1Click(Sender: TObject);
begin
applications.ADODataSet1.Insert;
end;

procedure TMemberReg.CancelRegistration1Click(Sender: TObject);
begin
applications.ADODataSet1.Cancel;
end;

procedure TMemberReg.Exit1Click(Sender: TObject);
begin
close;
end;

procedure TMemberReg.DataSource1StateChange(Sender: TObject);
var ds: string;
  begin
    case applications.ADODataSet1.State of
        dsInactive: ds:='Closed';
        dsBrowse  : ds:='Browsing';
        dsEdit    : ds:='Editing';
        dsInsert  : ds:='New record inserting';
    else
        ds:='Other states'
    end;
      Caption:='The Master detail datasource state: ' + ds;
    end;

procedure TMemberReg.DBEdit7KeyPress(Sender: TObject; var Key: Char);
begin
  begin
  // #8 is Backspace
if not (Key in [#8, '0'..'9']) then begin
    ShowMessage('Invalid key');
    // Discard the key
Key := #0;
  end;

 end;
end;

procedure TMemberReg.DBEdit8KeyPress(Sender: TObject; var Key: Char);
begin
 begin
  // #8 is Backspace
if not (Key in [#8, '0'..'9']) then begin
    ShowMessage('Invalid key');
    // Discard the key
Key := #0;
  end;

 end;
end;

procedure TMemberReg.DBEdit9KeyPress(Sender: TObject; var Key: Char);
begin
 begin
  // #8 is Backspace
if not (Key in [#8, '0'..'9']) then begin
    ShowMessage('Invalid key');
    // Discard the key
Key := #0;
  end;

 end;
end;

procedure TMemberReg.DBEdit14KeyPress(Sender: TObject; var Key: Char);
begin
 begin
  // #8 is Backspace
if not (Key in [#8, '0'..'9']) then begin
    ShowMessage('Invalid key');
    // Discard the key
Key := #0;
  end;

 end;
end;

end.


SOOMEONE PLEASE HELP ME THIS SHOULD BE EASIER THEN.....THANK YOU IN ADVANCE

[Clive]

Sorry Clarke, but Delphi is a very old and archaeic programing language and very few people still use it.  I think you are going to have to look elsewhere for an answer on this occasion.

[Sandra]

Youre old and archaic Clive so you should know