Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

Datagrid Delete

Hi I'm having a problem deleting rows from my datagrid. Basically I hit delete and a message box pops up and asks if Im sure I want to delete so I hit yes and then I get the following error -->Could not find stored procedure 'delete from SECTION_TBL where SECT_ID = @.SECT_ID'.

Is it my code thats wrong or is our test sql server that is the problem?

1<%@. Page Language="VB" EnableEventValidation="True" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>2<%@. import namespace="System" %>3<%@. import namespace="System.Data" %>4<%@. import namespace="System.Data.SqlClient" %>56<script language="VB" runat="server">78Dim sectionAs String9 Dim myconnectionAs SqlConnection10Dim mydaAs SqlDataAdapter11Dim dsAs DataSet1213Sub Page_Load(ByVal SourceAs Object,ByVal EAs EventArgs)14 BindData()15End Sub1617 Sub BindData()1819Dim strConnAs String ="server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS"20Dim sqlAs String ="Select * from SECTION_TBL"21 myconnection =New SqlConnection(strConn)22 myda =New SqlDataAdapter(sql, myconnection)23 ds =New DataSet24 myda.Fill(ds,"SECTION_TBL")25 sectList.DataSource = ds26 sectList.DataBind()2728End Sub2930 Private Sub sectList_ItemDataBound(ByVal senderAs Object,ByVal eAs DataGridItemEventArgs)Handles sectList.ItemDataBound3132Dim lAs LinkButton3334If e.Item.ItemType = ListItemType.ItemOr e.Item.ItemType = ListItemType.AlternatingItemThen35 l =CType(e.Item.Cells(0).FindControl("cmdDel"), LinkButton)36 l.Attributes.Add("onclick","return getconfirm();")37End If3839 End Sub4041 Sub sectList_DeleteCommand(ByVal sAs Object,ByVal eAs DataGridCommandEventArgs)4243Dim ConnectionStrAs String = ConfigurationManager.AppSettings("ConnStr")44Dim connAs SqlConnection45Dim cmdAs SqlCommand46Dim IdAs Integer4748 Id =CInt(e.Item.Cells(0).Text)49 conn =New SqlConnection("server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS")50 cmd =New SqlCommand("delete from SECTION_TBL where SECT_ID = @.SECT_ID", conn)51 cmd.CommandType = CommandType.StoredProcedure52 cmd.Parameters.Add("@.SECT_ID", SqlDbType.Int).Value = Id5354 cmd.Connection.Open()55 cmd.ExecuteNonQuery()56 cmd.Connection.Close()5758 DataBind()5960End Sub61626364</script>6566<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">6768<script language="javascript">6970function getconfirm()71{72if (confirm("Do you want to delete record?")==true)73return true;74else75return false;76}7778</script>7980<table cellpadding="2" cellspacing="2" width="760">81<tr>82<td>Sections</td>83</tr>84<tr>85<td>8687<asp:DataGrid OnDeleteCommand="sectList_DeleteCommand" ID="sectList" runat="server" DataKeyField="SECT_ID" AutoGenerateColumns="False">8889<Columns>9091<asp:BoundColumn DataField="SECT_ID" Visible="False" />9293<asp:HyperLinkColumn HeaderText="SECTION NAME" DataTextField="SECT_NAME" DataNavigateUrlField="SECT_ID" DataNavigateUrlFormatString="manageSection.aspx?SECT_ID={0}" />9495<asp:TemplateColumn>96<ItemTemplate>97<asp:LinkButton id="cmdDel" runat="server" Text="Delete" CommandName="Delete" CausesValidation="false" />98</ItemTemplate>99</asp:TemplateColumn>100101</Columns>102103</asp:DataGrid>104105</td>106</tr>107<tr>108<td></td>109</tr>110</table>111112</asp:Content>

Thanks in advance.

on line 51 you should use

CommandType.Text

|||

Brilliant - This works great

Thank you.

database type question

Hi everyone, I am trying to create a page on my website that will be unique depending on the logged on user. Basically, I have a aspnet_User table, a Team table, and a Player table. What I am trying to do is make a datagridview that is editable, but only showing the 'players' from the player table that is associated to that member. I have tried to set a relation between the tables in a dataset, but that only got me so far before I got stuck, and I've also tried to do it making a view between the three tables before I got stuck. My tables look like this:

'aspnet_Users' has a
User ID
User Name
along with the other column associated with this table

'Team' has a
Team ID
Team Name
User Name

'Player' has a
Player Name
Team ID
Player Rating
Class

What I would really like to be able to do, is when the webpage is loaded, the datagridview reads the User Name from the aspnet_User table and matches it to the corresponding Team ID in the Team Table and then filters the 'Player' table to only show players with the corresponding Team ID. I have been stuck on this for two weeks now and it is driving me crazy. I seem to get a breakthrough only to get stuck again. Please Help!!! If you need any more info from me, please let me know!

Use a stored procedure as your datasource for your GV. Pass the teamID in as a parameter.

|||

What you need is a gridview that is populated by a parameterised query, like so:

SELECT
PlayerName
FROM
Team INNER JOIN Player ON Team.TeamID = Player.TeamID INNER JOIN
aspnet_users ON Team.UserName = aspnet_users.UserName
WHERE
aspnet_users.UserName @.UserName

Set this at the SQL for the Gridview and then the @.UserName parameter's value is Context.User.Identity.Name. Context.User.Identity.Name is the username of the currently logged in user.

|||

Ok, now, stupid question

By gridview, you mean make a view from the database right?

I then put in the three tables, make my relationship and add in the sql statement you just provided me, correct?

Then where, and/or how do I set the parameter's value?

I really appreciate the help guys, I think this is starting to make sense!

|||

Ok,

I made my gridview with the 3 tables and tried to insert the sql statement you gave me and it tells me this

incorrect syntax near @.UserName

|||

Here's a good thread for you:

http://forums.asp.net/p/991273/1317881.aspx

|||

First off, thank everyone so far for all your help.

I think I have everything set up the way it is supposed to be. When I test the query and type in 'ryan', one of my UserNames it returns the correct team. if I try to type in a wrong name that does not exist (i even put a name in the Team table and not the username table, and it didn't pull, so i think it is pulling from the right table)

Here's the problem. when i go to web page preview, no datagridview shows up on my page, nothing, nada, zip...like it doesn't exist.

what do i have wrong?

|||

Ah. Try a databind for your grid on the GridView_PreRender event.

|||

So are you typing in a username in a textbox and then clicking a button? On the button click event are you calling the DataBind method on your gridview?

All you need to do is call this in your PageLoad event after setting the textbox.text to the name of the currently logged in user. Do you follow?

|||

I've got a login box that i inserted through the ide. in the code it doesn't give a pageload reference. i remember doing a pageload on a button click with visual basic, but i'm not sure how to work in into this.

<asp:LoginID="Login1"runat="server"BackColor="#EFF3FB"BorderColor="#B5C7DE"BorderPadding="4"

BorderStyle="Solid"BorderWidth="1px"Font-Names="Verdana"Font-Size="0.8em"

ForeColor="#333333"DestinationPageUrl="index.aspx">

<TitleTextStyleBackColor="#507CD1"Font-Bold="True"Font-Size="0.9em"ForeColor="White"/>

<InstructionTextStyleFont-Italic="True"ForeColor="Black"/>

<TextBoxStyleFont-Size="0.8em"/>

<LoginButtonStyleBackColor="White"BorderColor="#507CD1"BorderStyle="Solid"BorderWidth="1px"

Font-Names="Verdana"Font-Size="0.8em"ForeColor="#284E98"/>

</asp:Login>

It gives me a destination url, but thats about it. does the code need to go into here somewhere?

btw, thank you for your help and patience

|||

Where's your codebehind?

<%@. Page Language="C#"
MasterPageFile="~/MasterPage.master"
AutoEventWireup="true"
EnableEventValidation="false"
Inherits="blah.blah.blah"
Title="balh : blah"
Codebehind="blah.aspx.cs"
Description="blah."
Theme="blah"
EnableTheming="true"
%>

Then you access your page_load in the aspx.cs file.|||

can you give me an example?

btw, it tells me that codebehind is no longer used

|||

ummm. I'm using it. Is there something someone needs to tell me?

Are you using VS2005? When you start a project in C#, select Web and you'll get a aspx and aspx.cs (codebehind) pages. If you are using VB, you may need to wire up the codebehind manually, I don't know. Does anyone out there know about codebehind being obsoleted? AM I CODEBEHIND THE CURVE!?!?!?!

|||

sorry, i'm using vb not c#, my files are aspx.vb

its telling me that it is still compatible with asp.net 2.0, but now they would 'rather' you use the codefile and the inherits property

ok, i can get into the aspx.vb files and it gives me

PartialClass Teams_westerville

Inherits System.Web.UI.Page

EndClass

i put the page_load into here, right?

I'm at work right now, going to try it when i get home

|||

Must be a VB thing. My ASP.Net 2.0 C# page here has a codebehind and an inherits. I'm inheriting what appears to be the namespace... but it compiles. so it must be right, right?