Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Error connecting to a SQL Compact (CE) database


  • Please log in to reply
3 replies to this topic

#1 xavierh

xavierh

    Seeker

  • Active Members
  • 30 posts

Posted 07 June 2011 - 09:24 PM

The database file exists, and I can connect to it and read its data from C#.
But I also need to access the data from autoit. I have tried variants of the provider string below, but the connection just fails to open.
Dim $sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=Microsoft.SQLSERVER.OLEDB.CE.3.5;Data Source=C:\Auto\tests.sdf;Password=test")

Has anyone managed to connect to a sql ce database?

Thanks,

Xavier







#2 PsaltyDS

PsaltyDS

    Most Venerable Penguin

  • MVPs
  • 13,279 posts

Posted 09 June 2011 - 03:48 PM

Isn't that provider a .NET library? AutoIt can't use that. Do you have any examples of connection strings for SQL CE that work with ADODB in VBScript or some other non-.NET source?

:huh2:
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

#3 xavierh

xavierh

    Seeker

  • Active Members
  • 30 posts

Posted 10 June 2011 - 04:57 PM

I finally got it to work as long as the db is not encrypted (ie it was not created with a password). If you add a password to the db when created from C#, both vbs and autoIT fail to connect.
Here are working samples
vbscript:
set oCon = createobject("ADODB.Connection")
set oRS = createobject("ADODB.Recordset")
oCon.Open "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\Auto\PPDTM\PPDTM\PPDTM\bin\Debug\tests.sdf"
oRS.CursorLocation = 3 'adUseClient
oRS.Open "select * from PSMWinPrinters", oCon, 3, 3 'adOpenStatic, adLockOptimistic
msgbox oRS.recordcount & " records"
msgbox oRS.Fields(1).Value
oRS.Close
oCon.Close
AutoIT script:
Dim $sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\Auto\PPDTM\PPDTM\PPDTM\bin\Debug\tests.sdf")
Dim $oRS = ObjCreate ( "ADODB.Recordset" )
$oRS.open ( "select * from PSMWinPrinters", $sqlCon, 1, 1, 1 ) ;$adOpenKeyset, $adLockOptimistic=3, $adCmdText )
msgbox(0,"", $oRS.recordcount & " records")

#4 PsaltyDS

PsaltyDS

    Most Venerable Penguin

  • MVPs
  • 13,279 posts

Posted 10 June 2011 - 04:59 PM

Thanks for reporting your resolution.

Cheers!

:huh2:
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users