HOW TO: Create a Database, a Table, and Run a Query (SQL Server CE Sample)

Article translations Article translations
Article ID: 320785 - View products that this article applies to.
This article was previously published under Q320785
Expand all | Collapse all

On This Page

SUMMARY

This article contains a step-by step sample that creates a database, a table, and that adds records to a SQL Server CE database. The sample also queries the new table and displays the records in the table.

SQL Server CE Sample

  1. Open a new project in Microsoft eMbedded Visual Basic. Form1 is created by default.
  2. Add three command buttons to Form1.
  3. Change the caption for the command button named Command1 to Create Database. Change the caption for the command button named Command2 to Create Table. Change the caption for the command button named Command3 to Run Query.
  4. On the Project menu, click References. Make sure that the following references are selected:
    Microsoft CE ADO Ext. 3.1 for DDL
    Microsoft CE SQL Server Control 1.0
    Microsoft CE ADO Control 3.1
  5. To make sure that the Microsoft ADO for Windows CE SDK (ADOCE) and SQL Server CE components are downloaded to the device, on the Project menu, click Properties. On the General tab you will see an Update Components section. In the Frequency for the Update Components combo box, click to select Always for the frequency. In the Component to Update section, click to select both the Runtime Files and the Project Components check boxes.

How to Create a Database

To create a database, use this code sample:

Copy, and then paste the following code into the Command1_Click event:
Dim cat As ADOXCE.Catalog
Set cat = CreateObject("ADOXCE.Catalog.3.1")
cat.Create "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\DATAB.sdf"
MsgBox "Database Created"
				


How to Create a Table

To create a table, use this code sample:

Copy, and then paste the following code into the Command2_Click event:
Dim str_Connection As String
Dim cn As ADOCE.Connection

str_Connection = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\DATAB.sdf"
Set cn = CreateObject("ADOCE.Connection.3.1")
cn.Open str_Connection
cn.Execute "Create table table1(id int NOT NULL, field1 nvarchar(1))"
cn.Execute "CREATE UNIQUE INDEX idx1 ON table1(id)"
cn.Execute "insert table1 values (1, 'A')"
cn.Execute "insert table1 values (2, 'B')"
cn.Execute "insert table1 values (3, 'C')"
cn.Close
Set cn = Nothing
MsgBox "Table Created"
				

How to Query a Table

To query the table you created previously, follow these steps:
  1. Copy, and then paste the following code into the Command3_Click event:
    Dim cn As ADOCE.Connection
    Dim rs As ADOCE.Recordset
    Set cn = CreateObject("ADOCE.Connection.3.1")
    Set rs = CreateObject("ADOCE.Recordset.3.1")
    cn.Open "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\DATAB.sdf"
    rs.Open "select * from table1 where field1='A'", cn, adOpenKeyset, adLockReadOnly
    Dim x As Integer
    x = 0
    For x = 0 To rs.RecordCount - 1
        MsgBox rs(1).Value
        rs.MoveNext
    Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    					

  2. Save the project, and then run the project either through an emulator or a device.
  3. Click the Create Database command button.
  4. Click the Create Table command button.
  5. Click the Run Query command button. Note the message box that displays the character "A".

REFERENCES

SQL Server CE Books Online; topic: "Error Handling"

For additional information, visit the following Microsoft Web sites:

Troubleshooting SQL Server CE Connectivity Issues

SQL Server CE 1.1 Service Pack 1 Update

Properties

Article ID: 320785 - Last Review: January 3, 2007 - Revision: 2.4
APPLIES TO
  • Microsoft SQL Server 2000 Windows CE Edition
  • Microsoft SQL Server 2000 Windows CE Edition 1.1
  • Microsoft SQL Server 2000 Windows CE Edition 1.1 Service Pack 1
  • Microsoft SQL Server 2000 Windows CE Edition 1.1 SP1 Update
Keywords: 
kbgraphxlink kbhowto kbhowtomaster KB320785
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com