SQLServerCe connection string problems

[markmcgookin]

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

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

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:

Imports System.Data.SqlServerCe
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cn As SqlCeConnection
        cn = New SqlCeConnection("Data Source ="F:\Program Files(2)\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf";)
 
    End Sub
End Class
 
 

Thanks very much for your time.

[enreil]

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.

[enreil]

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

[markmcgookin]

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

 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
    Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
 
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim cn As SqlCeConnection
 
        Try
            cn = New SqlCeConnection("Data Source=\\My Documents\\NorthwindDemo.sdf;password=")
            cn.Open()
 
            Dim cmd As SqlCeCommand = cn.CreateCommand
 
            cmd.CommandText = "INSERT INTO Products (" & _
                                        "ProductName, " & _
                                        "SupplierID, " & _
                                        "CategoryID, " & _
                                        "UnitPrice, " & _
                                        "UnitsInStock, " & _
                                        "Discontinued, " & _
                                        "VALUES " & _
                                        "(?, ?, ?, ?, ?, ?)"
 
            cmd.Parameters.Add("@ProductName", "Axim")
            cmd.Parameters.Add("@SupplierID", 1)
            cmd.Parameters.Add("@CategoryID", 1)
            cmd.Parameters.Add("@UnitPrice", 350)
            cmd.Parameters.Add("@UnitsInStock", 20)
            cmd.Parameters.Add("@Discontinued", 1)
 
            cmd.Prepare()
            cmd.ExecuteNonQuery()
 
        Catch sqlex As SqlCeException
            Dim sqlError As SqlCeError
 
            For Each sqlError In sqlex.Errors
 
                MessageBox.Show(sqlError.Message)
 
            Next
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
 
            End If
        End Try
    End Sub
End Class
 

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.

[enreil]

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.

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

 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
    Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
 
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim cn As SqlCeConnection
 
        Try
            cn = New SqlCeConnection("Data Source=\\My Documents\\NorthwindDemo.sdf;password=")
            cn.Open()
 
            Dim cmd As SqlCeCommand = cn.CreateCommand
 
            cmd.CommandText = "INSERT INTO Products (" & _
                                        "ProductName, " & _
                                        "SupplierID, " & _
                                        "CategoryID, " & _
                                        "UnitPrice, " & _
                                        "UnitsInStock, " & _
                                        "Discontinued, " & _
                                        "VALUES " & _
                                        "(?, ?, ?, ?, ?, ?)"
 
            cmd.Parameters.Add("@ProductName", "Axim")
            cmd.Parameters.Add("@SupplierID", 1)
            cmd.Parameters.Add("@CategoryID", 1)
            cmd.Parameters.Add("@UnitPrice", 350)
            cmd.Parameters.Add("@UnitsInStock", 20)
            cmd.Parameters.Add("@Discontinued", 1)
 
            cmd.Prepare()
            cmd.ExecuteNonQuery()
 
        Catch sqlex As SqlCeException
            Dim sqlError As SqlCeError
 
            For Each sqlError In sqlex.Errors
 
                MessageBox.Show(sqlError.Message)
 
            Next
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
 
            End If
        End Try
    End Sub
End Class
 

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.