web form getting data to SQL Backend

[mcarlyle via AccessMonster.com]

I had a split DB with a front and BE in Access. I am converting the BE to
SQL because of the size and number of users. I have all the conversion done;
however, I have various vendors sending me information via a web page that
works fine on the Access BE but doesn't connect once the BE has been upsized
to SQL. I tried to create a connection to the SQL DB that looked like the
old BE with the same name, table names, and location as the old BE was;
however, when someone enters data on the web page, it errors out instead of
connecting and dropping the data.

Old code for Web page...

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\client
manager\cm4_be.mdb;Jet OLEDB:System Database=D:\client manager\Security.
mdw;User ID=jbiggs;Password=153480;"
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open(ConnectionString)

set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT FirstName FROM Clients WHERE HomePhone = '" & hphone & "'",
Connection

If Not rs.EOF Then
Response.Write("Lead already exists. Please enter a new lead.")
Else
If fname <> "" and lname <> "" Then
rs.close
rs.open "SELECT * FROM [TZ List] WHERE [Area Code] = '" &
areacode & "'", Connection
timezone = rs("Time Zone")
sql = "INSERT INTO Clients ([Time Zones],areacode,LeadType,
customerordersaleID,IP,Source,FirstName,LastName,StreetAddress,City,State,Zip,
HomePhone,WorkPhone,Email,DebtAmount,MonthsBehind,ContactTime,Comment,
CreditorName1) VALUES ('" & timezone & "','" & areacode & "','" & leadtype &
"','" & orderid & "','" & ip & "','" & source & "','" & fname & "','" & lname
& "','" & street & "','" & city & "','" & state & "','" & zip & "','" &
hphone & "','" & wphone & "','" & email & "','" & debtamt & "','" & mosbehind
& "','" & contacttime & "','" & comment & "','" & creditor & "')"
Connection.Execute sql,recaffected
Response.Write("Lead submitted successfully!")

Else
Response.Write("Missing Information! Please complete
source, name, phone, email, debt amount and city/state or zip.")
End If
End If
rs.close
Connection.close


This will dump data into an ACCESS BE but not an Upsized to SQL Access BE

Any ideas why?

 

[Sylvain Lafontaine]

First, if this is for a web page, you should ask to a newsgroup dedicated to
ASP (Classic or .NET) or to ADO or to OLEDB; as ADP has nothing to do with
this. For a description of the connection strings for SQL-Server, see:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

You don't say which version of SQL-Server you have, so I cannot tell you
which one is probably the best. The first thing to do would be obviously to
check that you can connect to the server and spit out some information.

Finally, for a web page, your code looks particularly sensible to SQL
injection attack. I would correct that part if I were you.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I had a split DB with a front and BE in Access. I am converting the BE to
SQL because of the size and number of users. I have all the conversion
done;
however, I have various vendors sending me information via a web page that
works fine on the Access BE but doesn't connect once the BE has been
upsized
to SQL. I tried to create a connection to the SQL DB that looked like the
old BE with the same name, table names, and location as the old BE was;
however, when someone enters data on the web page, it errors out instead
of
connecting and dropping the data.

Old code for Web page...

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\client
manager\cm4_be.mdb;Jet OLEDB:System Database=D:\client manager\Security.
mdw;User ID=jbiggs;Password=153480;"
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open(ConnectionString)

set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT FirstName FROM Clients WHERE HomePhone = '" & hphone &
"'",
Connection

If Not rs.EOF Then
Response.Write("Lead already exists. Please enter a new lead.")
Else
If fname <> "" and lname <> "" Then
rs.close
rs.open "SELECT * FROM [TZ List] WHERE [Area Code] = '" &
areacode & "'", Connection
timezone = rs("Time Zone")
sql = "INSERT INTO Clients ([Time Zones],areacode,LeadType,
customerordersaleID,IP,Source,FirstName,LastName,StreetAddress,City,State,Zip,
HomePhone,WorkPhone,Email,DebtAmount,MonthsBehind,ContactTime,Comment,
CreditorName1) VALUES ('" & timezone & "','" & areacode & "','" & leadtype
&
"','" & orderid & "','" & ip & "','" & source & "','" & fname & "','" &
lname
& "','" & street & "','" & city & "','" & state & "','" & zip & "','" &
hphone & "','" & wphone & "','" & email & "','" & debtamt & "','" &
mosbehind
& "','" & contacttime & "','" & comment & "','" & creditor & "')"
Connection.Execute sql,recaffected
Response.Write("Lead submitted successfully!")

Else
Response.Write("Missing Information! Please complete
source, name, phone, email, debt amount and city/state or zip.")
End If
End If
rs.close
Connection.close


This will dump data into an ACCESS BE but not an Upsized to SQL Access BE

Any ideas why?

 

[mcarlyle via AccessMonster.com]

I created our DB in Access and a few years back split it to a FE/BE Access DB.
Now I am having record issues due to the size of the DB. I already cannot
create as many Primary Key's as my tables need (notes table has over 2M
records so I have to archive records every 30 days). So I split the DB to a
Access FE/SQL 08 BE.

I have the DB all working correctly so that the Access FE talks to the SQL 08
BE in all aspects; however, our old IT guy that is no longer here setup a
webpage that would dump leads into an Access DB that would append to my BE DB.


Now that the BE is in SQL 08 I cannot append directly from the website
anymore which would be ok except that we search the DB for duplicate values
prior to allowing the append.

The webpage is sent directly to vendors and not something that we really
publish; however, I am concearned that you think it may make my DB vunerable.

Ultimately I would like there to be a simple web form with about 10 fields
that a vendor can post to that will append directly to my SQL DB and check
for duplicates prior to the append.

Thanks in advance for your help.

First, if this is for a web page, you should ask to a newsgroup dedicated to
ASP (Classic or .NET) or to ADO or to OLEDB; as ADP has nothing to do with
this. For a description of the connection strings for SQL-Server, see:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

You don't say which version of SQL-Server you have, so I cannot tell you
which one is probably the best. The first thing to do would be obviously to
check that you can connect to the server and spit out some information.

Finally, for a web page, your code looks particularly sensible to SQL
injection attack. I would correct that part if I were you.

I had a split DB with a front and BE in Access. I am converting the BE to
SQL because of the size and number of users. I have all the conversion

[quoted text clipped - 56 lines]

Any ideas why?

 

[mcarlyle via AccessMonster.com]

Also, I have SQL 2008 full version as well as any prior version if needed;
however, we have the DB working on SQL 2008.

I created our DB in Access and a few years back split it to a FE/BE Access DB.
Now I am having record issues due to the size of the DB. I already cannot
create as many Primary Key's as my tables need (notes table has over 2M
records so I have to archive records every 30 days). So I split the DB to a
Access FE/SQL 08 BE.

I have the DB all working correctly so that the Access FE talks to the SQL 08
BE in all aspects; however, our old IT guy that is no longer here setup a
webpage that would dump leads into an Access DB that would append to my BE DB.

Now that the BE is in SQL 08 I cannot append directly from the website
anymore which would be ok except that we search the DB for duplicate values
prior to allowing the append.

The webpage is sent directly to vendors and not something that we really
publish; however, I am concearned that you think it may make my DB vunerable.

Ultimately I would like there to be a simple web form with about 10 fields
that a vendor can post to that will append directly to my SQL DB and check
for duplicates prior to the append.

Thanks in advance for your help.

First, if this is for a web page, you should ask to a newsgroup dedicated to
ASP (Classic or .NET) or to ADO or to OLEDB; as ADP has nothing to do with

[quoted text clipped - 15 lines]

 

[Sylvain Lafontaine]

Also, I have SQL 2008 full version as well as any prior version if needed;
however, we have the DB working on SQL 2008.

This looks suspicious. Action Pack and/or Developer Edition and/or MSDN
Editions shouldn't be used for a production database.

However, as you are using SQL-2008, using the native driver for SQL-2008 is
probably your best option. See the previous references that I gave you.

2M(illion) or 2B(illion)? I don't see why you have any problems with the
primary key's if you have only 2 million rows in your database. However,
using SQL instead of JET as the backend is practically always a good idea.

I suppose that you have made an error in your ADO code. Please see an
appropriate newsgroup such as microsoft.public.data.ado OR
microsoft.public.inetserver.asp.db . This newsgroup is about ADP and has
nothing to do with classic ASP web pages. In the case that you are using
Dot.NET, see an appropriate ADO.NET or ASP.NET newsgroups.

It's not the fact that you are sending your webpage to vendors, it's the
fact that none of your fields are checked for SQL-Injection attack (a pretty
commun way of attacking a web server) and probably for Javascript injection
attack too.

For preventing against SQL-Injection, all numerical fields should be stored
in intermediary numerical variables in order to filter out any crap. All
character fields should make sure that any embedded single quote are
doubled:

timezone = Replace (timezone, "'", "''")

Notice that a numerical field that is enclosed between single quotes in the
SQL statement could also use this second method instead of the first one.
For Javascript injection, you should always use a call to Server.HTMLEncode
for writing any alphanumerical fields that could contain any kind of
javascript crap when you emit the HTML page.

I think that there is option in Access 2007 for that but I never tried it
myself. Any program capable of reading mail could also be able to do that
stuff.

Thanks in advance for your help.

First, if this is for a web page, you should ask to a newsgroup dedicated
to
ASP (Classic or .NET) or to ADO or to OLEDB; as ADP has nothing to do
with

[quoted text clipped - 15 lines]

Any ideas why?

 

[mcarlyle via AccessMonster.com]

First off I do not use MSDN or Action Packs or whatever you are talking about.
.. This is in a pretty large company and we have licensed versions of SQL
2000 - SQL 2008 with SA.

The ADO code was written by my previous IT guy who to append to the old
Access DB (also Licenced). As for why I get the error a 2M records, who
knows, but I have been told that my speed issues and concurrent user issues
would be solved by using a SQL Back end, so I spent a lot of money for a
dedicated SQL server, bought new Licenses for SQL 2008 with SA and began the
migration.

I have used this board for about 5 years to solve problems with my Access DB
and thought it would be helpful to explain it here since almost every other
issue has found it's resolution with the help of this board.

I will post it up on Experts Exchange and see if I can get some help there,
since I am either asking the question wrong or viewed as someone with pirated
software.

Also, I have SQL 2008 full version as well as any prior version if needed;
however, we have the DB working on SQL 2008.

This looks suspicious. Action Pack and/or Developer Edition and/or MSDN
Editions shouldn't be used for a production database.

However, as you are using SQL-2008, using the native driver for SQL-2008 is
probably your best option. See the previous references that I gave you.

I created our DB in Access and a few years back split it to a FE/BE Access [quoted text clipped - 4 lines]
a
Access FE/SQL 08 BE.

2M(illion) or 2B(illion)? I don't see why you have any problems with the
primary key's if you have only 2 million rows in your database. However,
using SQL instead of JET as the backend is practically always a good idea.

I have the DB all working correctly so that the Access FE talks to the SQL
08 [quoted text clipped - 6 lines]
values
prior to allowing the append.

I suppose that you have made an error in your ADO code. Please see an
appropriate newsgroup such as microsoft.public.data.ado OR
microsoft.public.inetserver.asp.db . This newsgroup is about ADP and has
nothing to do with classic ASP web pages. In the case that you are using
Dot.NET, see an appropriate ADO.NET or ASP.NET newsgroups.

It's not the fact that you are sending your webpage to vendors, it's the
fact that none of your fields are checked for SQL-Injection attack (a pretty
commun way of attacking a web server) and probably for Javascript injection
attack too.

For preventing against SQL-Injection, all numerical fields should be stored
in intermediary numerical variables in order to filter out any crap. All
character fields should make sure that any embedded single quote are
doubled:

timezone = Replace (timezone, "'", "''")

Notice that a numerical field that is enclosed between single quotes in the
SQL statement could also use this second method instead of the first one.
For Javascript injection, you should always use a call to Server.HTMLEncode
for writing any alphanumerical fields that could contain any kind of
javascript crap when you emit the HTML page.

I think that there is option in Access 2007 for that but I never tried it
myself. Any program capable of reading mail could also be able to do that
stuff.

Thanks in advance for your help.
[quoted text clipped - 5 lines]

Any ideas why?

 

[Sylvain Lafontaine]

First off I do not use MSDN or Action Packs or whatever you are talking
about.
. This is in a pretty large company and we have licensed versions of SQL
2000 - SQL 2008 with SA.

The ADO code was written by my previous IT guy who to append to the old
Access DB (also Licenced). As for why I get the error a 2M records, who
knows, but I have been told that my speed issues and concurrent user
issues
would be solved by using a SQL Back end, so I spent a lot of money for a
dedicated SQL server, bought new Licenses for SQL 2008 with SA and began
the
migration.

I have used this board for about 5 years to solve problems with my Access
DB
and thought it would be helpful to explain it here since almost every
other
issue has found it's resolution with the help of this board.

I will post it up on Experts Exchange and see if I can get some help
there,
since I am either asking the question wrong or viewed as someone with
pirated
software.

You are not asking the question wrong. In my first answer, I gave you
exactly the answer that you have asked for but you don't seem to have
noticed it at all. Just in case that you might have missed it, here it is
again:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

On these URLs, you will find all the info required to know how to write a
connection string for SQL-Server. As you are using OLEDB and SQL-Server
2008, using the SQL Native Client OLE DB Provider, version 10; is probably
your best bet. Look on the following page for the provider SQLNCLI10:

http://www.connectionstrings.com/?carrier=sqlserver2008

Before sending the INSERT command, you should also take the precaution of
closing the previous recordset that you have opened for getting the
timezone. Of course, if your insert don't work, the first thing to do would
be to first check if you can read the information from the SQL-Server; ie.,
that the recordset rs has been correctly opened.

As for the good money that you have spent on SQL-Server 2008 with QA,
personnally, I would had have tried the Express edition first. If your
previous installation was a JET (MDB) backend, possibly that the Express
edition of SQL-Server 2008 would have been more than sufficient.

 

[mcarlyle via AccessMonster.com]

I did see your first response... I just don't think I know what I am doing on
this.... I did post it on another board as suggested previously. The reason
I didn't use express (although it may be good enough) is that I have over 100
users that will be connecting to this and I will be migrating the FE to CRM
before long. In the meantime I wanted it to work in SQL and everything does
except the web submission.

From the link you sent previously I had made this connection string

ConnectionString = {SQL Server Native Client 10.0}
;Server=SQL;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;

When I use this one it does not connect. Again I know I don't know what I am
doing on this and was kinda hoping for a hand held answer.

the new code looks something like this but still doesn't work.

<font size=+1 color=red><center>
<%

leadtype = request.querystring("leadtype")
orderid = request.querystring("orderid")
ip = request.querystring("ip")
source = request.querystring("source")
fname = request.querystring("fname")
lname = request.querystring("lname")
street = request.querystring("street")
city = request.querystring("city")
state = request.querystring("state")
zip = request.querystring("zip")
hphone = request.querystring("hphone")
wphone = request.querystring("wphone")
email = request.querystring("email")
debtamt = request.querystring("debtamt")
mosbehind = request.querystring("mosbehind")
contacttime = request.querystring("contacttime")
comment = request.querystring("comment")
creditor = request.querystring("creditor")
balance = request.querystring("balance")

Dim areacode
Dim timezone
If Left(hphone,1)= "(" Then areacode=Right(Left(hphone,4),3) Else
areacode=Left(hphone,3)


If hphone <> "" and fname <> "" and lname <> "" and email <> "" and debtamt
<> "" Then


ConnectionString = {SQL Server Native Client 10.0}
;Server=SQL;Database=cm5final;Uid=newlogin;Pwd=newpassword;

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open(ConnectionString)

set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT FirstName FROM Clients WHERE HomePhone = '" & hphone & "'",
Connection

If Not rs.EOF Then
Response.Write("Lead already exists. Please enter a new lead.")
Else
If fname <> "" and lname <> "" Then
rs.close
rs.open "SELECT * FROM [TZ List] WHERE [Area Code] = '" &
areacode & "'", Connection
timezone = rs("Time Zone")
sql = "INSERT INTO Clients ([Time Zones],areacode,LeadType,
customerordersaleID,IP,Source,FirstName,LastName,StreetAddress,City,State,Zip,
HomePhone,WorkPhone,Email,DebtAmount,MonthsBehind,ContactTime,Comment,
CreditorName1) VALUES ('" & timezone & "','" & areacode & "','" & leadtype &
"','" & orderid & "','" & ip & "','" & source & "','" & fname & "','" & lname
& "','" & street & "','" & city & "','" & state & "','" & zip & "','" &
hphone & "','" & wphone & "','" & email & "','" & debtamt & "','" & mosbehind
& "','" & contacttime & "','" & comment & "','" & creditor & "')"
Connection.Execute sql,recaffected
Response.Write("Lead submitted successfully!")

Else
Response.Write("Missing Information! Please complete
source, name, phone, email, debt amount and city/state or zip.")
End If
End If
rs.close
Connection.close


Else
If fname <> "" or hphone <> "" or lname <> "" or email <> "" Then
Response.Write("Required Information Missing! Please complete source,
name, phone, email, debt amount and city/state or zip.")

End If
End If

%>
</center></font>
</head>
<body>
<table align=center>

<tr><td>
<img border="0" src="cclogo.jpg" width="244" height="59">
</td></tr>

<tr>
<td>
<font size=+2>Welcome to Credit Consultants!</font><p>Please enter the
information requested below:
</td>
</tr>
<tr>
<td>
<form method="get" action="<%=request.servervariables("script_name")%>">
<div>
<tr><td colspan="2" class="text">&nbsp;</td></tr>
<td align=left>
Source:
<input type="text" name="source" maxlength="40" size="20" ID="source">
Lead Type:
<input name="leadtype" maxlength="40" size="20" type="text" ID="leadtype"><p>
Order ID:
<input type="text" name="orderID" maxlength="40" size="15" ID="orderID">
IP Address:
<input type="text" name="ip" maxlength="15" size="15" ID="ip"><p>
First Name:
<input type="text" name="fname" maxlength="40" size="20" ID="fname">
Last Name:
<input type="text" name="lname" maxlength="40" size="20" ID="lname"><br>
Street Address:
<input type="text" name="street" maxlength="40" size="40" ID="street"><br>
City:
<input type="text" name="city" maxlength="40" size="20" ID="city">
State:
<input type="text" name="state" maxlength="40" size="10" ID="state">
Zip Code:
<input type="text" name="zip" maxlength="10" size="10" ID="zip"><p>
Home Phone:
<input type="text" name="hphone" maxlength="40" size="15" ID="hphone">
Work Phone:
<input type="text" name="wphone" maxlength="40" size="15" ID="wphone">
Email:
<input type="text" name="email" maxlength="40" size="25" ID="email"><p>
Debt Amount:
<input type="text" name="debtamt" maxlength="40" size="10" ID="debtamt">
Months Behind:
<input type="text" name="mosbehind" maxlength="3" size="5" ID="mosbehind">
Best Contact Time:
<input type="text" name="contacttime" maxlength="40" size="10"
ID="contacttime"><p>
Comment:
<input type="text" name="comment" maxlength="100" size="80" ID="comment"><p>
Creditor Name:
<input type="text" name="creditor" maxlength="40" size="15" ID="creditor">
Balance:
<input type="float" name="balance" maxlength="40" size="15" ID="balance"><p>
</td></tr>



<tr><td colspan="2" class="text"><input type="submit" name="submit"
ID="Submit1"></td>
</tr>

</div>
</form>
</td>
</tr>
</table>

First off I do not use MSDN or Action Packs or whatever you are talking
about.

[quoted text clipped - 21 lines]

pirated
software.

You are not asking the question wrong. In my first answer, I gave you
exactly the answer that you have asked for but you don't seem to have
noticed it at all. Just in case that you might have missed it, here it is
again:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

On these URLs, you will find all the info required to know how to write a
connection string for SQL-Server. As you are using OLEDB and SQL-Server
2008, using the SQL Native Client OLE DB Provider, version 10; is probably
your best bet. Look on the following page for the provider SQLNCLI10:

http://www.connectionstrings.com/?carrier=sqlserver2008

Before sending the INSERT command, you should also take the precaution of
closing the previous recordset that you have opened for getting the
timezone. Of course, if your insert don't work, the first thing to do would
be to first check if you can read the information from the SQL-Server; ie.,
that the recordset rs has been correctly opened.

As for the good money that you have spent on SQL-Server 2008 with QA,
personnally, I would had have tried the Express edition first. If your
previous installation was a JET (MDB) backend, possibly that the Express
edition of SQL-Server 2008 would have been more than sufficient.

 

[Sylvain Lafontaine]

Answers inline.

I did see your first response... I just don't think I know what I am doing
on
this.... I did post it on another board as suggested previously. The
reason
I didn't use express (although it may be good enough) is that I have over
100
users that will be connecting to this and I will be migrating the FE to
CRM
before long. In the meantime I wanted it to work in SQL and everything
does
except the web submission.

From the link you sent previously I had made this connection string

ConnectionString = {SQL Server Native Client 10.0}
;Server=SQL;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;

First, this is an ODBC connection string. For ADO, using OLEDB instead of
ODBC should give you a better performance. In fact, I don't even remember
if you can directly use an ODBC connection without first using the MSDASQL
provider as in intermediate level; so it's important to try using the OLEDB
provider instead of the ODBC provider in this case.

Second, the Server parameter is there to specify on which machine (or
server) the connection should be opened. It's not sufficient to specify the
database - even when it is located on the same local machine. I must warn
you that a lot of thing can go wrong (one of more blocking firewalls, named
instance versus unnamed instance, use of an alias, TCP/IP protocol versus
Named Protocol versus Shared Memory, dynamically allocated ports versus
static ports; LocalHost file, etc., etc.) at this step but as you are
already capable of connecting to the SQL-Server using an ADP project, then
you shouldn't have any problem; unless, of course, that this web server is
not located on the same machine as the machine that you have used to make
your ADP project.

Try replacing the SQL parameter with the name of the server; something like
(on a single line, the other examples are alternate examples without the «
ConnectionString= » but you must write it anyway):

ConnectionString =
"Provider=SQLNCLI10;Server=myServerAddress;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=myServerAddress\SQL2008;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=(local);Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=np:myServerAddress;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=tcp:myServerAddress;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=tcp:myServerAddress,1433;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=tcp:myServerAddress\SQL2008,1433;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

"Provider=SQLNCLI10;Server=127.0.0.1,1433;Database=cm5final;Uid=mynewlogin;Pwd=anewpassword;"

This should be easy because all you have to do is to use the exact same
address as you are using currently for the ADP project but with trying to
add the name of the procotol (tcp:, np before it and the port number
(,1433 in the case of a static port for the default (not a named) instance.

Don't forget to add the name of the instance is this is not the default
instance. Of course, the \SQL2008 in the above examples should be replace
with the real name of the instance that you have used if this is a named
instance and should not be used if this not a named instance. Take a look at
your ADP project to know what you should do here.

Also, I'm not sure but I think that you might try replacing Uid and Pwd with
User Id (with a single blank space in the middle) and Password as in:

"Provider=SQLNCLI10;Server=(local);Database=cm5final;User
Id=mynewlogin;Password=anewpassword;"

Etc., etc.


After that, before calling the Open method, you should add an ON ERROR GOTO
0 statement just before trying to open the connection; so that if the
connection is refused, the web server will stop and display an error
message. Make sure that the web server is configured to display full error
message. It's not necessary but it will make things easier to debug:

on error goto 0
Connection.Open(ConnectionString)

Alternatively, you could check for the state of the Connection object to see
if it's open. If it's closed, there is no need trying to open a recordset
or sending an EXEC command.

Finally, if the web server is not located on the same machine that the
machine that you have used for your ADP project, you must make sure that you
are not blocked by a firewall or some other network configuration on the
SQL-Server. If you are using an Alias for the name of the server, you must
also set up this Alias on the web server (or use the real name of the server
instead).

Finally - and very Important - , you should close the recordset before
sending the Insert command when you are re-using the same connection and not
after it:

rs.open "SELECT * FROM [TZ List] WHERE [Area Code] = '" &
areacode & "'", Connection
timezone = rs("Time Zone")

'
***************************************************************************
' !!!!!!!!!!!!!!!!!!!!!!!! ********** Add this line: **************
!!!!!!!!!!!!!!!!!!!!!
'
***************************************************************************

rs.Close

sql = "INSERT INTO Clients ([Time Zones],areacode,LeadType,
customerordersaleID,IP,Source,FirstName,LastName,StreetAddress,City,State,Zip,
HomePhone,WorkPhone,Email,DebtAmount,MonthsBehind,ContactTime,Comment,
CreditorName1) VALUES ('" & timezone & "','" & areacode & "','" & leadtype
&
"','" & orderid & "','" & ip & "','" & source & "','" & fname & "','" &
lname
& "','" & street & "','" & city & "','" & state & "','" & zip & "','" &
hphone & "','" & wphone & "','" & email & "','" & debtamt & "','" &
mosbehind
& "','" & contacttime & "','" & comment & "','" & creditor & "')"

Connection.Execute sql,recaffected
Response.Write("Lead submitted successfully!")

Else
Response.Write("Missing Information! Please complete
source, name, phone, email, debt amount and city/state or zip.")
End If
End If

'
***************************************************************************
' !!!!!!!!!!!!!!! *********** Remove this line: *************
!!!!!!!!!!!!!!!

'
***************************************************************************

rs.close

Connection.close

Finally, if you still have some problems, then you should ask your IT guy(s)
what you should use as the connection string.