I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
View 1 RepliesIn my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
In other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
Iam using front page to dispalay my results.
At the bottom it shows me 1/10 i.e 1st page of 10 pages.
but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).
or it would be really good if i get count of both no. of recors as well as no. of pages.
Is there a way to limit the number of rows returned by a SqlDataReader? I know I can do it by modifying the Stored Procedure, but I'd rather not modify a procedure that is used in multiple apps. I'm hoping there is something easy like setting SqlDataReader.RowsReturned = 100, but that might be too easy to hope for.
View 10 Replies View RelatedHello,
I have a select statement that looks like this:
SELECT TOP 10 [Id], [Abstract] FROM NewsArticles
Abstract is a TEXT Column. I'd like to limit it to 50 characters... how would I do that.
Thanks,
-- shawn
When I use for instance:
SELECT
FORMAT(BegDate,"dd.mm.yyyy")
FROM TABLE1
result is - |BegDate|22.12.2013.......................|
Is there any way to limit length of string returned from FORMAT function .
Database is on ACCESS.
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
Hello,
I am building a simple full text search engine for my site and I was wondering how would I retrieve rows 11-20 of the search result. This is required because I want to show my results only 10 at a time, like google does for instance. My query is as follows -
select top 10 ft_tbl.url,
ft_tbl.title, ft_tbl.body, ft_tbl.date,
(key_tbl.rank)
from mytable as ft_tbl inner join
freetexttable(mytable, (url, title, body),
'".$searchstring."', 10) as key_tbl
on ft_tbl.id = key_tbl.[key]
order by (key_tbl.rank) DESC
In MySQL I would use LIMIT but I believe that doesnt exist in MS SQL
Thanks
Hi,
I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in . I have the following query:-
Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName
In my database, the field blnActive is a BIT field. When I try to run this query, I get no results returned. If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results. The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh
Thanks to those who feel my pain !!
I'm busy writing a local site search engine that searches through a sql
server database and I want to know how or what is the correct sql
syntax to use in order to limit the amount of results a page loads at a
time? The idea is obviously similar to something like google where you
only see a certain amount of results first and then click at the botom
for the next eg. 10 results.
The second question is how do I, after the first page with the first
set of results that were shown, "clear" the second page of
the previous html in order to show the next set of results?
To give you an idea what my code looks like at the moment. Please don't
kill me if the code is done a bit a lame, because I'm still learning.
<%@ Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;
strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();
strQuery = strProvince + " " +
strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +
strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "
+ strWantChildren;
SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(
@"Server=THALIONTHALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROM
client," + "FREETEXTTABLE( client, * , @searchphrase ) searchTable "
+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();
//start display of results
lblResults.Text = "<table
width='100%' style='border-style:solid; border-width:thin;
border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"
].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"
].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();
// read the text file's info into a variable for display
if (
File.Exists( MapPath( "text files" +"\" +resTextDescription ) ) )
{
objStreamReader = File.OpenText( MapPath( "text files" +"\"
+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{
resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
resTextDescription2 = "myFile.txt does not exist!";
}
//determine whether male or female in order to display correct sign
if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correct
words for display
if ( resHaveChildren == "have kids" )
resHaveChildren = "Yes";
else
resHaveChildren = "No";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%'
bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +
resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'
class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><td
colspan='2'><div align='center'
class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'
bgcolor='#C7C9BE'><p align='center'><img src='images/" +
resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'
height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><p
class='text'><br></p><p class='text'>" +
resTextDescription2 + "</p><p
class='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'
valign='top'><img src='images/hotlist_1.jpg' alt='Add To
Favorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><img
src='images/email_1.jpg' alt='Email this profile' width='42'
height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'
class='text_bold'>Location :</td><td width='37%'
bgcolor='#E1E2DC' class='text'>" + resProvince +
"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Age :</td><td bgcolor='#E1E2DC'
class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Height : </td><td bgcolor='#E1E2DC'
class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'
class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'
class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'
class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Home Language : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Relationship Status : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Realtionship Interest : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Have Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Want Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'
bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><td
bgcolor='#497792'> </td><td colspan='2'
bgcolor='#678BA1'> </td><td colspan='2'
bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><td
colspan='2'> </td><td
colspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;
//create a connection
conClient = new
SqlConnection( @"Server=THALIONTHALION;Integrated
Security=SSPI;database=DateGame" );
//select statement
strSelect = "Select userName
From [client] Where userName=@username and userPassword=@userpassword";
cmdSelect = new SqlCommand( strSelect, conClient );
cmdSelect.Parameters.Add( "@username", txtNickName.Text );
cmdSelect.Parameters.Add( "@userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>
Hi,I need to limit results in the following query type:http://www.somewhere.com/php/sql-a....ql_order=&pos=1I found a reference that says I should be able to use LIMIT x[,y], butI don't know where/exactly how to add that to the string. Once I knowwhat it's supposed to look like, and can write something to generateit.If someone could post an example using the above and limiting theoutput to 100 records starting at position 1, that would be great.
View 1 Replies View RelatedI have a simple ForEach SMO Enumerator that returns the names of all the databases in the server identified by the SMO connection manager. I would like to populate the ForEach collection with just a subset of the names, based on some simple pattern matching. In his helpful book Integration Services, Kirk Haselden indicates that this can be done directly in the Enumerate field by adding qualifiers. However, I'm at a loss in regards to syntax.
What I have is: SMOEnumObj[@Name='Databases']/SMOEnumType[@Name='Names']
which returns the database names
What I want is to limit the names returned to those where Left(DBName,5) == "abcdb" (for example).
I have tried adding some test expressions to the Enumerate field, however, the URN is constructed based on the properties of a combination of the Connection Manager and Enumerate fields, and I'm just created invalid URNs.
Has anyone done something like this?
Thanks in advance for any help you can provide.
Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!
View 7 Replies View RelatedI am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491
Here is the SQL I'm using.
Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'
[Code] ....
is there a limitations of number of records that shows in the table when you use show data table?
View 1 Replies View RelatedI am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.
I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.
Is there a particular format for the date I am comparing with?
Am I missing something else in my query?
The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.
Many thanks for any help or comments you can provide.
Hi,
I'm trying to use the XML Task XPath operation for the first time but having some problems. I'm using the following settings:
Source type: File connection
SaveOperationResult: True (file destination)
SecondOperandType: Direct Input
PutResultInOneNode: False
XPathOperation: Values
The XML file I'm testing has the following stucture:
<?xml version="1.0" standalone="yes"?>
<BackupDataSet xmlns="http://tempuri.org/BackupDataSet.xsd">
<Device>
<DeviceID>6356452a-e7a6-42e1-895a-d4ade62210d5</DeviceID>
<UserID>1533c44f-c263-db11-9db3-000e9bd9f98d</UserID>
</Device>
</BackupDataSet>
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
//Device/DeviceID
/BackupDataSet/Device/DeviceID
//DeviceID
All of these return no results. What am I doing wrong?
Regards,
Greg
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
View 5 Replies View RelatedI have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1
Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1
Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1
Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1
Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....
when I execute the line:
reader = comm.ExecuteReader();
Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned.
For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form.
Here is my code snippet with further explanation of what I am trying to do:int Inumber = 0;foreach (string item in menuHeaders)
{string title = menuHeaders[Inumber];
sp.Value = menuHeaders[Inumber];
Inumber++;
conn.Open();reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
//Get the culture property of the thread.CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
//Create TextInfo object.TextInfo textInfo = cultureInfo.TextInfo;
// WHAT I AM TRYING TO DO....... Here I would like to wrap this with an if statement, if Records returned by the reader are 0, skip while loop and header display
// If one, then display in singular and if 2 add an s to the title. Convert to title case and display.content.Text += "<H3>" + textInfo.ToTitleCase(title) + "</H3>";while (reader.Read())
{
content.Text += "<a href='" + reader["website"] + "'>" + reader["f_name"] + reader["l_name"] + "</a>"+ ", " +reader["organization"]+"<br />";
}
//Close the connection.
reader.Close();
conn.Close();
}
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
View 2 Replies View RelatedIs it possible to get the number of Rows returned by a stored procedure in a profiler ?
if yes, what parameters shud I be looking for?
THanks
Harshal
I have a report that calls a stored procedure. I want to display the number of records that the stored procedure returned. I can't seem to find an expression that will do this. Is there an expression that will display the record count for a dataset?
Thanks,
Rob
How do you limit the number of records returned in a recordset? I only want the 10 most recent and I've got a Date column in my database.
View 1 Replies View RelatedHi,
Here's my problem.
I have an Area Chart, and I need to plot more than 300,000 records. Problem is RS cannot seem to handle this huge dataset. It would retrieve for 20mins, then after that it will just have an error "Internet Explorer cannot display the webpage". I am not encountering this if I just have a few records to plot. Please help
Does sql server limit max number of connections, or max connections open from a given source, or over a given time period?
View 3 Replies View RelatedProcessing Association Rules model on SQL 2005 Standard edition produced following error:
"Error (Data mining): The 'WO_3' mining model has 6690 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model."
How can I limit number of attributes in a model?
Thank you
Is there any limit to the number of expressions in an "IN" clause? (Where test-expression IN (list of expressions))
View 4 Replies View RelatedIs there a limit to the number of connections that can be made to the SQLCLR? If so, what is that limit and is it adjustable?
Thanks!
HI all !
I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.
At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.
I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!
BErnard Ong
Is there a limit of how many rows a table can have in SQL compact Edition? I didn't find anything in the documentation, but I get regularly a funny error message "Expression evaluation caused an overflow. [ Name of function (if known) = ]" when I try to create record number 32768 (is equal to 2 to the power of 15).
Where is this limit documented ?
Code Snippet
const string connectionString = "Data Source='" + dbFileName + "'; Max Database Size = 4091; temp file max size = 4091";
using (SqlCeEngine testEngine = new SqlCeEngine(connectionString)) {
testEngine.CreateDatabase();
}
using (SqlCeCommand addMValuesTableComand = testDbConnection.CreateCommand()) {
addMValuesTableComand.CommandText = "CREATE TABLE MValues (MSerieId int, TimeStamp smallint, Value real, PRIMARY KEY(MSerieId, TimeStamp))";
addMValuesTableComand.ExecuteNonQuery();
}
//fill table
StatusLabel.Text = "fill MValues Table";
using (SqlCeCommand fillTableComand = testDbConnection.CreateCommand()) {
int i = 0;
try {
int iterationCount = (int)RecordsCountNumericUpDown.Value;
ProgressBar.Value = 0;
ProgressBar.Maximum = iterationCount;
for (i = 0;i < iterationCount;i++) {
fillTableComand.CommandText = "INSERT MValues VALUES (1, " + i.ToString() + ", " + (i/100.0).ToString() + ")";
fillTableComand.ExecuteNonQuery();
ProgressBar.Value = i+1;
}
} catch (Exception ex) {
ErrorTextBox.Text = "Error occured" + Environment.NewLine +
"Iterations: " + i.ToString() + Environment.NewLine +
"Error Message: " + ex.ToString();
}
}
Is there a way to specify how many bars to display on a bar chart? And if the data set has more than the specified number of bars, then display another bar chart (on the next page) with the remaining bars?
View 3 Replies View Related