Page 1
Page 2
Deleting records in the database
dim strSQL as string
strSQL = "DELETE FROM TableName WHERE
Field1=Value1"
conConnection.Open
rsRecordset.Open strSQL, conConnection, CT_OPEN_DYNAMIC, LT_OPTIMISTIC
'Err_Handling
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
conConnection.Close
Err.Number = 0
Exit Function
End If
conConnection.Close
Updating records in the database
As with the inserts, you can build an SQL
string or update the recordset when you select it
dim strSQL as string
strSQL = "UPDATE TableName SET Field1=Value1,
Field2=Value2, Field3=Value3 WHERE Field4=Value4"
rsRecordset.Open strSQL, conConnection, CT_OPEN_DYNAMIC, LT_OPTIMISTIC
'Err_Handling
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
conConnection.Close
Err.Number = 0
Exit Function
End If
conConnection.Close
But there is little thing with updating one
record with the other method. You can't select a single record
and open that recordset to update it. This is a way to do it :
conConnection.open
rsRecordset.open "TableName", conConnection, CT_OPEN_FW_ONLY,
LT_OPTIMISTIC, TT_TABLEDIRECT
'Err_Handling
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
rsRecordset.Close
conConnection.Close
Err.Number = 0
Exit Function
End If
If Not rsRecordset.EOF Then
Do While Not rsRecordset.EOF
If rsRecordset.fields("Field1") = Value1 Then
rsRecordset.fields("Field2")= Value2
rsRecordset.Fields("Field3") = Value3
rsRecordset.update
End If
rsRecordset.movenext
Loop
End If
conConnection.close
In the case of updating records, I prefer
the first method.
To fill a grid with a recordset
My app uses many grids that needs data from
a recordset. This is how I fill in the grid values.
Private Sub FillGrid()
Dim lngRow As Long 'Rowcounter
Dim strSQL As String
Dim strLine As String 'To add line after line to the grid, this
is the fastest way !
On Error Resume Next
lngRow = -1
'To fill the grid
'If you make the grid invisible before inserting, it goes faster
!
grdGrid.Clear
grdGrid.Rows = 0
grdGrid.Visible = False
conConnection.Open
strSQL = "SELECT * FROM TableName"
rsRecordset.Open strSQL, conConnection, CT_OPEN_FW_ONLY, LT_OPTIMISTIC
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
rsRecordset.Close
conConnection.Close
Exit Sub
End If
Do While Not rsRecordset.EOF
lngRow = lngRow + 1
strLine = Trim("" & rsRecordset("Field1"))
strLine = strLine & vbTab & Trim("" & rsRecordset("Field2"))
strLine = strLine & vbTab & Trim("" & rsRecordset("Field3"))
grdGrid.AddItem strLine
grdGrid.Row = lngRow
rsRecordset.MoveNext
Loop
'Position on the first record
If grdGrid.Rows > 0 Then
grdGrid.Row = 0
End If
rsRecordset.Close
conConnection.Close
grdGrid.Visible = True
'Err_Handling
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
End If
End Sub
Field types of the table
As mentioned, I get my records by MSMQ. The
message is a string, that has to be parsed before inserting the
record. But they are all strings when I parse them. I needed to
know the type of field before inserting it into a recordset.
This is a method to do this :
First, declare the following constants :
'FieldTypes
Public Const C_FIELDTYPE_NVARCHAR = 202
Public Const C_FIELDTYPE_SMALLINT = 2
Public Const C_FIELDTYPE_INT = 3
Public Const C_FIELDTYPE_FLOAT = 5
Public Const C_FIELDTYPE_BIT = 11
Public Const C_FIELDTYPE_DATETIME = 135
Public Const C_FIELDTYPE_NTEXT = 203
These are the SQL Server datatypes
Now, before inserting a field in a recordset,
see what type is expected in the database (f is declared as Object):
Set f = rsRecordset("Field1")Select
Case f.Type
Case C_FIELDTYPE_SMALLINT
rsRecordset.Fields("Field1") = CInt(strValue)
Case C_FIELDTYPE_INT
rsRecordset.Fields("Field1") = CLng(strValue)
Case C_FIELDTYPE_FLOAT
rsRecordset.Fields("Field1") = strValue
Case C_FIELDTYPE_BIT
If strValue <> "0" Then
rsRecordset.Fields("Field1") = -1
Else
rsRecordset.Fields("Field1") = 0
End If
Case C_FIELDTYPE_DATETIME,C_FIELDTYPE_NVARCHAR, C_FIELDTYPE_NTEXT
rsRecordset.Fields("Field1") = strValue
End Select
That's it
That's about all for the implementation and
use of SQL Server CE in my application. I hope this helps other
developers in making their own database driven application. I
know some issues are not included (replication,for example,is
an undiscovered planet for me too).
When there are questions about this code,
or remarks, please send them to my email address, I'll try to
answer whenever I can.
Previous Page