asp questions before i begin project

**Mike Brind** · Sep 7 '06, 04:25 PM

Re: asp questions before i begin project

Jimmy wrote:

hi everyone. building a website for a client and have a few questions
related to best practices, what i should use etc...
>
the site will use a database to store info entered by users. each visit will
allow the user to input no more than 2 fields: email address, and a comment
field. the site has the potential to become pretty busy so my first question
is, what should i use on the backend? a dedicated SQL server isnt an option
so im looking at either an Access database, or the desktop edition of SQL
installed on the web server.
>
second question (and this might help decide which DB to use) is: what are
the proper ways to open the databse so that multiple writes can be done at
the same time in the case where more than 1 user are entering info at the
same time? im referring to LockType, CursorType etc... (im not entirely sure
of the workings of these things so if someone could give me a quick opinion,
and point me in the right direction i can take it from there)
>
thank you in advance

SQL Server 2005 Express Edition running on the web server is preferable
to Access - especially if there are a lot of Write operations.

The best way to wirte to the database, whether Access or SQL Server is
to create a stored procedure (saved query in Access). Take the input
from the submitted form, validate it and then pass the values as
parameters to the proc.

Pseudo code:

==Stored Proc==

Create Procedure procInsertComme nts
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments
GO

**Mike Brind** · Sep 7 '06, 04:35 PM

Re: asp questions before i begin project

Mike Brind wrote:

Jimmy wrote:

hi everyone. building a website for a client and have a few questions
related to best practices, what i should use etc...

the site will use a database to store info entered by users. each visit will
allow the user to input no more than 2 fields: email address, and a comment
field. the site has the potential to become pretty busy so my first question
is, what should i use on the backend? a dedicated SQL server isnt an option
so im looking at either an Access database, or the desktop edition of SQL
installed on the web server.

second question (and this might help decide which DB to use) is: what are
the proper ways to open the databse so that multiple writes can be done at
the same time in the case where more than 1 user are entering info at the
same time? im referring to LockType, CursorType etc... (im not entirely sure
of the workings of these things so if someone could give me a quick opinion,
and point me in the right direction i can take it from there)

thank you in advance

>
SQL Server 2005 Express Edition running on the web server is preferable
to Access - especially if there are a lot of Write operations.
>
The best way to wirte to the database, whether Access or SQL Server is
to create a stored procedure (saved query in Access). Take the input
from the submitted form, validate it and then pass the values as
parameters to the proc.
>
Pseudo code:
>
==Stored Proc==
>
Create Procedure procInsertComme nts
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments
GO

Damn - clicked wrong button.... Disregard erroneous code above

Pseudo code:

==Stored Proc==
Create Procedure procInsertComme nts
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments
END
GO

Then, when you have validated the form values, open the databse
connection, perform the insert and close immediately eg:

EmailAddress = validated form value
Comments = validated form value

strCon = connection string
con.open strCon
con.procInsertC omments EmailAddress, Comments
con.Close : Set con = Nothing

--
Mike Brind

**Bob Barrows [MVP]** · Sep 7 '06, 04:55 PM

Re: asp questions before i begin project

Mike Brind wrote:

INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments

I know this is air code, but the list of values should be parenthesized
INSERT INTO table (EmailAddress,C omments) VALUES (
@EmailAddress,
@Comments)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

**Jimmy** · Sep 7 '06, 06:25 PM

Re: asp questions before i begin project

perfect, thank you.

could you help me out with the best way to actually connect to the sql
database?
in other words, what would the code look like in the connection string?

would it have to be a DSN? or can SQL take dsn-less connections?
(ive heard dsn-less is actually better because the server doesnt have to dig
through the registry to resolve the dsn)

"Mike Brind" <paxtonend@hotm ail.comwrote in message
news:1157647378 .941962.239630@ m79g2000cwm.goo glegroups.com.. .

>
Mike Brind wrote:

>Jimmy wrote:

hi everyone. building a website for a client and have a few questions
related to best practices, what i should use etc...
>
the site will use a database to store info entered by users. each visit
will
allow the user to input no more than 2 fields: email address, and a
comment
field. the site has the potential to become pretty busy so my first
question
is, what should i use on the backend? a dedicated SQL server isnt an
option
so im looking at either an Access database, or the desktop edition of
SQL
installed on the web server.
>
second question (and this might help decide which DB to use) is: what
are
the proper ways to open the databse so that multiple writes can be done
at
the same time in the case where more than 1 user are entering info at
the
same time? im referring to LockType, CursorType etc... (im not entirely
sure
of the workings of these things so if someone could give me a quick
opinion,
and point me in the right direction i can take it from there)
>
thank you in advance

>>
>SQL Server 2005 Express Edition running on the web server is preferable
>to Access - especially if there are a lot of Write operations.
>>
>The best way to wirte to the database, whether Access or SQL Server is
>to create a stored procedure (saved query in Access). Take the input
>from the submitted form, validate it and then pass the values as
>parameters to the proc.
>>
>Pseudo code:
>>
>==Stored Proc==
>>
>Create Procedure procInsertComme nts
> @EmailAddress nvarchar(50),
> @Comments ntext
>AS
>BEGIN
>SET NOCOUNT ON
>INSERT INTO table (EmailAddress,C omments) VALUES
>@EmailAddres s,
>@Comments
>GO

>
Damn - clicked wrong button.... Disregard erroneous code above
>
Pseudo code:
>
==Stored Proc==
Create Procedure procInsertComme nts
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments
END
GO
>
Then, when you have validated the form values, open the databse
connection, perform the insert and close immediately eg:
>
EmailAddress = validated form value
Comments = validated form value
>
strCon = connection string
con.open strCon
con.procInsertC omments EmailAddress, Comments
con.Close : Set con = Nothing
>
--
Mike Brind
>

**Aaron Bertrand [SQL Server MVP]** · Sep 7 '06, 06:35 PM

Re: asp questions before i begin project

in other words, what would the code look like in the connection string?

>
would it have to be a DSN? or can SQL take dsn-less connections?

Yes, the latter is preferred.

http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html

**Bob Barrows [MVP]** · Sep 7 '06, 06:35 PM

Re: asp questions before i begin project

Jimmy wrote:

perfect, thank you.
>
could you help me out with the best way to actually connect to the sql
database?
in other words, what would the code look like in the connection
string?

http://www.aspfaq.com/show.asp?id=2126

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

**Mike Brind** · Sep 7 '06, 06:45 PM

Re: asp questions before i begin project

Bob Barrows [MVP] wrote:

Mike Brind wrote:
>

INSERT INTO table (EmailAddress,C omments) VALUES
@EmailAddress,
@Comments

>
I know this is air code, but the list of values should be parenthesized
INSERT INTO table (EmailAddress,C omments) VALUES (
@EmailAddress,
@Comments)
>

Thanks Bob.

--
Mike Brind

**Aaron Bertrand [SQL Server MVP]** · Sep 7 '06, 07:55 PM

Re: asp questions before i begin project

http://databases.aspfaq.com/database/using-stored-procedures.html

"Jimmy" <j@j.jwrote in message
news:uoeIHQr0GH A.1588@TK2MSFTN GP02.phx.gbl...

thank you both....
>
ok, last one for now....
>
ive never used stored procedures. does anyone have simple example or a
good site?
>
>
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
message news:eSaL80q0GH A.1256@TK2MSFTN GP02.phx.gbl...

>>in other words, what would the code look like in the connection string?
>>>
>>would it have to be a DSN? or can SQL take dsn-less connections?

>>
>Yes, the latter is preferred.
>>
>http://databases.aspfaq.com/database...look-like.html
>>

>
>

**Jimmy** · Sep 7 '06, 08:05 PM

Re: asp questions before i begin project

thank you both....

ok, last one for now....

ive never used stored procedures. does anyone have simple example or a good
site?

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:eSaL80q0GH A.1256@TK2MSFTN GP02.phx.gbl...

>in other words, what would the code look like in the connection string?
>>
>would it have to be a DSN? or can SQL take dsn-less connections?

>
Yes, the latter is preferred.
>

http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html

>

**Jimmy** · Sep 7 '06, 08:05 PM

Re: asp questions before i begin project

thanks. not off to a good start just trying to get SQL up and running.

have it installed, created a single databse with a single table with a
single entry.
getting error:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied."

code is like this:

Dim strConn, strSQL, objCon, objRS

strConn = "Provider=SQLOL EDB;" & _
"Data Source=10.10.13 1.193;" & _
"Initial Catalog=testdb; " & _
"Network=DBMSSO CN;" & _
"User Id=sa;" & _
"Password=passw ord"

Set objCon = CreateObject("A DODB.Connection ")
Set objRS = Server.CreateOb ject("ADODB.Rec ordset")

objCon.Open strConn

strSQL = "SELECT * FROM TABLE1"
objRS.Open strSQL, objCon

Response.Write objRS("NAME")

objRS.Close
Set objRS = Nothing
objCon.Close
Set objCon = Nothing

any ideas?

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:evILQlr0GH A.1588@TK2MSFTN GP02.phx.gbl...

http://databases.aspfaq.com/database/using-stored-procedures.html

>
>
>
>
"Jimmy" <j@j.jwrote in message
news:uoeIHQr0GH A.1588@TK2MSFTN GP02.phx.gbl...

>thank you both....
>>
>ok, last one for now....
>>
>ive never used stored procedures. does anyone have simple example or a
>good site?
>>
>>
>"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
>message news:eSaL80q0GH A.1256@TK2MSFTN GP02.phx.gbl...

>>>in other words, what would the code look like in the connection string?
>>>>
>>>would it have to be a DSN? or can SQL take dsn-less connections?
>>>
>>Yes, the latter is preferred.
>>>
>>http://databases.aspfaq.com/database...look-like.html
>>>

>>
>>

>
>

**Bob Barrows [MVP]** · Sep 7 '06, 08:15 PM

Re: asp questions before i begin project

Jimmy wrote:

thanks. not off to a good start just trying to get SQL up and running.
>
have it installed, created a single databse with a single table with a
single entry.
getting error:
>
"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied."
>

Is it SQL 2005? Have you enabled TCP/IP? If you are using SQL 2005
Express, I'm not sure how to do it so you'll have to post to a SQL
Server group.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

**Jimmy** · Sep 7 '06, 08:15 PM

Re: asp questions before i begin project

ive enabled it everywhere i can see that it has to be enabled....

far as you can tell the code is ok though? this way i know where to focus

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomwrote in message
news:Odnxxtr0GH A.1588@TK2MSFTN GP02.phx.gbl...

Jimmy wrote:

>thanks. not off to a good start just trying to get SQL up and running.
>>
>have it installed, created a single databse with a single table with a
>single entry.
>getting error:
>>
>"Microsoft OLE DB Provider for SQL Server (0x80004005)
>[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>access denied."
>>

>
Is it SQL 2005? Have you enabled TCP/IP? If you are using SQL 2005
Express, I'm not sure how to do it so you'll have to post to a SQL
Server group.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>

**Mike Brind** · Sep 7 '06, 08:35 PM

Re: asp questions before i begin project

This is the connection string I use for SQL Server 2005 Express:

"Provider=SQLOL EDB;Data Source=.\SQLEXP RESS;Initial
Catalog=testdb; Integrated Security=SSPI"

Or, if you want to use the SQL Native Client (apparently offers better
performance):

"Provider=SQLNC LI;Server=.\SQL EXPRESS;Databas e=testdb;Truste d_Connection=ye s;"

Create a user with INSERT, DELETE, UPDATE, READ and CONNECT permissions
on the db. Then run the script and see if it works.

--
Mike Brind

Jimmy wrote:

thanks. not off to a good start just trying to get SQL up and running.
>
have it installed, created a single databse with a single table with a
single entry.
getting error:
>
"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied."
>
code is like this:
>
Dim strConn, strSQL, objCon, objRS
>
strConn = "Provider=SQLOL EDB;" & _
"Data Source=10.10.13 1.193;" & _
"Initial Catalog=testdb; " & _
"Network=DBMSSO CN;" & _
"User Id=sa;" & _
"Password=passw ord"
>
Set objCon = CreateObject("A DODB.Connection ")
Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
>
objCon.Open strConn
>
strSQL = "SELECT * FROM TABLE1"
objRS.Open strSQL, objCon
>
Response.Write objRS("NAME")
>
objRS.Close
Set objRS = Nothing
objCon.Close
Set objCon = Nothing
>
>
>
any ideas?
>
>
>
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:evILQlr0GH A.1588@TK2MSFTN GP02.phx.gbl...

http://databases.aspfaq.com/database/using-stored-procedures.html

"Jimmy" <j@j.jwrote in message
news:uoeIHQr0GH A.1588@TK2MSFTN GP02.phx.gbl...

thank you both....
>
ok, last one for now....
>
ive never used stored procedures. does anyone have simple example or a
good site?
>
>
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
message news:eSaL80q0GH A.1256@TK2MSFTN GP02.phx.gbl...
>>in other words, what would the code look like in the connection string?
>>>
>>would it have to be a DSN? or can SQL take dsn-less connections?
>>
>Yes, the latter is preferred.
>>
>http://databases.aspfaq.com/database...look-like.html
>>
>
>

**Bob Barrows [MVP]** · Sep 7 '06, 08:35 PM

Re: asp questions before i begin project

Jimmy wrote:

ive enabled it everywhere i can see that it has to be enabled....
>

:-) That tells us nothing.

far as you can tell the code is ok though?

Yes, that's why I didn't mention anything about the code* and focussed
on the database server configuration. In SQL 2005, the Network Libraries
(TCP/IP, named pipes) have to be explicitly enabled. Again, if you are
using Express, I don't know how to enable TCP/IP so you need to post to
a SQL Server group, unless someone who's used it jumps in here ...

* I should have mentioned this but it has nothing to do with your
problem: don't use the sa account in your applications. Create a SQL
Login with limited privileges and use that in your applications. If a
hacker gets in as sa, he can wreak havoc, not only to your database, but
to your machine and network if you have not locked it down.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.