Cannot execute sql statement from asp after connecting to sql serv

Hi,
I am trying to test a simple asp page with backend as sql server 2000.
However, I cannot get to run the code due to error. I tried various ways but
still not resolved.
Any help is appreciated. Thanks.

CODE:




<%

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 = testserver"


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.Write "This is before the execution of the sql statement" & "
"


Conn.Execute strsql,adExecuteNoRecords

Response.Write "This is after the execution of the sql statement" & "
"

response.write "

User Created

Re: Cannot execute sql statement from asp after connecting to sql serv

What is the output from this line:
response.write strSQL & vbCRLF

Give that please.

Ray at work

"Jack" wrote in message
news:CE7A53E3-9F66-4167-8359-91124BB1A95A@microsoft.com...
> Hi,
> I am trying to test a simple asp page with backend as sql server 2000.
> However, I cannot get to run the code due to error. I tried various ways
> but
> still not resolved.
> Any help is appreciated. Thanks.
>
> CODE:
>
>
>
>
> <%
>
> 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 = testserver"
>
>
> 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.Write "This is before the execution of the sql statement" &
> "
"
>
>
> Conn.Execute strsql,adExecuteNoRecords
>
> Response.Write "This is after the execution of the sql statement" & "
"
>
> response.write "

User Created

Re: Cannot execute sql statement from asp after connecting to sql serv

Change
strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
" " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"

To
strcurrentdate = year(date) & "/" & month(date) & "/" & day(date) &
" " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now())

And
strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"

To
strSQL = strSQL & "'" & strcurrentdate & "', '" & strcurrentdate & "')"


(Single ' around the 2 dates)




"Jack" wrote in message
news:CE7A53E3-9F66-4167-8359-91124BB1A95A@microsoft.com...
> Hi,
> I am trying to test a simple asp page with backend as sql server 2000.
> However, I cannot get to run the code due to error. I tried various ways
> but
> still not resolved.
> Any help is appreciated. Thanks.
>
> CODE:
>
>
>
>
> <%
>
> 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 = testserver"
>
>
> 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.Write "This is before the execution of the sql statement" &
> "
"
>
>
> Conn.Execute strsql,adExecuteNoRecords
>
> Response.Write "This is after the execution of the sql statement" & "
"
>
> response.write "

User Created

Re: Cannot execute sql statement from asp after connecting to sql

Thanks for your reply Ray. Here is the sql statement that is being
generated.Regards.

INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
ApplicantFirstName, ApplicantLastName, ApplicantEmailAddress,
ApplicantAgencyText, ApplicantPhoneNumber, ApplicantSysCreateDate,
ApplicantSysModDate) VALUES ('tester', 'abcd00', 'John ', 'Doe', 'x@x.com',
'TestAgency', '111-1111', ''2005/9/13 11:19:34', '2005/9/13 11:19:34')

"Ray Costanzo [MVP]" wrote:

> What is the output from this line:
> response.write strSQL & vbCRLF
>
> Give that please.
>
> Ray at work
>
> "Jack" wrote in message
> news:CE7A53E3-9F66-4167-8359-91124BB1A95A@microsoft.com...
> > Hi,
> > I am trying to test a simple asp page with backend as sql server 2000.
> > However, I cannot get to run the code due to error. I tried various ways
> > but
> > still not resolved.
> > Any help is appreciated. Thanks.
> >
> > CODE:
> >
> >
> >
> >
> > <%
> >
> > 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 = testserver"
> >
> >
> > 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.Write "This is before the execution of the sql statement" &
> > "
"
> >
> >
> > Conn.Execute strsql,adExecuteNoRecords
> >
> > Response.Write "This is after the execution of the sql statement" & "
"
> >
> > response.write "

User Created

Re: Cannot execute sql statement from asp after connecting to sql

Do you see the extra ' character right before 2005?

Also, I suggest that you read this about dates and SQL Server:
http://www.aspfaq.com/show.asp?id=2023
http://www.aspfaq.com/show.asp?id=2313

Ray at work

"Jack" wrote in message
news:68D8B320-EC4D-4BC0-BE51-D92EEAFEE8C4@microsoft.com...
> Thanks for your reply Ray. Here is the sql statement that is being
> generated.Regards.
>
> INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
> ApplicantFirstName, ApplicantLastName, ApplicantEmailAddress,
> ApplicantAgencyText, ApplicantPhoneNumber, ApplicantSysCreateDate,
> ApplicantSysModDate) VALUES ('tester', 'abcd00', 'John ', 'Doe',
> 'x@x.com',
> 'TestAgency', '111-1111', ''2005/9/13 11:19:34', '2005/9/13 11:19:34')
>

Re: Cannot execute sql statement from asp after connecting to sql

Thanks a lot Jonathan and Ray for pointing out the mistake and the help on
this. I really appreciate your help. Best regards.

"Jonathan Grant" wrote:

> Change
> strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
> " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
>
> To
> strcurrentdate = year(date) & "/" & month(date) & "/" & day(date) &
> " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now())
>
> And
> strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
>
> To
> strSQL = strSQL & "'" & strcurrentdate & "', '" & strcurrentdate & "')"
>
>
> (Single ' around the 2 dates)
>
>
>
>
> "Jack" wrote in message
> news:CE7A53E3-9F66-4167-8359-91124BB1A95A@microsoft.com...
> > Hi,
> > I am trying to test a simple asp page with backend as sql server 2000.
> > However, I cannot get to run the code due to error. I tried various ways
> > but
> > still not resolved.
> > Any help is appreciated. Thanks.
> >
> > CODE:
> >
> >
> >
> >
> > <%
> >
> > 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 = testserver"
> >
> >
> > 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.Write "This is before the execution of the sql statement" &
> > "
"
> >
> >
> > Conn.Execute strsql,adExecuteNoRecords
> >
> > Response.Write "This is after the execution of the sql statement" & "
"
> >
> > response.write "

User Created

Re: Cannot execute sql statement from asp after connecting to sql

In addition to the other answers; Why are you going throught so much for the
date? Why don't you just use getdate in the insert,, or default the values
in the table?

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 & "getdate(), getdate())"

Bob Lehmann

"Jack" wrote in message
news:75F32F80-BDF0-46B9-ADC7-0429E7D466BE@microsoft.com...
> Thanks a lot Jonathan and Ray for pointing out the mistake and the help on
> this. I really appreciate your help. Best regards.
>
> "Jonathan Grant" wrote:
>
> > Change
> > strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date)
&
> > " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
> >
> > To
> > strcurrentdate = year(date) & "/" & month(date) & "/" & day(date) &
> > " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now())
> >
> > And
> > strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
> >
> > To
> > strSQL = strSQL & "'" & strcurrentdate & "', '" & strcurrentdate & "')"
> >
> >
> > (Single ' around the 2 dates)
> >
> >
> >
> >
> > "Jack" wrote in message
> > news:CE7A53E3-9F66-4167-8359-91124BB1A95A@microsoft.com...
> > > Hi,
> > > I am trying to test a simple asp page with backend as sql server 2000.
> > > However, I cannot get to run the code due to error. I tried various
ways
> > > but
> > > still not resolved.
> > > Any help is appreciated. Thanks.
> > >
> > > CODE:
> > >
> > >
> > >
> > >
> > > <%
> > >
> > > 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 = testserver"
> > >
> > >
> > > 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.Write "This is before the execution of the sql statement" &
> > > "
"
> > >
> > >
> > > Conn.Execute strsql,adExecuteNoRecords
> > >
> > > Response.Write "This is after the execution of the sql statement" &
"
"
> > >
> > > response.write "

User Created