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

Dynamically retrieve the name of all the parameters for a report.

Does anyone know, if you are inside the body of a report, of a way to have a
textbox dynamically populated with the name of all the parameters that are on
that report?
So, the idea is, this textbox in the body of the report would have some
expression, and that expression would always return the names of all
parameters that are defined for the report. So, if someone changes the report
to use have a new parameter, this textbox would automatically show the name
of that new parameter.
Any help would be greatly appreciated.
Thanks.Sorry, this is not supported at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Barzilli" <Mike Barzilli@.discussions.microsoft.com> wrote in message
news:D21AC308-F48C-4F68-B189-4737E03DFEC1@.microsoft.com...
> Does anyone know, if you are inside the body of a report, of a way to have
> a
> textbox dynamically populated with the name of all the parameters that are
> on
> that report?
> So, the idea is, this textbox in the body of the report would have some
> expression, and that expression would always return the names of all
> parameters that are defined for the report. So, if someone changes the
> report
> to use have a new parameter, this textbox would automatically show the
> name
> of that new parameter.
> Any help would be greatly appreciated.
> Thanks.

Dynamic SQL statements in a stored procedure

Hi

I have a small problem writing a stored procedure in a SQL Server 2000 database.

I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server dont generate a resulting SQL-statement.

Example:

CREATE PROCEDURE spDynStatement
AS

DECLARE @.sPartOfSQLStatement NVARCHAR(100)

-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .

-- As substitute I insert the string expression
SET @.sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''

-- SELECT @.sPartOfSQLStatement results in: '1' , '1.5'

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @.sPartOfSQLStatement ) -- does not work

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO

Thankfull for ideas on how to solve my problem,

PeterTry this

DECLARE @.sSQLStatement VARCHAR(4000)

SET @.sSQLStatement = 'SELECT * FROM BBNOrganization WHERE OrgStructureID IN(' + '''1''' + ', ' + '''1.5''' + ')'

EXEC @.sSQLStatement