troubleshooting Question

Binding SQL parameters using ADO.Net, bound to controls or to datatable?

Avatar of beihudson
beihudson asked on
Visual Basic.NET
2 Comments1 Solution605 ViewsLast Modified:
I have a question about programming the VB ADO.NET database tools.

I'm doing a program that talks to an SQL Server database using ADO.Net.  I've read the MS ADO.Net book through and am a bit confused in Chapter 10 where it talks about binding SQLCommand object parameters to columns in a DataTable, within a DataSet  vs binding those parameters to controls on the form.  

No, I'm not using a DataForm Wizard.

I'm kinda confused about how the DataAdapter objects Update() method actually works.  When I call objDataAdapter.Update(objDataSet)  what I think is happening is that the DataAdapter uses the SQLCommand associated with the Update method and the columns in the DataSet object to form an SQL command which is sent to the DB which does the table updating.  I think the values it uses are the values in the DataSet and if you tell it so by using the SQLParameter's SourceVersion property you can force it to use original values (in the DataSet) instead of current values (in the DataSet).

I'm using a set of controls bound to the DataSet so again I think that the current values in the DataSet are the values in the bound controls and the "original" values are the ones that came up from the database after the last time data was loaded from the database.

Here's my original code that (I think) binds SQL Command parameters to controls on the form.


        objUpdateExpenseCommand = New SqlCommand("UPDATE tblExpenses SET ExpenseReportNumber = @ExpenseReportNumber, " & _
                                                 "ExpenseDate = @ExpenseDate, " & _
                                                 "PrePaid = @PrePaid, " & _
                                                 "Vendor = @Vendor, " & _
                                                 "LocationVisited = @LocationVisited, " & _
                                                 "MilesTraveled = @MilesTraveled, " & _
                                                 "Class = @Class, " & _
                                                 "Days = @Days, " & _
                                                 "PersonsEntertained = @PersonsEntertained, " & _
                                                 "Company = @Company, " & _
                                                 "Purpose = @Purpose, " & _
                                                 "Place = @Place, " & _
                                                 "Other = @Other, " & _
                                                 "CategoryNumber = @CategoryNumber, " & _
                                                 "DirectBill = @DirectBill, " & _
                                                 "Cost = convert(money,@Cost) " & _
                                                 "WHERE ExpenseNumber = @ExpenseNumber")
        objUpdateExpenseCommand.Connection = objConnection
        With objUpdateExpenseCommand.Parameters
            .Add("@ExpenseReportNumber", strExpenseReportNumber)
            .Add("@ExpenseDate", objDataEntryForm.objExpenseDate.Value)
            .Add("@PrePaid", objDataEntryForm.chkPrePaid.Checked)
            .Add("@Vendor", objDataEntryForm.txtVendor.Text)
            .Add("@LocationVisited", objDataEntryForm.txtExpensePlace.Text)
            .Add("@MilesTraveled", objDataEntryForm.txtExpenseMiles.Text)
            .Add("@Class", objDataEntryForm.txtClass.Text)
            .Add("@Days", objDataEntryForm.txtDays.Text)
            .Add("@PersonsEntertained", objDataEntryForm.txtExpensePersonsEntertained.Text)
            .Add("@Company", objDataEntryForm.txtExpenseCompany.Text)
            .Add("@Purpose", objDataEntryForm.txtExpensePurpose.Text)
            .Add("@Place", objDataEntryForm.txtExpensePlace.Text)
            .Add("@Other", objDataEntryForm.txtExpenseOther.Text)
            .Add("@CategoryNumber", objDataEntryForm.lstCategories.SelectedIndex)
            .Add("@DirectBill", objDataEntryForm.chkExpenseDirect.Checked)
            .Add("@Cost", objDataEntryForm.txtExpenseCost.Text)
            .Add("@ExpenseNumber", strExpenseReportNumber)
        End With

In Chapter 10 of my book, it talks about a different Add() method syntax that looks like this:

        .Add("@ExpenseNumber", OleDBType.Integer, 0, "ExpenseNumber")

It seems to me that this does basically the same thing... just differently.  I'm confused about how the parameter is connected to the data columns.  I seems you just need to have a table with the same named column when you use the alternate syntax.

Does this make much of a difference?  If I'm bound to the controls and those have the current value how is this different from being bound to the DataSet or DataTable and using the current value there?
ASKER CERTIFIED SOLUTION
Sancler

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros