BigDeer
asked on
MSSQL UPDATE command when SELECT is joining multiple tables from separate databases
I have a SQL select pulling data from 3 different views in 2 different databases:
SELECT A.[ItemKey], A.[TagID], A.[PersonKey], A.[PersonSite], A.[ItemClass], A.[Location], A.[ItemStatus], A.[Manufacturer], A.[Model], A.[SerialNumber], A.[SystemName], A.[PersonID], A.[CompanyName], A.[SiteName], A.[ItemType], B.[CarrierName], B.[CarrierID], B.[TrackingNum], B.[Notes], C.[FirstName], C.[LastName]
FROM DATABASE.dbo.[View_OPS_Ite mTags] A
INNER JOIN DATABASE2.dbo.[View_ItemTr acker] B ON A.[ItemKey] = B.[ItemKey]
INNER JOIN DATABASE.dbo.[View_UserLis t] C ON C.[UserKey] = B.[UserKey]
WHERE ((A.[PersonID] = @PersonID) AND (A.[ItemStatus] = @ItemStatus)) ORDER BY A.[TagID]
This works great and display the data exactly how I need but I'm having trouble creating the controls to update the items in the GridView I have generated. I can create a separate gridview using just tables and modify the data with standard gridview edit controls but when I use the above select command and try to create the update string it either does nothing or errors out on the page:
IE:
UPDATE DATABASE2.dbo.[Tracking] A SET [UserKey] = @UserKey, [ItemKey] = @ItemKey, [TicketID] = @TicketID, [StatusID] = @StatusID, [Notes] = @Notes, [CarrierID] = @CarrierID, [TrackingNum] = @TrackingNum, [ReasonID] = @ReasonID WHERE [TrackID] = @original_TrackID
I'm relatively new to this and have no clue where to go from here since most examples online use a small single database that I'm having trouble translating over to what I'm trying to accomplish. Personally, I'd prefer to have this done in a code behind but I'll take whatever I can get at this point.
Full code:
I'll eventually want to add some kind of control to insert new rows into the second database and remove rows if needed.
FYI, DATABASE.dbo.* is read only where DATABASE2.dbo.* is the one I need to write to.
Thanks.
SELECT A.[ItemKey], A.[TagID], A.[PersonKey], A.[PersonSite], A.[ItemClass], A.[Location], A.[ItemStatus], A.[Manufacturer], A.[Model], A.[SerialNumber], A.[SystemName], A.[PersonID], A.[CompanyName], A.[SiteName], A.[ItemType], B.[CarrierName], B.[CarrierID], B.[TrackingNum], B.[Notes], C.[FirstName], C.[LastName]
FROM DATABASE.dbo.[View_OPS_Ite
INNER JOIN DATABASE2.dbo.[View_ItemTr
INNER JOIN DATABASE.dbo.[View_UserLis
WHERE ((A.[PersonID] = @PersonID) AND (A.[ItemStatus] = @ItemStatus)) ORDER BY A.[TagID]
This works great and display the data exactly how I need but I'm having trouble creating the controls to update the items in the GridView I have generated. I can create a separate gridview using just tables and modify the data with standard gridview edit controls but when I use the above select command and try to create the update string it either does nothing or errors out on the page:
IE:
UPDATE DATABASE2.dbo.[Tracking] A SET [UserKey] = @UserKey, [ItemKey] = @ItemKey, [TicketID] = @TicketID, [StatusID] = @StatusID, [Notes] = @Notes, [CarrierID] = @CarrierID, [TrackingNum] = @TrackingNum, [ReasonID] = @ReasonID WHERE [TrackID] = @original_TrackID
I'm relatively new to this and have no clue where to go from here since most examples online use a small single database that I'm having trouble translating over to what I'm trying to accomplish. Personally, I'd prefer to have this done in a code behind but I'll take whatever I can get at this point.
Full code:
<asp:GridView ID="GridView1" runat="server" EnableModelValidation="True" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="DATABASE" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ItemKey" HeaderText="ItemKey" SortExpression="ItemKey" ReadOnly="true"/>
<asp:BoundField DataField="TagID" HeaderText="TagID" SortExpression="TagID" ReadOnly="true"/>
<asp:BoundField DataField="PersonKey" HeaderText="PersonKey" SortExpression="PersonKey" ReadOnly="true"/>
<asp:BoundField DataField="PersonSite" HeaderText="PersonSite" SortExpression="PersonSite" ReadOnly="true"/>
<asp:BoundField DataField="ItemClass" HeaderText="ItemClass" SortExpression="ItemClass" ReadOnly="true"/>
<asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location" ReadOnly="true"/>
<asp:BoundField DataField="ItemStatus" HeaderText="ItemStatus" SortExpression="ItemStatus" ReadOnly="true"/>
<asp:BoundField DataField="Manufacturer" HeaderText="Manufacturer" SortExpression="Manufacturer" ReadOnly="true"/>
<asp:BoundField DataField="Model" HeaderText="Model" SortExpression="Model" ReadOnly="true"/>
<asp:BoundField DataField="SerialNumber" HeaderText="SerialNumber" SortExpression="SerialNumber" ReadOnly="true"/>
<asp:BoundField DataField="SystemName" HeaderText="SystemName" SortExpression="SystemName" ReadOnly="true"/>
<asp:BoundField DataField="PersonID" HeaderText="PersonID" SortExpression="PersonID" ReadOnly="true"/>
<asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" ReadOnly="true"/>
<asp:BoundField DataField="SiteName" HeaderText="SiteName" SortExpression="SiteName" ReadOnly="true"/>
<asp:BoundField DataField="ItemType" HeaderText="ItemType" SortExpression="ItemType" ReadOnly="true"/>
<asp:BoundField DataField="CarrierName" HeaderText="CarrierName" SortExpression="CarrierName" />
<asp:BoundField DataField="CarrierID" HeaderText="CarrierID" SortExpression="CarrierID" ReadOnly="true"/>
<asp:BoundField DataField="TrackingNum" HeaderText="TrackingNum" SortExpression="TrackingNum" />
<asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ReadOnly="true"/>
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ReadOnly="true" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>
<asp:SqlDataSource runat="server" ID="DATABASE"
ConnectionString="<%$ ConnectionStrings:DATABASE %>"
SelectCommand="SELECT A.[ItemKey], A.[TagID], A.[PersonKey], A.[PersonSite], A.[ItemClass], A.[Location], A.[ItemStatus], A.[Manufacturer], A.[Model], A.[SerialNumber], A.[SystemName], A.[PersonID], A.[CompanyName], A.[SiteName], A.[ItemType], B.[CarrierName], B.[CarrierID], B.[TrackingNum], B.[Notes], C.[FirstName], C.[LastName] FROM DATABASE.dbo.[View_OPS_ItemTags] A INNER JOIN DATABASE2.dbo.[View_ItemTracker] B ON A.[ItemKey] = B.[ItemKey] INNER JOIN DATABASE.dbo.[View_UserList] C ON C.[UserKey] = B.[UserKey] WHERE ((A.[PersonID] = @PersonID) AND (A.[ItemStatus] = @ItemStatus)) ORDER BY A.[TagID]"
UpdateCommand="UPDATE DATABASE2.dbo.[Tracking] A SET [UserKey] = @UserKey, [ItemKey] = @ItemKey, [TicketID] = @TicketID, [StatusID] = @StatusID, [Notes] = @Notes, [CarrierID] = @CarrierID, [TrackingNum] = @TrackingNum, [ReasonID] = @ReasonID WHERE [TrackID] = @original_TrackID"
DeleteCommand="DELETE FROM DATABASE2.dbo.[Tracking] WHERE [TrackID] = @original_TrackID"
>
<DeleteParameters>
<asp:parameter Name="original_TrackID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:parameter Name="UserKey" Type="Int32" />
<asp:parameter Name="ItemKey" Type="Int32" />
<asp:parameter Name="TicketID" Type="Int32" />
<asp:parameter Name="StatusID" Type="Int32" />
<asp:parameter Name="Notes" Type="String" />
<asp:parameter Name="CarrierID" Type="Int32" />
<asp:parameter Name="TrackingNum" Type="String" />
<asp:parameter Name="ReasonID" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:parameter Name="UserKey" Type="Int32" />
<asp:parameter Name="ItemKey" Type="Int32" />
<asp:parameter Name="TicketID" Type="Int32" />
<asp:parameter Name="StatusID" Type="Int32" />
<asp:parameter Name="Notes" Type="String" />
<asp:parameter Name="CarrierID" Type="Int32" />
<asp:parameter Name="TrackingNum" Type="String" />
<asp:parameter Name="ReasonID" Type="Int32" />
<asp:parameter Name="original_TrackID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:parameter DefaultValue="COMP" Name="PersonID" Type="String" />
<asp:parameter DefaultValue="Active" Name="ItemStatus" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I'll eventually want to add some kind of control to insert new rows into the second database and remove rows if needed.
FYI, DATABASE.dbo.* is read only where DATABASE2.dbo.* is the one I need to write to.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for BigDeer's comment #a39189098
for the following reason:
You're correct. I figured out how to build the update query automatically from SSMS and using the SELECT to join the tables I needed to create my view so I didnt have the 3 table uneditable view in my update command. Many thanks.
Accepted answer: 0 points for BigDeer's comment #a39189098
for the following reason:
You're correct. I figured out how to build the update query automatically from SSMS and using the SELECT to join the tables I needed to create my view so I didnt have the 3 table uneditable view in my update command. Many thanks.
ASKER
Is there a way in a code behind to use a separate select and update to modify the data from table to table instead of directly updating the view?