Page 1 of 1 1
Topic Options
#43523 - 2003-07-29 03:52 PM SQL database connection
JohnQ Offline
Starting to like KiXtart

Registered: 2003-03-04
Posts: 171
I am using the following code to make a connection to a SQL database. The code works fine on 98% of the PCs that I've run it on. The problem is, on a couple of PCs, the DB connection is never made. I know this because I can query for something that I know exists and an empty recordset is returned.

How can I check to see if the DB connection fails. It currently does not return any errors and doesn't affect the rest of the script, it just doesn't make the connection.

code:
  $DSN="DRIVER=SQL Server;SERVER=myserver;UID=me;PWD=mypwd;DATABASE=mydb"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")

$Connection.ConnectionString = $DSN
$Connection.Open()
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command

$Command.CommandText = "SELECT name FROM tbl_things where animal = 'dog'"
$Recordset.Open($Command)
If $Recordset.RecordCount >= 1
If $Recordset.Fields("animal").Value > ""
$rc = "found an name"
Else
$rc = "no name found"
EndIf
$Recordset.Close()
Else
$rc = "no records found" ;empty recordset
EndIf
$Connection.Close()
$Connection = 0
$Recordset = 0
$Command = 0


Top
#43524 - 2003-07-29 03:57 PM Re: SQL database connection
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
You can use the @ERROR codes to check whether the object creation and parameter assignments were successful. See also the UDF Forum, e.g. DBCommand() - Executes a SQL statement and returns a recordset if applicable
_________________________
There are two types of vessels, submarines and targets.

Top
#43525 - 2003-07-29 03:57 PM Re: SQL database connection
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
put ? " 1 "+ @serror
between every line to see where it fails

increment the 1 as well
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#43526 - 2003-07-29 05:15 PM Re: SQL database connection
JohnQ Offline
Starting to like KiXtart

Registered: 2003-03-04
Posts: 171
When the Connection.Open() executes, I receive the following error:

COM exception error "Open" (Microsoft OLE DB Provider for ODBC Drivers - Specified driver could not be loaded due to system error 126 (SQL Server).) [-2147352567/80020009]

I have been searching MS support, but haven't found anything yet.

Top
#43527 - 2003-07-29 05:17 PM Re: SQL database connection
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
The ODBC driver for SQL Server is not installed on that system.
_________________________
There are two types of vessels, submarines and targets.

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 145 anonymous users online.
Newest Members
GBMicros, Jodey, Hiker, GTRoberts, KatieQuinn
17685 Registered Users

Generated in 0.029 seconds in which 0.011 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org