473,372 Members | 1,572 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Connect to SQL Server CE

I am new to mobile development and for now I'm trying to create a mobile application using SQL Server CE as database. Could someone please provide a sample code in vb that will connect to SQL CE? Or the connection string at least?
Any help would be greatly appreciated
Jul 18 '08 #1
10 7564
markmcgookin
648 Expert 512MB
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
  2.         'Verify if the WildFlowers database already exists.
  3.         If File.Exists("WildFlowers.sdf") = False Then
  4.             'Allow creation of the database
  5.             Dim engine As New SqlCeEngine("Data Source = WildFlowers.sdf")
  6.             engine.CreateDatabase()
  7.             MsgBox("Wildflowers database successfully created.", MsgBoxStyle.OKOnly, "Create DataBase")
  8.         Else
  9.             MsgBox("Wildflowers database already exists, no action taken.", MsgBoxStyle.OKOnly, "Create DataBase")
  10.         End If
  11.     End Sub
  12.  
  13.     Private Sub btnCreateTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTables.Click
  14.         'Create connection with database and then Open connection
  15.         Dim conn As New SqlCeConnection("Data Source = WildFlowers.sdf")
  16.         Try
  17.             conn.Open()  'Open Connection to the Database
  18.  
  19.             'Create SQL command to create the tblDetails table
  20.             Dim cmdTabla As New SqlCeCommand("CREATE TABLE tblDetails(regID int IDENTITY(0,1) PRIMARY KEY, Flower_ID NTEXT, Name_Eng NTEXT, Name_Lat NTEXT, Location_ID NTEXT, Habitat_ID NTEXT, Season_ID NTEXT, Family_ID NTEXT, Height NTEXT, Flower_Shape NTEXT, Leaf_Shape NTEXT, Colour NTEXT, Plant_Type NTEXT, Bloom_Size NTEXT, Cluster_Type NTEXT, Description NTEXT)", conn)
  21.             'Excecute Create Table command
  22.             cmdTabla.ExecuteNonQuery()
  23.  
  24.         Catch ex As SqlCeException
  25.             MsgBox(ex.Message)
  26.  
  27.         Finally
  28.             MsgBox("Tables were successfully created.", MsgBoxStyle.OKOnly, "Create Tables")
  29.             conn.Close() 'Close database
  30.         End Try
  31.     End Sub
  32.  
Hope this helps... it is connection to a database in the root of the PDA ... so if the database is somewhere else ... i.e. \Program Files\MyApp\MyDb.sdf the path will have to represent that.

There are two subroutines in the above code the first creates the database and the second connects to the database and executes some SQL to create a table

Hope that helps

Mark
Jul 18 '08 #2
Thanks Mark. This is better. Just a few question though. Suppose I created the database on runtime based on your code, where can I find it? Or suppose i have created the database somewhere(like SQL server 2005), where should I place my SDF file to avoid problems in my connection string? Actually I think my problem here is my connection string.
Jul 21 '08 #3
markmcgookin
648 Expert 512MB
Thanks Mark. This is better. Just a few question though. Suppose I created the database on runtime based on your code, where can I find it? Or suppose i have created the database somewhere(like SQL server 2005), where should I place my SDF file to avoid problems in my connection string? Actually I think my problem here is my connection string.
Personally I tend to keep the database in my application folder to avoid issues.

i.e.

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New SqlCeConnection("Data Source = \Program Files\MyApplication\myDatabase.sdf")
Then when you deploy using Visual Studio it will be in the right place. If you have already created the database and deployed it to the device but can't connect. Just use File Explorer to navigate to the folder to get the path.

If you use my code above, as I have not specified a path for the db it will be created in the device root.

Are you getting an error?

Hope this helps,

Mark
Jul 21 '08 #4
Thanks. I can finally connect to my database, I tried to create a function that will add record to my table tblProducts which is empty at first

Private Sub add_record()
cn = New SqlCeConnection(Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection .Assembly.GetExecutingAssembly().GetName().CodeBas e)) + "\\productsDB.sdf;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("INSERT INTO tblProducts(ProductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

cn.Close()

MessageBox.Show("New record added")
End Sub

At first I thought this was working fine, my datagrid control was updated by the new record I added but when restart my application, the newly added record was gone. And tblProducts is still empty. Is there something wrong in my code?
Jul 22 '08 #5
bump......................................
Jul 22 '08 #6
markmcgookin
648 Expert 512MB
Thanks. I can finally connect to my database, I tried to create a function that will add record to my table tblProducts which is empty at first

Private Sub add_record()
cn = New SqlCeConnection(Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection .Assembly.GetExecutingAssembly().GetName().CodeBas e)) + "\\productsDB.sdf;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("INSERT INTO tblProducts(ProductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

cn.Close()

MessageBox.Show("New record added")
End Sub

At first I thought this was working fine, my datagrid control was updated by the new record I added but when restart my application, the newly added record was gone. And tblProducts is still empty. Is there something wrong in my code?
When you say "restart" your application did you mean that you ran debug again and deployed it to the mobile again? Chances are that the database is being copied across ontop of the original again if this is the case. Because it looks like the insert it going in properly. If it is in the solution explorer in VS you can click on properties > build action > Do not copy to avoid this (but you will obviously need it copied across at least once to show up!)
Jul 22 '08 #7
Yes, I did ran debug and deployed my application. I actually created my database in VS and is located in the application folder. What must I do to make sure that the database table is really updated?
Jul 23 '08 #8
markmcgookin
648 Expert 512MB
Yes, I did ran debug and deployed my application. I actually created my database in VS and is located in the application folder. What must I do to make sure that the database table is really updated?
Well when you are deploying your application you are RE-deploying the database ontop of the old one and over-writing it. A safe bet would be to deploy your application and add your row to the database. THEN run this without copying over the database; you can either stop copying it as mentioned above or go to

File Explorer (on your device/emulator) > Program Files > YourApplication > YourApplication.exe

and run it directly from the device. If you do this it will run the application without copying over a new blank database and your row should be in it.

When you deploy an application it re-copies any files you have added or altered and datasources, including your database, this is why it is getting over written.
Jul 23 '08 #9
Personally I tend to keep the database in my application folder to avoid issues.

i.e.

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New SqlCeConnection("Data Source = \Program Files\MyApplication\myDatabase.sdf")
Then when you deploy using Visual Studio it will be in the right place. If you have already created the database and deployed it to the device but can't connect. Just use File Explorer to navigate to the folder to get the path.
You cant imagine how thankfull I am, thanks for the great explanation. Sometimes we the devs are stuck in something easy like where the heck I place my db file so the emulator can use it since it is not reading from my computer. I searched lot and lots of pages, sample code and I created my own samples.

I just registered to say thanks. Now will also help if I can.
Oct 5 '08 #10
markmcgookin
648 Expert 512MB
I just registered to say thanks. Now will also help if I can.
Always glad to help!

Hope to see you around Bytes more often!

Mark
Oct 7 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Jerry Orr | last post by:
We are trying to determine the easiest (and cheapest) way to get connections for about 50 Win2K workstations on our LAN to DB2 on a z/OS server. We could install DB2 Connect Personal Edition on...
4
by: Scott Holland | last post by:
HELP - Need to connect to DB2 database on AIX from NT server. Also AS/400 from NT Server -- I am experienced in ORACLE and a novice at DB2. What tools would be the equivalent of Net*8 or...
0
by: Zorba.GR | last post by:
IBM DB2 Connect Enterprise Edition v8.2, other IBM DB2 (32 bit, 64 bit) (MULTiOS, Windows, Linux, Solaris), IBM iSoft Commerce Suite Server Enterprise v3.2.01, IBM Tivoli Storage Resource Manager...
5
by: mayamorning123 | last post by:
A comparison among six VSS remote tools including SourceOffSite , SourceAnyWhere, VSS Connect, SourceXT, VSS Remoting, VSS.NET To view the full article, please visit...
14
by: DaTurk | last post by:
I am makeing a Multicast server client setup and was wondering what the difference is between Socket.Connect, and Socket.Bind. It may be a stupid question, but I was just curious. Because I...
0
by: Clodoaldo Pinto | last post by:
Can't connect to postgresql server php error log message: PHP Warning: pg_connect() : Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server...
14
by: Marcus | last post by:
I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that...
0
by: tamayi | last post by:
I have a problem (like most others posting issues on this forum :) ) I have a remote server running Windows XP SP2, with both SQL Server 2005 Express with Advanced Features and SQL 2000...
7
by: RN1 | last post by:
Is it possible to connect to my local SQL Server 2005 database from a remote web server? If yes, what ConnectionString do I use? Thanks, Ron
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.