Page 1
Page 2
Page 3
Adding records
Click on the ‘Add Record’ button, to initiate adding records. This shows 6 text boxes and 6 labels. Fill in all the text boxes, and click on ‘Add’. All the text boxes must be filled in before you can add a record (the program will check for this).
There are effectively 2 ways of adding records. You can either use a ‘straight’ insert statement e.g.
Insert into stores values (‘1’,’2’,’3’,’4’,’5’,’6’)
or you can use command parameters, e.g.
Insert into stores values (@stor,@nam,@add,@city,@st,@zip)
In our sample program, we use command parameters. We first create the SQL statement, and then add the parameters.
strSql = "Insert into stores (stor_id, stor_name, stor_address, city, state, zip)"
strSql += " Values (@stor_id,@stor_name,@stor_address,@city,@state,@zip)"
sqlCmd = New SqlCommand
sqlCmd = sqlConn.CreateCommand
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = strSql
sqlCmd.Parameters.Add("@stor_id", txtStoreID.Text)
sqlCmd.Parameters.Add("@stor_name", txtStoreName.Text)
sqlCmd.Parameters.Add("@stor_address", txtAddress.Text)
sqlCmd.Parameters.Add("@city", txtCity.Text)
sqlCmd.Parameters.Add("@state", txtState.Text)
sqlCmd.Parameters.Add("@zip", txtZip.Text)
Try
sqlCmd.ExecuteNonQuery()
Clear_Fields()
Catch ex As Exception
DisplaySQLErrors(ex, strSql)
End Try
Figure 4 – Adding a record.
The record will be inserted into ‘stores’, and the fields cleared. When the ‘done’ button is clicked, the ‘stores’ table will be displayed, to show any added records, as in Figure 5.
Figure 5 – Showing your added record
Deleting records
If we list the ‘stores’ table, a context menu is set, to allow us to delete records from the table. Click on the row you wish to delete, and then tap and hold, to bring up the delete menu, as in Figure 6.
In order to delete records (as well as update or add records), we use the ‘executenonquery’ method. First of all set up the SQL statement to delete the record, then create the command, and execute the query.
strSql = "Delete from stores where stor_id = '" & strCurrentStore & "'"
sqlCmd = New SqlCommand
sqlCmd = sqlConn.CreateCommand
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = strSql
Try
sqlCmd.ExecuteNonQuery()
List_Table("stores")
Catch ex As Exception
DisplaySQLErrors(ex, strSql)
End Try
Figure 6 – Deleting a record
Closing the database
Click on the ‘Close Pubs’ button, to close the database. This simply closes the connection.
Try
sqlConn.Close()
btnOpen.Text = "Open 'Pubs'"
pnlFunctions.Visible = False
lstTables.Visible = False
pnlAddRec.Visible = False
grdResults.Visible = False
Catch ex As Exception
DisplaySQLErrors(ex, "Open")
End Try
SqlClient – Conclusion
The program accompanying the article shows a lot of the capabilities of accessing a remote SQL Server from the Compact Framework. SqlClient allows you to create and drop tables, execute stored procedures and use the power of SQL Server from your handheld.
[link to the project for this article]
Previous Page