|
There's no problem in using a cursor within a while loop -> have a look at the MSDN, to review the example and you'll have your're problem solved.
|
|
|
|
|
i have a date format in 06/01/2008 which datatype is nvarchar(50).how i will change it into 06/jan/2008 format in sql. Plz help me.
select log_in_date from test1
out_put
06/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
|
|
|
|
|
mon***z wrote: nvarchar(50).
I am not sure about NVarchar, but if you were using DateTime type, use convert function to change the display format.
|
|
|
|
|
thanks man for your answer
I missed the namespace
<br />
Messagebox.show("Invalid Data Entry");<br />
<br />
ANd the message Messagebox, does not Exists in the Current Context
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Well, a couple of sample dates would have done rather than dozens, but your answer is:
Using @a to represent your column
<br />
declare @a nvarchar(50)<br />
set @a = '28/10/2008'<br />
set dateformat dmy<br />
select convert(varchar,datepart(day,@a)) + '/' + left(datename(month,@a),3) + '/' + convert(varchar,datepart(year,@a))
You need the set dateformat dmy so SQL knows its day month year
Bob
Ashfield Consultants Ltd
|
|
|
|
|
actually i have five columns like system,name,qty,weight,area
I have to fetch the details from database. I have to write a query where i have to multiply qty and weight and put it in weight column and at the same time i have to display the records in the descending order of total weight . How can this be done. Please help
skd
|
|
|
|
|
It's a home work question, how about this
SELECT
[system]
,[name]
,[qty] * [weight] As TotalWeight
FROM
[dbo].[TestTable]
ORDER BY
TotalWeight DESC
|
|
|
|
|
Hi there,
I'm looking for some advise for a generic data fetch pattern for IDataReader I'm working on, as follow:
// main load
using(GenericDataReader reader = new GenericDataReader(cm.ExecuteReader()))
{
while (reader.Read())
{
orders.Add(reader.Get<Order>()); // "GetList" pattern
//return new Order(reader); // "Get" pattern
}
}
// generic reader; interface implementation removed
public class GenericDataReader : IDataReader
{
// ... \\
public T Get<T>(string name)
{
int i = _dataReader.GetOrdinal(name);
if (_dataReader.IsDBNull(i))
return default(T);
else
return (T)_dataReader.GetValue(i);
}
public T Get<T>() where T : ILoadable, new()
{
T entity = new T();
entity.Load(this, false);
return entity;
}
}
// All business objects must implements this interface
public interface ILoadable
{
void Load(GenericDataReader reader, bool deep);
}
// Sample business object
class Order : ILoadable
{
int id;
Customer customer; // Note
DateTime orderDate;
DateTime requiredDate;
DateTime? shippedDate;
List<OrderDetail> orderDetails;
public Order(){ }
public Order(GenericDataReader reader)
{
Load(reader, true);
}
#region ILoadable Members
public void Load(GenericDataReader reader, bool deep)
{
id = reader.Get<int>("OrderID");
customer = reader.Get<Customer>(); // Note
orderDate = reader.Get<DateTime>("OrderDate");
requiredDate = reader.Get<DateTime>("OrderRequiredDate");
shippedDate = reader.Get<DateTime?>("OrderShippedDate");
if (deep && reader.NextResult())
{
orderDetails = new List<OrderDetail>();
while (reader.Read())
{
orderDetails.Add(reader.Get<OrderDetail>());
}
}
}
#endregion
}
What do you think? Are there any major drawback with this model I'm missing? Are there any more clever way to do that?
I really appreciate your time reading this. Thanks in advance,
Rubens
|
|
|
|
|
Seems a nice idea. Just when you are done, try to post a article on CodeProject.
Good Luck,
Mehroz
|
|
|
|
|
Hi Mehroz,
I'm working on some model limitations and, when I ready to go, I'll post @ CP, for sure.
Thanks for your incentive,
Rubens
|
|
|
|
|
Hi All
We know the ALTER TABLE ADD <columnname> adds the column on the end of the table. There have been posts about how to add it in the middle, and people agree it can't be done easily, and doesn't need to be done.
The application doesn't care about the order, but...
I am a little pedantic, and like everything neat and tidy. I like my PK first, then my FK's, then some logical group as we move down the table definition. So, when I add a new column that will form a FK, I don't want it on the end.
It looks better on a database diagram, and makes the data easier to read in a select * from ...
That said, am I hanging on to an ideal that I shouldn't be? Is adding a column 3 from the top using EM causing overhead in my DB by copying the table and data, then copying it back?
I am interested in people's opinions on keeping the database logically grouped.
Thanks
Brent
|
|
|
|
|
Hi. I am trying to figure out how to write a select statement that will retrieve all entries that have a certian letter combination in. EG: If the user enters "a" it will retrieve all words starting with an "a". if he enters "ad" it will return all words stsrting with "ad" etc. Please advise 
|
|
|
|
|
Select * from Table Where Field like 'a%'
Select * from Table Where Field like 'ad%'
Is that what you are asking for? If it is, you might want to google next time before you ask, it's pretty basic.
|
|
|
|
|
Hi
How can i combine the follow SQL statements? Without compromising on performance.
<br />
select ppar_engparameters.parmval, ppar_engparameters.parmname<br />
from ppar_engparameters<br />
where ppar_engparameters.prcdversion = (select max(prcd.prcdversion) from prcd where<br />
prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A') and <br />
ppar_engparameters.parmval='myValue' <br />
<br />
<code>select category from catg_pll<br />
where catgnumber = 01 and partprcdname||' '||partprcdversion = 'myValue2'
select catg_pll.category, prcd.activeflag, prcd.prodstatus from catg_pll, prcd<br />
where prcd.prcdname||' '||prcd.prcdversion = 'myValue2'<br />
and catgnumber = '05' and <br />
catg_pll.partprcdname||' '||catg_pll.partprcdversion = 'myValue2'<br />
|
|
|
|
|
Hi,
I'm facing this problem which i don't know how to solve it.
Can anyone guide me?
I have a list of data in table @tmp1. Format like below. This table consists > 100 rows of data.
Format:
PartID PartNumber Yield
866 A100 0.98154735437972429
866 A100 0.9997734994337486
866 A100 0.99168797953964194
868 A200 0.70783730158730163
868 A200 0.99929971988795518
868 A200 0.99889908256880733
When i run this query,
select partid,partnumber,convert(float,exp(sum(log(yield))))
from @tmp1
group by partid,partnumber
Error encountered.Error message is "A domain error occurred".
I'm trying to multiply all data from 'Yield' column. That's why i query with
convert(float,exp(sum(log(yield))))
But I suspect convert(float,exp(sum(log(yield)))) is giving the error.
So I tried to change datatype to int or varchar or decimal.
But still cannot be solved.
Pls help me.
Thanks in advance.
|
|
|
|
|
Check whether column yield contains any -ve value
this might result in domain error
Regards
KP
|
|
|
|
|
It contains zero value. But don't have -ve value.
How can i multiply all data when there is zero value?
still convert(float,exp(sum(log(yield)))) ??
Can you please help to guide me?
Thanks.
|
|
|
|
|
in one of mydatabase,i want date store in ddmmyy format in sqlserver 2005,
currently it stores in mmddyy format.
i know i can display in any format using conver function,but if it can store in format which i need,then i dont want to use extra function.
is there any wany to do that??
thank you
|
|
|
|
|
I don't think that you can change the storage format. Using Convert function would be better. If your application uses common date format in all areas, consider putting the convert inside a custom function. Use this custom function everywhere.
|
|
|
|
|
SQL Server stores the data in a datetime datattype (a number underneath it all), the format is only a concern of your presentation layer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
Is there any way to get current weeks detail frm the database using SQL 2000??
For e.g. today is wednesday, i want all data from monday to sunday.
Plz can anyone help in this
--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work. 
|
|
|
|
|
DATEPART() function should solve your problem
Ex.
<br />
WHERE DatePart(wk, Convert(DateTime, ColA)) = DatePart(wk, Convert(DateTime, getdate()))<br />
AND Year(ColA) = Year(GetDate())<br />
Regards
KP
|
|
|
|
|
Thanks Krish..
It worked....
--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work.
|
|
|
|
|
Does anyone know how to (or if it is possible) to change SQL 2005 editor to select text inside square brackets without the brackets?
eg USE [MyDatabase]
If you doubleclick on 'MyDatabase' it selects the brackets as well. Good old QA in SQL 2000 just selected the text.
|
|
|
|
|
Hi All,
I have a table report which has 10 fields as cols.The last 2 fields come from 2 diffrent subreport, having a single field each. There are also 2 groups in this table. I have to limit the rows by checking each cell in a group is 0. If all the cells have got a 0 then hide the row. I can check for all the cells except the last 2(Subreport).
Since the last 2 cells has only got 2 subreports instead of a textbox, as it is the case for the remaining 8 cells, i don't know how to check for data returning from a subreport. For all the other textboxes i have checked it as ReportItems!textbox1.value=0
Can anyone help me with this Please?
Thanks & Regards,
Payal
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
