Page 1 of 1 1
Topic Options
#87431 - 2002-08-23 05:06 PM DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
I'm using 4.11b and I'm having a problem opening an access DB.

code:
	$DATABASE   = "\\a06\logon\inventory\inventory.mdb"
$DSN = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
$Connection = CreateObject("ADODB.Connection")
? @error @serror
$Command = CreateObject("ADODB.Command")
? @error @serror
$Recordset = CreateObject("ADODB.Recordset")
? @error @serror
? "inventory $Connection"
if $Connection
? @error @serror "I'm in" ;Check for connection object
$Connection.ConnectionString = $DSN

generates
code:
...
0The operation completed successfully.
0The operation completed successfully.
0The operation completed successfully.
inventory
...

it never validates the $connection

Any Ideas??
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87432 - 2002-08-23 05:22 PM Re: DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
more detail...

code:
	$DATABASE   = "\\a06\logon\inventory\inventory.mdb"
$DSN = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")
; if $Connection ;Check for connection object
$Connection.ConnectionString = $DSN
$Connection.Open() ;Open connection to database
? @error @serror
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command

? @error @serror
$Command.CommandText = "SELECT * FROM [tbl_Users] WHERE NTAccount='@userid'"
$Recordset.Open($Command)
? @error @serror
If $Recordset.RecordCount < 1
$Recordset.AddNew
$Recordset.Fields("NTAccount").Value = @USERID
$Recordset.Fields("FirstName").Value = $firstname
$Recordset.Fields("LastName").Value = $lastname
$Recordset.Fields("OrgCode").Value = $OrgCode
$Recordset.Fields("PositionNumber").Value = $positionnum
$Recordset.Fields("PhoneNumber").Value = $phoneno
$Recordset.Fields("RoomNumber").Value = $roomno
$Recordset.Fields("Office").Value = "Area$area"
$Recordset.Update
? @error @serror
endif
$Recordset.Close()

returns
code:
...
0The operation completed successfully.
0The operation completed successfully.
-2147352567COM exception error "Open" (ADODB.Recordset - The connection cannot b
e used to perform this operation. It is either closed or invalid in this context
.) [-2147352567/80020009]
...

_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87433 - 2002-08-23 05:30 PM Re: DB open problem...
Waltz Offline
Seasoned Scripter

Registered: 2002-08-01
Posts: 485
Loc: Waterloo, Ontario, Canada
Do you get the error under kix4.02?
_________________________
We all live in a Yellow Subroutine...

Top
#87434 - 2002-08-23 05:35 PM Re: DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
nope....
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87435 - 2002-08-23 05:39 PM Re: DB open problem...
Waltz Offline
Seasoned Scripter

Registered: 2002-08-01
Posts: 485
Loc: Waterloo, Ontario, Canada
This snippet works for me with an Access 97 DB...
code:
; write to mytable
$db = "mypathto\mydatabase.mdb"
$cn = createobject("adodb.connection")
$cn.connectionstring = "data source=$db; provider=microsoft.jet.oledb.4.0;persist security info=false"
$cn.open()
$rs = $cn.execute("insert into mytable (SysTag,SysNam,SysOwner) values ('$tag','$cname','$uname')")
IF @error <> 0
$inserterr=@error
$insertflag = 0
ELSE
$insertflag = 1
ENDIF
$cn.close()
$cn = 0
;

Cheers...

[ 23. August 2002, 18:38: Message edited by: Waltz ]
_________________________
We all live in a Yellow Subroutine...

Top
#87436 - 2002-08-23 05:40 PM Re: DB open problem...
Waltz Offline
Seasoned Scripter

Registered: 2002-08-01
Posts: 485
Loc: Waterloo, Ontario, Canada
under kix4.10a...
_________________________
We all live in a Yellow Subroutine...

Top
#87437 - 2002-08-23 06:42 PM Re: DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
under 4.02 this works fine but fails under 4.11b
code:
	$DATABASE   			= "$logon\inventory\inventory.mdb"
$Connection = CreateObject("ADODB.Connection")
$Connection.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
$Connection.Open()
if not @error ;Check for connection object
$Command = CreateObject("ADODB.Command")
$Command.ActiveConnection = $Connection
$Recordset = CreateObject("ADODB.Recordset")
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command

$Command.CommandText = "SELECT * FROM [tbl_Users] WHERE NTAccount='@userid'"
$rc=$Recordset.Open($Command)
? @serror
If $Recordset.RecordCount < 1
$Recordset.AddNew
$Recordset.Fields("NTAccount").Value = @USERID
$Recordset.Fields("FirstName").Value = $firstname
$Recordset.Fields("LastName").Value = $lastname
$Recordset.Fields("OrgCode").Value = $OrgCode
$Recordset.Fields("PositionNumber").Value = $positionnum
$Recordset.Fields("PhoneNumber").Value = $phoneno
$Recordset.Fields("RoomNumber").Value = $roomno
$Recordset.Fields("Office").Value = "Area$area"
$Recordset.Update

it fails at line before the @error statement with.

COM exception error "Open" (ADODB.Recordset - The connection cannot be used to perform this operation. It is either closed or invalid in this context.) [-2147352567/80020009]
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87438 - 2002-08-23 08:10 PM Re: DB open problem...
Waltz Offline
Seasoned Scripter

Registered: 2002-08-01
Posts: 485
Loc: Waterloo, Ontario, Canada
It's a known COM issue with kix41x. kix402 handled COM differently than later versions.

I believe sealeopard is currently working on his suite of UDFs for DB handling to try to resolve this issue.
_________________________
We all live in a Yellow Subroutine...

Top
#87439 - 2002-08-26 08:45 PM Re: DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
no change with 4.11 final...

arrgggghhhhh
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87440 - 2002-08-26 11:01 PM Re: DB open problem...
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
I found this on the MS web site

What are all these other parameters?

objRecordset.Open "SELECT * FROM Hardware" , objConnection, adOpenStatic, adLockOptimistic
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#87441 - 2002-12-05 01:44 AM Re: DB open problem...
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Standard parameters, opens a connection with a static cursor and uses opportunistic table locking when updating a table. You can utilize other locks so that for example only one connection can be made to a table at any time. They are VBScript constants. Check out my DBGetRecordset ( http://81.17.37.55/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=12;t=000197 )

[ 05. December 2002, 01:44: Message edited by: sealeopard ]
_________________________
There are two types of vessels, submarines and targets.

Top
#87442 - 2002-12-06 07:08 PM Re: DB open problem...
FourEyes Offline
Lurker

Registered: 2002-12-06
Posts: 1
Loc: PORTUGAL
Donīt know if you solved that recordset problem...but if not try this:

change your connectionstring to:
Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=$db

It worked for me!!
_________________________
Work smarter, not harder...

Top
#87443 - 2002-12-06 07:11 PM Re: DB open problem...
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
fourEyed-man,
I think this problem was just kixtart version related.

once 4.12 released, have not seen error complains.
_________________________
!

download KiXnet

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 259 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.066 seconds in which 0.026 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