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" then
SelectCommand="SELECT [nr1] FROM [Artikel]"
Else
SelectCommand="SELECT [nr2] FROM [Artikel]"
End if
%></asp:SqlDataSource>
Is that possible and how can I manage that?
Thanks for your answers, Chris.
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;
System.Data.SqlClient.SqlParameter sparam=new SqlParameter("@song",SqlDbType.NVarChar);
sparam.Direction=ParameterDirection.Input;
sparam.Value=str;
da.SelectCommand=new SqlCommand();
da.SelectCommand.Connection=sqlConnection1;
da.SelectCommand.CommandText="CdInfoSP";
da.SelectCommand.CommandType=CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add(sparam);
da.Fill(dataSet11,"CdInfoSP");
DataGrid1.DataSource=dataSet11;
DataGrid1.DataBind();
DataGrid1.Visible=true;
}
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
I hope someone can help me!
Thanks in advance!
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.
Thank you.
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
Hi
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.
Nuwanda
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.
View 3 Replies View RelatedGreetings 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"
View 4 Replies View RelatedThis 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
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:
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="IP Tracker - Search" EnableSessionState="True" %>
<script runat=server>Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Search_GridView.DataBind()
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="MasterContent" Runat="Server">
<br />
<strong>Search for IP</strong><br />
<br />
<input id="SearchBox" type="text" /><br />
<asp:Button ID="Button1" runat="server" Text="Please Work..." onclick="Button1_Click"/><br />
<br />
<asp:GridView ID="Search_GridView" runat="server" DataSourceID="SqlDataSource1" EmptyDataText="No Records Match">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IP_TrackerConnectionString %>"
SelectCommand="SELECT * FROM [AddressTable] WHERE ([IPAddress] LIKE '%' + SearchBox.text + '%')">
<SelectParameters>
<asp:FormParameter FormField="SearchBox" Name="IPAddress" Type="String" />
</SelectParameters></asp:SqlDataSource>
</asp:Content>
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?
Hi,
I would like to set the selectcommand property of the sqldatasource at runtime.
how to do that?
can somebody help me out?
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.
View 3 Replies View RelatedHello 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
View 2 Replies View RelatedHelp! 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?
View 1 Replies View RelatedHello
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
View 1 Replies View RelatedI am setting the SelectCommand of the SqlDataSource in my page_load. This is the WHERE part of my SelectCommand:WHERE ProductName LIKE '%' ? '%' OR ProductRef LIKE '%' ? '%'The above uses two select parameters. But the two select parameters will have the same value. So is it possible to re-write my select command so it just uses one parameter? I have tried the following, but this doesn't work. WHERE (ProductName OR ProductRef) LIKE '%' ? '%'Is there similar syntax I could use that works?
View 2 Replies View RelatedHi all
I have a cms-page where i want to display various entry-categories like news etc....I want to define which kind of entries should be shown by a parameter in the URL (e.g. cms.aspx?category=news). So far everything is OK.
To display the entries actually I'm using the following SqlDataSource:
<asp:SqlDataSource ID="SqlDataSourceCMS" runat="server" ConnectionString="......."SelectCommand="SELECT * FROM [cms] where category = news"></asp:SqlDataSource>
What i need, is to set the category which i want to show dinamically, like with a variable.
Does anybody know how i can set something like a variable in the SelectCommand property of the SqlDataSource?
Although writing a parameterized SQL statement has been simplified using the asp:parameter options, it still may benefit to use the old fashioned method of writing a sql statement using an input string. I have noticed this for wanting to make a parameter to select which table I want to pull from. Here is some code I wrote to pull information from a database based on input from a search box and write it to a gridview.
Partial Class Private_SearchResultsInherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Table As String
Dim SearchString As StringSearchString = Request.QueryString("SearchString")
Table = Request.QueryString("Table")
If Len(Trim(SearchString)) > 0 ThenSelect Case Table
Case "Plant"
SqlDataSource1.SelectCommand = "SELECT PlantName as 'Plant',PlantAddr as 'Address',PlantCity as 'City',PlantState as 'State',PlantCountry as 'Country',PlantZip as 'ZIP' FROM Plant WHERE PlantName LIKE '%" & SearchString & "%' OR PlantCity LIKE '%" & SearchString & "%' OR PlantState LIKE '%" & SearchString & "%' OR PlantCountry LIKE '%" & SearchString & "%' OR PlantZip LIKE '%" & SearchString & "%' ORDER BY PlantName"
Case "Contacts"
SqlDataSource1.SelectCommand = "SELECT ContactPosition as'Position',ContactTitle as 'Title',ContactLast as 'Last Name',ContactFirst as 'First Name',ContactPhone as 'Phone No' FROM Contacts WHERE ContactLast LIKE '%" & SearchString & "%' OR ContactFirst LIKE '%" & SearchString & "%' OR ContactPosition LIKE '%" & SearchString & "%' OR ContactPhone LIKE '%" & SearchString & "%' OR ContactTitle LIKE '%" & SearchString & "%' ORDER BY ContactLast"
Case "Events"
SqlDataSource1.SelectCommand = "SELECT EventName as 'Event',CONVERT(varchar,EventStartDate,101) as'Start Date',CONVERT(varchar,EventEndDate,101) as 'End Date',EventNotes as 'Notes',EventNoAttendees as 'Attendees',EventType as 'Event Type' FROM Events WHERE EventName LIKE '%" & SearchString & "%' OR EventStartDate LIKE '%" & SearchString & "%' OR EventEndDate LIKE '%" & SearchString & "%' OR EventNotes LIKE '%" & SearchString & "%' OR EventNoAttendees LIKE '%" & SearchString & "%' OR EventType LIKE '%" & SearchString & "%' ORDER BY EventName"
Case ""
Label1.Text = "Nothing Selected in Drop Down Box"
End Select
Else
Label1.Text = "No Search Parameters Entered"
End If
GridView1.DataBind()End Sub
End Class
Here is also my code to the front end of the page...
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="SearchResults.aspx.vb" Inherits="Private_SearchResults" title="Search Results" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<br />
<strong><span style="font-family: Tahoma">Search Results<br />
<br /></span></strong>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<asp:Label ID="Label1" runat="server" Font-Names="Tahoma" Font-Size="10pt"></asp:Label><br />
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" DataSourceID="SqlDataSource1" Width="1020px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" Font-Names="tahoma" Font-Size="Small" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="Indigo" Font-Bold="True" ForeColor="White" Font-Names="tahoma" Font-Size="Small" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRMConnectionString %>"></asp:SqlDataSource>
</asp:Content>