0 Replies - 2915 Views - Last Post: 08 January 2010 - 10:36 AM Rate Topic: -----

#1 jpcsmit  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 26-September 09

Implement custom sql query to a gridview with template field

Posted 08 January 2010 - 10:36 AM

I would like to know how to go about making a custom sql query then put the list to a gridview with a template field. Custom sql query because first I have a textfile to read. Then do a for loop for each line to add to the sql query. Here's my sql query to query all data in db:
SELECT tb_hijack.entry, tb_hijack.info, 
(SELECT AVG(Rating) AS Expr1 
FROM tb_comments 
WHERE (Entry = Entry)) AS Expr1, tb_hijack.kind 
FROM tb_hijack 
INNER JOIN tb_comments AS tb_comments_1 
ON tb_hijack.entry = tb_comments_1.Entry



Once the file was read, it would have this query:
SELECT tb_hijack.entry, tb_hijack.info, 
(SELECT AVG(Rating) AS Expr1 
FROM tb_comments 
WHERE (Entry = Entry)) AS Expr1, tb_hijack.kind 
FROM tb_hijack 
WHERE Entry=line1 OR Entry=line2 OR Entry=line3 OR ...
INNER JOIN tb_comments AS tb_comments_1 
ON tb_hijack.entry = tb_comments_1.Entry



This is easy if only I don't have to read the line and just query all data in db, I just have to configure the sql query in the smart tag of the gridview...but with this custom sql query, I'm not sure on how to go about it.

Here's the html source
		<asp:GridView ID="GridView1" runat="server" CellPadding="2" ForeColor="Black" 
		GridLines="None" AutoGenerateColumns="False" DataKeyNames="entry" 
		DataSourceID="SqlDataSource1" BackColor="LightGoldenrodYellow" 
		BorderColor="Tan" BorderWidth="1px" EnableViewState="False">
		<Columns>
			<asp:ButtonField ButtonType="Image" CommandName="show"
				ImageUrl="~/pics/discuss.gif" Text="Button">
				<ItemStyle VerticalAlign="Top" />
			</asp:ButtonField>
			<asp:TemplateField HeaderText="Entry" SortExpression="entry">
				<EditItemTemplate>
					<asp:Label ID="Label3" runat="server" Text='<%# Eval("entry") %>'></asp:Label>
					<br />
					<br />
					<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" 
						ShowHeader="False">
						<Columns>
							<asp:BoundField DataField="Entry" HeaderText="Entry" SortExpression="Entry" 
								Visible="False" />
							<asp:BoundField DataField="Comment" HeaderText="Comment" 
								SortExpression="Comment" />
							<asp:BoundField DataField="Rating" HeaderText="Rating" 
								SortExpression="Rating" />
							<asp:BoundField DataField="Whois" HeaderText="Whois" SortExpression="Whois" />
						</Columns>
					</asp:GridView>
				</EditItemTemplate>
				<ItemTemplate>
					<asp:Label ID="Label3" runat="server" Text='<%# Bind("entry") %>'></asp:Label>
					<br />
					<br />
					<asp:Panel ID="Panel1" runat="server" style="margin-right: 0px" Visible="False">
						<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" 
							DataSourceID="SqlDataSource2">
							<Columns>
								<asp:TemplateField HeaderText="Rating" SortExpression="Rating">
									<EditItemTemplate>
										<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Rating") %>'></asp:TextBox>
									</EditItemTemplate>
									<ItemTemplate>
										<asp:Label ID="Label9" runat="server" Text='<%# Bind("Rating") %>' 
											Visible="False"></asp:Label>
										<asp:Image ID="Image3" runat="server" />
									</ItemTemplate>
								</asp:TemplateField>
								<asp:BoundField DataField="Comment" HeaderText="Comment" 
									SortExpression="Comment" />
								<asp:BoundField DataField="Whois" HeaderText="Whois" SortExpression="Whois" />
							</Columns>
						</asp:GridView>
						<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
							ConnectionString="<%$ ConnectionStrings:hijackConnectionString %>" SelectCommand="SELECT Comment, Rating, Whois FROM tb_comments
WHERE Entry = @Entry">
							<SelectParameters>
								<asp:ControlParameter ControlID="Label3" Name="Entry" PropertyName="Text" />
							</SelectParameters>
						</asp:SqlDataSource>
						<br />
						<br />
						<asp:Label ID="Label4" runat="server" Text="Visitor's Assessment"></asp:Label>
						<br />
						<asp:TextBox ID="TextBox2" runat="server" Height="86px" Width="385px" 
							MaxLength="1" TextMode="MultiLine"></asp:TextBox>
						<asp:RegularExpressionValidator ID="regval" runat="server" ControlToValidate="TextBox2" ErrorMessage="Not more than 200 characters" Display="Static" ValidationExpression='^[\s\S]{0,200}$'></asp:RegularExpressionValidator>
							
						<br />
						<br />
						<asp:Label ID="Label6" runat="server" Text="Rating"></asp:Label>
						<br />
						<asp:DropDownList ID="DropDownList1" runat="server">
							<asp:ListItem>1 - Extremely Nasty</asp:ListItem>
							<asp:ListItem>2 - Nasty</asp:ListItem>
							<asp:ListItem Selected="True">3 - Neutral</asp:ListItem>
							<asp:ListItem>4 - Safe</asp:ListItem>
							<asp:ListItem>5 - Very Safe</asp:ListItem>
						</asp:DropDownList>
						<br />
						<br />
						<asp:Button ID="btnComment" runat="server" CommandName="comment" 
							Text="Submit" CommandArgument="<%# Container.DataItemIndex %>" />
					</asp:Panel>
				</ItemTemplate>
				<ItemStyle Font-Size="Small" VerticalAlign="Top" Wrap="True" />
			</asp:TemplateField>
			<asp:TemplateField HeaderText="Kind" SortExpression="kind">
				<EditItemTemplate>
					<asp:Image ID="Image2" runat="server" />
					<asp:Label ID="Label2" runat="server" Text='<%# Bind("kind") %>' 
						Visible="False"></asp:Label>
				</EditItemTemplate>
				<ItemTemplate>
					<asp:Image ID="Image2" runat="server" Height="30px" Width="27px" />
					<asp:Label ID="Label2" runat="server" Text='<%# Bind("kind") %>' 
						Visible="False"></asp:Label>
				</ItemTemplate>
				<ItemStyle HorizontalAlign="Center" VerticalAlign="Top" />
			</asp:TemplateField>
			<asp:TemplateField HeaderText="Rating" SortExpression="Expr1">
				<EditItemTemplate>
					<asp:Image ID="Image1" runat="server" Height="10px" Width="50px" />
					<br />
					<asp:Label ID="Label1" runat="server" Text='<%# Eval("Expr1") %>' 
						Visible="False"></asp:Label>
				</EditItemTemplate>
				<ItemTemplate>
					<asp:Image ID="Image1" runat="server" Height="11px" Width="40px" />
					<br />
					<asp:Label ID="Label1" runat="server" Text='<%# Bind("Expr1") %>' 
						Visible="False"></asp:Label>
				</ItemTemplate>
				<ItemStyle HorizontalAlign="Center" VerticalAlign="Top" />
			</asp:TemplateField>
			<asp:TemplateField HeaderText="Info" SortExpression="info">
				<EditItemTemplate>
					<asp:TextBox ID="TextBox1" runat="server" Height="44px" 
						Text='<%# Bind("info") %>' TextMode="MultiLine" Width="300px"></asp:TextBox>
				</EditItemTemplate>
				<ItemTemplate>
					<asp:Label ID="Label7" runat="server" Text='<%# Bind("info") %>'></asp:Label>
				</ItemTemplate>
				<ItemStyle VerticalAlign="Top" Wrap="True" Font-Size="Small" />
			</asp:TemplateField>
			<asp:CommandField ShowEditButton="True">
			<ItemStyle VerticalAlign="Top" />
			</asp:CommandField>
		</Columns>
		<FooterStyle BackColor="Tan" />
		<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" 
			HorizontalAlign="Center" />
		<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
		<HeaderStyle BackColor="Tan" Font-Bold="True" />
		<AlternatingRowStyle BackColor="PaleGoldenrod" />
	</asp:GridView>



Please help. Thanks!

Is This A Good Question/Topic? 0
  • +

Page 1 of 1