Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
Regards
For the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>
|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
RegardsFor the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

referencing inserted and deleted tables with sp_executeSql

Hi everyone. Thanks in advance to anyone who might be able to shed some light on this situation.

I have a trigger in which the following SQL code exists.

SET @.tempInserted = N'SET @.dummy = (SELECT '+@.cftColumnName+' FROM INSERTED)'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output

When the trigger executes I receive the following error message...

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'INSERTED'.

My question is, is it possible to in some way reference the INSERTED and DELETED tables using sp_executeSQL?I was able to replicate your problem:
-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET

create table #Tmp(f1 int, f2 char(1))
go
create trigger TmpTrigger on Tmp
FOR DELETE, INSERT, UPDATE
AS
BEGIN
declare @.tempInserted nvarchar(100)
, @.cftColumnName nvarchar(100)
, @.tempAddress nvarchar(100)
set @.cftColumnName = 'f2'
SET @.tempInserted = N'SELECT @.dummy = ' + @.cftColumnName + ' FROM inserted'
SET @.tempInserted = N'select * From #Tmp'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output
select @.tempAddress
END
go
insert into #Tmp values(2,'B')
insert into Tmp values(1,'A')
select * From Tmp
go
drop table #Tmp
drop table Tmp
go

The only thing I can figure out is that since inserted and deleted are special temp tables they are not available to the new process created during the execution of sp_executeSQL.|||Thanks Paul. I ended up taking a different approach that essentially enabled me to achieve what I was attempting to do. Instead of directly referencing the INSERTED and DELETED tables I first create another set of temp tables to which I copy all the records from INSERTED and DELETED. I can then make a reference to these temporary tables during the execution of sp_executeSQL.

Not perfect but it works =).|||A workable solution is better than nothing working! You can always go back and change your code once everything runs end to end.