CODEWITHSUNDEEP
sql-serverdelphisql-server-2008r2-express
Ouerghi Yassine
Ouerghi Yassine

Reputation: 1887

Connect Delphi to SQL database

I'm using Microsoft SQL Server R2 Express to create databases.

When I try to connect to a database using delphi, I have to use this Connection string : 

ADOConnection1.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=db_formation;Data Source=EVILKID-pc\evilkid;';

where "EVILKID-PC\evilkid" is the name of my pc, enter image description here

As you can seem this will only work on my PC. What should I do to make it connect to localhost? I have tried changing the "Data Source" to "localhost" but it would not connect and I would get:

[DBNETLIN][ConnectionOpen(Connect()).]SQL Server does not exist or access denied

Any idea where the problem is?

Upvotes: 1

Views: 24703

Answers (3)

alzaimar
alzaimar

Reputation: 4622

I would create a UDL file (Connection.UDL) in the local app data path on your PC and let the connection string in the application use it

Var
  plainAppName : String;
  appDataPath : String;

Begin
  plainAppName := ChangeFileExt(ExtractFilePath(ParamStr(0)),'.EXE','');
  appDataPath := IncludeTrailingBackslash(GetSpecialFolderLocation(CSIDL_APPDATA))+plainAppName+'\';
  ADOConnection.Connectionstring := 'FILE NAME = '+appDataPath+'Connection.UDL';

And the Connection.UDL contains

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=YourPC\InstanceName

in fact, it could be empty. However, you (or the customer) doubleclicks the file, and the oleDB connection dialog opens. now set the server, instance, login credetionals, test the connection and save.

done.

Your app will open the file (well, the ADOConnection will) and you're safe.

Another more generic approach is to simply store the IP/name of the server pc in a INI file and substitute it in the code 

CONST
  adoStr = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;'
          +'Persist Security Info=False;Initial Catalog=test;'
          +'Data Source=%s';

ADOConnection.ConnectionString := Format(adoStr,[TheIPOrNameOfThePC]);
...

Upvotes: 1

Mike
Mike

Reputation: 11

Couple ways you could get around this. As Michael said, use a config file. Other possibles are use a DSN to access the DB. If it's always localhost/machinename you could retrieve the machine name using any number of methods. I use TtvAPIThing in some older projects and JEDI has a system info component to make accessing these easier.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Even though your instance is on localhost, it's still a named instance that you'll need to specify in your connection string:

ADOConnection1.ConnectionString := '... Data Source=localhost\evilkid;';

Upvotes: 4

Related Questions