Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > .net > answers

SQLServerCe connection string problems

markmcgookin's Avatar
Moderator
  
Join Date: Dec 2006
Location: Northern Ireland / England
Posts: 546
#1: Jan 29 '07
Hi Folks,

I am having some trouble with a connection to a database in Visual Studio 2005, I have managed to add the Northwind.sdf demo database to my "server explorer" window in VS and in the properties window, the connection string

Expand|Select|Wrap|Line Numbers
  1. Data Source ="F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf";
  2.  
appears, but when I go to paste it into my code, the "Data Source" bit is hi-lighted in red, as is the trailing " ; ) I am unfortunatly working from a VB .Net SQLServer CE book, and this is Visual Studio 2005, so there must be some changes that I don't know about.

Mousing over the "F:" bit which is underline in squiggley blue gives this error:

"Comma, ')', or a valid expression continuation expected"

Then mousing over the "\" gives

"Syntax Error"

I don't know if VS2005 means I don't have to declare all this as it is already in the Server Explorer window, but I'd really appreciate some help, as I am totally stuck here. Some relevant websites would also be helpful, I am finding it a real hard time to get any decent ones outside of Microsoft, which isn't great at explaining stuff.

The code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.SqlServerCe
  2.  
  3. Public Class Form1
  4.  
  5.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  6.         Dim cn As SqlCeConnection
  7.         cn = New SqlCeConnection("Data Source ="F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf";)
  8.  
  9.     End Sub
  10. End Class
  11.  
  12.  
Thanks very much for your time.

Member
  
Join Date: Jan 2007
Posts: 86
#2: Jan 29 '07

re: SQLServerCe connection string problems

It looks like you are having trouble with the quotation marks in your connection string. Your code contains the line:

cn = New SqlCeConnection("Data Source ="F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf";)

You need to either use single quotation marks or escape the internal double quotation marks. I'd try the following first:

cn = New SqlCeConnection("Data Source ='F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf;'")

If the single quotes give you trouble (sorry, I haven't been able to test this myself), try:

cn = New SqlConnection("Data Source =""F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf;""")

Hopefully that'll put you on the right track.
Member
  
Join Date: Jan 2007
Posts: 86
#3: Jan 29 '07

re: SQLServerCe connection string problems

Sorry about the wink - I guess that's what I get for using the ";" and the ")" characters together.
markmcgookin's Avatar
Moderator
  
Join Date: Dec 2006
Location: Northern Ireland / England
Posts: 546
#4: Jan 30 '07

re: SQLServerCe connection string problems

Quote:

Originally Posted by enreil

Sorry about the wink - I guess that's what I get for using the ";" and the ")" characters together.

Hi I am considering changing and designing my application in visual studio 2003 and deploying it on a Pocket PC 2003 device instead of a Windows Mobile 5 Device. Simply because there is so much more documentation and reading material on it. Do you have any expertise in this area?

I would really like to have someone to chat to about this.

I am trying to design a program at the minute and am having real trouble connecting to SQL Server CE databases etc, and I would love someone to be able to show/send me a complete example of a working one so I could see it in action. The phrase I am using at the minute is "I am having trouble figuring out how to talk to the DB, once I get that sorted, I can figure out what to say myself!" hehe, if you understand what I mean? I think I can write mysql etc myself, but I can not seem to find anyone willing to show me a working VB/SQL Server CE program that I can look at and see where I am going wrong.

For example I am working through a book and have written this code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Class Form1
  3.  
  4.     Inherits System.Windows.Forms.Form
  5.     Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
  6.  
  7.  
  8.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  9.  
  10.         Dim cn As SqlCeConnection
  11.  
  12.         Try
  13.             cn = New SqlCeConnection("Data Source=\\My Documents\\NorthwindDemo.sdf;password=")
  14.             cn.Open()
  15.  
  16.             Dim cmd As SqlCeCommand = cn.CreateCommand
  17.  
  18.             cmd.CommandText = "INSERT INTO Products (" & _
  19.                                         "ProductName, " & _
  20.                                         "SupplierID, " & _
  21.                                         "CategoryID, " & _
  22.                                         "UnitPrice, " & _
  23.                                         "UnitsInStock, " & _
  24.                                         "Discontinued, " & _
  25.                                         "VALUES " & _
  26.                                         "(?, ?, ?, ?, ?, ?)"
  27.  
  28.             cmd.Parameters.Add("@ProductName", "Axim")
  29.             cmd.Parameters.Add("@SupplierID", 1)
  30.             cmd.Parameters.Add("@CategoryID", 1)
  31.             cmd.Parameters.Add("@UnitPrice", 350)
  32.             cmd.Parameters.Add("@UnitsInStock", 20)
  33.             cmd.Parameters.Add("@Discontinued", 1)
  34.  
  35.             cmd.Prepare()
  36.             cmd.ExecuteNonQuery()
  37.  
  38.         Catch sqlex As SqlCeException
  39.             Dim sqlError As SqlCeError
  40.  
  41.             For Each sqlError In sqlex.Errors
  42.  
  43.                 MessageBox.Show(sqlError.Message)
  44.  
  45.             Next
  46.  
  47.         Catch ex As Exception
  48.             MessageBox.Show(ex.Message)
  49.  
  50.         Finally
  51.             If cn.State <> ConnectionState.Closed Then
  52.                 cn.Close()
  53.  
  54.             End If
  55.         End Try
  56.     End Sub
  57. End Class
  58.  
and keep getting an output in the Autos section at the bottom where "cmd" and "cn" are stuck with null values and some other errors. I think it has something to do with the location of the DB being in My Doc, but the book I am reading insists on it being there.
Member
  
Join Date: Jan 2007
Posts: 86
#5: Jan 30 '07

re: SQLServerCe connection string problems

It looks like your connection string is giving you problems. You can stick your database anywhere you please - there's nothing special about it being in My Documents. Wherever you stick the Northwind DB, make sure you include the full path reference in your command string. For instance, if you place it in My Documents, make sure the path is as follows:

"C:\\Documents and Settings\\YOUR USERNAME\\My Documents\\Northwind.mdb"

You can check connectionstrings.com for further guidance (http://www.connectionstrings.com/) if this doesn't quite do it for you.

I haven't had any experience deploying to mobile devices - sorry, wish I could help you there.

Quote:

Originally Posted by markmcgookin

Hi I am considering changing and designing my application in visual studio 2003 and deploying it on a Pocket PC 2003 device instead of a Windows Mobile 5 Device. Simply because there is so much more documentation and reading material on it. Do you have any expertise in this area?

I would really like to have someone to chat to about this.

I am trying to design a program at the minute and am having real trouble connecting to SQL Server CE databases etc, and I would love someone to be able to show/send me a complete example of a working one so I could see it in action. The phrase I am using at the minute is "I am having trouble figuring out how to talk to the DB, once I get that sorted, I can figure out what to say myself!" hehe, if you understand what I mean? I think I can write mysql etc myself, but I can not seem to find anyone willing to show me a working VB/SQL Server CE program that I can look at and see where I am going wrong.

For example I am working through a book and have written this code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Class Form1
  3.  
  4.     Inherits System.Windows.Forms.Form
  5.     Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
  6.  
  7.  
  8.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  9.  
  10.         Dim cn As SqlCeConnection
  11.  
  12.         Try
  13.             cn = New SqlCeConnection("Data Source=\\My Documents\\NorthwindDemo.sdf;password=")
  14.             cn.Open()
  15.  
  16.             Dim cmd As SqlCeCommand = cn.CreateCommand
  17.  
  18.             cmd.CommandText = "INSERT INTO Products (" & _
  19.                                         "ProductName, " & _
  20.                                         "SupplierID, " & _
  21.                                         "CategoryID, " & _
  22.                                         "UnitPrice, " & _
  23.                                         "UnitsInStock, " & _
  24.                                         "Discontinued, " & _
  25.                                         "VALUES " & _
  26.                                         "(?, ?, ?, ?, ?, ?)"
  27.  
  28.             cmd.Parameters.Add("@ProductName", "Axim")
  29.             cmd.Parameters.Add("@SupplierID", 1)
  30.             cmd.Parameters.Add("@CategoryID", 1)
  31.             cmd.Parameters.Add("@UnitPrice", 350)
  32.             cmd.Parameters.Add("@UnitsInStock", 20)
  33.             cmd.Parameters.Add("@Discontinued", 1)
  34.  
  35.             cmd.Prepare()
  36.             cmd.ExecuteNonQuery()
  37.  
  38.         Catch sqlex As SqlCeException
  39.             Dim sqlError As SqlCeError
  40.  
  41.             For Each sqlError In sqlex.Errors
  42.  
  43.                 MessageBox.Show(sqlError.Message)
  44.  
  45.             Next
  46.  
  47.         Catch ex As Exception
  48.             MessageBox.Show(ex.Message)
  49.  
  50.         Finally
  51.             If cn.State <> ConnectionState.Closed Then
  52.                 cn.Close()
  53.  
  54.             End If
  55.         End Try
  56.     End Sub
  57. End Class
  58.  
and keep getting an output in the Autos section at the bottom where "cmd" and "cn" are stuck with null values and some other errors. I think it has something to do with the location of the DB being in My Doc, but the book I am reading insists on it being there.
Reply

« previous question | next question »

Similar .NET Framework bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved