Connect to SQLCE

[foxbox]

I want to fill a MS SQL Mobile (SDF) database with records from a DBF file.
So i hoped this is working:


oConnSDF=Createobject("ADODB.Connection")
oConnSDF.open("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myfile.sdf;")

USE SOURCE
DO WHILE NOT EOF()
cSQL = "INSERT INTO target(field1,field2) values('" + ;
source->veld1 + "','" + ;
source->veld2 + "')"

oConnSDF.Execute( cSQL)

SKIP 1
ENDDO


but this results in a cryptic error message: "OLE IDispatch exception code 0 from Microsoft SQL Server Compact Edition OLE DB Provider: the command contained one or more erorrs. [,,,,,],,"


Maybe my approach is all wrong?

 

[Mike Lewis]

Foxbox,

No, your approach isn't wrong, but your command might be.

Why not suspend the program just before you do oConnSDF.Execute( cSQL), and examine the contents of the cSQL variable.

If that doesn't help, copy the contents of the variable into this thread, and we can take a look.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Check

http://www.connectionstrings.com/?carrier=sqlserver2005ce

to see how the connection string should look like.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[foxbox]

Mike:

cSQL = "INSERT INTO Leden(llidnr,lnaam,ltelf,ltelf2) values('0001','SENHORST J.P.','0313-631304','')"

and yes: llidnr is a char field


Borislav:
the connection is oke, de .SDF file is in the same folder as de .DBF's and .EXE.

 

[bborissov]

I just didn't saw the path to the DB
What if you use parameters instead of direct values?
Also did you try this:

USE SOURCE
SCAN
 TEXT TO lcSQL NOSHOW PRETEXT 15
      INSERT INTO target (field1,field2)
       values ('<<STRTRAN(source.veld1,['],[''])>>',
               '<<STRTRAN(source.veld2,['],[''])>>')
 ENDTEXT
 oConnSDF.Execute(lcSQL)
ENDSCAN

Also: how many fields [target] table has?
Did you list ALL of them into INSERT statement?
If not did others have default values?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Mike Lewis]

Foxbox,

The command look OK to me. Do you have any way of executing it directly in SQL Server Mobile? (In the full SQL Server, you could paste it into the query analyzer.) If you can do that, you should get a more informative error message.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[foxbox]

<blush>
yes, that showed a misspelling in a fieldname. beginners mistake! completely fooled by that OLE IDispatch exception message i was looking the wrong way.
</blush>