Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

How do I connect to SQL CE 2008 using VB.NET 2008 and manipulate the data without data binding?

Hi,

I am new to SQL CE 2008 and I am having trouble programmatically connecting to the .sdf database and selecting/updating/inserting/deleting the data. I have tried various tutorials and examples on MSDN, Google and EE and haven't been able to get any of the suggested methods to work properly. Could someone please help me by providing an example of programmatically connecting to the database and then manipulating some data eg. update some records etc.

Thank you in anticipation.

Cheers,

Scott.
0
Scott_Y
Asked:
Scott_Y
  • 5
  • 4
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
you can use data from SQL CE just like any other source using DataSet/DataTables
0
 
Scott_YAuthor Commented:
I have tried following some samples on using DataSet/DataTables but, I haven't been able to get it to work successfully thus far. Any chance of an example?
0
 
Éric MoreauSenior .Net ConsultantCommented:
Easy if you use the right objects:

        Dim strCS As String = "Data Source=|DataDirectory|\Database1.sdf"
        Dim cn As New SqlCeConnection(strCS)

        cn.Open()

        Dim selectCmd As SqlCeCommand = cn.CreateCommand()
        selectCmd.CommandText = "SELECT * FROM Test"

        Dim da As New SqlCeDataAdapter(selectCmd)

        Dim ds As New DataSet()
        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

        If cn IsNot Nothing Then
            cn.Close()
            cn.Dispose()
            cn = Nothing
        End If
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott_YAuthor Commented:
That's great! It worked perfectly! Thank you!

The second part of my question that I still need help with is programmatically manipulating some data eg. update some records etc.
0
 
Éric MoreauSenior .Net ConsultantCommented:
You can create any SqlCeCommand containing INSERT, UPDATE, or DELETE statements just like you would do with a non CE database. Have a look at http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.executenonquery(VS.80).aspx
0
 
Scott_YAuthor Commented:
Hi,
Here is a copy of the code that I tried. I got the following exception "'Expression evaluation caused an overflow. [ Name of function (if known) =  ]" caused by the line in bold. Any suggestions on what I did wrong?

 
Dim strCS As String = "Data Source=|DataDirectory|\LERT.sdf"
Dim cn As New SqlCeConnection(strCS)

cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand()
cmd.CommandText = "INSERT INTO UVLevels (UVStationID, UVLevel) VALUES ('Test',12.34)"

Dim returnValue As Integer

returnValue = cmd.ExecuteNonQuery()

MessageBox.Show(returnValue.ToString)

If cn IsNot Nothing Then
cn.Close()
cn.Dispose()
cn = Nothing
End If
0
 
Éric MoreauSenior .Net ConsultantCommented:
what are the datatypes for UVStationID and UVLevel?

can you also try this:

Dim strCS As String = "Data Source=|DataDirectory|\LERT.sdf"
Dim cn As New SqlCeConnection(strCS)

cn.Open()

Dim cmd As New SqlCeCommand("INSERT INTO UVLevels (UVStationID, UVLevel) VALUES ('Test',12.34)", cn)
Dim returnValue As Integer

returnValue = cmd.ExecuteNonQuery()

MessageBox.Show(returnValue.ToString)

If cn IsNot Nothing Then
cn.Close()
cn.Dispose()
cn = Nothing
End If


0
 
Scott_YAuthor Commented:
I played around with it today and you were right, I had stuffed up the data type for UVLevel.

Thanks again for your help!

Cheers,

Scott.
0
 
Scott_YAuthor Commented:
Thanks for your help!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now