Easy question for experienced developers

How do I add a previously-made SQL Compact Edition database to my Visual Basic.Net 2005 project inside Visual Studio, and ensure that it gets built into the ClickOnce-deployed solution? Our dba has completed the db design for our project, and I need to add the file to the project I am writing business logic for.

I've been writing software for about 15 years but just now using SQL CE for the first time. I hope my questions are not too simplistic for this forum, but I ran into a problem with something so simple that I'm at a loss, and I thought I'd throw it in this newbie thread.

To wit, what is wrong with this syntax:

INSERT INTO refState(StateName)
VALUES('Alberta')
INSERT INTO refState(StateName)
VALUES('Alaska')

This is a snippet from a T-SQL script I've been using for years that populates reference tables for things like states, countries, etc.

When I try to run the query in Visual Studio 2005 Pro while I have my refState table open in the IDE, I receive the following error:

'Unable to parse query text'.

Then I say to myself, What? Why? So I try to verify the SQL syntax, and then I receive this new error:

'This command is not supported by this provider.'

On the surface it looks like ANSI SQL is out the window. So, I'm guessing I'm missing some fundamental understanding.

Little help? thanx

|||

Well, consider that the Compact Edition is not SQL Server and does not understand TSQL. The problem with your SQL is that the SQLCe engine can process one (and only one) SQL statement at a time. It does not understand the concept of multiple operations or resultsets as supported in TSQL.

In my EBook, I show an example (and a class) used to import schema files that can be used to create a SQLCe database and populate the schema. Gettting data into the database is also a challenge as SSIS does not work well with SQLCe (yet). One approach is to setup Merge Replication with a SQL Server Workgroup (or better) engine, use RDL or the new (unreleased) ADO Synchronization Services. Sure, you can write your own INSERT loop to import data and this would not be that hard to do... code-intensive but doable.

hth

See www.hitchhikerguides.net

|||

Thx for the feedback Bill.

Also, I found what we needed as far as setting up private ClickOnce deployments (which is what we're using) at the following URL:

http://msdn2.microsoft.com/en-us/library/bb219482.aspx

Early issues with Compact Edition

I've just downloaded and installed sql server 2005 compact edition, and I'm having some issues that I'd like to get clarification on. Minor stuff, but irritating.

1. Although you can drop an sql file onto the management studio for a normal mssql connection, you can't do this with a compact edition connection. Instead you have to paste the contents. True? Or am I goofing up somehow?

2. Compact edition doesn't support varchar, but does support nvarchar. True? Or [etc]

3. I can't seem to get compact edition to run a sql script. It seems to only take one command at a time. I'm pretty sure this is a screwup on my part since the tutorials show scripts being run. Are there limitations that I don't know about?

TIA for any help.

Thanks for using SQL Server Compact.

1) We integrate with tools for big products like SQL Server Management Studio, Visual Studio. We have just strated this. Sure, we will take this feedback and improve upon like Drag-N-Drop you are expecting.

2) Yes, we dont support varchar. The world started with ANSI and is now moving to UNICODE. We being new product started now, dont want to reinvent the wheel and started with UNICODE. After all UNICODE is super set of ANSI. And, we have internal techniques to store it in a compact manner. So, no plans to support varchar/char/text in future too.

3) SQL Compact does not support batch queries. However, in the tools you should not experience it at tools level like SSMS/VS. If you select multiple SQL queries, there is a batch processor in these tools which automatically splits the batch into individual queries and runs them. However, all of them are exected in a single transaction.

Thanks,

Laxmi

each time I build program, data is lost

I am using visual basic 2008

I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,

when I close the program and build again, the data previosly updated are deleted, and I get empty database? why is that. do i need to set some copy to.........properties. i have used copy if new.

I want to add something to it, that when I manually entered data, they are not gone. But when I entered from form, next time the data lost.|||

See this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2115234&SiteID=1