Using Remote Data Access with SQL Server CE 2.0
Pages: 1, 2, 3

Selecting, Updating, and Deleting Records

Now that the records are saved locally, the user can display the student information by selecting the class and lesson:

Figure 11. Displaying the student records from the local SQL Server CE database

So let's code the Display button now. I will have a function displayStudent() to do this. First, ensure that the class and lesson are selected:

Public Sub displayStudents()
    ' display the students according to Class and Lesson
    If cmbClass.Text = "" Or cmbLesson.Text = "" Then
            MsgBox("Please select Class and Lesson", _
                 MsgBoxStyle.Exclamation, "Select Class and Lesson")
            Return
    End If

Then, open the connection to the local SQL Server CE database and retrieve the appropriate records, and add three columns to the ListView control:

    conn.Open()
    Dim reader As SqlCeDataReader
    Dim cmd As New SqlCeCommand( _
        "SELECT StudentID, StudentName, " & _
        cmbLesson.Text & _
        " FROM Module WHERE ClassID='" & _
        cmbClass.Text & "'", conn)
    reader = cmd.ExecuteReader

    ListView1.Clear()
    ListView1.Columns.Add("StudentID", -1, _
                          HorizontalAlignment.Center)
    ListView1.Columns.Add("Name", 150, _
                          HorizontalAlignment.Left)
    ListView1.Columns.Add(cmbLesson.Text, -1, _
                          HorizontalAlignment.Center)

Finally, add the record to the ListView control:

    While reader.Read
        Dim ls As New ListViewItem(reader.Item("StudentID").ToString)
        ls.SubItems.Add(reader.Item("StudentName").ToString)
        ls.SubItems.Add(reader.Item(cmbLesson.Text).ToString)
        ListView1.Items.Add(ls)
    End While
    conn.Close()
End Sub

To change the attendance information of a student, select the student and right-click on it (on the emulator) to invoke the context menu.

Figure 12. Invoking the context menu to change the attendance of a student

This is done by servicing the Click event of each menu item:

Private Sub MenuItem1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MenuItem1.Click

In each menu item, we invoke the setStatus() method with the appropriate argument -- PR for present, AB for absent and MC for Medical Certificate:

Public Sub setStatus(ByVal status As String)
    ' set the status of the attendance
    Dim i As Integer
    Dim studentID As String
    Try
        i = ListView1.SelectedIndices.Item(0)
    Catch ex As Exception
        MsgBox("Please select item", MsgBoxStyle.Exclamation, _
         "Student not selected")
        Return
    End Try

    studentID = ListView1.Items.Item(i).Text
    Dim sql As String = "UPDATE Module Set " & cmbLesson.Text & _
       "='" & status & "' WHERE StudentID='" & studentID & "'"
    conn.Open()
    Dim cmd As New SqlCeCommand(sql, conn)
    cmd.ExecuteNonQuery()
    conn.Close()
    displayStudents()
End Sub

Essentially, the local SQL Server CE database is updated.

Pushing Data Back to SQL Server 2000

When the user gets back to the office, he can then push the updated data back into the SQL Server 2000. Here, we code the Push button.

As usual, set the connection string to connect to SQL Server 2000:

    Dim rdaOleDbConnectString As String = _
                    "Provider=sqloledb; Data Source=mercury2;" & _ 
                    "Initial Catalog=Attendance; User Id=sa;Password="

And finally, create the RDA object and push the local SQL Server CE table back to the SQL Server 2000 database:

    ' Initialize the RDA object.
    Dim rda As SqlCeRemoteDataAccess = Nothing

    Try
        rda = New SqlCeRemoteDataAccess
        rda.InternetUrl = "http://mercury2/SQLCE/sscesa20.dll"
        rda.LocalConnectionString = _
                   "Provider=Microsoft.SQLSERVER." & _
                   "OLEDB.CE.2.0;Data Source=\My Documents\ssce.sdf"
        rda.Push("Module", rdaOleDbConnectString, _
                 RdaBatchOption.BatchingOn)
        MsgBox("Push operation completed", _
               MsgBoxStyle.Information, "Push")
    Catch err As SqlCeException
        ShowErrors(err)
    Finally
        rda.Dispose()
    End Try

That's it! You can now make changes to the SQL Server CE database and update the SQL Server 2000 database.

SQL Server CE Analyzer

Before I end this article, I want to quickly show you the included SQL Server CE Query Analyzer. When you first run your mobile application with the SQL Server CE assembly added, the SQL Server CE Query Analyzer will automatically be installed. You can find it at Programs->SQLCE Query:

Figure 13. Running SQL Server CE Query Analyzer

Using the SQL Server CE Query analyzer, you can connect to the local database and examine the database structure, as well as execute SQL queries.

Figure 14. Examining the local SQL Server CE database

Debugging Tips

As I worked through the sample codes in this article, there are various sources of errors that I encountered. Some are silly mistakes that I made, while a significant portion were caused by the quirks of Visual Studio .NET and the emulators. I will share with you some of the pitfalls and hopefully, you can save yourself some frustrating hours chasing after an error that wasn't caused by you.

• Always trap your errors by using the Try-Catch-Finally block and send the error object to the ShowErrors() method. The ShowErrors() method will reveal a lot more information and save you a lot of time wondering about the source of the error.
• Always stop your debugging process by closing the application within the emulator. Using the Stop button is Visual Studio .NET often causes errors the next time you run the application again. You will see an error message like the following:

The file 'SmartDeviceApplication3.pdb' cannot be copied to the run directory. The process cannot access the file because it is being used by another process. Could not copy temporary files to the output directory. In such cases, simply restarting Visual Studio .NET should solve the problem.

• I constantly encounter the following errors when I try the pull operation on my application for the first time:

Figure 15. A common error connecting to the IIS

After some trial and error, I realized that the problem could be solved most of the time by invoking Internet Explorer on the emulator and connecting to the URL http://mercury2/SQLCE/sscesa20.dll. This URL points to the SQL Server CE Server Agent, and you should see the following message in return: "SQL Server CE Server Agent." Try this a few times and your application should work fine. If not, restart your emulator and Visual Studio .NET.

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.

---

Return to ONDotnet.com