I'm trying to create a simple search against a SQL Database. I setup a TextBox and a GridView, I simply need the contents of the textbox to be used on databind() in my SQL select statement. Seems like it should be a simple thing to do, any guidance is appreciated! Here is my code:
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
Hi all, I want to do something like this, but I cannot get it working: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AFS_WAWI_DBConnectionString1 %>"<%If Session("my") = "123" thenSelectCommand="SELECT [nr1] FROM [Artikel]"ElseSelectCommand="SELECT [nr2] FROM [Artikel]"End if %></asp:SqlDataSource> Is that possible and how can I manage that? Thanks for your answers, Chris.
Hi! I dont know if i will explain this correctly, but my problem is with reporting service. I'm supposed to Sum value in one textbox and than that sum use it in sum in other textbox.
Something like this: Sum(Fields!Abc.Value/(Fields!dfg.Value+Sum(Fields!abc.Value)),"matrix1_RowGroup1")*100 I get error msg 'The Value expression for the textbox 'textbox49' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.'
Something like this wont work either Sum(Fields!abc.Value/ReportItems("textbox56").Value)*100 Error The Value expression for the textbox 'textbox55' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
So, pls help if you know how to reference textbox in other in body of report. Thx.
for some reason the following code fails. I want to present all of the information of the CD (from table B)that inlcudes the song name the user types in the text box(table A). Meaning the user types the name, it's located in table A, for each CD id in table A I want to present all the info from table B. Do I have to use DataRelation?: private void Button1_Click(object sender, System.EventArgs e) { System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter(); string str=TextBox1.Text;
My Stored procedure is: CREATE PROCEDURE dbo.CdInfoSP (@song nvarchar)AS select cd_id,cd_name,year from song_cd_artist,cd_info where song_name like '%@song%' and cdofsong_id=cd_idGO
Hello everyone, I would like to know if there is a way to know if a sqldatasource.selectcommand returns tables or not? I'm trying to make a If statement so that if the selectcommand isn't returning anything it will disable a checkbox.
If I hard code the select statement everything works fine. <asp:SqlDataSource ID="SqlDataSourceZip" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="SELECT * FROM [Station] WHERE ([ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528')"> </asp:SqlDataSource> Want to use a label control to return data, but can't find anything that works. <asp:Label ID="zipLabel" runat="server"></asp:Label> [ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528' something like this, but it don't work. SelectCommand="SELECT * FROM [Station] WHERE (<%=zipLabel.Text%>)"> </asp:SqlDataSource>
Hello, I'm new to ASP.NET (past 10 months) and am a little stuck. I will try to explain the best I can. I have a dropdown (ddNames) with a list of peoples names from my database and the value is their (staffID). When I select a name from the dropdown I want to get that Staff members details from my database. Everything works perfectly until I get to the [[ WHERE staffID = "dropdown value??" ]] Can you help?
I am trying to set a select command in a VB script which is activated by a button and to display the data in a gridview The script code is SqlDataSource1.SelectCommand = "SELECT MemberID, Title, FirstName, LastName, EmailAddress, Password, DateTimeStamp, IPAddress, Description, Location, Position FROM Members, Careers WHERE " + Search.ToString() + " AND MemberID = CareerID" and the grid view is<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"AutoGenerateColumns="False" DataKeyNames="MemberID" ataSourceID="SqlDataSource1"BorderWidth="0" BorderColor="White"EmptyDataText="There are no data records to display." GridLines="None" ShowHeader="False" > this works fine until the next page button is pressed then the message "No data records" comes up even though there are plenty of records still to come. It works fine if I set AllowPaging to be False. Can anyone help?
Hi, I try to do a simple search: <SelectCommand="SELECT color FROM mytable where color LIKE @PARAMETER1 "> <SelectParameters> <asp:Parameter Name="PARAMETER1 " DefaultValue="%" Type="string"/> </SelectParameters> have a code with CASE Dim pColor As String = "%" Case "All" I put to parameter "%"Case "Equals to" I put to parameter Me.TextBoxColor.Text Case "Begins with" I put to parameter Me.TextBoxColor.Text + "%" Case "Contains" I put to parameter "%" + Me.TextBoxColor.Text + "%" Me.SqlDataSource1.SelectParameters("PARAMETER1 ").DefaultValue = pColor Everything work find except my "Equal to case" and I try SELECT color FROM mytable where color LIKE blue and is don't work (return nothing)??? and I don't want %color% because is return me blue sky, not just blue. Thank for help me
Do you guy's know if there is an upper limit on the length of the string that can go into the selectcommand of the sqldatasource? I'm getting strange behavior from the sqldatasource. I'm dynamically generating select strings in the business layer and then programmatically using them in a sqldatasource up in the website. I sometimes get really large select statements (whole pages of joins) and was wondering if there was a character limit to the selectcommand property.
Hi, I use the Count function to get the total from my SelectCommand. However, I need to get my selectcommands dynamically, so is there a way of writing like this somehow: "SELECT COUNT(1) FROM (MypagesSqlDataSource.SelectCommand) Thanks in advance, Pettrer
How do you programmatically change the SQL select statement for the SelectCommand in a SqlDataSource?For instance, if I had two buttons on a page, and for one button, I'd like it to change the select command to be "SELECT * FROM authors WHERE au_id > 100". Then for the second button, I'd like it to show "SELECT * FROM authors WHERE au_lname like S%".Anybody know the code to do this? I was trying to type the code, but the intellisense didn't give me any options to pick from after I typed SqlDataSouce1.SelectCommand.
Greetings all, I am new to ASP, .NET, and VWD2005. My experience lies primarily with PHP. I was impressed with how simple it was to create a datagrid view of a table in VWD, however, now I'm stuck and need some assistance. I have an Access database, and have configured an SQL Data Source to it. (forgive me if my terminology is bad) The code is: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT phones.FirstName, phones.LastName, phones.Phone, Departments.DepartmentName FROM (Departments INNER JOIN phones ON Departments.DEPT_ID = phones.DEPTID) WHERE (phones.FirstName BETWEEN 'A' AND 'AZZZ') ORDER BY phones.FirstName"></asp:SqlDataSource> It works fine and returns all the names/phone numbers of people beginning with 'A' as anticipated. My original intent was to create a sting variable "strQry" which contained the actualy Select statement above. The strQry would be programatically created to alter the WHERE statement accordingly for B,C,D,E - Z. My code would then be <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand=strQry></asp:SqlDataSource> This did not work. What am I doing wrong? I also tried programatically testing the condition of the desired letter (A-Z) and having it use a select case statement to call multiple occurences of the asp:SqlDataSource control. The problem I ran into there is that the ID "SqlDataSource1" is already in use on my subsequent occurences. (regardless, this would produce a huge bloated section of code having 26 instances of the control) I need some help please. Any advice would be greatly appreciated.
Hallo,Why doesnt SelectCommand have problems with angle brackates if I place = insted of <> then it works fine??? Also do I have to convert a Session datetime dtdate like this to select from SQL2000?SelectCommand = "SELECT (SELECT PICTUREID FROM PICTURE WHERE TYPE = 1 AND HOTELID = HOTEL.HOTELID) AS PictureID, dbo.Hotel.HotelID, dbo.Hotel.Name AS HName, dbo.Hotel.Star, dbo.Loc.GLoc, dbo.Loc.NameCZ, dbo.Loc.NameCZ2, MIN(dbo.HSRSeason.Price) AS LowestPrice FROM dbo.Hotel INNER JOIN dbo.HSR ON dbo.Hotel.HotelID = dbo.HSR.HotelID INNER JOIN dbo.HSRSeason ON dbo.HSR.HSRID = dbo.HSRSeason.HSRID INNER JOIN dbo.Room ON dbo.HSR.RoomID = dbo.Room.RoomID INNER JOIN dbo.HSeason ON dbo.Hotel.HotelID = dbo.HSeason.HotelID INNER JOIN dbo.Loc ON dbo.Hotel.LocID = dbo.Loc.LocID WHERE (dbo.Room.Name <> 'Extra Bed') AND (dbo.HSeason.[From] <= CONVERT(DATETIME, '" + @dtdate1 + "', 104)) AND (dbo.HSeason.[To] >= CONVERT(DATETIME, '" + @dtdate1 + "', 104)) GROUP BY dbo.Hotel.HotelID, dbo.Hotel.Name, dbo.Hotel.Star, dbo.Loc.GLoc, dbo.Loc.NameCZ, dbo.Loc.NameCZ2, dbo.Hotel.Com ORDER BY Com DESC"
This is my SQLDataSource for my gridview <asp:SQLDataSource ConnectionString="<%$ ConnectionStrings:Pubs %>" id="sqlProducts" Runat="Server" SelectCommand=' "Select * from adressen INNER JOIN Adressoort ON adressen.AD_ID = Adressoort.AD_ID where " & lblsql.Text ' ></asp:SQLDataSource> the label is something like : adressoort.AD_soort = '3' or adressoort.AD_soort = '1' I keep on getting the error sever tag not well formed
why i type the select command like below the disctinct doesnt work??? the query stil show all the Category i hav so how do i fix it?? <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @UserName + '%') ORDER BY ID DESC"> <SelectParameters> <asp:SessionParameter Name="UserName" SessionField="UserName" Type="String" /> </SelectParameters> </asp:SqlDataSource>
I'm try to achieve the following ; <asp:SqlDataSource ID="CardDataSource" runat="server" ConnectionString = "my connection SelectCommand = "String.Format("Select * from cards where active not like '0' or active is NULL and (card like ' {0}%' or card like '{0}%')", Request.QueryString("db"))" /> I'm trying to pass a value into my query from the sqldatasource, but I'm having trouble properly using the string. This code used to be in a vb code, but theres a considerable performance difference when I have it load from my vb file and when I front load it here. Anyone know what i would have to add or remove to make the above script work? Do I have my single/double quotes mixed up?
Dear Group, I'm upgrading a "classic" ASP app to ASP.NET 2.0. The database is stored on a SQL Server box in-house. I'm looking to inject a bit of flexibility into the SQLDataSource SelectCommand property. What I mean is this: depending on a selection a user makes in the form, I'd like to present a GridView object that displays data filtered by the user's selection. So I took the original long query string from ASP and broke it in two pieces. The first piece contains the bulk of the query and it ends just after the WHERE clause. The second piece is the ORDER BY clause. I'd like to have the ability to inject an additional term at the end of the WHERE clause (using AND). That would be the filter. For example,SELECT LastName, FirstName, InOut, TimeEntered, PhoneExt FROM TimeClock WHERE TimeEntered > '6/17/2008' AND EmploymentState > '' ORDER BY LastName, FirstName So, in between the "AND State > '' " and the ORDER BY clause I'd like to insert something like this:AND Department = 'Production' I'd like the SelectCommand of the SQLDataSource to look like this:SelectCommand="<%= (sql1 & " AND Department = 'Production' " & sql2) %>" sql1 and sql2 are defined in a <script runat="server"></script> block in the page <head>. The following errors occur even if I have a single variable containing the whole SQL query string (i.e., SelectCommand="<%= sql1 %>"). When I try to run the page I get this error:Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'. If I remove the quotation marks from around the SelectCommand statement I get:Parser Error Message: Server tags cannot contain <% ... %> constructs. What's funny about this is that the ConnectionString property of the SQLDataSource looks like this:ConnectionString="<%$ ConnectionStrings:PersonnelDatabaseConnectionString %>" No complaints from the compiler about the ConnectionString. Any ideas? Am I simply going to be unable to construct a SelectCommand on-the-fly?
Good morning.I'm writing an application that allows users to generate Personal Leave requests and route them to their managers.I'm using the integrated Windows Authentication, so I'm able to get the user's username from HttpContext.Current.User.Identity.Name.How do I dynamically generate the SelectCommand? Right now, I do:Dim strQuery As String = "SELECT * FROM [tblPLRequest] WHERE employee = '" & strUsername & "'"to get the query I want, but when I set my SelectCommand to that, it thinks it's a stored procedure (and can't find it).Am I going about this in the completely wrong way?Thanks.
Hello friends Can anyone tell me the means by which I can populate a label or a datagrid by executing a selectcommand statement on the form of my webpage. And what is the best way to retrieve data from a SQL datasource, is it by using separate sqldatasources for each different parts of a page that requires different data from different tables in the same database or by using a tableadapter to retrieve the data for the best performance? Thanks for replying for my previous questions. It was helpful and great in my program implementation. Thank you
Hi folks,I'm having problems using the SqlDataSource to return certain values from a SQL database. I have two DropDownLists. When the first DDL's selected index is changed, I need to take the value (an int id) from the field, use the value to look up a string in a different colum in the table for that record id, which should then be used to automatically select a value from the second DDL. I think my problem is I'm not sure how to get the SelectCommand to actually return the value, let alone in string form!Here is my code:{ string selectedProject = ProjectDDL.SelectedValue; SqlDataSource temp = new SqlDataSource(); temp.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["DBConnectionString"].ToString(); temp.SelectParameters.Add("id", selectedProject); temp.SelectCommand = "SELECT [username] FROM dbo.projects WHERE id = @id"; //temp.Select(); ? //AssigneeField.SelectedValue = string returned from Select Statement!} I'd appreciate it if somone could point me in the right direction? Thanks,Ally
Help! I'm trying to figure out how to pass a string variable to my sqldatasource's "selectcommand" attribute. I'm trying to construct my SQL dynamically to adjust some things. My code is something like this, <%@ Page Language="VB" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="_Default" EnableEventValidation="false" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><%@ Import Namespace="System.IO" %><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"></head><body style="font-family: Arial,Verdana; font-size: 8"> <% Dim selectCommandVar as String = "select column from mytable" %> <form runat="server"> <asp:SqlDataSource ID="SqlChild" runat="server" ConnectionString="<%$ ConnectionStrings:mynnectionString %>" ProviderName="<%$ ConnectionStrings:gmConnectionString.ProviderName %>" selectcommand="selectCommandVar"> </asp:SqlDataSource></form></body></html> What's the best way to get the contents of my selectCommandVar variable into the SelectCommand attribute of my sqldatasource? The above syntax doesn't work and I've tried several permutations that do not work either. The examples of sqldatasource I keep finding have the SQL statement hardcoded in the <asp:sqldatasource> section, which won't work for me. HELP!
Hey,I've inherited a project from my office and am stuck.I'm trying to take input from multiple souces (DropDownLists, TextBoxes, etc) and depending on which ones are used, update a SELECT string with additional AND statements. <script language=VB runat=server> Dim resultsql As String Public Sub Button_Click(ByVal client As String, ByVal state As String) Dim dv As String resultsql = resultsql & "SELECT ClientName, Address1, City, State FROM tblClient" dv &= "" If (StrComp(client, dv) <> 0) Then resultsql &= "AND ClientName = " & client End If If (StrComp(state, dv) <> 0) Then resultsql &= "AND State = " & state End If resultsql &= " ORDER BY ClientName ASC" End Sub </script>Now when I got to display the results of this new string in a GridView I am recieving errors trying to pass my variable "resultsql" into SelectCommand. <asp:GridView ID="Results" runat="server" AutoGenerateColumns="False" DataKeyNames="ClientNumber" DataSourceID="SqlDataSource3"> <Columns> <asp:BoundField DataField="ClientName" HeaderText="ClientName" SortExpression="ClientName" /> <asp:BoundField DataField="Address1" HeaderText="Address1" SortExpression="Address1" /> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>" SelectCommand=resultsql> </asp:SqlDataSource> I've scoured the web without any success. Any suggestions are appreciated.
I would like a form that has 3 text boxes. These 3 boxes are going to be used for entering search criteria. The first box is for searching a field called CasePK. The second is for searching a field called LinePK and the third is for searching a field called DOS. DOS is a date field. The other two are text fields.I am using a SQLDATASOURCE and a Gridview. What will the SelectCommand look like so that I can search the 3 fields using the data from the textboxes?
Hello I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display. Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed. For instance in my page load event I would have a CASE statement with numerous SQLString Variables. Here is the current coding for my datasource <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter"> <SelectParameters> <asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" /> </SelectParameters> </asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the SelectCommand="SQLString" but this causes a syntax error Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'. Any help would be greatly appreicated!!
Hi Everyone, I set the session("MyTable") with the user's name as contents when the user logs in, then I copy a new table name with session("MyTable") , I set PK and this all works, but I can't seem to figure out how to use the session var as table name SQLDataSource Selectcommand? I try a bunch of different stuff, putting it into a key in the web.config. Seems like this should be a piece of cake. Im getting ~ "Session variable is not a valid datasource item" etc. Below is one of the things I have tried.<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connectio String %>" SelectCommand="Select * From [" + Session("MyTable") + "] Where ID = @ID"> TIA Cebo
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#) For example, <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable"> I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind. Appreciatively,Peter
I'm just getting started with ASP.NET and I've created a query page that displays the user’s results in a gridview. The user can then edit, delete or sort the results. I use two text boxes for the user to supply the query criteria (Lname and Year). The user can provide one, both, or neither items. When no criteria are supplied the SQL query returns all records (this is the desired result). Here's the problem. On the initial load the page returns all records because it is submitting the Selectcommand with the query fields blank. Can I prevent this from happening on the initial page load but permit it on all other reloads. I initially removed the Selectcommand from the Gridview and created a codebehind for the query button that submits the query. The problem with this was that the selectcommand is required for all other gridview operations (i.e. if the user sorts the results or edit a record, when the page reloads no records are displayed in the gridview). I then started to use codebehind for all possible Gridview events but this got very unwieldy. Any suggestions would be greatly appreciated. Thanks FZev
Hi all, I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run. In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run. I've finally narrowed down the issue to something that resolves the symptoms to something in my query; SELECT f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical, SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total, SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM t LEFT OUTER JOIN d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN tblFac AS f ON t.sUnitID = f.UnitID_1WHERE (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division If I change my WHERE clause to the following WHERE (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5))) performance is MUCH better. How could such a little change make such a big difference in speed? Thanks. -e
Hi I have a DataSource control which is currently set with no initial SelectCommand Property when it is constructed. I have a number of standard buttons, each of which when clicked fires an event that analyses and modifies the SelectCommand for the DataSource. Each event sets the SelectCommand Property for the DataSource using eg:dataSource.SelectCommand = "SELECT * FROM table WHERE tableID IN ('1', '2', '3')";(To help you understand, the aim is to have each button either inserting or removing an ID number into an SQL 'IN' clause as shown above) When the events are fired the DataSource is updated with the new SelectCommand and the bound ListBox updates correctly. However any subsequent events don't seem to read the previously modified SelectCommand setting. When they try to read the SelectCommand using:string queryToModify = dataSource.SelectCommand;They just get a empty string returned instead of the query that the previous button click set (temporarily). It seems like a state issue, but as a server side control it should automatically keep the state, right? Thanks for taking the time to read this and many thanks in advance for any assistance (I've only been learning ASP.NET for a few days... and I'm quite impressed so far :-) ) PurplePerson