Access to one table in quick succession cause problem. - Database Discussion Boards

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

Please stop using text speak. Read the forum guidelines. Also, we don't care if it is urgent or not.

_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!

Try this[^]. Is this homework?

But is it the same guy using two different accounts hoping to get two different answers?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

I know this was urgent for you, but I didn't see you offer to pay for a solution, so I wasn't sure just how urgent it really was. Nevertheless, I did take a few minutes and try to read your select statement. I'm really blind about these things so I took the liberty of reformatting it a bit so I could see what was going on:

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)?;

Apart from putting in spaces, carriage returns, and changing case on a couple of reserved words, I've also placed two '?' marks where I don't understand the query. The first one SEEMS to have a missing operator. The next one SEEMS to have a missing ')'. You said you are running this query and getting results, so I guess I don't understand the dialect you are using. Would it be possible for you to re-write the intention of the query in a dialect I could understand, and re-post it? If so, I might be able to look at it a couple of minutes and POSSIBLY offer some suggestions.

David

Hello, i have some visual foxpro tables and i try to insert data into them using ole db. I have the following command:

INSERT INTO comanda_a (comanda, data, agent, sofer, client, magazin, discount)
VALUES ('2', '07/20/2007', 104, 1, '10016705', 2, 30)

the comanda_a table looks like this:

---------------------------
comanda | OleDbType.Char
data | OleDbType.DBDate
agent | OleDbType.Integer
sofer | OleDbType.Integer
client | OleDbType.Char
magazin | OleDbType.Integer
discount| OleDbType.Double
---------------------------

After the insert i get a data type mismatch error, i dont know which column.
Sniff | :^)

I never used ole db before, (but i know the sql commands,) if u know some links or tutorials please post them also.

In the meantime i have found a way to insert rows using parameters(in c#).

any help would be greatly appreciated.

-- modified at 7:59 Friday 20th July, 2007

Using parameters would be the recommendation in any case, since this is a generally more robust solution and avoids the likelihood of sql injection attacks by malicious users. It is most likely the date column that is complaining, or the double column (need a .0?). Parameters solve this by providing a more explicit opportunity for the data conversion, and they encapsulate the variables in the query, minimizing chances for SQL injection from the UI.