[RESOLVED] New to VB.NET and SQLServerCE

[easymoney]

I am sort of an old timer, .NET is somewhat new to me as I am a VB6 leftover. So to help me and maybe others, I am detailing my first experience with .NET and SQLServerCE development on Windows CE 5.0. I have done some reading, but seems pretty confusing with all the versions of Sql and Windows mobile.

My goal in this thread is to programtically create a sql db, add fields and data, and add the ability to add, edit, and query data on my handheld mobile device.

Here is where I started:

Imports System.Data.SqlServerCe

Public Class Form1

    Private Sub cmdCreateDb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateDb.Click
        Try
            Dim engine As New SqlCeEngine("Data Source = \My Documents\test.sdf")
            engine.CreateDatabase()
            MsgBox("Db creation complete!")
        Catch ex As SqlCeException
            MsgBox(ex.Message)
        End Try
    End Sub

End Class

At this point, when I click the button CreateDb, it seem the file 'test.sdf' is created since when I click it twice it says file already exists,

I have two questions at the point:

1) I am testing using the emulator for PocketPC 2003 SE image. Is it possible to see the file on the emulator file system?

2) Is there a way to design Sql tables for SqlServerCe in a visual intercface?

Next, I will attempt to create a table and add fields to the table.

[easymoney]

I added a buttton named CreateTable, with the following code.

    Private Sub cmdCreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateTable.Click

        Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\test.sdf")
        ssceconn.Open()

        Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()

        sqlCreateTable.CommandText = _
          "CREATE TABLE Accounts(Account_id int IDENTITY(0,1) PRIMARY KEY, AcctNo ntext, Address ntext)"
        sqlCreateTable.ExecuteNonQuery()

        ssceconn.Close()

        MsgBox("Table Accounts created.")

    End Sub

I get the following error:

Error	1	Reference required to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=969db8053d3322ac, Retargetable=Yes' containing the base class 'System.Data.Common.DbCommand'. Add one to your project.	C:\Documents and Settings\Admin\Local Settings\Application Data\Temporary Projects\DeviceApplication1\Form1.vb	17	31	DeviceApplication1

Seems I need to add a refernce or Imports statement referring to SqlCeCommand.

Not sure just yet....

[easymoney]

Ok. cool.

I was just introduced to Visual Studio 2005's new features, I clicked on the error message, the IDE automatically added the need reference.

So now the error posted above is solved, the table Accounts is created when I click the button.

Next, write data to the table...

[easymoney]

Okay, now I added another button and some more code to insert the data into the table.

       Private Sub cmdAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddRows.Click

        Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\test.sdf")
        ssceconn.Open()

        Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()
        sqlInsertRow.CommandText = "INSERT INTO Accounts(AcctNo, Address) VALUES('000100', '111 Any Street')"
        sqlInsertRow.ExecuteNonQuery()

        ssceconn.Close()

        MsgBox("Data inserted into table.")

    End Sub

Next task, add a datagrid and a button to load rows from db into the control.

[easymoney]

I added another button to load DataGrid with data from Sql db:

    Private Sub cmdGetRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGetRow.Click

        'Make sure the button cannot be clicked twice
        cmdGetRow.Enabled = False

        Dim objAdapter As SqlCeDataAdapter
        Dim objDataset As Data.DataSet
        Dim strSQL As String

        strSQL = "SELECT * FROM Accounts"

        objAdapter = New SqlCeDataAdapter(strSQL, "Data Source = \My Documents\test.sdf")

        objDataset = New Data.DataSet()
        objAdapter.Fill(objDataset, "Accounts")

        dgAccounts.DataSource = objDataset.Tables("Accounts")

        cmdGetRow.Enabled = True

    End Sub

[easymoney]

Okay. This seems to be pretty straight forward. Honestly, it does not seem to be as hard as it first appears. I think it is time for me to get fimilar with the Visual Studio 2005 IDE in general.

[IamMacro]

If you wanted a visual interface try , using this

http://www.microsoft.com/downloads/d...8-5A0F62BF7796

its a free tool from microsoft and lets you create a SQL CE database file..
After that for inserting test data i recommend using Vs2005 "extra" " connect to database" rest is obvious.

PS: using datasets isnt the fastest way to go , I.e. filling up a combobox for instance.

[easymoney]

If you wanted a visual interface try , using this

http://www.microsoft.com/downloads/d...8-5A0F62BF7796

its a free tool from microsoft and lets you create a SQL CE database file..
After that for inserting test data i recommend using Vs2005 "extra" " connect to database" rest is obvious.

PS: using datasets isnt the fastest way to go , I.e. filling up a combobox for instance.

Thanks, good call. That does the trick.