asp questions before i begin project

[Jimmy]

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

 

[Mike Brind]

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 procInsertComments
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,Comments) VALUES
@EmailAddress,
@Comments
GO

 

[Mike Brind]

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 procInsertComments
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,Comments) VALUES
@EmailAddress,
@Comments
GO

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

Pseudo code:

==Stored Proc==
Create Procedure procInsertComments
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,Comments) 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.procInsertComments EmailAddress, Comments
con.Close : Set con = Nothing

 

[Bob Barrows [MVP]]

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

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

 

[Jimmy]

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)

 

[Aaron Bertrand [SQL Server MVP]]

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]]

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

 

[Mike Brind]

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

Thanks Bob.

 

[Jimmy]

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]]

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

 

[Jimmy]

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=SQLOLEDB;" & _
"Data Source=10.10.131.193;" & _
"Initial Catalog=testdb;" & _
"Network=DBMSSOCN;" & _
"User Id=sa;" & _
"Password=password"

Set objCon = CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

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?

 

[Bob Barrows [MVP]]

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.

 

[Jimmy]

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

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.

 

[Bob Barrows [MVP]]

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.

 

[Mike Brind]

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

"Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
Catalog=testdb;Integrated Security=SSPI"

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

"Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=testdb;Trusted_Connection=yes;"

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

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=SQLOLEDB;" & _
"Data Source=10.10.131.193;" & _
"Initial Catalog=testdb;" & _
"Network=DBMSSOCN;" & _
"User Id=sa;" & _
"Password=password"

Set objCon = CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

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?

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

 

[Bob Barrows [MVP]]

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

Have we seen confirmation that he is using Express?

"Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
Catalog=testdb;Integrated Security=SSPI"

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

I haven't seen anything touting better performance. I've seen
recommendations against using it if not using SQL 2005 features such as
MARS.

 

[Mike Brind]

Have we seen confirmation that he is using Express?

No, but since in his OP he said that using the full version is not an
option, and then mentioned the "desktop" version, I think it's a fair
assumption that he is unlikely to have a full blown copy of SQL Server
2005 lying around to play with and has installed Express. He could, of
course, have downloaded the 180-day trial of the full version and be
using that, but why he would do so is beyond me. Nevertheless, you
have a point.

I haven't seen anything touting better performance. I've seen
recommendations against using it if not using SQL 2005 features such as
MARS.

I haven't seen these recommendations. Time for me to google, unless
you have any relevant bookmarks handy....

 

[Jimmy]

thanks everyone. it is indeed 2005 express.
heres what i have learned... so by default sql is lietening on port 1433 i
believe?
FROM the server where sql express is running i can telnet to port 1433. but
i can NOT even telnet to this port from any other machine on the network,
including the webserver which is why my ASP page cant connect.
is there some setting somewhere in sql that prevents network connections by
default?

if no one knows ill go to a sql group i guess

 

[Bob Barrows [MVP]]

thanks everyone. it is indeed 2005 express.
heres what i have learned... so by default sql is lietening on port
1433 i believe?

No. From what I've read, this is not turned on by default. Again, I
don't know how to turn it on in Express (I know how to turn it on using
SMSS in SQL 2005 Enterprise, but that's not relevant to Express).

 

[Bob Barrows [MVP]]

Does this help? it shows how to turn on TCP/IP:
http://www.aspfaq.com/sql2005/show.asp?id=3