|
cmd.CommandText = "SELECT KODU,CDISMI,CDTURU,CDADET,ACIKLAMA FROM TBL_PS_2" +<br />
" WHERE RECORD_DATE BETWEEN '" + txtDate1.Text + "' AND '" + txtDate2.Text + "' ";<br />
Just for your info, that would be subject to dangerous sql injection attacks.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hello Forum!
Here is my problem: I have a table called TblArticle (artnr, comment) and a table called TblArticlemoves (autoid, artnr, quantity, date). Now I need a report that looks like the following example:
artnr ....|01.11.07 | 02.11.07 | 03.11.07 | 04.11.07 | 05.11.07 | ...
A ........| 20 ........| 20 .........| 35.......... | 45 ........| .........15 | ...
B........ | 5 ..........| 15......... |.25 ..........| 5 ......... | ..........20 | ...
.
.
.
The main thing is I need to display each day of a month, but I 'm not having an entry for each day of a month in my table TblArticlemoves. Therefore i need to calculate the stock quantity for each day of the month. How can i do this?
regards adyck
|
|
|
|
|
you can loop through all the days of months and store count of moves in another table.
|
|
|
|
|
u can create a proc with following steps which will return a table..
1) Get no. of days in month. say @no and @p = 1
2) while (@p < @no)
3) Get date and write query GetDate[^]
4> create table with date and count and return it.
Lemme know if it is useful...
|
|
|
|
|
Hello Prateek G,
excuse me, I'm a beginner but
1)what do you mean with @p?
3)can You give me some more hints
4)do You mean selecting the count from TblArticlemoves into a Date table?
Thank You!
Regards adyck
|
|
|
|
|
Doing this with straightforward SQL might get messy and complex. If you're up to it, create a temp table, fill in the days of the month and the stock value and PIVOT it to form the report.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi SG,
I've managed to create a temp table like this:
Dim dt As DataTable = New DataTable("TblDate")
dt.Columns.Add("artnr", Type.GetType("System.String"))
Do While startDate < endDate
dt.Columns.Add((startDate), Type.GetType("System.String"))
startDate = startDate.AddDays(1)
Loop
Now, how do fill in the stock values into the TblDate?
Regards adyck
|
|
|
|
|
I meant a Temp table in SQL Server. Instead of creating a tables with dates as columns, you can use the PIVOT command to prepare your report. PIVOT [^]is easy to master. Just look it up.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi all
i have a SQL Query that is like this
<br />
select p1.Property_ID,p1.Lis_key,p1.Func_key,p1.Attrib_code<br />
from sde.property p1<br />
where p1.func_key = 'PV000000'<br />
and exists (select 1<br />
from sde.property p2<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS'<br />
and substring(p1.Attrib_code,3,2)=22<br />
having count(p2.func_key) = 2)<br />
order by Actual_extent Asc<br />
<br />
And this Query works fine, and i want to update the records found by this query above,i tried this
<br />
update sde.property<br />
set Attrib_code = substring(Attrib_code,1,2)+ '10'+ substring(Attrib_code,5,8)<br />
where Exists (select p1.Property_ID,p1.Lis_key,p1.Func_key,p1.Attrib_code<br />
from sde.property p1<br />
where p1.func_key = 'PV000000'<br />
and exists (select 1<br />
from sde.property p2<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS'<br />
and substring(p1.Attrib_code,3,2)=22<br />
having count(p2.func_key) = 2 and <br />
P1.aCTIVE =1))<br />
<br />
The above query will try to execute the whole day, please help me combine the Query to create an update from these update that will update the records found in the first query and please can you explain, so that i can see my mistakes.
<br />
<br />
update sde.property<br />
set Attrib_code = substring(Attrib_code,1,2)+ '10'+ substring(Attrib_code,5,8)<br />
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
|
|
|
|
|
Update p1 set p1.Attrib_code = substring(p1.Attrib_code,1,2)+ '10'+ substring(p1.Attrib_code,5,8)
from sde.property p1
where p1.func_key = 'PV000000'
and exists (select 1
from sde.property p2
where p2.lis_key = p1.lis_key
and substring(p2.func_key,1,5)='GEOSS'
and substring(p1.Attrib_code,3,2)=22
having count(p2.func_key) = 2)
order by Actual_extent Asc
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Thanks man, i have seen my mistake,
you are a star
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
|
|
|
|
|
Hi
Anyone with a working TSQL string tokenizer? I found this but seems like it's not working and I'm too lazy to debug this.
Thanks
|
|
|
|
|
Twas working when I used a few months back...
CREATE FUNCTION [dbo].[Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
INSERT INTO @t(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
RETURN
END
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
|
Did you try it? It returns an empty table
select *
from dbo.Split('aaa.bbb.ccc', '.')
|
|
|
|
|
Oops, the last time i pasted the code, <d> was interpreted as HTML.
CREATE FUNCTION [dbo].[Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))<br />
RETURNS @t TABLE (data NVARCHAR(max))<br />
AS<br />
BEGIN<br />
<br />
DECLARE @textXML XML;<br />
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);<br />
<br />
INSERT INTO @t(data)<br />
SELECT T.split.value('.', 'nvarchar(max)') AS data<br />
FROM @textXML.nodes('/d') T(split)<br />
<br />
RETURN<br />
END<br />
<br />
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi all,
I am working in sql 2005 migration.
when i ran same query in sql 2000 and sql 2005,no of values return are same.But the Resulting data is in different order.
In sql 2005 the data returns either in asc or desc order.
In sql 2000 the data returns in random order.
plz clarify me
1.the reason for the rows order difference.(is it because of execution plan?)
2.The resulting data of sql2005 should be in same order of sql 2000.
how to overcome this problem.
Thanks in advance,
cheers
sangeet
|
|
|
|
|
ramyasangeet wrote: 1.the reason for the rows order difference.(is it because of execution plan?)
The order in which the rows are returned is determined by a lot of factors, clustered index, page placements, row order etc. For example, if there's a clustered index on the table, the optimizer will find it cheaper to return rows in the index order. Also, the query engine has undergone vast changes in 2005 compared to 2000.
ramyasangeet wrote: 2.The resulting data of sql2005 should be in same order of sql 2000.
Use an Order by clause.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
As the previous poster said, if you want a specific order then use the order by clause. Do not rely on the intrinsic order that SQL Server uses as it can, as you've seen, be non-deterministic.
|
|
|
|
|
Dear all
I created a Data Entry web page (PHP) that has a form with a number of components such as name, mobile , age, etc... (text fields). These data is entered in the database. This page is used by more than one user at a time.
The problem is that some records are entered twice or more in the database. There are some records was entered 15 times and all of them at the same time.
I am sure that my code execute the SQL statment once only.
What could be the problem? Why the script is executed more than one time?
Is it a Thread issue?
Is it a Server issue?
My database is Oracle and the page is in PHP.
regards
-- modified at 2:47 Wednesday 28th November, 2007
|
|
|
|
|
hi mhmo
you see, sometimes when we design our tables, we bare in mind that there might be two "john" or "Marry", so we need to identify them uniquely, we add an identity column or we look for something that will make them unique, like Social Security number, or ID number in your Country, but if you want to use that you can use IDentity column, by that you can track your problem, Another thing is that after your user submit your form that will insert data into the table, you have make sure that your user click it once. after a user click your Submit or save button once, Disable your button and display a Progress bar and you will have less Problems.
Hope this Helps
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
|
|
|
|
|
hi
I know that. And already I have an identity column and also I am disabling the button.
It seems that the problem is a Thread related or Server related.
Thanks
|
|
|
|
|
hi
Am not strong in threads, i always make sure that i dont use them, but some situations like concurency handling, or your case, you have to use them. am sorry i could not help
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
|
|
|
|
|
hi friends....I am using following query..its display output with site_id like row by row..but I want to display the site_id like 1,2,3,4,5(single row)...
SELECT dbo.ad_schedule.startdate, dbo.ad_schedule.enddate,
dbo.ad_sitereg.sitename, dbo.ad_schedule.Status, dbo.ad_schedule.creative_id,
dbo.ad_schedule.site_id
FROM dbo.ad_sitereg INNER JOIN
dbo.ad_schedule ON dbo.ad_sitereg.id = dbo.ad_schedule.site_id
WHERE (dbo.ad_schedule.Status = 'q') AND (dbo.ad_schedule.creative_id = '42')
anybody will plz reply me...
|
|
|
|
|
The following article[^] should help.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|