Error in sql open statement

Hi,
I am new to sql server. I am trying to open a asp page by connecting to a
sql server database. The sql statement is coming correct. However I am
getting the following error:
ADODB.Connection (0x800A0E79)
Operation is not allowed when the object is open.
The error is happening to the following line of the code
conn.Open strSQL
Any help is appreciated here. Thanks.
CODE:
<!-- #include file="adovbs.inc" -->
<%
set conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcde;Initial
Catalog=manpowerweb;Data Source = DOJHLNBCC-003;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096"
Conn.Open sConnString
Set cmd.ActiveConnection = Conn
dim strSQL
UserID = request.Form("UserID")
Passwd = request.Form("password")
FirstName = request.Form("FirstName")
LastName = request.Form("LastName")
AgencyName = request.Form("AgencyName")
EmailAddress = request.Form("emailAddress")
PhoneNumber = Request.Form("PhoneNumber")
strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
" " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
strSQL = "INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
ApplicantFirstName, ApplicantLastName, "
strSQL = strSQL & "ApplicantEmailAddress, ApplicantAgencyText,
ApplicantPhoneNumber, "
strSQL = strSQL & "ApplicantSysCreateDate, ApplicantSysModDate) "
strSQL = strSQL & "VALUES ('" & UserID & "', '" & passwd & "', '"
strSQL = strSQL & FirstName & "', '" & LastName & "', '" & EmailAddress &
"', '"
strSQL = strSQL & AgencyName & "', '" & PhoneNumber & "', '"
strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
response.write strSQL & vbCRLF
'Response.end
Response.Write "This is before the execution of the sql statement" & "<br>"
conn.Open strSQL
Response.Write "This is after the execution of the sql statement" & "<br>"
%>> conn.Open strSQL
The Open method of an ADO Connection object expects a connection string, not
a SQL statement. You can use the Execute method to execute a SQL statement:
conn.Open strConnectionString
conn.Execute strSQL
conn.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B901C4EE-D0CF-4C75-8CA8-823DE4BFF517@.microsoft.com...
> Hi,
> I am new to sql server. I am trying to open a asp page by connecting to a
> sql server database. The sql statement is coming correct. However I am
> getting the following error:
> ADODB.Connection (0x800A0E79)
> Operation is not allowed when the object is open.
> The error is happening to the following line of the code
> conn.Open strSQL
> Any help is appreciated here. Thanks.
> CODE:
> <!-- #include file="adovbs.inc" -->
> <%
> set conn = Server.CreateObject("ADODB.Connection")
> set cmd = Server.CreateObject("ADODB.Command")
>
> sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcde;Initial
> Catalog=manpowerweb;Data Source = DOJHLNBCC-003;Use Procedure for
> Prepare=1;Auto Translate=True;Packet Size=4096"
>
> Conn.Open sConnString
> Set cmd.ActiveConnection = Conn
> dim strSQL
>
> UserID = request.Form("UserID")
> Passwd = request.Form("password")
> FirstName = request.Form("FirstName")
> LastName = request.Form("LastName")
> AgencyName = request.Form("AgencyName")
> EmailAddress = request.Form("emailAddress")
> PhoneNumber = Request.Form("PhoneNumber")
>
>
> strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
> " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
> strSQL = "INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
> ApplicantFirstName, ApplicantLastName, "
> strSQL = strSQL & "ApplicantEmailAddress, ApplicantAgencyText,
> ApplicantPhoneNumber, "
> strSQL = strSQL & "ApplicantSysCreateDate, ApplicantSysModDate) "
> strSQL = strSQL & "VALUES ('" & UserID & "', '" & passwd & "', '"
> strSQL = strSQL & FirstName & "', '" & LastName & "', '" & EmailAddress &
> "', '"
> strSQL = strSQL & AgencyName & "', '" & PhoneNumber & "', '"
> strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
> response.write strSQL & vbCRLF
> 'Response.end
> Response.Write "This is before the execution of the sql statement" &
> "<br>"
> conn.Open strSQL
> Response.Write "This is after the execution of the sql statement" & "<br>"
> %>
>