Re: iterating values in sql - Database Discussion Boards

hi paul,
i tried , but up till now i have no result found. i also try for crystalreport viewer but in CrystalReportViewer case i am sure that it does not support subscript & superscript.
have you any idea about superscript & subscript in Microsoft Reporting service related to VS.NET 2005(ReportViewer control)?if yes , please guide me or send any link.
Thanks in advance.

Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)

hello

i have a database where i get links out off for images standard is 1 image per page but sometimes i want to load multible images on to a page without changing the page everytime i have got 1 asp page were can load aal of the record from the database from i want to add a script thats checking if the is a link for second image availeble if it's there then that he show it if not he does nothing. i think it a true or false script but i dont know how toe work it out

cool349

From your description, I'm not 100% sure what you are trying to achieve but I think I know. Anyway, if you were creating an ASP.NET page and you wanted to display the images, I would wrap each required image up inside its own control. The control would have a primary key identifier that told it which image to download from the database and the control would then be responsible for displaying the resulting image.

Deja View - the feeling that you've seen this post before.

hi,

I have two tables reciept and issue.
Reciept have the foloowing details

partno date rquantity rrate
a 10/1/2005 50 15
a 15/4/2005 10 22
a 11/7/2005 40 20

Issue have the foloowing details

partno date iquantity irate value
a 14/1/2005 10 10
a 15/5/2005 20 22
a 1/8/2005 20 22

what i have to do is
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column
iquantity(irate-rrate)
example, first we have to subtract
10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50
20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440
20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440

Can anyone tell to solve this?

Chaitra N

n_gchaitra wrote:
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column

How about trying to itrate this from Front-End and thereby updating the column in the DB accordingly?

I thought of using asp.net with vb script. But I think then also we need to write appropriate sql statements only.

Chaitra N

What you are looking for is a cursor (at least if you are using sql server).
cursors allow you to iterate through a set of results returned by a select statement and perform any number of sql actions on each iteration. Be careful though as SQL is designed for Set based actions not iterative procedures so the performance overhead against a properly designed query (if one would be possible for what you want to do) is huge.

Just google for 'SQL Cursor' and you should find what you want.

Jonathan

I dont know whether i have to use cursor or simply a stored prcedure.
This is FIFO inventory system. Recieved and Issue items will be in different table say, RECIEVE & ISSUE. Each item may be recieved and issued many times. Each time may be with different rate,quantiy and date. For example let us considere the following tables,

RECIEVE TABLE

PARTNO RDATE RRATE/UNIT rQUANTITY

A 10/10/2006 15 20

A 10/10/2007 20 50

A 1/11/2006 18 10

B 10/12/2006 15 30

B 13/2/2007 25 15

ISSUE TABLE

PARTNO IDATE IRATE/UNIT iQUANTITY

A 10/5/2007 18 18

A 1/12/2007 20 45

A 13/2/2007 20 15

B 13/2/2007 20 20

B 1/5/2007 20 25

Ultimately i have to get the following table

PARTNO IDATE IRATE/UNIT iQUANTITY ShortTerm LongTerm

Here I want to find the LongTerm Value and Shorttem value depending on the difference in date between idate and rdate of each item. This is to find the gain or loss of the item in that particular date. The formula is iquatity(irate)+iquatity(irate-rRate),if the date difference between recieve and issue is >365 day then the calculated value should be place in LongTerm value column else in the Short term value column

First we take A, the first issue quanitity is 18, but the no of quantity recieved was 20 so the value will be 18(18)+18(18-15). Now the remaining no of items present in first recieved is 2.The difference between issue date 10/5 /2007 and recieve date 10/10/2006 is less than 365 days so has to be placed in ShortTerm column.

So when calculating the amount of second issue first we have issue remaining 2, then go for second recieve. Here issue quantity is 45. so, the value must be (2(20)+2(20-15)). The difference between issue date 1/12/2007 and recieve date 10/10/2006 is more than 365 days so has to be placed in LongTerm column. Then remaining 43 items, the value has to be (43(20)+(43(20-20). The difference between issue date 1/12/2007 and recieve date 1/12/2007 is less than 365 days so has to be placed in ShortTerm column. Now the remaining items in Recieve table for A item is 7+10.

Now we have to calculate the next A item as we did for second. i.e. first issuing 7 items from the remaining RECIEVE and then another 8. So we are left with 2 recieved items which will be there until next issue.

This has to be repeated for the Item B and so on.

Chaitra N

I have written following sqlstatements to update a column based on the no of days between two dates. but it is giving the error,
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In what other way i can get the expected result

if (select datediff(dd,transactiondate, Issuedate) from receipt)>365
begin
update receipt set longvalue=datediff(dd,transactiondate, Issuedate)
end
else
begin
update receipt set shortvalue=datediff(dd,transactiondate, Issuedate)
end

Chaitra N

Use a WHERE in the UPDATE

UPDATE receipt set longvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) >365
 
UPDATE receipt set shortvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) <= 365

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Hi,

I want to find the date difference of date1 and date2. But these two colums are present in different tables. I have used joins but not getting proper results because the no of rows i am getting is the cartesian product of the two tables. I am includeing two tables and the result i have got after using joins. Please tell the right way to do this.

Tran_type Partno Description Transactiondate Quantity Rate Balqty
--------- ------ ----------- ------------------- ------------ --------- -------
R ABC NULL 2005-01-01 00:00:00 15.0 20.0 15.0
R ABC NULL 2005-04-02 00:00:00 15.0 30.0 15.0
R ABC NULL 2005-09-01 00:00:00 50.0 30.0 50.0

(3 row(s) affected)

Tran_type Partno Description Transactiondate Quantity Rate Balqty
---------- ------ ----------- ------------------- ----------- --------- --------
S ABC NULL 2005-06-03 00:00:00 5.0 25.0 NULL
S ABC NULL 2005-07-01 00:00:00 5.0 10.0 NULL
S ABC NULL 2006-06-01 00:00:00 15.0 50.0 NULL

(3 row(s) affected)

tran_type transactiondate transactiondate tran_type Date diff
--------- --------------------------- --------------------------- ---------- -----------
R 2005-01-01 00:00:00 2005-06-03 00:00:00 S 153
R 2005-04-02 00:00:00 2005-06-03 00:00:00 S 62
R 2005-09-01 00:00:00 2005-06-03 00:00:00 S -90
R 2005-01-01 00:00:00 2005-07-01 00:00:00 S 181
R 2005-04-02 00:00:00 2005-07-01 00:00:00 S 90
R 2005-09-01 00:00:00 2005-07-01 00:00:00 S -62
R 2005-01-01 00:00:00 2006-06-01 00:00:00 S 516
R 2005-04-02 00:00:00 2006-06-01 00:00:00 S 425
R 2005-09-01 00:00:00 2006-06-01 00:00:00 S 273

(9 row(s) affected)

Chaitra N

Could you show the query ?

My Website | Ask smart questions

select 'Difference in Date'=datediff(dd,r.rdate,i.idate) from receive r,issue i where r.partno=i.partno

Chaitra N

Hallo

for the overview I only copied the code wich is necessary to, I hope, solve my problem.

1. With the following command line "retError = dbExtDel.DeleteSQL(sql)" I am deleting one recordset from my table. [Succsessful]

2. With the following command line "arrMedExtension = ReadExtensions(retError)" I import the data from my table with the before deleted record again. [Unuccsessful]

Even the record has been deleted in the first step, in the second step I still can see the deleted record. If I insert the command line " System.Threading.Thread.Sleep(1000)" an wait for a litle while sometimes it is Succsessful but not ever. It seems that it is a timing problem but I cant find the cause. Can anybody assist me to solve my problem ?

Thanks in advance.

The sql string I used

sql = "DELETE Audio_Extension.ID, Audio_Extension.AudioExtension FROM " + _
"Audio_Extension WHERE (((Audio_Extension.AudioExtension)='" + ExtDel + "'));"
______________________________________________________________________________________
If diagReceive = 2 Then
retError = dbExtDel.DeleteSQL(sql)
If retError = True Then Exit Sub

arrMedExtension = dbReadExt.ReadFieldsFromTable("Audio_Extension", "AudioExtension", retError)
If retError = True Then Exit Sub

....

End if
_____________________________________________________________________________________
Public Function DeleteSQL(ByVal _sql As String) As Boolean
Dim retVal As Integer
Dim AbortApp As Boolean
Dim cmdStr As String = _sql
Dim con As New OleDb.OleDbConnection(ConString)
Dim cmd As New OleDb.OleDbCommand(cmdStr, con)

Try

con.Open() connection .
retVal = cmd.ExecuteNonQuery

If retVal = 0 Then
MsgBox(gResErr.GetString("dbErr6"), MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, gResErr.GetString("dbErrHead1"))
AbortApp = True

End If

Catch ex As Exception
MsgBox(gResErr.GetString("dbErr1") + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3Sol1") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErrMes") + vbCrLf + ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Mod: DeleteSQL >" + mod_GlobalVar.gResErr.GetString("dbErrHead1"))

AbortApp = True
Finally
Try
con.Close()
con = Nothing

Catch ex As Exception
'No further error mesage.

End Try
End Try

Return AbortApp

End Function
___________________________________________________________________________________

Public Function ReadFieldsFromTable(ByVal _tableName As String, ByVal _FieldName As String, ByRef AbortApp As Boolean) As ArrayList

Dim rs As Object = CreateObject("ADODB.Recordset")
Dim FieldContent As String = ""
Dim arr_Fileds As New ArrayList

Try

rs.Open(_tableName, ConString, 3, 3)

If rs.RecordCount > 0 Then
Do Until rs.EOF
arr_Fileds.Add(rs.Fields(_FieldName).Value)
rs.MoveNext()

Loop
End If

Catch ex As Exception

MsgBox(mod_GlobalVar.gResErr.GetString("dbErr1") + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr2") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3Sol1") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErrMes") + vbCrLf + ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Mod: ReadFieldsFromTable >" + mod_GlobalVar.gResErr.GetString("dbErrHead1"))

AbortApp = True

Finally
Try
rs.Close()
rs = Nothing

Catch ex As Exception
'No further error mesage.

End Try
End Try

Return arr_Fileds 'return value.

End Function

Hii,

I have a MSDE or SQL sever, it is logging the data from an instrument continuously. I want to genarate an Report on Excel sheet on daily basis and separate Monthly basis, which should happen automatically.

In this case,

For, Daily report,each hour data has to be reported in MS-Excel sheet by row wise in the perticular column. 00:00 hrs to 24:00 hrs data should be updated frm SQL sever or MSDE to Excel sheet. In another column of Excel, 01:00-00:00(subtraction) hr data should be updated as a subtracted result, 02:00-00:00 hr data likewise for 24:00 hr, 24:00-00:00 hr data to be updated (From each hour 00:00 hr's data to be subtract), and below side of the excel sheet, all these 24 subtracted result should be added and this is the Result. All this things happen automatically.This is the daily report.

In the monthly Reoprt, all the result of the daily report update in another excel sheet row wise for each day and after the complition of month, all data's to be added and it will be the result in that report.

So please give me the solution regarding this. This Reports should genarate automatically.

With regards,
Ravisubrahmnaya

Sounds like you have the start of a nice spec for a reporting application including a scheduler. It could be implemented in C# using the System.Data.SqlClient classes to read the SQL data and System.Data.Oledb classes to write the Excel spreadsheet.

Another alternative would be to use SQL Server reporting services, since this could also provide the scheduling aspect.

Dear Mr.Rob Graham,

Thanks for ur suggestions. If i have further doubt i will ask ur help.

with regards,
Ravi

With regards,
Ravisubrahmnaya

Dear Rob Graham,

Can i just illustrate me how it will be done using C# and by SQL Server reporting services. Just for day's report genaration can u illustrate me how it will be done using programmng?. if u have a time and if my help is nt seems to be wrong, pls reply me.

With regards,
Ravisubrahmnaya

Hii,

Pls, help me to restore MSDE sever data in to SQL server database.

With regards,
Ravisubrahmnaya

Since MSDE IS SQL Server, just back up your MDE data nad then use SQL Server to restore it to an SQL database.

hi,
i use following procedure for display report in reportviewer control

Public Sub ItemSummaryReport(ByVal team As String, ByVal idString As String)<br />
        Try<br />
            Dim str As String<br />
            Dim dsIndividualinventory As New DataSet<br />
            Dim oleadapter As OleDbDataAdapter<br />
<br />
            str = "SELECT master.item, master.size1, master.cost, master.used, master.out AS MasterOut, master.onhand, master.team, individual.id1, individual.id, individual.name1, individual.mi, issue.itemid, issue.issued, issue.issueddate, issue.returned, issue.returneddate, issue.out, issue.tagsout " _<br />
           & " FROM (individual INNER JOIN master ON individual.team=master.team) INNER JOIN issue ON (master.id=issue.itemid) AND (individual.id=issue.personid) where master.team='" & team & "'"<br />
<br />
            GlobalModule.myconnectionopen()<br />
            oleadapter = New OleDbDataAdapter(str, GlobalModule.conn)<br />
            oleadapter.Fill(dsIndividualinventory, "IndividualInventory")<br />
            GlobalModule.myconnectionclose()<br />
<br />
          <br />
            Dim RDS As Microsoft.Reporting.WinForms.ReportDataSource = New Microsoft.Reporting.WinForms.ReportDataSource<br />
            IndividualInventoryBindingSource.DataSource = dsIndividualinventory<br />
            IndividualInventoryBindingSource.DataMember = "IndividualInventory"<br />
            RDS.Name = "dsIndividualinventory_IndividualInventory"<br />
            RDS.Value = IndividualInventoryBindingSource<br />
            Me.ReportViewer1.LocalReport.DataSources.Add(RDS)<br />
            ReportViewer1.LocalReport.ReportEmbeddedResource = ReportSource & ".ReportItemInventory.rdlc"<br />
            Me.ReportViewer1.RefreshReport()<br />
            Me.Show()<br />
        Catch ex As Exception<br />
            MsgBox(ex.Message.ToString)<br />
        End Try<br />
       <br />
    End Sub

Every thing is going well.Above procedure show report properly.i show all of my report using procedure similar to above procedure.in each procedure only binding source & report name & dataset is changed.

when i run my application then reportviewer control show some of my report.if i again restart my application then it show also remaining report. so i am sure there is no error in my procedure.

my problem is that reportviewer control does not show all report.some of report is generated properly while some of report show message "Report is being Generated". This message is appear until i do not close form related to reportviewer. However next time it show this report(mean when i restart my application).i don't know where i am wrong

Please guide me where i am wrong ? or provide some link.
any help will be appreciated

Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)

Hi I want to select records from last two days, the following query does not return any record.

select * from tablename where datefield = dateadd(day,-2,Getdate())

Any help will be appreciated.

Shahzad Aslam
Software Engineer

The = sign is your problem. Try >=, otherwise you're only asking for records that have exactly the same time as today, but two days ago.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Try the following solutions:

1) use the abbreviations 'd' OR 'dd' inplace of day in your syntax.
2) Check out your table. Are you storing your data alongiwth the time in the database. If yes then this query will never return any result

Thanks,

Vikash Kumar Singh

vikash

i have th followwing qury and i need to show only one record for the customer that is last transiction that he done.
i have three tables and i write the query

SELECT cust_order.order_id AS order_id, customers.fname AS fname, customers.lname AS lname, cust_order.order_date AS order_date, cust_order.order_status AS order_status
FROM cust_order, customers WHERE cust_order.customer_id=customers.customer_id cust_order.order_id In (SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000) AND cust_order.customer_id=customers.customer_id and cust_order.customer_id in ( SELECT DISTINCT customer_id FROM cust_order
WHERE order_id IN ( SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000);
in which i get the distinct customers from the
SELECT DISTINCT customer_id FROM cust_order
WHERE order_id IN ( SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000
when i run the query individually i get distinct(one record for the customer) but when i run the above whole query i got all order which the customer place.

plz its urgant i need ur help