Compact SQL DB creation ?

[JohnSavage]

I want to create a SQL Compact Database to use with my app but when trying to do it with the IDE (2005) I get all sorts of strange messages (like this cannot be the final method (of creation) etc) and it will not let me do it.

I have searched this forum and all of the examples appear to be in the 2003 version of dot net and when I have tried to copy the code it does not work with my 2005 IDE, I even tried to rename the Northwind database example that comes with the Compact Database files and then connect to that but you guessed it that did not work either

There surely must be a simple way to do this ? Can somebody please point me in that direction?

[si_the_geek]

SQL Server CE is very new... it was released a long time after VS2005, so it is not entirely surprising that VS2005 doesn't know how to deal with it properly.

Have you tried downloading the latest version of SQL Server Management Studio, and using that to create the database?

[JohnSavage]

Just done it and well as you can see below at least the error message is different


And if you really want a good chortle check out the linked message


TITLE: Connect to Server
------------------------------

Cannot connect to YOUR-D6DCA04BF3\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

[szlamany]

That is connecting to SQL SERVER EXPRESS - you do not connect to a server when using CE - you connect to the DATABASE itself.

[JohnSavage]

szlamany

But thats my problem I have not got a Database to connect too and am trying to create one !

[szlamany]

Well - that certainly doesn't explain why you are connecting to a SQLEXPRESS instance!

I'm also guessing you did not download the SQL CE install from MS - probably the reason why you aren't getting any SQL CE action from the IDE. This thread talks about the download

http://www.vbforums.com/showthread.p...ghlight=sql+ce

This one talks about encryption with SQL CE

http://www.vbforums.com/showthread.p...ghlight=sql+ce

At any rate - SQL CE databases are created in code like this. We have an extension of .dlh for our SQL CE DB's - so that people will not pick up on the fact that it is in fact a database (dlh=don't look here).

Dim dse As New SqlCeEngine("DataSource=" & strBaseFolder & "\APC.dlh; Password=" & strPassword & ";encrypt database=TRUE")
Try
    lblStatus.Text = "Creating database"
    lblStatus.Refresh()
    dse.CreateDatabase()
    Dcn.Open()
    lblStatus.Text = ""
    lblStatus.Refresh()
    If LoadDatabase(Dcn, LDcn) = False Then
        Dcn.Close()
        Dcn.Dispose()
        System.IO.File.Delete(strBaseFolder & "\APC.dlh")
        Cursor.Current = Cursors.Default
        MessageBox.Show("Database did not sync!")
        OpenDatabase = False
        Me.Close()
        Exit Function
    End If
    txtInput.PasswordChar = ""
Catch ex As SqlCeException
    MessageBox.Show(ex.Message)
Catch ex As SqlException
    MessageBox.Show(ex.Message)
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

There is no SERVER to attach to - or open the SQL CE DB against - it's open by your MOBILE APP process.

This is how we create objects in that database.

        lblWhat.Text = "Syncing..."
        lblWhat.Refresh()
        Drc.Connection = Dcn1
        Drc.CommandType = Data.CommandType.Text
        Drc.CommandText = "Create Table APC (Created nvarchar(50))"
        booError = False
        Try
            Drc.ExecuteNonQuery()
        Catch ex As Exception
            booError = True
            MessageBox.Show(ex.Message)
        End Try

[JohnSavage]

szlamany,

Your guess that I did not have SQL CE installed was wrong I do, the one I cannot install is SQL Server, I keep getting the message that I have some incompatible beta software installed ?

So I have spent all of today un-installing all of my dot net software and then re-installing it only to find that I still get the same message so I have given up one that one.

Now to your help:

What imports do I need other than "Imports System.Data.SqlServerCe" ?

"lblStatus" is a label

"strBaseFolder" is just a text variable statement to define the folder position ?

What in your code makes the database a CE compact one instead of a SQL normal one (whatever that is) ?

I am hoping that once I have created it that I will then be able to use the IDE to change and modify it ?

Can you use any file extention eg XML, EXE, TXT etc ?

[szlamany]

What imports do I need other than "Imports System.Data.SqlServerCe" ?

The only two imports I have are:

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient

I have SqlClient because the PPC opens a network SQL database to copy the data to the SQL CE database on the PPC.

"lblStatus" is a label

- yes

"strBaseFolder" is just a text variable statement to define the folder position ?

- yes

    Public strBaseFolder As String = "\Storage Card"
    'Public strBaseFolder As String = "\Program Files\APC"

What in your code makes the database a CE compact one instead of a SQL normal one (whatever that is) ?

The use of the "New SqlCeEngine" to create the database. Note how that code creates a database similar to how you would create any old file - not through a reference to a server or anything like that at all.

I am hoping that once I have created it that I will then be able to use the IDE to change and modify it ?

I'm not sure of that - I'll make a second post after this one with my attempts to use VS 2005 to make the SDF file.

Can you use any file extention eg XML, EXE, TXT etc ?

The normal extension is .SDF

[szlamany]

Here is what I just did to make a SDF with VS 2005.

Create new project - Visual Basic - Smart Device - Pocket PC 2003

Add New Data Source - "Database" - Next

[New Connection]

Data Source already says "MS SQL Server Mobile Edition (..."

[Create] - call the file Test - I left the password blank...

It expands that out to Test.sdf

[Test Connection] - this succeeded

I clicked OK and answered YES to make the SDF part of the solution - now it's in the Solution Explorer.

If I select it I get the database in the Server Explorer - and I can expand the Tables tree and create new tables and everything else.

This seems to work for me.

[JohnSavage]

Many thanks for all the help, my problem is that despite the fact that I have installed SQL CE it does not show up in the IDE list of database types.

I will have to try your code solution!

[szlamany]

I don't give up so easily

Are you saying you followed my steps up to the Data Source not mentioning MS SQL server mobile edition??

[JohnSavage]

I don't give up so easily

That’s good to hear and if you will stay the course I plan to make the most of your kind advice – here goes!

You ask “Are you saying you followed my steps up to the Data Source not mentioning MS SQL server mobile edition??”

Yes that was the case but after 18 hours of trying installing and re-installing both ce compact database and totally un-installing and reinstalling the 2005 IDE and then repairing it twice I have got the MS SQL server mobile edition to show up in the list and yes now after following your instructions I have finally managed to create a database.

I cannot get the full blown SQL database to install as I always get the message that it will not as a result of some beta software or earlier editions are installed but they are not? So it’s a shame but at the moment I don’t need that so I will have to give up on that one as I cannot do any more than I have done to date!

So back to your advice and first your code example:

1.You say “I have SqlClient because the PPC opens a network SQL database to copy the data to the SQL CE database on the PPC” This is done automatically by the system and I have know need to manage this action?

2.If I am correct in “1” above then for all PPC app’s the only two imports required for working with a database are “SqlServerCe” and “SqlClient”

3.Working the way down the code errors on my machine Dcn-is not declared? LoadDataBase-is not declared? OpenDataBase- is not declared? TxtInput-is not declared – now this one I would guess is a text box so that you can input some data in the created database?

Now back to some general questions about using and working with ce databases in code

1.Is there an “app path” function like VB6 so I can use is in my code?

2.How do I “in code” add a column to a table?

3.How do I “in code” add a new record?

4.How do I “in code” populate this new record?

5.How do I “in code” get a value?

6.How do I “in code” write a value?

Now given that a PPC app is always running what is your advice regarding opening and closing connections? In VB6 I would open a connection in the form load event and then only closing it when the user closed the app.

Finally

1.The code for opening a database connection?

2.The code for closing the database connection?

3.The code for opening and closing a recordset (dataset?)

[szlamany]

1.You say “I have SqlClient because the PPC opens a network SQL database to copy the data to the SQL CE database on the PPC” This is done automatically by the system and I have know need to manage this action?

It is not done automatically - we grab recordsets from the SERVER SQL box and load them into the SQL CE DB on the PPC

Dim LDcn As New SqlConnection
LDcn.ConnectionString = "Data Source=" & APCSettings.sServer _
                                            & "; Initial Catalog=" & APCSettings.sDatabase _
                                            & "; Integrated Security=SSPI; User Id=" _
                                            & APCSettings.sUserId & "; Password=" & strPassword
            Try
                LDcn.Open()
            Catch ex As SqlException
                MessageBox.Show(ex.Message)
                Exit Function
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Exit Function
            End Try

That code opens the SQL SERVER DB on the network. Note the really important thing here is that you can specify SSPI security along with a WINDOWS USERNAME and PASSWORD.

2.If I am correct in “1” above then for all PPC app’s the only two imports required for working with a database are “SqlServerCe” and “SqlClient”

Those are the only two IMPORTS I have in my small PPC app.

3.Working the way down the code errors on my machine Dcn-is not declared? LoadDataBase-is not declared? OpenDataBase- is not declared? TxtInput-is not declared – now this one I would guess is a text box so that you can input some data in the created database?

Dcn is just a connection object - LoadDatabase and OpenDatabase are just functions I wrote to do just those tasks. Here's what the top of the .vb file looks like in the IDE

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient

Public Class APC

    Public connId As Long
    Public strPassword As String = ""
    Public booLoading As Boolean
    Public Dcn As New SqlCeConnection
    Public APCSettings As New Settings
    Public strLastServer As String = ""
    Public strLastDatabase As String = ""
    Public strLastUserId As String = ""
    Public strLabelText As String = ""
    Private Drc As SqlCeCommand
    Private Dse As SqlCeEngine
    Private LDrd As SqlDataReader

    Public strBaseFolder As String = "\Storage Card"
    'Public strBaseFolder As String = "\Program Files\APC"

    Public lngOpenAttempt As Long

1.Is there an “app path” function like VB6 so I can use is in my code?

I'm not sure - we actually force stuff into specific folders. You might want to post that as a separate thread and ask for others advice and experience.

2.How do I “in code” add a column to a table?

I showed how to CREATE a table in a prior post in this thread - CREATE TABLE APC with a single column. To add a column to a table you use the ALTER TABLE statement - is that what you meant??

3.How do I “in code” add a new record?

This inserts a new record into a table

        Drc.Connection = Dcn1
        Drc.CommandType = Data.CommandType.Text
        Drc.CommandText = "Insert into APC values ('" & strDateTime & "')"
        booError = False
        Try
            Drc.ExecuteNonQuery()
        Catch ex As Exception
            booError = True
            MessageBox.Show(ex.Message)
        End Try

4.How do I “in code” populate this new record?

Isn't adding a new row and populating it the same? We do that in one step.

5.How do I “in code” get a value?

This gets a whole bunch of rows and puts them into a list view.

        Dim Drc As New SqlCeCommand
        Dim Drd As SqlCeDataReader
        Drc.Connection = Dcn
        Drc.CommandType = Data.CommandType.Text
        Drc.CommandText = "Select CL.Period, CL.Days, CL.Semester, CR.Descr, CL.Class From Class CL" _
            & " Left Join Course CR on CR.Class=CL.Class" _
            & " Where StuId=" & strId _
            & " Order by Period, Days, Semester"
        Drd = Drc.ExecuteReader()
        While Drd.Read
            Dim lwi As New ListViewItem
            lwi.Text = Drd(0)
            lwi.SubItems.Add(Drd(1).ToString)
            lwi.SubItems.Add(Drd(2).ToString)
            lwi.SubItems.Add(Drd(3).ToString)
            lwi.Tag = Drd(4)
            lstClass.Items.Add(lwi)
        End While

6.How do I “in code” write a value?

Don't know what you mean by WRITE a value...

Now given that a PPC app is always running what is your advice regarding opening and closing connections? In VB6 I would open a connection in the form load event and then only closing it when the user closed the app.

It's not a network connection - you need to forget about all the rules and arguments that people have about network connections and opening and closing them. It's a local database open by your app in it's own processor space. You leave the connection open always.

1.The code for opening a database connection?

2.The code for closing the database connection?

3.The code for opening and closing a recordset (dataset?)

I think I've already shown those - if you need something more specific please post back.

[JohnSavage]

szlamany,

I have a problem again with the 2005 IDE I have just posted on the database forum if I get it working I will be back to this thread - thanks again

[JohnSavage]

Finally I have sorted my IDE and created a database with a table, whilst doing this a dataset was also created, it is in the list of files in my project ? I DON’T KNOW HOW OR WHY BUT WHO CARES! And I can delete it if it’s not needed.

Now all I need to know is how do I do this in SQL CE Compact? All the help and video help I have looked at just shows datagrids and how to use them which is not what I want to do.

Set Connect = New ADODB.Connection
Connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\maxaa.mdb;Jet OLEDB:Database Password=Open Operation;"
Connect.Open
Set RS = New ADODB.Recordset
RS.Open "PureT", Connect, adOpenKeyset, adLockPessimistic, adCmdTable
RS.MoveFirst

'GET VALUES SAVED FROM DATABASE
        NF = CDbl(Encrypt(RS.Fields("8").Value, KeyJs))
RS.Close
        Set RS = Nothing

‘SAVE VALUES TO DATABASE
Set RS = New ADODB.Recordset
        RS.Open "PureT", Connect, adOpenKeyset, adLockPessimistic, adCmdTable
        RS.MoveFirst
        RS.Fields("6").Value = Encrypt((CStr(JF)), KeyJs)

RS.Update
RS.Close
Set RS = Nothing