VS 2010 SQL Update for SQL Statement

Question

Please bear with me as I'm just beginning to learn this stuff.

I have a VS 2010 Web project up and I'm trying to understand how I can make a custom UpdateCommand (Because I chose to write my own SQL statement, I do not have the option for VS 2010 to auto generate an update command for me.) Problem is: I don't know what the UpdateCommand should look like. Here is my Select:

SELECT * FROM Dbo.MainAsset, dbo.Model, dbo.Hardware WHERE MainAsset.device = Hardware.DeviceID AND MainAsset.model = Model.DeviceID

Which, VS 2010 turns into:

SELECT MainAsset.pk, MainAsset.img, MainAsset.device, MainAsset.model, MainAsset.os, MainAsset.asset, MainAsset.serial, MainAsset.inyear, MainAsset.expyear, MainAsset.site, MainAsset.room, MainAsset.teacher, MainAsset.FirstName, MainAsset.LastName, MainAsset.Notes, MainAsset.Dept, MainAsset.AccountingCode, Model.Model AS Hardware, Model.pk AS Model, Model.DeviceID, Hardware.Computer, Hardware.pk AS Expr3, Hardware.DeviceID AS Expr4 FROM MainAsset INNER JOIN Hardware ON MainAsset.device = Hardware.DeviceID INNER JOIN Model ON MainAsset.model = Model.DeviceID

How would I approach updating one column, say "MainAsset.site" if that's changed in the Gridview DDL? Any help constructive help would be appreciated. Thank you.

Answer 1

I am asuming that you would like to use the UpdateCommand with a SqlDataSource and a GridView.

The update command uses SQL:

Update yourTable SET columnName = 'yourValue';
Update MainAsset Set site ='http://stackoverflow.com';

In the example from msdn the database columns (fields) are bound to the gridview1. The values for the BoundField can be passed by using the value of DataField with an @. The database coulmn LastName will be bound to a grid column <asp:BoundField HeaderText="Last Name" DataField="LastName" /> and in the update command @LastName is used to pass the value:

 <asp:SqlDataSource
      id="SqlDataSource1"
      runat="server"
      DataSourceMode="DataSet"
      ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
      SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
      UpdateCommand="Update Employees SET   
         FirstName=@FirstName,LastName=@LastName,
         Title=@Title WHERE EmployeeID=@EmployeeID"
      OnUpdated="OnDSUpdatedHandler">
  </asp:SqlDataSource>

  <asp:GridView
      id="GridView1"
      runat="server"
      AutoGenerateColumns="False"
      DataKeyNames="EmployeeID"
      AutoGenerateEditButton="True"
      DataSourceID="SqlDataSource1">
      <columns>
          <asp:BoundField HeaderText="First Name" DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" DataField="LastName" />
          <asp:BoundField HeaderText="Title" DataField="Title" />
      </columns>
  </asp:GridView>

Update to address your comment

Your Select * from Dbo.MainAsset, dbo.Model ... loads data from different tables and displays them as one big table. This is called a join. Your Select uses the old join syntax instead of the newer syntax

I am uncertain if i understand your comment: "I'm asking for how the syntax should look for the UpdateCommand." In your update do you want to update the values in all 3 tables (MainAsset, Model and Hardware)? If this is the case we need to know the single columns that are displayed in your grid. You can take a look at this question or at this update join explanation to better understand updates with a join.

Update 2 Syntax for UpdateCommand

1. How I can make a custom UpdateCommand?
2. How would I approach updating one column, say "MainAsset.site"
3. How to update a record if a dropdownlist changes?

First you need to know how a normal update statement in SQL Server looks like:

UPDATE MainAsset
    SET site= NewValueForSite
        ,teacher = 'Dr. Seuss'
    WHERE rowId = FilterValue

To create a custom update command you write the sql that you need and assign / pass it to the property UpdateCommand of SqlDataSource. You could do the following (assuming that pk stands for primary key and identifies a row):

 <asp:SqlDataSource
      id="SqlDataSource1"
      // ... see above          
      UpdateCommand="Update MainAsset SET   
         site=@site
         ,teacher=@teacher,
         WHERE pk=@pk"
      OnUpdated="OnDSUpdatedHandler">
  </asp:SqlDataSource>

Regarding your third question i would like to point you at this question:

<asp:DropDownList ID="ddlSite" runat="server"  
   AutoPostBack="True" 
   OnSelectedIndexChanged="SelectionHasChanged"
   DataSourceID="SqlDataSource1" DataTextField="site"
   DataValueField="pk" AppendDataBoundItems="true">

Answer 2

Long story short, here is what I did:

The DDL in question:

            <asp:TemplateField HeaderText="site" SortExpression="site">
                <EditItemTemplate>
                    <asp:DropDownList ID="ddlSites" runat="server" AutoPostBack="True" 
                        DataSourceID="RetrieveSiteSQL" DataTextField="Sites" DataValueField="Sites" 
                        SelectedValue='<%# Bind("site") %>'>
                    </asp:DropDownList>
                </EditItemTemplate>

I figured out that my RetrieveSiteSQL was not the one that needed the update command, Instead, it was my "main" SQL Data Source that was updated when I click on "Update" (in the edit template.) So, I figured out that the UpdateCommand="UPDATE MainAsset ..." statement had to look something similar to:

UpdateCommand="UPDATE MainAsset SET [site] = @Site WHERE [pk] = @pk">

I have a different problem now, but it's a separate issue, so I will post a different question on this site.

Thanks for your help all.