Access add record button grayed

Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't add
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
Hugo
The really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:

>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't add
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo

Access add record button grayed

Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't ad
d
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
HugoThe really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:

>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't a
dd
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo

Access 2000 forms record count wont show

I have a SQL Server 2000 backend with an Access 2000 .mdb frontend using ODBC to connect to the SQL tables.

I have forms with their Recordsource set to the SQL tables but when I open the forms, the total record count isn't shown beside the record navigator unless I click to go to the end of the recordset. I really need the record count to show up.

HELP!This question really belongs in the Acces forum, but try creating an On Open event in your form with this code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
End Sub

blindman

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
100@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
000001000051@.000055@.000057000058@.
000060000064@.
000066000068@.000071@.000073000074@.
000076000080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X96@.
100@.103
Any advice, thanks!
Angi
Do you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001000051@.000055@.000057000058@.00006000 0064@.
000066000068@.000071@.000073000074@.00007600 0080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
000010
000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
000020
000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
000030
000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
000040
000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
000050
000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
000066
000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
000080
000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
000103 ...
Now, I use store procedure as follow and print the result
¡°SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
¡°The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
96¡@.100¡@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
100¡@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
100¡@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
--
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '¡@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '¡ã' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '¡@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001¡ã000051¡@.000055¡@.000057¡ã000058¡@.000060¡ã000064¡@.
000066¡ã000068¡@.000071¡@.000073¡ã000074¡@.000076¡ã000080¡@.
000085¡ã000087¡@.000089¡ã000090 ....
"wei xiao" <weix@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
> 100¡@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a serial numbers like follow and each of number is a record.
> >
> > 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> > 000010
> > 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> > 000020
> > 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> > 000030
> > 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> > 000040
> > 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> > 000050
> > 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> > 000066
> > 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> > 000080
> > 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> > 000103 ...
> >
> > Now, I use store procedure as follow and print the result
> >
> > ¡°SQL Store Procedure
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > DECLARE @.front INT
> > DECLARE @.frontsav INT
> > DECLARE @.frontbk INT
> > DECLARE @.key INT
> >
> > SET @.key = 0
> >
> > DECLARE spDashCursor CURSOR FOR
> > SELECT DISTINCT srno
> > FROM all
> > WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> > AND pqty IN ('Q','X') AND cate = 'IV'
> > ORDER BY srno
> > OPEN spDashCursor
> >
> > FETCH NEXT FROM spDashCursor INTO @.front
> > WHILE @.@.FETCH_STATUS = 0
> >
> > BEGIN
> >
> > IF @.key = 0
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > PRINT CAST(@.front AS VARCHAR(6))
> > SET @.key = @.key + 1
> > END
> > ELSE
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > END
> >
> > IF @.frontsav - @.front > 1
> > BEGIN
> > IF @.frontbk = @.front
> > BEGIN
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> > ELSE
> > BEGIN
> > PRINT CAST(@.front AS VARCHAR(6))
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> >
> > SET @.frontbk = @.frontsav
> > END
> >
> > SET @.front = @.frontsav
> > END
> >
> > CLOSE spDashCursor
> > DEALLOCATE spDashCursor
> > GO
> >
> > ¡°The Result
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> > 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> > 96¡@.100¡@.103
> >
> > So, How could I make result like follow..
> > It's mean when numbers were continuous then use "DASH" to link.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> > 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> > 100¡@.103
> >
> > Any advice, thanks!
> > Angi
> >
> >
>

About Disconnected Record Sets

Hi,
I am using disconnected ADO record sets while using an Access DB for
storing
my intermediate values in processing. I had initially used a server side
cursor, however it gave a bad performance while inserting a record one at a
time.
Now using a client side cursor gave a decent performance while inserting
records, but while finding records within the record set, I get deteriorated
performance which far overrides the benefits I receive during record
inserts.
I am using 'Find' while Deleting and updating records within the
recordset. My understanding is the Find will check the disconnected set for
the record that matches the Find criteria and fetches the record from the
server if no record matching the criteria is found. Is this assumption
right?
Any ideas why the performance is bad while using disconnected record
sets for Find? If anything, it should be as bad as it was while using server
side cursor because at that point, I used to fire a SELECT query everytime.
Thanks in advance,
MadhuHi
Does this posting by Bob help?
http://tinyurl.com/gkrv4
John
"Madz" <noemail@.none.net> wrote in message
news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using disconnected ADO record sets while using an Access DB for
> storing
> my intermediate values in processing. I had initially used a server side
> cursor, however it gave a bad performance while inserting a record one at
> a
> time.
> Now using a client side cursor gave a decent performance while
> inserting
> records, but while finding records within the record set, I get
> deteriorated
> performance which far overrides the benefits I receive during record
> inserts.
> I am using 'Find' while Deleting and updating records within the
> recordset. My understanding is the Find will check the disconnected set
> for
> the record that matches the Find criteria and fetches the record from the
> server if no record matching the criteria is found. Is this assumption
> right?
> Any ideas why the performance is bad while using disconnected record
> sets for Find? If anything, it should be as bad as it was while using
> server
> side cursor because at that point, I used to fire a SELECT query
> everytime.
> Thanks in advance,
> Madhu
>
>|||Hi,
I am not using disconnected record sets per se. I am using a client side
cursor. I have set the the cursorlocation property to use client side
cursor.
Let me briefly explain wherein I am facing these problems. I set the
cursorlocation and the maxrows properties on the recordset and open the
recordset with a "SELECT * FROM Table". Now, while looping through the
recordset, I do not see any benefits of using a client side cursor in terms
of time i.e. it takes as much time as it did while using a server located
cursor. I got around my other performance deteriorations by using command
executes for inserts and deletes.
Why does looping thru' the recordset in this case take the same amount
of time as a server located cursor?
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
> Hi
> Does this posting by Bob help?
> http://tinyurl.com/gkrv4
> John
> "Madz" <noemail@.none.net> wrote in message
> news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Without knowing exact details of your systems it may be hard to tell why
but...using SELECT * may mean you are returning unnecessary data back to
your client. The quality of the client system may effect the response times.
If you are continually using find then it could mean that you need to order
the recordset better! I would expect MoveNext to be quicker than find. Do
you always movefirst before the find, in which case it could be slower.
John
"Madz" <noemail@.none.net> wrote in message
news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am not using disconnected record sets per se. I am using a client
> side cursor. I have set the the cursorlocation property to use client side
> cursor.
> Let me briefly explain wherein I am facing these problems. I set the
> cursorlocation and the maxrows properties on the recordset and open the
> recordset with a "SELECT * FROM Table". Now, while looping through the
> recordset, I do not see any benefits of using a client side cursor in
> terms of time i.e. it takes as much time as it did while using a server
> located cursor. I got around my other performance deteriorations by using
> command executes for inserts and deletes.
> Why does looping thru' the recordset in this case take the same amount
> of time as a server located cursor?
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
>|||Hi John,
I am no longer using Find method. Let me explain :-)...
I had 2 connections open to two different databases. One of them
required serial access to data while the other needed random. I was
initially using the Find method in both the cases but was well advised
against it because I read in forums that recordset method of updating data
is ill served against using command object.
The database that needed random access to data has now been modified to
use command object thru'out [and hence, I have no issues with this] while
the one requiring serial access to data uses client side cursor for obvious
reasons. Now my question :-)...
Why does using client side cursor still not tremendously improve my
response time? I see that all the records are dragged into my process even
though I have set MaxRows property on the client side cursor. Even with
this, I see that traversing thru a recordset of about 20000 records takes
about 2-3 minutes. I am using Access 2000 as my database. I hope I have been
clear!
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Hi
> Without knowing exact details of your systems it may be hard to tell why
> but...using SELECT * may mean you are returning unnecessary data back to
> your client. The quality of the client system may effect the response
> times. If you are continually using find then it could mean that you need
> to order the recordset better! I would expect MoveNext to be quicker than
> find. Do you always movefirst before the find, in which case it could be
> slower.
> John
>
> "Madz" <noemail@.none.net> wrote in message
> news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Once all the recordset has been retrieved locally I would also expect moving
through the recordset to be quicker, therefore I can't say why in this
instance it is not. If you were using SQL Server you could tell when the
server was being accessed i.e. you were re-fetching the data, but I do know
know how to tell this for Access. The only suggestion I can put forward is
to check that you have a suitable amount of free space on the system, make
sure that your disc is not fragmented and use perfmon to check that you
don't have any resource bottlenecks.
John
"Madz" <noemail@.none.net> wrote in message
news:%23Vn%23Li7YGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Hi John,
> I am no longer using Find method. Let me explain :-)...
> I had 2 connections open to two different databases. One of them
> required serial access to data while the other needed random. I was
> initially using the Find method in both the cases but was well advised
> against it because I read in forums that recordset method of updating data
> is ill served against using command object.
> The database that needed random access to data has now been modified to
> use command object thru'out [and hence, I have no issues with this] while
> the one requiring serial access to data uses client side cursor for
> obvious reasons. Now my question :-)...
> Why does using client side cursor still not tremendously improve my
> response time? I see that all the records are dragged into my process even
> though I have set MaxRows property on the client side cursor. Even with
> this, I see that traversing thru a recordset of about 20000 records takes
> about 2-3 minutes. I am using Access 2000 as my database. I hope I have
> been clear!
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
>