Best practices for SQL data access

Hi,

i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)

now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.

careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

have a class which will return connection to your page or business object and then u can use that connection to do db related stuff.

some thing in between that you create a sqlcommand and pass it to a class which will take care of connections and execute you request.

what i am worried about more is the connections to database and the connection pooling n sharing stuff. i dont have any idea how they works.

please help me in this regard

thanks

Have you looked at the Data Access Application Block?

http://msdn2.microsoft.com/en-us/library/aa480458.aspx

|||

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

|||

aakbar:

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

Yes, follow those suggestions there...

|||

Outside of that, if was using ADO, I'd create a Business Logic class for each of the tables in my database using this class to generate all of the business logic which in turn would then call the ADO function within the DAL which would be your Data Application Blocks.

Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best Practices - SQL Transactions

I have a web app (ASP) that does all updates, inserts by calling
transaction-supported COM+ components (with the transaction started in the
ASP page, i.e. transaction=required) that use ADO to call stored procedures
(that usually involve single tables). If there is any error (missing SP,
parameter value of wrong type, etc.) with the database insert/update, MTS
automatically rolls back everything that was done in the database (and maybe
SQL Server does that with any error in an SP anyway'). As such, when I
write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
So, I have many SPs (that do not return any indication of success, or not)
like
INSERT INTO Table
(ColumnA, ColumnB)
VALUES
(ValueA, ValueB)
WHERE
Some condition
As a matter of best practice, should SQL programmers always enclose INSERTs,
UPDATEs within transactional statements in a production database? Should one
always check for errors with INSERTS or UPDATES? Or, with errors like I
describe (but not with business logic), does SQL Server automatically
rollback everything in an SP? Or, should one just save those statements for
when the SQL script and logic itself takes care of rolling back a database
when a series of updates or inserts are made?
Thanks for any thoughts."Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES?
No,

>Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP?
No, but the client will get an error message and rollback.
Usually, stored procedures should not contain transactional logic. Let the
client take care of it.
David|||Don Miller wrote:
> As a matter of best practice, should SQL programmers always enclose INSERT
s,
> UPDATEs within transactional statements in a production database? Should o
ne
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements fo
r
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
My thoughts are:
1) Transactions are only required if there's more than one DML
statement (or SELECT statement that needs to maintain a lock).
2) Error checking should be done after any statement that can fail.
This includes all DML and DDL statements. Pretty much everything except
SELECTs (though I guess they could technically fail as well...).
Kris|||David wrote:
> Usually, stored procedures should not contain transactional logic. > Let the clie
nt take care of it.
Are you sure? Isn't it better to ensure your stored procedures are
transactionally correct regardless of where they are executed from?
Kris|||To my knowledge, an error is not sufficient for MTS to roll back: if you do
not call ObjectContext.SetAbort explicitely, MTS will think that the whole
operation has been successfull and will commit it; even if there have been
one or multiple errors.
In the same way, if there is an error inside a SP, SQL-Server will not
rollback the transaction automatically for you: you must check for any error
(@.@.error) and call the rollback operation yourself.
Even if the SP is already enrolled in a transaction, you still need to check
for a transaction error (@.@.error) if there is such a possibility inside the
SP and this, even if you have not included a BEGIN TRAN inside it.
To be clear, transactions and errors are not same: the fact that there have
been an error doesn't mean that the transaction will be or need to be
aborted.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
> for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>|||Don Miller (nospam@.nospam.com) writes:
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs, UPDATEs within transactional statements in a production
> database? Should one always check for errors with INSERTS or UPDATES?
> Or, with errors like I describe (but not with business logic), does SQL
> Server automatically rollback everything in an SP? Or, should one just
> save those statements for when the SQL script and logic itself takes
> care of rolling back a database when a series of updates or inserts are
> made?
If it's a single statement, there is no reason to have BEGIN/COMMIT
TRANSACTION around it, since the statement is a transaction in itself.
However, if you procedure performs several INSERT/UPDATE/DELETE statements
there should be a transaction around it. The procedure should not rely on
that the caller has set up a transaction. Sometimes you have a procedure
that you know is only performing part of a game. In this case, it is a
good habit to have this in the beginning:
IF @.@.trancount = 0
BEGIN
RAISERROR ('This procedure must be called with an active transaction',
16, 1)
RETURN 1
END
In SQL 2005, error checking in stored procedures can be handled with
TRY-CATCH. In SQL 2000, you need to check @.@.error, and if you have
started a transaction, you should rollback, since you know that you
were not able to fulfil your contract.
I have two articles on error handling in SQL Server on my web site.
http://www.sommarskog.se/error-handling-II.html gives more suggestions
on implementing error handling.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
The exception is when SET XACT_ABORT ON is active on the connection or proc.
SQL Server will then rollback the transaction and abort the batch when
runtime errors are encountered. However, compile errors are not affected
with XACT_ABORT ON so @.@.ERROR still needs to be checked when you want to
safeguard against all errors.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uUuZ5PNbGHA.1536@.TK2MSFTNGP02.phx.gbl...
> To my knowledge, an error is not sufficient for MTS to roll back: if you
> do not call ObjectContext.SetAbort explicitely, MTS will think that the
> whole operation has been successfull and will commit it; even if there
> have been one or multiple errors.
> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
> Even if the SP is already enrolled in a transaction, you still need to
> check for a transaction error (@.@.error) if there is such a possibility
> inside the SP and this, even if you have not included a BEGIN TRAN inside
> it.
>
> To be clear, transactions and errors are not same: the fact that there
> have been an error doesn't mean that the transaction will be or need to be
> aborted.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>|||<kriskirk@.hotmail.com> wrote in message
news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
> David wrote:
> Are you sure? Isn't it better to ensure your stored procedures are
> transactionally correct regardless of where they are executed from?
>
Ideally yes. Stored procedures should be atomic, consistent, and isolated.
They should typically not be durable because that makes assumptions about
where the procedure fits inside user transactions. But it requires quite a
bit of transaction handling code to make that happen.
Here's an example. A stored procedure should almost never issue a ROLLBACK
except to a savepoint. If it does then it can't be called in the scope of
an existing transaction. That might be OK for administrative stuff that you
know will be run from Management Studio, but for regular database
transactions.
create procedure foo
as
begin
begin transaction foo
begin try
'do work here
commit transaction
end try
begin catch
rollback transaction foo
commit transaction
exec usp_reraise_error
end catch
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4G3seSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> <kriskirk@.hotmail.com> wrote in message
> news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
>
> Ideally yes. Stored procedures should be atomic, consistent, and
> isolated. They should typically not be durable because that makes
> assumptions about where the procedure fits inside user transactions. But
> it requires quite a bit of transaction handling code to make that happen.
> Here's an example. A stored procedure should almost never issue a
> ROLLBACK except to a savepoint. If it does then it can't be called in the
> scope of an existing transaction. That might be OK for administrative
> stuff that you know will be run from Management Studio, but for regular
> database transactions.
>
Oops, here's a correction after morinng coffee.
create procedure foo
as
begin transaction
save transaction proc_scope
begin try
--DO WORK HERE
commit transaction
end try
begin catch
rollback transaction proc_scope
commit transaction
declare @.errormessage nvarchar(4000),
@.errorseverity int
select
@.errormessage = error_message(),
@.errorseverity = error_severity()
raiserror(@.errormessage, @.errorseverity, 1)
end catch
In SQL 2005 you can just cut and paste all this junk around your procedure,
and you don't have to pollute the implementation with a bunch of error
handling noise.
So there is a right way to do transaction handling in a stored proceudre,
and it isn't all that hard, but transacaction handling is basically the
responsibility of the client code.
David|||Thanks to all who have responded, although I'm still not quite sure whether
I should (or need to) revisit about 100 SPs I have (that are called from a
client transaction through COM+ - and yes, the client does the
ObjectContext.SetAbort duties). It does work today with rollbacks as
necessary and expected but I felt lazy by relying on ASP to start the
transaction and have the MTS blackbox take care of the details especially
when dealing with SQL Server. But I guess that's a feature ;)
And thanks to Erland Sommarskog for the very thoughtful piece about error
handling.
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
> I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
INSERTs,
> UPDATEs within transactional statements in a production database? Should
one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>

Best Practice for Windows Authentication?

Hi,
We are changing our classic ASP web application to use Windows
Authentication instead of SQL Server Authentication.
I would like to know the best practice for:
1. IIS and SQL Server are on the same machine and
2.When they are on different machines in the same domain.
I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
Server (this works but is it the best practice?)
For 2. I have read different opinions. Some say create a IUSR_IISMACHINENAME
account on the SQL Server and make sure they have the same password. Other
say create a user on the domain and use that in IIS as the anonymous user
(and give that user the relevant rights on SQL Server)
I would like to know what is considered the best practice for this sort of
authentication.
Thanks in advancewhat version of IIS you running ?, using ASP.NET ?
http://msdn2.microsoft.com/en-us/library/bsz5788z
"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a
> IUSR_IISMACHINENAME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>|||The solution should work with IIS5 and above.
We are not using ASP.NET this is a classic ASP application.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> what version of IIS you running ?, using ASP.NET ?
> http://msdn2.microsoft.com/en-us/library/bsz5788z
> "Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
> news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
Other[vbcol=seagreen]
user[vbcol=seagreen]
of[vbcol=seagreen]
>|||What a coincidence. Same here. I would definitely be interested to know
how to do this best practice also as this is the exact same thing that I'm
currently working on. One slightly different thing here is that we require
individual accounts (so we can track user activity with our sql profiler)
and would believe we would create a windows account on our domain controller
which resides on a different machine than our web (.asp files) and sql
server (also on separate machine) and was wondering if this would be
possible and how to go about doing this. Would it be as straight forward in
changing the connection string in our .asp files specifying windows
authentication? I am not too familiar in how to do this but was thinking of
maybe removing the anonymous account in IIS so that it would force the user
to login with the windows authentication pop up (in the possible situation
if users share a public machine and/or if the machine's operating system is
not windows with a valid corresponding domain windows account on our domain
controller?...which makes me wonder how this would be incorporated into our
connection string in our .asp files? Thanks in advance
"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:eJLVIyruFHA.2312@.TK2MSFTNGP14.phx.gbl...
> The solution should work with IIS5 and above.
> We are not using ASP.NET this is a classic ASP application.
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Other
> user
> of
>|||_-=?/today_-=?/354
"Hugh Mungo" wrote:

> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a IUSR_IISMACHINENA
ME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>
>

Best practice for SQL connections and Asp.Net

Hi.

We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.

Usualy when I do a query against a SQL database, I do something like this:

Function GO_FormatRecordBy(ByVal intRecordByAs Integer)
Dim dbQueryStringAs String
Dim dbCommandAs OleDbCommand
Dim dbQueryResultAs OleDbDataReader

dbQueryString ="SELECT Name FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
dbCommand = New OleDbCommand(dbQueryString, dbConn)

dbConn.Open()

dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
dbQueryResult.Read()

dbConn.Close()

dbCommand = Nothing

Return dbQueryResult("Name")

End Function

Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?

When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.

Any tips on this?

Best reagrds,
Johan Christensson

The thing with DataReaders is that you have to explicitly close them when you have finished with them. The CommandBehaviour.CloseConnection option makes sure that the connection is closed when you close the DataReader.

Since you are only retrieving one value in the example you have shown, you should use ExecuteScalar(), which doesn't need a DataReader. That would be the most efficient way of accomplishing this particular task.

Also, in answer to your question about global connections, forget you ever thought about it. That is a very poor idea. It forces everyone who accesses your app to use the same connection, and as traffic increases, delays occur as requests are queued.

I don't quite understand what you mean when you talk about resolving records from another table, but my instinct is that nested datalists or join queries might be a better way to go.

|||

Thanks for your awnser.

What I mean with "resolving records fro another tabel" is that lets say that I have one table that consists, in this case of a SiteID and a TechnicanID, when the DataList is generated, I invoke a function where the output in the DataList is looked up in another table and resolved as a name. I guess that this is quite stupid, since I guess that it would generate an extra query, in this case two querys for each row in the datalist. Right now I have a datalist/asp page that generated about 400 connections to the database, and because of this, the firewall blocks the webserver.

I guess that this would better be done using some kind of SQL statement, but I'm not sure on how. Lets say that I have the following SQL string:

dbQueryString ="SELECT SiteID, TechnicanID FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"

And I want the values from SiteID and TechnicanID to be the result from a query in another table, how do I do this?

Best reagrds,
Johan Christensson

|||

I think this will work for you:

dbQueryString = "Select tblSites.SiteName, tblTechnicians.TechnicianName from tblRegistrators inner join tblSites on tblRegistrators.SiteID = tblSites.SiteID inner join tblTechnicians on tblRegistrators.TechnicianID = tblTechnicians.TechnicianID where tblRegistrators.ID = '"&intRecordBy&"'"

you may want to look up some information on Joins (Inner and Outer primarily) they make life much simpler, and more complicated at the same time. Trying to sort out queries with upwards of 20 joins is one of the reasons I have so much grey hair

Hope this helps

-madrak

|||

Madrak has given you a solution using Joins in your SQL as I suggested earlier, but another approach could be to select all from the SiteID and TechnicianID tables into separate DataSets on one connection. Once the dataset has been populated, you can close the connection and work with it in a disconnected fashion, looping though it to your heart's content.

You may well have trouble getting to grips with Joins to start with, so I suggest that you make use of the Query Designer in SQL Management Studio (Express) to write the query for you. Simply add the 3 tables to the designer, then drag foreign keys onto primary keys to create relationships eg SiteID in tblRegistrators would be a foreign key and should be dragged to SiteID (primary key) in what I presume might be called tblSites. As soon as you do that, you will see that the textual part of the query builds the default inner join into itself.

But in essence, your instinct is correct. Hammering a database repeatedly in a loop is poor practice - especially if you aren't closing DataReaders correctly

|||

After some googeling I figured out that I should do some kind of nested statment, but the anwser from Madrak actualy was in a format that I understod it. :) Not only does it work, is fast as lightning. It took som trial and error in teh Query Designer before I got hold of it. The way I have done this previusly, always was very slow, so I have some tweeking to do i guess on some other webpages.

So thanks a milion.

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Thanks again.

Best regards,
Johan Christensson

|||

JohanCh:

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Oh, without a doubt I would use Joins in my SQL. I'm a firm believer in making as few requests of the database as possible. The Join approach requires just one request. DataSets require 3. You might think 3 is nothing compared to the 35,000 you were making (), but I look at it as 300% more than I need. And it's a lot of code less too.

Best method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
RonaldIf your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
--
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
Ronald
If your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
Thanks
Did you restarted the service ?
HTH, jens Suessmeyer.
|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnect ion)
Source File: F:\MyCommerceSite\JokePoint\BusinessObjects\Catalo g.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObjects\Catalo g.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls\Departmen tsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated
|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
----
--
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
ThanksDid you restarted the service ?
HTH, jens Suessmeyer.|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnection)
Source File: F:\MyCommerceSite\JokePoint\BusinessObje
cts\Catalog.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObje
cts\Catalog.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls
\DepartmentsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
----
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
ThanksDid you restarted the service ?
HTH, jens Suessmeyer.|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnection)
Source File: F:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls\DepartmentsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner needs help installing MSDE

Hi, I am trying to teach myself ASP.NET from the SAMS 24 Hrs book and I am trying to install MSDE for use with the web matrix project.

I think I succesfully installed it the first time I tried but entered a four digit SAPWD password not knowing it needed to be strong. I clicked on the data tab in the web matrix project as instructed to open the database and entered my user and password but it gave me some error message.

Now when I click the data tab it say I need to install MSDE on my computer and links me to www.asp.net.

Then when I try to install MSDE after it has downloaded, it gives me this error message. -- 'Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.'--

I have tried uninstalling and reinstalling everything as well as everything else I can think of.

I would be forever grateful if someone could help me out with this.

Many Thanks

Mark
E-Mail: poolstar_uk@.hotmail.comTry this link. It might help.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B829386

Goodluck|||Many Thanks for the advice but I am still having no luck with it.

Any other ideas? I'm not sure my pc will live to see many more days!|||Are you using the Web Matrix server or The IIS ?|||I appreciate your replies, thanks.

I am using the web matrix server.

I have tried installing IIS but it is asking for my windows disk and I cannot find it. I've just moved house and there are boxes and bags everywhere.

Do you think I could use IIS if I could find the disk? Or do you have any ideas on using the web matrix project?|||Follow this tutorial. It's from this website.

http://www.asp.net/webmatrix/guidedtour/Appendices/installMsde.aspx

Perfect example and exact step-by-step of what you need to do. Also, please post what your error logs state if this still doesn't work. You can specify where your logs dump to when you install MSDE from the command line.

Hope this helps. Good luck.|||Thanks everyone for your help, problem solved.

Many Thanks|||Hi poolstar, actually I have the same problem as yours, can u tell me how u solved the problem, thanks a lot man. can u email me this through this email jie_jeep@.yahoo.com|||I am having the same problem but the tutorial did not help.

How do you specify where the log dumps to?

I am using an XP machine networked to another XP machine for internet purposes. My machine is the slave and does not have its own modem. I normally turn off the connection unless I want to be hooked to the internet.

The instructions I had read that time said to install with sapwd=matrixsa instancename=matrixsa but did not add securitymode=sql so I tried the install without it (with the lan connection active). MSDE did install but then had no red x or green arrow in the white circle on the icon in the system tray. When I clicked on the icon, it was looking for the master machine and there were no possible selections in the dropdown box.

I uninstalled, restarted and tried it again with the securitymode and began to get the error again saying that setup failed to configure the server. I have tried three more times, restarting each time, and consistently get this error. All of the successive times, the lan connection has been off.

Any suggestions? Could this be caused by the lack of a modem in this machine and/or having the lan connection active?

Thanks,
Beth

Beginner looking for help

Hi All,

I'm not very good at SQL but I want to finish my ASP project and have run into a little problem. I have a variable that I formatted represent a nice comma delimited character list with all "words" in single quotes ('A','B','C'). I was going to pass this variable to a SELECT statement -

SELECT col FROM table WHERE item IN (<my variable>)

Am I doing this right?

Thanks!

BI don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.|||Originally posted by cvandemaele
I don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.

Thanks. I learned the dynamic SQL way last night shortly after posing this.

I appreciate your resonse!