Dear sir
Your "Professional SQL server development with access
2000" is a very good book  but I'm not a experient
user and I got stuck in reallife practising, I wrote
this personal mail and hope You can help me to settle
it.

I create a stored proc, with 6 option parameters. The
proc will return the recordset if any, all or none of
the parameters supplied. If I used "if" to make sure
what parameters is null then it will come out to
6x6=36 situation, then I used sp_executesql to execute
my built-in dynamic SQL statement.

 In your book on page 371 shows open form by setting
programmatically setting the form recordset property,
so I use an ADOrecordset(rst1) to open this proc and
set the form recordset to it (set frm1.recordset=rst1)
and the message come out that "the  object you entered
is not valid recordset property". I think the problem
is this proc because I used the same technique for
other proc, it worked fine. I write down this proc so
you can help.
AH.., when I execute this proc in query analyzer,
everythings is as expected(It returned record)

The proc is as below
--**************************
use qlkh2000sql
go

ALTER proc PhucVuTimKiem_TrenHopDong_Version4
	@Ma_kh	nvarchar(9)	= null,
	@Ten_kh	nvarchar(42)	=null,
	@Dia_chi	nvarchar(52)	=null,
	@So_Hdong	nvarchar(10)	=null,
	@So_cong_to	nvarchar(10)	=null,
	@Dchi_dungdien nvarchar(52)	=null,
	@TuGia_Coquan	nvarchar(2) =null
with encryption
AS	
Declare @strSQL	nvarchar(50)
Declare @strWhere	nvarchar(2000)
Declare @strExecute	nvarchar(2000)
SELECT @strSQL = 'SELECT * from [hop dong] ' 
select @strWhere=''
if @Tugia_Coquan is not null and @Tugia_Coquan !=''
	SELECT	@strWhere = @strWhere + ' AND Ma_loaikh = "' +
@tugia_coquan +'"'
if @Ma_kh is not null and @Ma_kh !=''
	SELECT	@strWhere = @strWhere + ' AND Ma_kh like "' +
@Ma_kh + '"'
if @So_Hdong is not null and @So_Hdong !=''
	SELECT	@strWhere = @strWhere + ' AND so_hdong like "'
+ @So_Hdong + '"'
if @Dchi_dungdien is not null and @Dchi_dungdien !=''
	SELECT	@strWhere = @strWhere + ' AND Dchi_dungdien
like "' + @Dchi_dungdien + '"'
if @ten_kh is not null and @ten_kh !=''
	SELECT	@strWhere = @strWhere + ' AND Ma_kh in (select
 Ma_kh from [khach hang] where Ten_kh like  "' +
@Ten_kh + '" ) '
if @dia_chi is not null and @dia_chi !=''
	SELECT	@strWhere = @strWhere + ' AND Ma_kh in (select
 Ma_kh from [khach hang] where dia_chi like  "' +
@dia_chi+ '" ) '
if @So_cong_to is not null and @So_cong_to !=''
	SELECT	@strWhere = @strWhere + ' AND so_hdong in
(select so_hdong from congtokh
								join quanlydodem.dbo.[cong to]	c
								on congtokh.ctid= c.ctid
								where c.so_cong_to like "' + @So_cong_to +
'")'
--print @strWhere
	SELECT	@strWhere=Right(@strWhere,len(@strWhere)-4)

if len(@strWhere)>0
	SELECT	@strExecute=@strSQL+ ' Where ' + @strWhere
else 
	SELECT 		@strExecute=@strSQL
--select * from [hop dong] where ma_kh like '%z%'
print 'string return is ' + @strExecute
Exec sp_executesql @strExecute


Thank a lot for your time with me.
I'm looking forward to hearing from you


__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/