how do you specify a port # when setting up a TadoConnection?

[Keith G Hicks]

I know how to do this in code but how do you do it in the properties of an
actual object on a form? I've tried everythign I can think of and it won't
work. I need a connection to connect during design in order to get at
backend things. I was using the default MS SQL port of 1433 and it always
worked without any intervention on my part but recently changed to a
different port # and now I can't connect. Like I said, I can do it in code
when my app starts up by including "Data Source = <server name>,<port #>"
but that doesn't work in the actuall connection object.

Thanks in advance,

Keith

[Brian Bushay TeamB]

Post by Keith G Hicks
I know how to do this in code but how do you do it in the properties of an
actual object on a form? I've tried everythign I can think of and it won't
work. I need a connection to connect during design in order to get at
backend things. I was using the default MS SQL port of 1433 and it always
worked without any intervention on my part but recently changed to a
different port # and now I can't connect. Like I said, I can do it in code
when my app starts up by including "Data Source = <server name>,<port #>"
but that doesn't work in the actuall connection object.

I am not following you here. Are you saying that if you enter the
Data Source = <server name>,<port #>
syntax in the connection string that doesn't work for you?
It works when I try it.


--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

Correct. It doesn't work. I can set up a connection string in code and it
works fine but if I enter it into the ConnectionString property of the
TAdoConnection object it doesn't work. Odd huh? Doesn't make much sense to
me. I have 2 connection objects on my data form. One is for run time and
the other is for design time. I set the connection string for the one used
at run time in code because it gets some info from the registry. It works
perfectly well including the port setting. The one I use during design time
is identical in every way except for the name of course. That's the one I'm
having trouble with. Like I said it doesn't really make any sense. My
connection string is simply like this:

Provider=SQLOLEDB.1;Password=<password>;Persist Security Info=True;User
ID=<user name>;Initial Catalog=<db name>;Data Source=<server name>,<port#>

Post by Keith G Hicks
I know how to do this in code but how do you do it in the properties of an
actual object on a form? I've tried everythign I can think of and it won't
work. I need a connection to connect during design in order to get at
backend things. I was using the default MS SQL port of 1433 and it always
worked without any intervention on my part but recently changed to a
different port # and now I can't connect. Like I said, I can do it in code
when my app starts up by including "Data Source = <server name>,<port #>"
but that doesn't work in the actuall connection object.

I am not following you here. Are you saying that if you enter the
Data Source = <server name>,<port #>
syntax in the connection string that doesn't work for you?
It works when I try it.


--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

Something even stranger. My brother (who's working with me on the same
project) couldn't get any of the stuff below to work so he tried "Data
Source = <server name>; Port = <Port #>" and it works for him. He found this
in a MySql webpage somewhere. We're both running MS SQL 2000 and not MySql.
What he's doing doesn't work at all on my machine. I'm clueless as to why it
would work on his. I'm wondering if this is some weird MDAC issue. I'm going
to check with him to see what version he has. I'm sure it's recent since
he's running Windows XP and has updated his SQL installation. I can't
beleive that would make a difference though.

"Keith G Hicks" <***@comcast.net> wrote in message news:43769a0c$***@newsgroups.borland.com...
Correct. It doesn't work. I can set up a connection string in code and it
works fine but if I enter it into the ConnectionString property of the
TAdoConnection object it doesn't work. Odd huh? Doesn't make much sense to
me. I have 2 connection objects on my data form. One is for run time and
the other is for design time. I set the connection string for the one used
at run time in code because it gets some info from the registry. It works
perfectly well including the port setting. The one I use during design time
is identical in every way except for the name of course. That's the one I'm
having trouble with. Like I said it doesn't really make any sense. My
connection string is simply like this:

Provider=SQLOLEDB.1;Password=<password>;Persist Security Info=True;User
ID=<user name>;Initial Catalog=<db name>;Data Source=<server name>,<port#>

Post by Keith G Hicks
I know how to do this in code but how do you do it in the properties of an
actual object on a form? I've tried everythign I can think of and it won't
work. I need a connection to connect during design in order to get at
backend things. I was using the default MS SQL port of 1433 and it always
worked without any intervention on my part but recently changed to a
different port # and now I can't connect. Like I said, I can do it in code
when my app starts up by including "Data Source = <server name>,<port #>"
but that doesn't work in the actuall connection object.

I am not following you here. Are you saying that if you enter the
Data Source = <server name>,<port #>
syntax in the connection string that doesn't work for you?
It works when I try it.


--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

Well I spoke too soon. His method does work in code but not in the
ConnectionString property in the object inspector. This is becoming very
frustrating. Here's our code for setting up the connection on application
startup:

sDbConnStr := 'Provider=SQLOLEDB.1;Password=' + sDbPwd + ';'
+ 'Persist Security Info=True;User ID=' + sDbUsrName + ';'
+ 'Initial Catalog=CustDB;Data Source=' + sDbSrvName + ';Port=' +
sSqlPortNum;

cnnCon.ConnectionString := sDbConnStr;
try
cnnCon.Connected := true;
except
Windows.MessageBox(Application.Handle, 'Database connection failed.',
PChar(oInternalSysInfo.CoDBAName), MB_ICONINFORMATION or MB_OK);
Application.Terminate;
end;

The above works fine. If I set a break point and get the value of
"sDbConnStr" and then paste that into the ConnectionString property of the
object (without the quote marks of course) I get "Invalid connection string
attribute" as I'd expect.

If I change the code above to:

... + 'Initial Catalog=CustDB;Data Source=' + sDbSrvName + ',' +
sSqlPortNum;

it also works fine from code but NOT from the ConnectionString property in
the object inspector (I can paste it in there but when I try to set the
Connected property to true I get an error that it can't find the db.

Why would I get different behavior in code then I get in the object
inspector at design time?????


"Keith G Hicks" <***@comcast.net> wrote in message news:4377519f$***@newsgroups.borland.com...
Something even stranger. My brother (who's working with me on the same
project) couldn't get any of the stuff below to work so he tried "Data
Source = <server name>; Port = <Port #>" and it works for him. He found this
in a MySql webpage somewhere. We're both running MS SQL 2000 and not MySql.
What he's doing doesn't work at all on my machine. I'm clueless as to why it
would work on his. I'm wondering if this is some weird MDAC issue. I'm going
to check with him to see what version he has. I'm sure it's recent since
he's running Windows XP and has updated his SQL installation. I can't
beleive that would make a difference though.

"Keith G Hicks" <***@comcast.net> wrote in message news:43769a0c$***@newsgroups.borland.com...
Correct. It doesn't work. I can set up a connection string in code and it
works fine but if I enter it into the ConnectionString property of the
TAdoConnection object it doesn't work. Odd huh? Doesn't make much sense to
me. I have 2 connection objects on my data form. One is for run time and
the other is for design time. I set the connection string for the one used
at run time in code because it gets some info from the registry. It works
perfectly well including the port setting. The one I use during design time
is identical in every way except for the name of course. That's the one I'm
having trouble with. Like I said it doesn't really make any sense. My
connection string is simply like this:

Provider=SQLOLEDB.1;Password=<password>;Persist Security Info=True;User
ID=<user name>;Initial Catalog=<db name>;Data Source=<server name>,<port#>

[Brian Bushay TeamB]

Post by Keith G Hicks
Well I spoke too soon. His method does work in code but not in the
ConnectionString property in the object inspector. This is becoming very
frustrating. Here's our code for setting up the connection on application
sDbConnStr := 'Provider=SQLOLEDB.1;Password=' + sDbPwd + ';'
+ 'Persist Security Info=True;User ID=' + sDbUsrName + ';'
+ 'Initial Catalog=CustDB;Data Source=' + sDbSrvName + ';Port=' +
sSqlPortNum;
cnnCon.ConnectionString := sDbConnStr;
try
cnnCon.Connected := true;
except
Windows.MessageBox(Application.Handle, 'Database connection failed.',
PChar(oInternalSysInfo.CoDBAName), MB_ICONINFORMATION or MB_OK);
Application.Terminate;
end;
The above works fine. If I set a break point and get the value of
"sDbConnStr" and then paste that into the ConnectionString property of the
object (without the quote marks of course) I get "Invalid connection string
attribute" as I'd expect.
... + 'Initial Catalog=CustDB;Data Source=' + sDbSrvName + ',' +
sSqlPortNum;
it also works fine from code but NOT from the ConnectionString property in
the object inspector (I can paste it in there but when I try to set the
Connected property to true I get an error that it can't find the db.
Why would I get different behavior in code then I get in the object
inspector at design time?????

I don't have an answer but a couple more questions
First have you run the Server network utility to make sure Tcp/IP is installed
for SQL server

Second if you run the Data Link property editor and enter your server as
<server name>.<Port#> then press the Test button does the test connection work?

What version of MDAC are you using and have you tried updating your version

You may just have a corrupted MDAC. You can do an internet search and find
directions to replace your MDAC version
--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

Post by Brian Bushay TeamB
I don't have an answer but a couple more questions
First have you run the Server network utility to make sure Tcp/IP is installed
for SQL server

Yes it's installed. I use it for MS Access/SQL (DSN's) and unless I don't
understand something, how could I use the Network utility to change the SQL
port if TCP/IP were not installed? Is that possible?

Post by Brian Bushay TeamB
Second if you run the Data Link property editor and enter your server as
<server name>.<Port#> then press the Test button does the test connection work?

Interestingly I can do it from outside of Delphi but not in Delphi. If I
create a Test.udl object on my desktop and set the connection up it works
just fine. When I use the Data Link properties editor to create the
connection strign for the TAdoConnection object in Delphi it fails. It's the
same editor. I assume Delphi is just opening up what's in Windows. Maybe
this is a bug in D7.1? Another thing that's interesting about this is that
if I don't use the port# in the Test.udl it also works (my port is
not 1433).

Post by Brian Bushay TeamB
What version of MDAC are you using and have you tried updating your version

2.81. I keep it updated when necessary.

Post by Brian Bushay TeamB
You may just have a corrupted MDAC. You can do an internet search and find
directions to replace your MDAC version

This is doubtful. My brother has the exact same problem on an entirely
different computer running Windows XP (I'm running Win2kPro) 8000 miles from
me.

--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

I also created an ODBC datasource and used it to access my SQL tables from
an Access 2000 mdb. It works just fine too. But if I use that same ODBC
datasource in a TAdoConnection using the ODBC settigns it doesn't work at
all. It fails.

keith

Post by Brian Bushay TeamB
I don't have an answer but a couple more questions
First have you run the Server network utility to make sure Tcp/IP is installed
for SQL server

Yes it's installed. I use it for MS Access/SQL (DSN's) and unless I don't
understand something, how could I use the Network utility to change the SQL
port if TCP/IP were not installed? Is that possible?

Post by Brian Bushay TeamB
Second if you run the Data Link property editor and enter your server as
<server name>.<Port#> then press the Test button does the test connection work?

Interestingly I can do it from outside of Delphi but not in Delphi. If I
create a Test.udl object on my desktop and set the connection up it works
just fine. When I use the Data Link properties editor to create the
connection strign for the TAdoConnection object in Delphi it fails. It's the
same editor. I assume Delphi is just opening up what's in Windows. Maybe
this is a bug in D7.1? Another thing that's interesting about this is that
if I don't use the port# in the Test.udl it also works (my port is
not 1433).

Post by Brian Bushay TeamB
What version of MDAC are you using and have you tried updating your version

2.81. I keep it updated when necessary.

Post by Brian Bushay TeamB
You may just have a corrupted MDAC. You can do an internet search and find
directions to replace your MDAC version

This is doubtful. My brother has the exact same problem on an entirely
different computer running Windows XP (I'm running Win2kPro) 8000 miles from
me.

--
Brian Bushay (TeamB)
***@NMPLS.com

[Brian Bushay TeamB]

Post by Keith G Hicks
Yes it's installed. I use it for MS Access/SQL (DSN's) and unless I don't
understand something, how could I use the Network utility to change the SQL
port if TCP/IP were not installed? Is that possible?

I thought you might have done it from another machine.

Post by Keith G Hicks

Post by Brian Bushay TeamB
Second if you run the Data Link property editor and enter your server as
<server name>.<Port#> then press the Test button does the test connection

work?
Interestingly I can do it from outside of Delphi but not in Delphi. If I
create a Test.udl object on my desktop and set the connection up it works
just fine. When I use the Data Link properties editor to create the
connection strign for the TAdoConnection object in Delphi it fails. It's the
same editor. I assume Delphi is just opening up what's in Windows. Maybe
this is a bug in D7.1? Another thing that's interesting about this is that
if I don't use the port# in the Test.udl it also works (my port is
not 1433).

SQL servers default connection mode is named Pipes so my guess is that is that
is being used when you don't specify a port

Post by Keith G Hicks

Post by Brian Bushay TeamB
What version of MDAC are you using and have you tried updating your

version
2.81. I keep it updated when necessary.

Post by Brian Bushay TeamB
You may just have a corrupted MDAC. You can do an internet search and

find

Post by Brian Bushay TeamB
directions to replace your MDAC version

This is doubtful. My brother has the exact same problem on an entirely
different computer running Windows XP (I'm running Win2kPro) 8000 miles from
me.

Well I don't have any problem.
Using Delphi 7.1

Here is a connection string that works for me from design
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data Source=localhost,1434
--
Brian Bushay (TeamB)
***@NMPLS.com

[Keith G Hicks]

Post by Brian Bushay TeamB
SQL servers default connection mode is named Pipes so my guess is that is that
is being used when you don't specify a port

The ODBC connection I created that doesn't specify the port is set to TCP/IP
and it connects fine outside of D7.1. Is that what you are talking about?
-keith

[unknown]

Post by Keith G Hicks

Post by Brian Bushay TeamB
SQL servers default connection mode is named Pipes so my guess is
that is being used when you don't specify a port

The ODBC connection I created that doesn't specify the port is set to TCP/IP
and it connects fine outside of D7.1. Is that what you are talking about?

I was just reading this thread and after seeing the Named Pipes argument I
remembered I once had to put information about the networklibrary used in my
connection string in order to have a succesful connection to a MS SQL
database server.

Adding this assignment to your connection string should have the database
connection use TCP/IP connections:

Network Library=DBMSSOCN;

More info: http://www.connectionstrings.com/

Gert

[Keith G Hicks]

Well I just tried all this on my laptop which is running MDAC 2.7 and it all
works fine. I can create and connect with TadoConnection in design. What
version of MDAC are you running? I wonder if D7.1 has a problem with MDAC
2.81 because that's all I'm left with considering now. And with all the
nightmares MDAC can cause I'm not really considering reverting to an earlier
version on my main PC. The idea of doing that sort of scares me. -keith