|
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))
ORDER BY PD_Users.FirstName DESC
I got the error: the order by clause is invalid in views inline functions ...
do you have any idea?
|
|
|
|
|
Have you tried typing the error message into Google? I did and I got 94,000 results; Maybe one of those could be of help?
|
|
|
|
|
What exactly are you trying to order? Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
order by users FirstName or LastName
|
|
|
|
|
If you want it ordered by either FirstName or LastName, then why does your original post also have an order by clause here;
<br />
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber<br />
The ORDER BY in this context is in error.Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I think you should not try to order intermediate results. You should only end-results.
|
|
|
|
|
can you change my SQL Statement as you think?
|
|
|
|
|
Actually, my answer was not correct. I think what's wrong with your query is the syntax i.e where the Order By Clause comes in. The syntax should be in form
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
|
|
|
|
|
Thanks Silim it works
bt now I have anoter problem
when I use ArticleCount I get the Invalid column name error
do you hae any idea?
|
|
|
|
|
I am inserting different records using this this:
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 0, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 0
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 1, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 1
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 2, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 2
I want after that to update the item_guid so all records having the same item_code should have the same item_guid, something like:
UPDATE prices SET item_guid = ONE_GUID_FOR-ALL where item_code = SAME_ITEM_CODE and price_level_id = WHAT_VER_ID_i_PASS
|
|
|
|
|
Try this:
declare @Guid uniqueidentifier
select @Guid = newid()
update prices set item_guid = @Guid ... rest of code.... Tychotics: take us back to the moon
"Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars."
H. G. Wells
|
|
|
|
|
I have a medical software and I want to know what's the best control to use and what's the best SQL field type for blood pressure?
The blood pressure as you know is in this format: 999/999
it also has a flag for either right hand (L) or left hand (R), something like radio control
I don't want the traditional way!! I mean two numeric and two radio?!
I thought of the ButtonEdit from DevExpress with a ###/### mask but I faced a problem with the L and R flags..
What is your advise?
|
|
|
|
|
This is not a database question. WHy are you spamming the fourms I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Maybe you could have split the question into two and posted the db related part here?
How about creating a new table for storing the historical data (ReadingDate DATE, Side BIT, UpperReading INT, LowerReading INT). By doing this, you will have a history of readings as well as the current one.
Ian
|
|
|
|
|
My advice is never store multiple pieces of data in one column unless you absolutely can't avoid it. Systolic pressure and diastolic pressure are distinct pieces of data and should be recorded separately. Do you have a valid reason for avoiding the "traditional" way?
To show you why they should be stored separately here is part of a blood donation form. "Donors must have blood pressure values of 180 or less for the top (systolic) number and 100 or less for the bottom (diastolic) number. Donors with values greater than 140/90 may wish to consult their personal physician." So as you can see, the numbers for eligibility are different than the numbers where you should be concerned. Not only that, but the difference between the concern and max numbers is different for the two pieces of data. So if anyone ever wanted a report where the pressure was above the rate of concern, but below the maximum for donation that query would be much more difficult to write if you combined the data. You also couldn't do proper sorting.
|
|
|
|
|
well, i won't have issue saving it separately in the sql server but I don't want many controls on the form? so what's the best way to have some thing like a mask of 999/999 Left or 999/999 Right?
|
|
|
|
|
ALTER procedure Villas_proc_Insert_EnquiryBookingDetail
(@BId int ,@VId int,@FromDate datetime,@Todate datetime)
As
BEGIN TRAN AAA123
WHILE (@FromDate < @Todate)
BEGIN
insert into Villas_EnquiryBookingDetail(BId,VId,BookedDate)
values(@BId,@VId,@FromDate)
SET @FromDate = dateadd(day,1,@FromDate)
END
update Villas_Enquiry set status= 'Confirm' where BId= @BId
if @@error =0
BEGIN
COMMIT TRAN AAA123
END
ELSE
BEGIN
ROLLBACK TRAN AAA123
END<pre>
here VId +BookedDate are unique key in Villas_EnquiryBookingDetail table
exe Villas_proc_Insert_EnquiryBookingDetail 5,2,'03/02/2010','03/07/2010';
exe Villas_proc_Insert_EnquiryBookingDetail 5,2,'03/07/2010','03/10/2010'
how to roll back whole tran if Violation of UNIQUE KEY constraint hep pend
in my procedure
|
|
|
|
|
Use Try Catch block and in
catch block write statement rollback tran
this may work for you easily
|
|
|
|
|
I can't get the Total box to calculate. I am trying to create an online invoice for my customer to print from my webpage. I have scoured the internet looking for some help, but it doesn't help that I am clueless about javascript. This is what I have tried: In my table, all I want to happen is for the user to type in a price in the Amount boxes and then have a total at the bottom. Seems pretty basic, but I just don't understand why it won't work (not really sure of what I am doing)
Can somebody please help me?
The form itself can be found at www.ahtcsonline.com/invoice.html
Thank you in advance,
Evan
<script type="text/javascript">
function totalamountdue(){
{
var amount, amount2, amount3, amount4, amount5, amount6, amount7, amount8, amount9, amount10;
Amount = document.Invamt.Amount.value;
Amount2 = document.Invamt.Amount2.value;
Amount3 = document.Invamt.Amount3.value;
Amount4 = document.Invamt.Amount4.value;
Amount5 = document.Invamt.Amount5.value;
Amount6 = document.Invamt.Amount6.value;
Amount7 = document.Invamt.Amount7.value;
Amount8 = document.Invamt.Amount8.value;
Amount9 = document.Invamt.Amount9.value;
Amount10 = document.Invamt.Amount10.value;
Total = document.Invamt.Total.value;
document.Invamt.Total.value = (amount + amount2 + amount3 + amount4 + amount5 + amount6 + amount7 + amount8 + amount9 + amount10);
}
}
</script>
</head>
<table width="738" border="1">
<tr class="Title-centered">
<td width="214" align="left"><div align="center"><strong>Name of Attendee</strong></div></td>
<td width="229"><div align="center"><strong>Description</strong></div></td>
<td width="118"><div align="center"><strong>Amount</strong></div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name" type="text" class="Input_box" id="name" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class" class="Input_box" id="select_class">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount" type="text" class="Input_box" id="Amount" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name2" type="text" class="Input_box" id="name2" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class2" class="Input_box" id="select_class2">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount2" type="text" class="Input_box" id="Amount2" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name3" type="text" class="Input_box" id="name3" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class3" class="Input_box" id="select_class3">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount3" type="text" class="Input_box" id="Amount3" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name4" type="text" class="Input_box" id="name4" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class4" class="Input_box" id="select_class4">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount4" type="text" class="Input_box" id="Amount4" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name5" type="text" class="Input_box" id="name5" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class5" class="Input_box" id="select_class5">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount5" type="text" class="Input_box" id="Amount5" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name6" type="text" class="Input_box" id="name6" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class6" class="Input_box" id="select_class6">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount6" type="text" class="Input_box" id="Amount6" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name7" type="text" class="Input_box" id="name7" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class7" class="Input_box" id="select_class7">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount7" type="text" class="Input_box" id="Amount7" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name8" type="text" class="Input_box" id="name8" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class8" class="Input_box" id="select_class8">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount8" type="text" class="Input_box" id="Amount8" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name9" type="text" class="Input_box" id="name9" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class9" class="Input_box" id="select_class9">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount9" type="text" class="Input_box" id="Amount9" size="15" />
</div></td>
</tr>
<tr>
<td><div align="center"><span class="style15">
<input name="name10" type="text" class="Input_box" id="name10" size="40" />
</span></div></td>
<td><div align="center"><span class="style12">
<select name="select_class10" class="Input_box" id="select_class10">
<option selected="selected">--Select a Class-- </option>
<option>TRACS 202c</option>
<option>Special Claims</option>
<option>Enterprise Income Verification</option>
<option>Tenant File Management</option>
<option>Secure System</option>
<option>iMAX</option>
<option>Section 504 </option>
</select>
</span></div></td>
<td><div align="center">$
<input name="Amount10" type="text" class="Input_box" id="Amount10" size="15" />
</div></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td class="standard_label">Terms: NET 15 from class date</td>
<td><div align="right" class="Title"><strong>Total Amount Due</strong>:</div></td>
<td align="center">$
<input name="total" type="text" class="Input_box" id="total"size="15"> <input type="button" value="Calculate" onclick="totalamountdue();">
<br></tr>
</table>
|
|
|
|
|
The Web Development forum might be a better place for this.
|
|
|
|
|
Sorry for posting in the wrong area. I corrected this by post in the suggested area of Web Development. Thank you for correcting me.
Evan
|
|
|
|
|
Hi there,
I have a text field that I've stored a date pulled from a SQL Anywhere DB. It displays fine, the problem I'm having is writing the date back to the database from the same text field, I recieve the following exception message:
"iAnywhere.Data.SQLAnywhere.SAException: Cannot convert 1/1/1982 12:00:00 AM to a timestamp at ..."
The field that I'm trying to write back to in the SQL Anywhere database is of type 'Date'. Really I only want to write the date portion of the DateTime back to the database.
Hope you can help
|
|
|
|
|
You need to put the date into a format that SQL recognises and can convert back to a proper DateTime type. I don't know about Anywhere DB but most systems would handle this OK. txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|
Thanks for the feedback Richard. I understand that the problem is caused by the format of the data being passed back into the database. I have the format as yyyy-MM-dd as this is the format of the existing dates in the database so I know the filed accepts or should accept this format.
Has anyone ever used the built-in ASP.Net calendar to populate a text field and in turn a database date field? I think this is the way I'm going to go, with a button beside the text box to disply the calendar.
If you know of any examples or have tried it yourself I would be more than greatful for links etc.
Thanks
|
|
|
|
|
MWRivera wrote: I have the format as yyyy-MM-dd as this is the format of the existing dates in the database
Unless you are storing your dates as strings then this is not the case. Dates in databases should always be stored as DateTime types which is the UTC value of the date and time. It is only when you need to display them that the actual formatting takes place, when it should be in accordance with your regional settings.txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|