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
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
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:
In each menu item, we invoke the
Private Sub MenuItem1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MenuItem1.Click
setStatus()method with the appropriate argument --
ABfor absent and
MCfor 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:
And finally, create the RDA object and push the local SQL Server CE table back to the SQL Server 2000 database:
Dim rdaOleDbConnectString As String = _ "Provider=sqloledb; Data Source=mercury2;" & _ "Initial Catalog=Attendance; User Id=sa;Password="
' 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|
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-Finallyblock and send the error object to 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