How To Separate Period Amount From YTD Amount

Mar 18, 2008

I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:

SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid

FROM
UrsinusCollege.dbo.gl7accounts a

join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid

where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')

order by a.accountnumber

Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.

Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.

Thanks very much.

Sue

View 6 Replies


ADVERTISEMENT

Large Amount Of Text Pushing To Separate Page After PDF Render

May 25, 2006

I am having a small problem.

When I generate a report with a large amount of text data (Paragraphs) the report will not split the text between two pages.

It will move the entire text box to the next page leaving a large amount of space on the previous page.

I tried every control on the page to render this. I tried textboxes, tables, lists, rectangles, subreports, etc..

The data is stored in a SQL table using a text data type (there can be a large amount of data entered into these database.

Any help on this would be great. I like reporting services, but there are just a few bugs.

I am using Reporting Services 2000 with Service Pack 2 and Hotfix

http://support.microsoft.com/?kbid=912424 (That fixed a different PDF rendering issue) and this problem was occuring prior to this hotfix.

Thanks in advance

View 5 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,



I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.



I kindly request for help.

Thanx in advance.

Ronnie

View 4 Replies View Related

SQL 2012 :: Log File Data Transfer Amount (MB) Versus Data File Transfer Amount (MB)

Mar 19, 2014

In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?

I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?

I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?

View 6 Replies View Related

How Do I View The Amount Of The Log That Is Used?

Apr 3, 2008

How do I view the amount of the log that is used?

How can I tell how often a checkpoint is being executed?

I have a lot of data to insert into a table (via a SQL insert from another database on the same server), and I do not want to fill the log ... so I will do a SET ROWCOUNT 100000, insert records, wait for the checkpoint to run which will clear the log, ... and repeat this process untill all the records are inserted.

Thanks

View 7 Replies View Related

Highlighting Max Amount

Jun 6, 2008

I have a report which lists sales figures by salesperson, and I need the report to highlight the maximum amount (ie. the person who got the highest sales figures). How can I do this?

View 2 Replies View Related

Amount Between Parameters Like Or

Apr 7, 2014

I am having table Employee with Employee Salaries details, from that i need to get the values based on parameters(@Param1,@Param2)

select * from Employee
where Salaries >@Param1 and Salaries < @Param2

Conditions :

1) @Param1 >10000 and @Param2 <25000
2) @Param1 >10000 and @Param2 =''
3) @Param1 <15000 and @Param2 =''
(may or may not pass the values for @Param2)

View 3 Replies View Related

Amount Of Normalization

Jun 8, 2007

Hello all,

My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.



- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.

- the largest data type is nvarchar 125.

- number of rows in the largest table will eventually grow to hundreds of thousands.

- users access the database online







This is large to me but I expect that to some of you this not.

My application would be easier to setup if the contacts table were to include address info.



So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.



Any help you could provide with this scetchy info would be greatly appreciated.



View 6 Replies View Related

Size Amount

Nov 24, 2005

Can anyone tell me how much space the SQL Server 2005 actually has. And if I can access the server from pretty much any computer?

View 1 Replies View Related

Large Amount Of Text?

Feb 22, 1999

We have created a databse in SQL Server 7 for support issues.
We are having trouble with only one aspect of the databse and that is the body of the supported problem.

The databse is basically a Question/Answer Support database whch house Frequently Asked Questions.
When the user does a search on a specific problem they have a list of matching questions shows on the screen (links).
When the question is clicked on it shows the Questions with the Answer of possible fixes.

Our problem lies in the fact that SQL server is truncating the Answer portion. I have tried different Data Types with maximum lengths with no success it keeps truncating it.
Right now I am on Data Type nvarchar with a length of 4000.

If anyone has any pointers on how to solve this problem all input is appreciated. You can post here to the forum or e-mail me directly at jason@flnet.com

Thank You.

-JR

View 2 Replies View Related

Loop Through Table Until Max Amount?

Sep 10, 2012

I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.

The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount
CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,

[Code] ......

View 3 Replies View Related

Get Total Amount By Date

Apr 10, 2008

this is my sample records in my table


Sdate Amount
January 2007 250000.00
March 2007 300000.00
January 2008 350000.00



how can i get the amount from February to
May 2007 and sum it up like this


Details
February 2007 250000.00
March 2007 300000.00
April 2007 300000.00
May 2007 300000.00



this is all that i want to get
Total Amount: 1,150,000.00

i also posted this one in new to sql server section, but im hoping to get others opinion here. thnx

View 4 Replies View Related

How To Know When Total Salary Is At Certain Amount

Jul 24, 2013

I would like to know how to use a criterion on this example. I want to know only when the total salary is at a certain amount

SELECT SUM (salary) as Total Salary
FROM employees
WHERE Total Salary > 25000; ---this is where i am having issue

View 4 Replies View Related

How To Get Average Amount Of A Product

Oct 15, 2013

I have a table with product_name and introduction_date(when the product was first introduced)as columns. now i wana calculate average as below

if item is sold in previous business year(suppose 2011-12) then avg should be avg price in businessyear(2010-11), if it is newly introduced(suppose 2013-14)then avg should be of current year(2013-14).

Note:- business year Apr-march

View 1 Replies View Related

Subtracting The Amount In Query?

Apr 16, 2015

If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:

Declare @TotalAmount money
@TotalAmount = 673000
Col1 Col2 Col3
Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45
Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3)
Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)

View 1 Replies View Related

AMOUNT OF DATA REPLICATION

Feb 10, 2007

Hy im PCV
I want to know how to calculate the amount of data(in MB) that is transfered from 1 server trought another
Puplisher--->Subscriber, using a merge replication. I know that the amount of data depends on the number of the rows and the scale of the colums. I only want to know how to calculate that amount of data. I am using Sql server 2000, and a OS windows XP profesional, thank you

PCV

View 2 Replies View Related

Adding Amount For Each Customer

Mar 22, 2007

I am fairly new to sql. Reading a table I need to show amount for each customer. Also I need to add amount for each customer that has more than one entry. What would the sql statement look like? Thank you.

Records in file:
Company Amount
Customer1 24.000
Customer2 36.000
Customer3 72.000
Customer1 20.000
Customer3 15.000

Desired results:
Company Amount
Customer1 44.000
Customer2 36.000
Customer3 87.000

View 1 Replies View Related

How To Select Max Amount Rows

Jul 20, 2005

please help to select these rows from these tables my tables aretable1table1Id groupId table2id price1 1 1 102 1 3 10003 1 4 5004 2 1 55 2 3 10006 2 2 2000table2table2id name1 hello2 test3 test14 test2ok i want to select maximum priced row from table1 grouped by groupidwith table2id and table2.namemy out put isgroupid price table2id table2.name1 1000 3 test12 2000 2 testif i do :select groupid,max(table1.price) as price from table1 group by pricethis will give me the max priced row from table1but when i join them with the table2 it gives me all rowslikeSelect groupid,max(price) as price,table2id,table2.name from table1inner join on table2group by groupid,table2id,table2.nameit gives me all rows cause i had to group by table2.id and table2.namebut i can't take it out cause it give me no aggrigated value errori can't figure out any other way, please helpSQL Server 2000thankseric

View 2 Replies View Related

AVG Amount Spreaded Accross

Dec 6, 2007

Can any help me regarding the following

When you are pulling the data from PPV
SELECT
[MATERIAL]
,[IR_AMOUNT]
FROM [LATCUBDAT].[dbo].[ppv]
where [MATERIAL] = 'MR004' AND [IR_AMOUNT] = 53728.85
Result







material

IR_Amount


MR004

53728.85


When you want to join ppv to rmu and then pull the data then
select R.[PRODL]
,P.[MATERIAL]
,P.[IR_AMOUNT] AS 'IR_AMOUNT'




FROM [LATCUBDAT].[dbo].[ppv]P
INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL]
where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85












PRODUCT LINE

MATERIAL

AMOUNT


30000

MR004

53728.85


32000

MR004

53728.85


36000

MR004

53728.85


50000

MR004

53728.85


52000

MR004

53728.85


54000

MR004

53728.85


When I use avg in the query I get this report
select R.[PRODL]
,P.[MATERIAL]
,avg (P.[IR_AMOUNT]) AS 'IR_AMOUNT'




FROM [LATCUBDAT].[dbo].[ppv]P
INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL]
where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85
GROUP BY R.[PRODL],P.[MATERIAL]
ORDER BY R.[PRODL],P.[MATERIAL]










PRODUCT LINE

MATERIAL

AMOUNT


30000

MR004

53728.85


32000

MR004

53728.85


36000

MR004

53728.85


50000

MR004

53728.85


52000

MR004

53728.85


54000

MR004

53728.85


I will like to see








PRODUCT LINE

MATERIAL

AMOUNT


30000

MR004

8954.808


32000

MR004

8954.808


36000

MR004

8954.808


50000

MR004

8954.808


52000

MR004

8954.808


54000

MR004

8954.808


If there where 4 product line then avg distributed between 4 and like wise

View 5 Replies View Related

Problem With Huge Amount Of Data

Nov 20, 2006

Hi,I've an application, lets call it simply "A", which creates in a Microsoft Sql Database two huge tables.Lets call them "table1" and "table2"It safes really much data into this tables.After application "A" has finished another application is executed which deletes this two tables.Then application "A" is started again and it will create this two tables again, but the amount of data becomes bigger.It can only proceed if the tables were deleted completely before and the database is empty. This is the procedure which I repeat very often, but everytime the amount of data becomes bigger (table1 and table2 becomes bigger).A couple if times it works fine, but once it seems data becomes too big and application "A" fails. Mostlikely because the data wasnt removed correctly / completely.   This is my code of deleting the two tables, maybee there is something I have to change:</p><p>&nbsp;try&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlConnectionStringBuilder builder =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new SqlConnectionStringBuilder("Server=mycomputerdbname;Integrated Security=SSPI;" +&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Initial Catalog=testing");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; builder["Server"] = "(local)dbname"; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; builder["Connect Timeout"] = 10;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; builder["Trusted_Connection"] = true;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; builder["Initial Catalog"] = ((ComponentConfiguration)this.componentConfig).Persistency.DatabaseName;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlConnection sqlconnection = new SqlConnection();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlconnection.ConnectionString = builder.ConnectionString;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlconnection.Open();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlCommand cmd1 = new SqlCommand("DROP TABLE table1");&nbsp; // TO Do delete all tables&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlCommand cmd2 = new SqlCommand("DROP TABLE table2");&nbsp;&nbsp; // TO Do delete all tables&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd1.Connection = sqlconnection;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd2.Connection = sqlconnection;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd1.ExecuteNonQuery();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thread.Sleep(7000);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd2.ExecuteNonQuery();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thread.Sleep(7000);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlconnection.Close();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thread.Sleep(3000);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; catch { }</p><p>&nbsp;</p><p> Thanks for help! mulata 

View 6 Replies View Related

How To Query Data By Various Amount Of Filter Value ????

Jul 2, 2007

Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.
But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
 
For example
On screen which have information of people in any province.
So user would like to search it by identify no matter how value to search.
There are check box of any province on filter part which enable users choose it.
 
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC  to see description only 3 chosen province.
and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
 
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
 
 
Help me Pleaseeeee

 
 
 

View 1 Replies View Related

Returning Amount Of Rows From Sqldatasource

Oct 10, 2007

TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
 
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
 Thanks Andy,

View 5 Replies View Related

Form To Subtract An Amount From A Table On Sql.

Mar 29, 2008

Hi Guys,
Does anyone have a good example how to subtract an amount from an sql table?
At present i have a table such as this


id

View 2 Replies View Related

Can I Have SQL Server Clear A Value After A Certain Amount Of Time?

Apr 5, 2004

I have an application that allows users to "reserve" a certain time and location for practice sessions. I want them to look at a web form that shows which times are available and then allows them to select an open slot and then submit this form with some personal information. My problem is that as soon as they select an open slot, other users should see that this slot is no longer available. On the other hand, if someone selects an open slot and then takes too long to type in their personal information, I want it somehow to kick them out and re-open this slot back up.

I know that I can create a field called 'status' in my database that gets turn to something like 'pending' as soon as the first user clicks on an open slot, but how would I turn this field back to the 'open' state if the user took too long to enter their data?

Any help would be surely appreciated.

Cheers,
AzF

View 1 Replies View Related

How To Limit Amount Of Results In A Search?

Jun 23, 2005

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'>&nbsp;</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'>&nbsp;</td></tr>";
           
lblResults.Text += "<tr><td
bgcolor='#497792'>&nbsp;</td><td colspan='2'
bgcolor='#678BA1'>&nbsp;</td><td colspan='2'
bgcolor='#678BA1'>&nbsp;</td></tr>";
           
lblResults.Text += "<tr><td>&nbsp;</td><td
colspan='2'>&nbsp;</td><td
colspan='2'>&nbsp;</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>

View 1 Replies View Related

Maximum Amount Of Memory Used For TempDB

Sep 15, 1998

I am trying to find a suitable size for TempDB in RAM .

Is it possible to find the instantaneous maximum usage of the TempDB in RAM?

In other words how can I find the maximum amount of memory used* by TempDB ?

bim@sifas.com.tr
mikiz@turk.net

View 3 Replies View Related

Please Help: HOW TO FORMAT CURRENCY TO NEGATIVE AMOUNT

Aug 23, 2004

Please help!!!
I'm building a database that I have a form. This form allows user input the invoice amount field which is the currency field. My ultimate goal is allow user put in the postive numbers and it will store as negative amount. Please let me know if there is the way to do that.
Thanks in advance.
Linh

View 1 Replies View Related

Calculate Cumulative Amount From Table

Sep 2, 2014

I need to calculate cum amount from the following table.

CREATE TABLE #TotalRevenue_Investments
( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))
INSERT INTO #TotalRevenue_Investments
( Month ,
Year ,
TotalRevenue ,

[Code] ....

AND so ON ..

I need the OUTPUT AS FOR example

SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company

View 3 Replies View Related

Procedure Converts Amount Into Words

Jun 6, 2008

hi i need a procedure that converts amount in numer to words

Regrds
srinivas

View 7 Replies View Related

Inserting Large Amount Of Data

Jan 12, 2006

hello

i have just created a test database and now need to insert a large number of records into one of the tables, we were thinking of about 1 million records, has anyone got an sql script that i could use to create these records

cheers
john

View 6 Replies View Related

Make A Job That Updates Data By Row Amount

Aug 6, 2007

I need to make a job that will update up to 8000 rows with the list description of 'berkhold' to 'berknew' in SQL 2000. This is something that I have to do with several projects manually every day by doing the following 2 steps.

SELECT ListDescription, CRRecordID
FROM dbo_BerkleyGroupInventory
WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID

I then scroll to the 8000th row and copy the CrrecordID and run the following query

UPDATE dbo.berkleygroupinventory
SET listdescription ='berknew'
WHERE ListDescription ='BerkHold' AND CRRecordID <=5968432 AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'

I'm sure there's an easier way to do this, but I'm very new to SQL and haven't figured it out yet

View 11 Replies View Related

Large Amount Of Pages For Few Rows

Jul 23, 2005

Hello,I have experienced that some of my tables occupies an extremely large amountof pages but with few rows. An example is a table with 37 rows over 22000pages !. The columns are simple integer and char. I fixed the problem byintroducing a clustered index. Now it only uses 1 page. But can anyoneexplain this behaviour in SQLServer 2000 ?regards Jakob Mathiasen

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved