Risk Developer - C# - T-SQL - ASP.Net (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

Now that we've looked at how to list the products to allow for updates, and we've shown how this information will be passed to UpdateCosts.asp, we need to examine how, exactly, UpdateCosts.asp will record the changes. Since we pass the number of text boxes to UpdateCosts.asp, we can perform a loop through this number, retrieving each cost and associated ProductID. We can then create a SQL UPDATE statement with this information.

Let's look at the code for UpdateCosts.asp! First, we need to create a connection to the database:

Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=TestDB" objConn.Open

This again uses the exact same database DSN as in ListProducts.asp. Next, we need to read in the number of text boxes from ListProducts.asp, and create some needed variables:

'First, we need to get the total number of items that could be updated Dim iCount iCount = Request("Count") 'We need to obtain each cost and ID Dim strCost, strID 'We will also need to build a SQL statement Dim strSQL

Finally, we need to loop through iCount, creating a SQL statement in each iteration, and Executeing that statement.

'Now, we want to loop through each form element Dim iLoop For iLoop = 0 to iCount strCost = Request(iLoop & ".Cost") strID = Request(iLoop & ".ID") strSQL = "UPDATE Products SET Cost = " & CDBl(strCost) & _ " WHERE ProductID = " & strID objConn.Execute strSQL Next

Note that we loop from 0 to iCount, since in ListProducts.asp, the first text box was 0.Count, the second 1.Count and so on. We create a simple SQL statement to update the Products table based upon the cost and ProductID. Finally, we use the Execute method of the Connection object to run our SQL.

There is one caveat, though. We have performed no data validation. If the user entered in "ABC" as a cost, when this loop is run, an error will occur when the Execute method tries to insert "ABC" into a currency field in the database. Adding data validation is an exercise left to the reader. You can either use client-side validation on ListProducts.asp, or server-side validation in UpdateCosts.asp. Actually, you shold use both methods, to ensure that invalid data won't be entered.

There are a couple of things left to do with UpdateCosts.asp. We need to close and free our Connection object, and either display a message to the user, or send them back to ListProducts.asp. I chose to send them back to ListProducts.asp, but you can do whatever you like. Here is the remainder of the code:

objConn.Close Set objConn = Nothing Response.Redirect "ListProducts.asp"

I hope this article has been educational! Blake, and others, I hope this article is sufficient! :)

Happy Programming!

Attachments: