DataSource on windows mobile 6 (cant connect to sqlce database file [sdf])

[SGiorgos]

Hi,
I am currently developing an application using vb.net 2005 and windows mobile 6 sdk,and sql server ce for my database.

I try to connect to the database i created using the following code: I get an identifier expected error
pointing the dot[.] .The code is this ("Data Source ="[B].[/B]\ MyDatabase.sdf;"") Visual Basic.Net 2005. The whole class is the following:

Dim conn As SqlCeConnection = Nothing

Try
    conn = New SqlCeConnection("Data Source =".\ MyDatabase.sdf;"")
    conn.Open()

    Dim cmd As SqlCeCommand = conn.CreateCommand()
    cmd.CommandText = "INSERT INTO Customers (Customerid], Name) Values('9', 'Smith')"

    cmd.ExecuteNonQuery()
Finally
    conn.Close()
End Try

[williamrojas78]

Hi

I think the problem is that the syntax of your command is wrong.
You have:

conn = New SqlCeConnection("Data Source =".\ MyDatabase.sdf;"")

it should be:

conn = New SqlCeConnection("Data Source =.\ MyDatabase.sdf")

I use this and it works.

dim data_base As String = "\Program Files\My App\MyDB.sdf"

    dim dap As SqlCeDataAdapter = New SqlCeDataAdapter
    dim conn As SqlCeConnection = New SqlCeConnection("Data Source=" & data_base)
    dim cmnd As SqlCeCommand = New SqlCeCommand("SELECT FROM table", conn)

   conn.open()
   ...

Hope it helps

[SGiorgos]

"\Program Files\My App\MyDB.sdf"

is this relative to the windows mobile path(emulator) or the local computer path ?

[williamrojas78]

This is relative to the PPC (mobile device) path.
That is the location of the database in the PPC - I use the same directory as my application.

[SGiorgos]

Hi, Thank you . Now the path seems to work. The other problem is I cant add data to the database.

The code I use is:

Dim MyConnection As SqlCeConnection
        Dim sqlstring As String = "INSERT INTO Titles VALUES ('2','1','0')"

        Dim data_base As String = "\Program Files\vbdata\vbdata.sdf"

        MyConnection = New SqlCeConnection("Data Source=" & data_base)

        Dim concom As SqlCeCommand = MyConnection.CreateCommand()

        Dim cmnd As SqlCeCommand = New SqlCeCommand(sqlstring, MyConnection)

        Dim myreader As SqlCeDataReader

        MyConnection.Open()

        myreader = cmnd.ExecuteReader


        MyConnection.Close()

Thanks again.

[williamrojas78]

Hi
your code is not executing the command (i think); try this:

Dim MyConnection As SqlCeConnection
        Dim sqlstring As String = "INSERT INTO Titles VALUES ('2','1','0')"

        Dim data_base As String = "\Program Files\vbdata\vbdata.sdf"

        MyConnection = New SqlCeConnection("Data Source=" & data_base)

        Dim concom As SqlCeCommand = MyConnection.CreateCommand()

        MyConnection.Open()        
        Dim cmnd As SqlCeCommand = New SqlCeCommand(sqlstring, MyConnection)
        
        cmnd.ExecuteNonQuery()        ' Execute the command here

        'and then read
        Dim myreader As SqlCeDataReader
 
        myreader = cmnd.ExecuteReader

        MyConnection.Close()

[SGiorgos]

hi,
I have also tried the code you provided with no luck again :/

[williamrojas78]

Hi

I just noticed that you are missing the field names in the sql command, you have:

Dim sqlstring As String = "INSERT INTO Titles VALUES ('2','1','0')"

and it should be:

Dim sqlstring As String = "INSERT INTO Titles(fileld0, field1, field2)  VALUES ('2','1','0')"

I just tried it on my PPC and it works. I also noticed that you the command specifies all the values as strings, but they are numbers. Make sure you input the values in the right format, otherwise you might have an error.

[SGiorgos]

thnx a lot, everything is working like a charm :)

The net thing now is how to query the emulators database :p ( i mean through visual basic query manager ) . Because when i insert data through the emulator and then close the emulator the database is not stored anywhere .

thnx again :)

[clj1973]

I am trying to do a similar thing with VB 9 and win mobile 6.

when the button is clicked i want to run a query against northwind.sdf.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim data_base As String = ".\northwind.sdf"
Dim dap As SqlCeDataAdapter = New SqlCeDataAdapter
Dim conn As SqlCeConnection = New SqlCeConnection("Data Source=" & data_base)

'The query i want to run
Dim cmnd As SqlCeCommand = New SqlCeCommand("SELECT [English Name]FROM Products WHERE ([Product Name] = N'ikura') AND ([English Name] = N'fish roe')", conn)

conn.Open()

'here it want the result of the query to be displayed in textbox1.text

conn.Close()
End Sub

Thanks