Problem with fetching cursor into local variables - Database Discussion Boards

Use round(val, 0)

I am getting this error when I am try to connec to MS Access database through OleDb

Selected collating sequence not supported by the operating system.

I am just running a select statement to get the data from the table showing it in a datatable.

Can someone help me out with this problem please? thanks

Did you check Microsoft KB? If not, maybe you have one of their described scenarios:

Selected collating sequence not supported by the operating system[^]

Hi,

I'm new here to the forum, and only slightly less new to programming. Does anyone have any books on databases that helped them when they started out?

Any help would be greatly appreciated!

Thanks!

Are you looking for books about database admin?
You need to specify which database. Microsoft SQL Server, Oracle, MySQL, Postgres, ...
They all have different underlying methods of storage and controlling data.
Even though they all work with SQL in similar ways.

Or writing SQL queries?
SQL From the Ground up, by Pyefinch was pretty good.

Or writing Procedural SQL?
-Nothing basic about that.

Or programming C# or C++ against a database?
-Not my field, someone else here will have to speak to that.

You might want to be more specific as to what your goal might be.

Is your objective simply to learn more about what is inside a database?
Relational Database Design by Harrington
Database Administration by Mullins

I've also had pretty good luck with integration books like "Oracle and Microsoft SQL server integration" because they tend to expose things I wouldn't otherwise have found out through comparison.

What's your poison?

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

If you are using Windows Forms, I found 'Data Binding with Windows Forms 2.0' by Brian Noyes, to be useful. It is all about programming in .NET, rather than database theory, but useful for that aspect of the development process. It also covers, briefly, ASP.NET.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Normally I avoid answering this type of question due to the fact that there are so many good books on the subject out there. This one though shines bright...

http://www.sql.co.il/books/tsqlfund2008/[^]

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

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.

Just out of curiosity.

If you bump this value up
@strSQL varchar(2000),

to something higher like

@strSQL varchar(3000),

do you get a different error?

edit:
And I don't know if this matters but you've got a disagreement here:
[GLName] Varchar(200),
@f_GLName varchar(50)

[FileNo] Varchar(50),
@f_FileNo varchar(30)

Weird errors creep in on things like this down the road even if the language lets you do it. I saw one TAL issue that took months to figure out because a variable was cutting a value off with a method like this.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

modified on Monday, June 8, 2009 1:44 PM

select @variable will only give the value inside it
you have try things like exec(@variable)

Hi Team,
I have an functional to retrieve data from hosting server to local server.Is there any option to set the custom installation to get facility of import and export via sqlserver2005 Express addition or is it needed to install any service pack for this.

Could any body help me out from the above scenario.

Thanks in advance.
Ram Panda(S.E.)
Ergode

Huh ! you question makes no sense (at least to me anyway) try restating your requirement and see if it gets a more positive response.

Never underestimate the power of human stupidity
RAH

The following list highlights the major SQL Server 2005 components that are not supported in SQL Server Express:

Reporting Services

Notification Services

Integration Services

Analysis Services

Full text search

OLAP Services / Data Mining

From: http://msdn.microsoft.com/en-us/library/ms165636(SQL.90).aspx[^]

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Hello,
I have table in microsoft access with fields- partNumber, Date, Status.
( the status field always is one of two words - on or off
can I write a iif statement in sql that says IIf ([partNumber = a certain Date]), the status will automatically display off for those records with that part number.
I already have alot of records entered in database.
I have tried and not used to writing IIf statements in sql-
also wonder can you change a field that has data in it with a iif condition statement.
thank you

As I understand you then you want to display Status to OFF for some records which contain one certain data in field Date ?

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

modified on Saturday, June 6, 2009 5:12 PM

yes you are correct

here it is

select iif(value='2009.01.02','OFF',value)  from table1

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

unfortunately this did not solve problem
I have partNumber Date and Status
I have tried Select partNumber,Date IIF(partNumber= 1011 and Date = #01/01/2006#,"OFF,ON) From table1
but still gettng errors-your sql statement is missing partNumber
thank you

modified on Sunday, June 7, 2009 7:34 AM

johnnysmith1 wrote:
Date IIF(partNumber= 1011 and Date = #01/01/2006#,"OFF,ON)

Should be

Date <big>,</big> IIF(partNumber= 1011 and Date = #01/01/2006#,'OFF','ON')

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

thank you it works

You are welcome.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Hi Experts
How to alter Column To On Identity on Column
i am try this but not working
ALTER TABLE TEST ALTER COLUMN iD IDENTITY (200, 2)

Sharma Dinesh Kumar wrote:
How to alter Column To On Identity on Column

ALTER TABLE Foo
ALTER COLUMN FooKey
ADD CONSTRAINT IDENTITY(100,1)

Regards
Aman Bhullar
www.arlivesupport.com[^]

This is not Working Give Me Error on CONSTRAINT

Does anyone have any ideas?