Selecting Data From the Database
You are probably already aware of the most basic type of SQL statement the SELECT statement. Use the SELECT statement to retrieve records from a database. For example, if we want to see all information in the Products table, we would run the following SQL statement The asterisk above designates that we want to see all fields in the Products table. If, instead, we only wanted to find the name of the product, the price, and units that are in stock, the SQL statement below would accomplish this...
Info Reo
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID productId 5. Press Ctrl S to commit the stored procedure to the database. 6. To test the stored procedure, right-click on the code editor and select Run Stored Procedure. When prompted, enter 1 for the product ID 7. After pressing OK, the Output window should appear and the details of the product with a ProductID of 1 should be...
Info Ycy
1. Open Form1 in Design view, and double-click on the Send Message button to create a new Click handler. Add this code Private Sub btnSendMessage_Click ByVal sender As System.Object, _ ByVal e As System.EventArgs Handles btnSendMessage.Click Dim smsService As New 1 make sure the message goes through proxytrace If chkUseProxy.Checked True Then smsService.Proxy New 8080 End If Dim result As Boolean smsService.SendMessage _ txtPhoneNumber.Text, txtMessage.Text, _ txtSenderId.Text,...
Info Qrg
The true power of Web Services, however, comes from the ease with which they can be integrated with an existing system. Say, for example, that our customer uses software they've written themselves for tracking stock. The application might show a list of all outstanding orders placed with their suppliers. Our client could customize that software to automatically consume our Web Service and display live information about the status of outstanding orders. Once set up, there's no need to check our...
Info Bxs
'error handling goes here UnhandledExceptionHandler End Try 5. Place the following function LoadCompleteDataSet into the clsDatabase class. Function LoadCompleteDataSet ByVal strConnection As String As DataSet 'The purpose of this function is to populate a data set with 'the local tables from the Products, Suppliers, and Categories 'tables in the database. This is an example of a data set that 'uses relations among the tables. Dim blnRunStoredProc As Boolean True 'Create a Products table in the...
Info Xig
10 . We need to add a namespace declaration to the service. If we don't do this, we can run into problems when and if we make the Web Service publicly available. Add this attribute to the ProviderService class definition Inherits System.Web.Services.WebService 11. Find the constructor for the ProviderService class. You might have to expand out the Windows Form Designer generated code region by clicking the little gray plus sign. Add this code 'This call is required by the Web Services Designer....
Exercises Ium
3. What is the difference between complex data binding and simple data binding Where does property binding fit in 4. Briefly describe the ErrorProvider control and what it can allow you to accomplish. 5. What is the purpose of a DataView 6. Briefly describe the DataReader and when it can be used. Can you bind a DataReader to controls on a form such as a DataGrid When should you use a DataReader versus a DataSet Answers are available at http p2p.wrox.com exercises .
Info Gpf
Try It Out - Building the ConnectionMode and Connection Properties 1. The first thing we have to do is go back to the members of the Provider class and add this new member Private Shared _connectionMode As Provider.ConnectionModes _ ConnectionModes.NotConnected Private Shared _connection As ProviderConnection 1 ConnectionMode - what mode are we in Public Shared Property ConnectionMode As Provider.ConnectionModes Get ' Set the connection mode _connectionMode Value ' Reset the connection...
Info Hos
4. The existing SQL SELECT statement should appear on the next pop up window. As we are now going to insert, update, and delete records, we want to enable the wizard's automatic generation of INSERT, UPDATE, and DELETE commands. Click the Advanced Options button. 5. The advanced dialog gives us three options. Select the first check box, which will enable the rest of the check boxes. We want all of the options selected. Click OK when you're finished. Review the SELECT statement on the next...
SMS Messaging
In this last section, we're going to use Salcentral to find a Web Service that we can call from our existing client application. Specifically, we're going to use their SMSMessaging Web Service. This Web Service can send a text message via SMS, the Short Message Service, to a handheld unit such as a compatible cell phone. Without a suitable Web Service, this is a particularly tricky proposition that can entail physically connecting a cell phone to your computer and controlling it through the...
Info Xxv
strSQL strSelectFromCriteria amp strWhereCriteria 'todo remove this message box after finished testing SQL syntax MsgBox The SQL Statement is 11 amp strSQL 'error handling goes here UnhandledExceptionHandler End Try 8. Last of all, add the following code to both the Products and Suppliers forms frmSearchProducts.vb and frmSearchSuppliers.vb Private Sub btnSearch_Click ByVal sender As System.Object, ByVal e As System.EventArgs Handles btnSearch.Click custCB As SqlClient.SqlCommandBuilder New _...
Info Xxq
Now, all non-primary key fields in the Orders table OrderDate, CustomerNumber, and OrderShipDate depend on the whole key - the OrderNumber - and are not unnecessarily repeated. In addition, all non-key fields in the OrderProducts table depend on that table's complete primary key, composed of the OrderNumber and ProductIdentifier. This means that Price and Quantity are information that describes the situation represented by the whole key - that is, each product of any given order. Now that all...
Info Lbf
Like . aspx pages, .asmx pages actually comprise two files. One Servicei.asmx is used for the pages that power the Web Service. The other Servicei .asmx.vb is the code behind page specifying the actual methods for the Web Service. Unlike ASP.NET, you don't tend to do anything with the in-front page - everything is done in the code behind page. The Service1.asmx.vb ASMX page contains this code Imports System.Web.Services 1 Windows Form Designer generated code region Inherits...
Info Dvh
To use the OLE DB data provider in your project, you must include the OleDb namespace in your code modules To use the SQL Server data provider in your project, you include the SqlClient namespace in your code modules Within each managed provider are four components the Connection, DataAdapter, Command, and DataReader objects. Stores commands such as SELECT, UPDATE, INSERT, and DELETE Requests a connection, sends out commands from DataAdapter. Builds commands If you're using the SQL Server Data...
Info Ofi
Now, let's look at an example. Recall in Chapter 2 that we learned how to use SQL Server Service Manager in the taskbar to start and stop the SQL Server, SQL Server Agent, and Distributed Transaction Coordinator services of SQL Server. The Services node in Server Explorer allows you to do the same thing manage those services and many others as well. Expand the Services node and browse until you see MSSQLSERVER, the name of SQL Server Service, in the list. Right-click MSSQLSERVER on the list and...
Info Dxd
Return and here's the way it was called in the client application ProductDataSet The great news for us with all this is that we have to do literally nothing to get all of this working. .NET's Web Service implementation handles most of the stress involved in wrapping up a DataSet and passing it over the network, almost without us having to do anything. Of course, all we've done is proven that we can build a Web Service that can consume the same objects that the client can. Now we have to prove...
Info Wfl
By default, the DataGrid control allows you to edit the data that it displays by clicking on an item and entering a new value. Go ahead and try this out - notice however that the underlying database entries are not changed even though at first this may appear to be the case. Therefore, we want to disable this feature, as we are going to create separate forms for letting users edit data. Also, another improvement we can implement at this point is to avoid trying to bind to the DataSet if the...