SubQuery SqlDatasource

Ahelbling
Ahelbling used Ask the Experts™
on
Is it possible to write an update query for use in a sqldatasource similar to this?  I cannot seem to figure out how to update the original QTY from the DB to (plus or minus), depending on the value entered in the grid edit column.

db
grid edit
Private Sub rgTG_EditCommand(sender As Object, e As GridCommandEventArgs) Handles rgTG.EditCommand
	If e.CommandName = "Edit" Then
		Dim item As GridEditableItem = TryCast(e.Item, GridEditableItem)
		Dim skey As String = item.GetDataKeyValue("TGID").ToString()
		Session("sTGID") = skey

		sqldsRGTG.UpdateCommand = "UPDATE [TubeGroups] SET QTY=(SELECT QTY FROM [TubeGroups] WHERE TGID=@TGID + @QTY) WHERE TGID=@TGID"
		sqldsRGTG.UpdateParameters.Clear()
		sqldsRGTG.UpdateParameters.Add("TGID", CType(Session("sTGID"), String))
		sqldsRGTG.UpdateParameters.Add("QTY", ?????)
		sqldsRGTG.DataBind()
	End If
End Sub

Open in new window


TIA,
Andrew
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
assuming those columns return a value which is NOT NULL you could use a calculated grid column value like:

<asp:TextBox ID="TextBox3" runat="server"
Text='<%# Convert.ToInt32(Eval("Qty"))* Convert.ToDouble(Eval("Price")) %>'></asp:TextBox> 

Open in new window


if you have NULLs then would be something like:

<asp:TextBox ID="TextBox3" runat="server" 
                Text='<%# Eval("Quantity)==DBNull.Value?0:Convert.ToInt32(Eval("Quantity"))* 
                          Convert.ToDouble(Eval("Price")) %>'>
</asp:TextBox>

Open in new window


where obviously you must replace "Price" with "ActualFieldYouNeedToMultiplyWithQTY"

Author

Commented:
Ok, so I would then need to convert the grid column from a GridBoundColumn to a GridTemplateColumn first correct?

<telerik:RadPageView ID="RadPageView3" runat="server" Width="100%">
	<telerik:RadGrid ID="rgTG" runat="server" RenderMode="Lightweight" AutoGenerateColumns="False" CellSpacing="-1" AllowSorting="True" width="100%" Visible="True"
				 AllowAutomaticUpdates="True" DataSourceID="sqldsRGTG" >
		<GroupingSettings CollapseAllTooltip="Collapse all groups"></GroupingSettings>
		<ClientSettings  EnableRowHoverStyle="True" Selecting-AllowRowSelect="False" EnablePostBackOnRowClick="False">
			<Scrolling AllowScroll="True" UseStaticHeaders="True"/>
		</ClientSettings>
		<MasterTableView DataSourceID="sqldsRGTG" DataKeyNames="TGID" EditMode="InPlace" CssClass="MyGridClass">
			<Columns>
				<telerik:GridEditCommandColumn UniqueName="EditCommandColumn" ItemStyle-HorizontalAlign="center">
				</telerik:GridEditCommandColumn>
				<telerik:GridBoundColumn DataField="SGID" HeaderText="SGID" SortExpression="SGID" UniqueName="SGID" ReadOnly="True">
				</telerik:GridBoundColumn>
				<telerik:GridBoundColumn DataField="TGID" HeaderText="TGID" SortExpression="TGID" UniqueName="TGID" ReadOnly="True">
				</telerik:GridBoundColumn>
				<telerik:GridBoundColumn DataField="CreatedBy" HeaderText="Created By" SortExpression="CreatedBy" UniqueName="CreatedBy" ReadOnly="True">
				</telerik:GridBoundColumn>
				<telerik:GridBoundColumn DataField="Qty" HeaderText="Qty" SortExpression="Qty" UniqueName="Qty" ReadOnly="False">
				</telerik:GridBoundColumn>
				<telerik:GridTemplateColumn UniqueName="blank" HeaderText=" " Visible="True">
					<ItemTemplate>
						
					</ItemTemplate>
				</telerik:GridTemplateColumn>
			</Columns>
		</MasterTableView>
		<FilterMenu RenderMode="Lightweight"></FilterMenu>
		<HeaderContextMenu RenderMode="Lightweight"></HeaderContextMenu>
	</telerik:RadGrid>
</telerik:RadPageView>

Open in new window

Author

Commented:
And actually I am trying to update the QTY in the TubeGroups table to reflect the edit to the QTY in the grid that is tied to the ShipGroupDetail table for that particular TubeGroup.  For instance, there is a TubeGroup #3550 in the TubeGroup table with a QTY of 10.  I then create a ShipGroup #1134 by selecting 5 of those Tubes from TubeGroup #3550.  Later I want to edit(add/remove) say 3 of these same Tubes from the ShipGroup #1134.  I need to be able to go back and update the TubeGroup #3550 QTY by adding 3 to the 5 that are in the TubeGroup table for #3550 to give a QTY of 8 left in the TubeGroup#3550 and a QTY of 3 in the ShipGroup#1134 for this TubeGroup #3550.  Sorry, I don't feel I am explaining this all that great...

TubeGroupsShipGroupsShipGroupDetails
lcohanDatabase Analyst

Commented:
Sorry for late reply -  I think what you asked for
how to update the original QTY from the DB to (plus or minus), depending on the value entered in the grid edit column

is more like described here https://www.devexpress.com/Support/Center/Question/Details/Q535797/how-to-create-calculated-column-in-gridcontrol as the GridTemplateColumn provides only the  number and the widths of columns in a grid layout but they are not doing the calculation of the value for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial