SQL Mobile - Basic functions...

[magohn]

Hello All,
I have manage to put together an application and then added a database (SQL Mobile). I have performed hours of searching without finding an answer to the following: Is it possible somebody could post the following:

If my database is called "Person" and has a 'name' column and an 'age' column, how do I do the following:

INSERT a name

INSERT an age

DISPLAY all in the name columen where and age > 30

DELETE a name

DELETE an age

Also, do I need to add any kind of 'include' to allow access to my database from the main form?

Using VB 2008.

Thanks much, magohn

[pmd13]

There is actually quite a bit of reference available on MSDN. Below is something you might be looking for. I think there might be an easier way, but i'm still pretty new at this. It works for me though.
http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand(VS.80).aspx



Imports System.Data.SqlServerCe


'To Write to DB
Public Sub WriteDatabase(ByVal a, ByVal b)
Dim conn As SqlCeConnection = Nothing
Dim cmd As New SqlCeCommand
'Save transaction to Database
Try
conn = New SqlCeConnection("password = 'password'; Data Source = 'DBname.sdf'")
conn.Open()

cmd1 = conn.CreateCommand()
cmd1.CommandText = "INSERT INTO Person (name, age) VALUES (" & a & ",'" & b & "')"
cmd1.ExecuteNonQuery()

Finally
conn.Close()
End Try
End Sub



Dim conn As SqlCeConnection = Nothing
Dim rdr As SqlCeDataReader = Nothing
Dim val1 = ""
Try
conn = New SqlCeConnection("password = 'password'; Data Source = 'DBname.sdf'")
conn.Open()

'Select record with age > 30
Dim cmd As New SqlCeCommand("SELECT name FROM Person WHERE age > 30", conn)

rdr = cmd.ExecuteReader()

While rdr.Read
val1 = rdr.GetValue(0)
End While

Finally
rdr.Close()
conn.Close()
End Try

[magohn]

Thank you pmd13 - I truly appreciate the time you took to respond. I will go give your suggestions a try! :)

[magohn]

Hi pmd13,

I tried the insert code but I get a debug error at the line that specifies my .sdf name:

conn1 = New SqlCeConnection("password = 'password'; Data Source = 'MyDB.sdf'")

The path is not valid. Check the directory for the database. [ Path = MyDB.sdf ]

I have tried replacing with the following but no luck so far:

/MyDB.sdf
\MyDB.sdf
/My App/MyDB.sdf (where My App is the name of the main folder
\MyDB.sdf\MyDB.sdf

This error occurs after the application is deployed to the emulator but when I try to access the form where the code is located. The DB exists in the root of the folder on the emulator and the names match.

Any help appreciated.

Thanks much, Magohn.

I have tried the following

[pmd13]

Try moving the db file to your root directory. It should be with the other folders: Program Files, Temp, Windows, etc that way you don't need to add any slashes or anything. Then it would be Data Source = 'MyDB.sdf'

If that doesn't work try running it live on your phone

[magohn]

Try moving the db file to your root directory. It should be with the other folders: Program Files, Temp, Windows, etc that way you don't need to add any slashes or anything. Then it would be Data Source = 'MyDB.sdf'

If that doesn't work try running it live on your phone

Hoorah! Its working for insert on the emulator - here was my issue (HTH others). My path was:

conn1 = New SqlCeConnection("password = 'password'; Data Source = 'MyDB.sdf' ")

Note that I incorrectly had single quotes around my DB name. I also changed to the full path:

conn = New SqlCeConnection("password = ''; Data Source = \program files\SmartDeviceProject6\MyDB.sdf")

and (dont know if this matters) changed the DB to always copy over to the device from Solution Explorer.

I ran the program and from the emulators 'Query Analyzer' could see that my two values were added to my database!

Thanks you so much for your help - this new tip will keep me busy all weekend...:)