|
problem arises when fetching cursors.
please help asap.
the function code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION Get_Coll_Ovd_Report
(
@fromDate datetime,
@ToDate datetime,
@dtOvdDate datetime,
@dtOvdDatePrev datetime,
@strAndPaid VARCHAR(300),
@rdoFileInfoSpecific int,
@SpecificFileNo varchar(20),
@strAndFOfficer varchar(100),
@strAndZone varchar(100),
@strCollType varchar(50),
@rdoStatusNID smallint,
@rdoStatusBankOpen smallint,
@chkOvdVal smallint,
@rdoRepTypeAllDet smallint
)Returns @retVal table(
[SLNo] int,
[TDate] DateTime,
[GLRef] Varchar(50),
[LdgAcNo] Varchar(50),
[AcName] Varchar(200),
[MRNo] int,
[GLName] Varchar(200),
[Cash] decimal(18,6),
[Clear] decimal(18,6),
[Trans] decimal(18,6),
[CnTot] decimal(18,6),
[FileNo] Varchar(50),
[NidStat] smallint,
[UserID] Varchar(50),
[District] Varchar(50),
[PaidAt] Varchar(50),
[ZMrNo] Varchar(50),
[ZMrDate] DateTime,
[Model] Varchar(50),
[FOID] int,
[FClBal] decimal(18,6),
[TotCol] decimal(18,6),
[BLoan] decimal(18,6),
[BTotDr] decimal(18,6),
[OvdPrv] decimal(18,6),
[P1] decimal(18,6),
[P2] decimal(18,6))
AS
BEGIN
declare @f_SLNo int,@f_TDate datetime,@f_GLRef varchar(50), @f_LdgAcNo varchar(50) , @f_AcName varchar(200) , @f_MRNo int, @f_GLName varchar(50), @f_Cash decimal(18,6), @f_Clear decimal(18,6), @f_Trans decimal(18,6), @f_CnTot decimal(18,6), @f_FileNo varchar(30) , @f_NidStat smallint , @f_UserID varchar(50), @f_District varchar(50) , @f_PaidAt Varchar(50), @f_ZMrNo Varchar(50), @f_ZMrDate datetime, @f_Model Varchar(50) , @f_FOID int, @f_FClBal decimal(18,6), @f_TotCol decimal(18,6), @f_BLoan decimal(18,6), @f_BTotDr decimal(18,6), @f_OvdPrv decimal(18,6), @f_P1 decimal(18,6), @f_P2 decimal(18,6)
declare @strSQL varchar(2000),
@LdgAcNo varchar(12),
@MAcName varchar(80),
@FileNo varchar(30),
@MaxiInstlAmt decimal(18,0),
@MaxiMinInstlAmt decimal(18,0),
@DCPDt datetime,
@FClBal decimal(18,6),
@Ac_Status smallint,
@FileClosedDt datetime,
@ReconV varchar(15),
@GuestPrdComp smallint,
@TermComp smallint,
@FOID int,
@FOName Varchar(100),
@DistName Varchar(50),
@ZName Varchar(50),
@Model Varchar(150),
@Ac_StatusB smallint,
@BClosedDt datetime,
@SzdStatus varchar(50),
@SzdDate datetime,
@IntRt decimal(18,6),
@LoanAmt decimal(18,6),
@TotAmt decimal(18,6)
set @strSQL = 'SELECT Distinct
tblILedger.LdgAcNo,
tblBuyerMst.MAcName,
tblVLedger.FileNo,
tblVLedger.MaxiInstlAmt,
tblVLedger.MaxiMinInstlAmt,
tblDCDetail.DCPDt,
tblILedger.FClBal,
tblILedger.Ac_Status,
tblVLedger.FileClosedDt,
tblVLedger.ReconV,
tblVLedger.GuestPrdComp,
tblVLedger.TermComp,
tblVLedger.FOID,
tblFOfficer.FOName,
tblDistMst.DistName,
tblZoneMst.ZName,
tblModelMst.Model,
tblLoanDoc.Ac_StatusB,
tblLoanDoc.BClosedDt,
tblVLedger.SzdStatus,
tblVLedger.SzdDate,
tblVLedger.IntRt,
tblLoanDoc.LoanAmt,
ISNULL((SELECT SUM(tblMRMst.TotAmt)From tblMRMst Where (tblMRMst.LdgAcNo = tblILedger.LdgAcNo) AND (tblMRMst.TotAmt > 0) AND (tblMRMst.Rvrs = 0 OR tblMRMst.Rvrs is Null) ' + @strAndPaid + '),0) AS TotAmt '+
'FROM tblVLedger INNER JOIN tblILedger ON tblILedger.LdgAcNo = tblVLedger.LdgAcNo INNER JOIN
tblDCDetail ON tblVLedger.DCNo = tblDCDetail.DCNo INNER JOIN tblBuyerMst ON
tblDCDetail.BNo = tblBuyerMst.BNo INNER JOIN
tblDistMst ON tblBuyerMst.MDistID = tblDistMst.DistID INNER JOIN tblZoneMst ON tblBuyerMst.MZID = tblZoneMst.ZID
Inner Join tblModelMst ON tblDCDetail.MNo=tblModelMst.MNo
Inner Join tblFOfficer ON tblVLedger.FOID=tblFOfficer.FOID
INNER JOIN tblLoanDoc ON tblDCDetail.DCNo = tblLoanDoc.DCNo
WHERE (tblDCDetail.DCPDt <'''+cast(@fromDate as varchar(20))+''')
AND ((tblILedger.Ac_status = 1) OR ((tblILedger.Ac_status = 0) AND (tblVLedger.FileClosedDt >='''+cast(@fromDate as varchar(20))+''')))'
if (@rdoFileInfoSpecific=1)
begin
set @strSQL = @strSQL + ' AND (tblVLedger.FileNo= '+@SpecificFileNo+')'
end
if (@rdoStatusBankOpen=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=1 OR (tblLoanDoc.Ac_StatusB = 0 AND tblLoanDoc.BClosedDt >= '''+cast(@fromDate as varchar(20))+'''))'
end
else
begin
if(@rdoStatusNID=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=0 AND (tblLoanDoc.BClosedDt is null OR tblLoanDoc.BClosedDt< '''+cast(@fromDate as varchar(20))+'''))'
end
end
set @strSQL = @strSQL + @strAndFOfficer;
set @strSQL = @strSQL + @strAndZone;
set @strSQL = @strSQL + @strCollType;
set @strSQL = @strSQL + ' ORDER BY tblZoneMst.ZName,tblVLedger.FOID,tblVLedger.FileNo'
-- end of main query string
declare curMain cursor for select @strsql
open curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallint
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- A lot of codes goes here . but they do not raises the error. I checked it.
--insert into @retVal values(@f_SLNo,@f_TDate,@f_GLRef , @f_LdgAcNo , @f_AcName , @f_MRNo, @f_GLName , @f_Cash, @f_Clear, @f_Trans, @f_CnTot, @f_FileNo , @f_NidStat , @f_UserID , @f_District , @f_PaidAt , @f_ZMrNo , @f_ZMrDate, @f_Model , @f_FOID, @f_FClBal, @f_TotCol, @f_BLoan, @f_BTotDr, @f_OvdPrv, @f_P1, @f_P2)
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
END
CLOSE curMain
DEALLOCATE curMain
return
END
calling with:
select * from dbo.Get_Coll_Ovd_Report
(
'14 Apr 2009',
'10 May 2009',
'13 Apr 2009',
'10 May 2009',
'',--' this will be andpaid clause',
0,-- this is tag for file specific
'900',-- this is file no
'470',--'officer id', -- all officer
'',--' zone id',-- zone id
'',--'coll type',
0,--is nid
0,
0,
1
-- is bank open
)
output:
Msg 16924, Level 16, State 1, Line 1
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
|
|
|
|
|
Your post is a database question, and you should post this in the database forum.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys | Onyx
|
|
|
|
|
Pete O'Hanlon wrote: you should post this in the database forum
It's a fantastic candidate for the "Coding Horrors" forum instead. 
It is a crappy thing, but it's life -^ Carlo Pallini
|
|
|
|
|
you know the sp has lots of code than high level language
|
|
|
|
|
try to remove the next from from the code below and try again
open curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallint
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
change it into fetch curMain into @LdgAcNo,....
|
|
|
|
|
I have tried it.
returns same message
mir
|
|
|
|
|
Be sure that the number of columns you are selecting in the select statement for the cursor is the same number of variables that you have when calling the Fetch
|
|
|
|
|
I have chacked it several times . Everything is fine . also the length for varchar types. the message not shown if I select into the first variable only. I don't know whats the problem with it . but I have wrote a lot of sp with such select into. 
|
|
|
|
|
In the query you are building you have Select Distinct and when you are creating the cursor you have another SELECT. it can be that there is the problem. when it tis being executed you are havin
SELECT SELECT DISTINCT...
|
|
|
|
|
No change takes place.
Instead, would you please tell me the ways to open cursors excluding
Open cursor cursor_NAme for select_statement
I think as I provide the select_statement as a string variable , it may functioning another way
thanks for your time
br,
mir
|
|
|
|
|
mmdullah wrote: Everything is fine
No its not, otherwise you wouldn't be getting the error. The error TELLS you what is wrong, you need to double check it. At the end of the day, its right and you're wrong.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Would make a good entry for the Code Horrors forum.
|
|
|
|
|
The problem is here:
mmdullah wrote:
declare curMain cursor for select @strsql
the select statement selects a string not a result set!
no need to write all that code to generate the error, a simple script of 4/5 lines will do the trick! 
regards
|
|
|
|
|
I'm not a T-SQL expert so I may be wrong here. On the other hand, if you want the opinion of database experts you should probably have posted this in the database forum.
I suspect that:
declare curMain cursor for select @strsql
will select the literal contents of the variable strsql. It seems to me that you will need to exec the strsql variable, or whatever the T-SQL equivalent is, in order to get it to run as an actual query.
Of course, if anyone from the database forum happens to be passing by and knows better than me, please feel free to correct me.
|
|
|
|
|
Why are you using a cursor for this? A cursor is a very inefficient way of manipulating data, and as far as I can see, all you are doing is returning data. Why not use a stored function to return this data?
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys | Onyx
|
|
|
|
|
Hi,
How can I use wscript in c# to return the result of the vbscript.
For example I have a vbscript file called test.vbs
This file contains:
wscript.echo 100
wscript.echo 200
In c#, I would like to run this file and return the two returned values i.e. 100 and 200
How can I do this please?
Thanks
|
|
|
|
|
I'm not 100% sure but I don't think you can
If you were using cscript you could just read from its standard output (untested, but it's supposed to work)
|
|
|
|
|
|
I am doing a small windows application using C# which connects to an MS Access Database. I am using OleDb via ADO to interact with the database. when I am trying to get the data from the database I'm being shown this error message
"Selected collating sequence not supported by the operating system."
I googled the problem but all the results that I have tried failed. Can someone help me about this please?
Thanks
|
|
|
|
|
Can you post the connection and selection code please? We may be able to help if we see the code that you using!!
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
This is the method that I am calling to get the data
public DataTable GetData(string fromDate, string toDate, string connectionString)
{
DataTable dt = new DataTable();
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
try
{
cmd.CommandText = String.Format("SELECT CHECKINOUT.CHECKTIME, CHECKINOUT.CHECKTYPE, USERINFO.TITLE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID WHERE (((CHECKINOUT.CHECKTIME) Between #{0}# And #{1}#));", fromDate, toDate);
cmd.Connection = con;
con.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
dataAdapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
dt = null;
}
finally { con.Close(); }
return dt;
}
|
|
|
|
|
Have a look at the following post[^]. Maybe this will give you some insight into a solution. I think it might be something to with the BETWEEN statement.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
Hello Experts!!
As we all know that we have to include a item number whenever i add in 1 item into the datagrid.
eg.
ItemNo Name Address
1. Mary Canada
2. John USA
i have included an Item no column in my datagrid but i dont know how can i make it work. i want to increment the item no by 1 everytime i add in a new record. but after when i exit the form and come back again. the item no will start at 1 again, it will not continue on with the last number. i will "reset" and start from 1 again. Hope someone will be able to help me!
Any doubts, you can clarify with me. hope i managed to explain what i want to do.
|
|
|
|
|
When you close the form the count is destroyed - normally the number is stored in the database. If you do not store it in a database you need to store it some where. There are a number of options, settings or a text file or a xml file even a registry item can be used.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Have you tried setting the item number as the Primarykey in the Table? This will then increase automatically everytime you add a new item.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
