p2p.wrox.com: Archives - access

access_asp thread: Insert Into error

Message #1 by "Larry Rosenzweig" <rosenzl@o...> on Wed, 28 Nov 2001 19:37:03

Can anyone tell me what's wrong with the Inert Into? I keep getting the 

following error:   Syntax error in Insert Into statement



It's pointing to the cn.execute(strsql) statement. I am using M/S Access 

2000 and Visual Interdev.



Please see code below! this code is part of a larger group of code and is 

contained in the global.asa. I assume that you can add a record in the 

global.asa?



Thanks,



Larry





If strcheckbox1 = 1 Then

			

Session("varpassword") = strpassword

Session("varUserID") = struserid

session("varcheckbox1") = strcheckbox1

strsql = "Insert Into tblLogons(" &_

	"userid, " &_

	"logon) " & _				 

	"VALUES( " 

		strsql = strsql & varuserid & ","

	        strsql = strsql & varpassword & ""

		strsql = strsql & ")"

cn.Execute(strsql)				

Response.Write strsql

cn.close

set cn=nothing

set rs=nothing	

Response.Redirect "default.asp"

Message #2 by "Pritesh Badiani" <pritesh_b@t...> on Wed, 28 Nov 2001 20:19:43 -0000

Have you tried the following : -



strsql = "Insert Into tblLogons(userid, logon) VALUES ("'"& varuserid

&"'", "'" & varpassword & "'");"



Response.Write strsql





Please if this doesn't work don't execute the strsql just see what the

Response.Write function writes.  



Please Advise 



Thanks





-----Original Message-----

From: Larry Rosenzweig [mailto:rosenzl@o...] 

Sent: 28 November 2001 19:37

To: Access ASP

Subject: [access_asp] Insert Into error



Can anyone tell me what's wrong with the Inert Into? I keep getting the 

following error:   Syntax error in Insert Into statement



It's pointing to the cn.execute(strsql) statement. I am using M/S Access



2000 and Visual Interdev.



Please see code below! this code is part of a larger group of code and

is 

contained in the global.asa. I assume that you can add a record in the 

global.asa?



Thanks,



Larry





If strcheckbox1 = 1 Then

			

Session("varpassword") = strpassword

Session("varUserID") = struserid

session("varcheckbox1") = strcheckbox1

strsql = "Insert Into tblLogons(" &_

	"userid, " &_

	"logon) " & _				 

	"VALUES( " 

		strsql = strsql & varuserid & ","

	        strsql = strsql & varpassword & ""

		strsql = strsql & ")"

cn.Execute(strsql)				

Response.Write strsql

cn.close

set cn=nothing

set rs=nothing	

Response.Redirect "default.asp"		




$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks

Message #3 by lrosenzweig <rosenzl@o...> on Wed, 28 Nov 2001 15:36:39 -0500

It did not work. The Response.write shows nothing because it is in the

Global.asa file. In any event it just did the response.redirect

And went to that page. Why is it so complicated to store a record with 2

fields?



Thanks,



Larry





-----Original Message-----

From: Pritesh Badiani [mailto:pritesh_b@t...]

Sent: Wednesday, November 28, 2001 3:20 PM

To: Access ASP

Subject: [access_asp] RE: Insert Into error



Have you tried the following : -



strsql = "Insert Into tblLogons(userid, logon) VALUES ("'"& varuserid

&"'", "'" & varpassword & "'");"



Response.Write strsql





Please if this doesn't work don't execute the strsql just see what the

Response.Write function writes.



Please Advise



Thanks





-----Original Message-----

From: Larry Rosenzweig [mailto:rosenzl@o...]

Sent: 28 November 2001 19:37

To: Access ASP

Subject: [access_asp] Insert Into error



Can anyone tell me what's wrong with the Inert Into? I keep getting the

following error:   Syntax error in Insert Into statement



It's pointing to the cn.execute(strsql) statement. I am using M/S Access



2000 and Visual Interdev.



Please see code below! this code is part of a larger group of code and

is

contained in the global.asa. I assume that you can add a record in the

global.asa?



Thanks,



Larry





If strcheckbox1 = 1 Then



Session("varpassword") = strpassword

Session("varUserID") = struserid

session("varcheckbox1") = strcheckbox1

strsql = "Insert Into tblLogons(" &_

        "userid, " &_

        "logon) " & _

        "VALUES( "

                strsql = strsql & varuserid & ","

                strsql = strsql & varpassword & ""

                strsql = strsql & ")"

cn.Execute(strsql)

Response.Write strsql

cn.close

set cn=nothing

set rs=nothing

Response.Redirect "default.asp"




$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks










Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks

Message #4 by "Pritesh Badiani" <pritesh_b@t...> on Wed, 28 Nov 2001 20:58:38 -0000

No Ideas mate



I have this that works :



subtime=NOW



MyCols="[Name],[Compname],[Phone],[email],[Comments],[IPAddress],[Posted

]"



MyVals="'"&name&"',"

MyVals=MyVals & "'"&compname&"',"

MyVals=MyVals & "'"&phone&"',"

MyVals=MyVals & "'"&email&"',"

MyVals=MyVals & "'"&comments&"',"

MyVals=MyVals & "'"&Request.ServerVariables("REMOTE_ADDR")&"',"

MyVals=MyVals & "'"&subtime&"'"



'Response.Write ("The information you entered is :" + "<BR>")

'Response.Write (phone + email)





MySql = "INSERT INTO Visitors ("&MyCols&") VALUES ("&MyVals&")"



set conn = Server.CreateObject("ADODB.Connection")

conn.open "guest"

conn.execute (MYSQL)

conn.close

-----Original Message-----

From: lrosenzweig [mailto:rosenzl@o...] 

Sent: 28 November 2001 20:37

To: Access ASP

Subject: [access_asp] RE: Insert Into error



It did not work. The Response.write shows nothing because it is in the

Global.asa file. In any event it just did the response.redirect

And went to that page. Why is it so complicated to store a record with 2

fields?



Thanks,



Larry





-----Original Message-----

From: Pritesh Badiani [mailto:pritesh_b@t...]

Sent: Wednesday, November 28, 2001 3:20 PM

To: Access ASP

Subject: [access_asp] RE: Insert Into error



Have you tried the following : -



strsql = "Insert Into tblLogons(userid, logon) VALUES ("'"& varuserid

&"'", "'" & varpassword & "'");"



Response.Write strsql





Please if this doesn't work don't execute the strsql just see what the

Response.Write function writes.



Please Advise



Thanks





-----Original Message-----

From: Larry Rosenzweig [mailto:rosenzl@o...]

Sent: 28 November 2001 19:37

To: Access ASP

Subject: [access_asp] Insert Into error



Can anyone tell me what's wrong with the Inert Into? I keep getting the

following error:   Syntax error in Insert Into statement



It's pointing to the cn.execute(strsql) statement. I am using M/S Access



2000 and Visual Interdev.



Please see code below! this code is part of a larger group of code and

is

contained in the global.asa. I assume that you can add a record in the

global.asa?



Thanks,



Larry





If strcheckbox1 = 1 Then



Session("varpassword") = strpassword

Session("varUserID") = struserid

session("varcheckbox1") = strcheckbox1

strsql = "Insert Into tblLogons(" &_

        "userid, " &_

        "logon) " & _

        "VALUES( "

                strsql = strsql & varuserid & ","

                strsql = strsql & varpassword & ""

                strsql = strsql & ")"

cn.Execute(strsql)

Response.Write strsql

cn.close

set cn=nothing

set rs=nothing

Response.Redirect "default.asp"




$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid

=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks

Message #5 by lrosenzweig <rosenzl@o...> on Wed, 28 Nov 2001 17:35:04 -0500

Can someone please help? I am using the Global.asa file for authentication

of userid and password. It works fine everywhere except where I check to see

if the user checked the checkbox. If the user did and the userid and

password are not in the database, then it uses the BOLD code below and in

particular the statement cn.Execute(strsql), is causing the problem. I am

attempting to add the user to my table (tbllogons). The table has 2 fields

to columns to store the data. One is userid and the other is logon, which is

the password. The error message is:



* Error Type:

Microsoft JET Database Engine (0x80004005)

Field 'tbllogons.UserID' cannot be a zero-length string.

/projcal/global.asa, line 104

* Browser Type:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

* Page:

POST 62 bytes to /projcal/default.asp

* POST Data:

txtuserid=john&txtpassword=doe&txtcheckbox1=1&btnSubmit=Submit

Thank you very much for help with this.



Larry





<SCRIPT LANGUAGE=VBScript RUNAT=Server>



'You can add special event handlers in this file that will get run

automatically when

'special Active Server Pages events occur. To create these handlers, just

create a

'subroutine with a name from the list below that corresponds to the event

you want to

'use. For example, to create an event handler for Session_OnStart, you would

put the

'following code into this file (without the comments):



'Sub Session_OnStart

'**Put your code here **

'End Sub



'EventName              Description

'Session_OnStart        Runs the first time a user runs any page in your

application

'Session_OnEnd          Runs when a user's session times out or quits your

application

'Application_OnStart    Runs once when the first page of your application is

run for the first time by any user

'Application_OnEnd      Runs once when the web server shuts down



</SCRIPT>





<SCRIPT LANGUAGE=VBScript RUNAT=Server>



' try to validate the user, see comments in pgAccountAccess.htm

Sub Session_OnStart

	Dim cn				' ado connection object

	Dim rs				' ado recordset object

	Dim strSQL			' sql query string

	Dim struserid	' name of user(from logon2.asp)

	Dim strPassword		' password (from logon2.asp)

	Dim strcheckbox1    ' new user checks box

	' set session timeout to 10 minutes

	Session.Timeout = 10



	' get the login info the user entered in logon2.asp

	struserid = Request.Form("txtuserid")

	strPassword = Request.Form("txtPassword")



	strcheckbox1 = Request.Form("txtcheckbox1")

	If request.Form("txtcheckbox1") = 1 then

	   strcheckbox1 = 1 ' checked

	else

	   strcheckbox1 = 0 ' not checked

	end if

	' make sure they did not leave the fields blank

	If struserid = "" And strPassword = "" Then

		' no login information at all, abandon the

		' session and send to the login page

		Session.Abandon

		Response.Redirect "logon3.htm"

	Else

		' see if user entered valid login information



		' first, create the connection and recordset



		Set cn = Server.CreateObject("ADODB.Connection")

		Set rs = Server.CreateObject("ADODB.Recordset")



		' build the query string using info collected from the

		' login page (pgAccountAccess.htm)

		strSQL = "SELECT userid, logon FROM tbllogons WHERE " & _

			"(UserID = '" & struserid & "') AND " & _

			"(Logon = '" & strPassword & "')"



		' Open the connection, the connection string, username and password

		' are stored in the Application object which was created by the

		' Data Environment connection wizard

		cn.Open Application("maxxschedule_ConnectionString"), _

			Application("maxxschedule_RuntimeUserName"), _

			Application("maxxschedule_RuntimePassword")



		' open the recordset

		rs.Open strSQL, cn



		' see if found a recordset, if we did, the user entered

		' valid information in the login page

		If Not rs.EOF Then

			' login successful

			' save variables that can be accessed by other pages

			Session("varpassword") = rs("Logon")

			Session("varUserID") = rs("UserID")

			session("varcheckbox1") = strcheckbox1

			' clean up the ado objects

			Set cn = Nothing

			Set rs = Nothing



			' display the success page

			Response.Redirect "default.asp"

		Else

			' login failed

			' clean up the ado objects









		If strcheckbox1 = 1 Then

			Session("varpassword") = strpassword

			Session("varUserID") = struserid

			session("varcheckbox1") = strcheckbox1

			strsql = "Insert Into tblLogons (userid, Logon) Values ('" & varuserid &

"', '" & varpassword & "')"



		cn.Execute(strsql)

			Response.Write strsql

			cn.close

			set cn=nothing

			set rs=nothing

			Response.Redirect "default.asp"



		else

			Set cn = Nothing

			Set rs = Nothing



			' abandon the session and display the

			' login failed page

			Session.Abandon

			Response.Redirect "pgerror2.htm"

		End If

		End If

	End If

End Sub

</SCRIPT>

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart

	'==Visual InterDev Generated - startspan==

	'--Project Data Connection

		Application("maxxschedule_ConnectionString") 

"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data

Source=F:\lprcal\data\MaxxSchedule.mdb;Mode=Share Deny None;Extended

Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry

Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet

OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet

OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet

OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet

OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica

Repair=False;Jet OLEDB:SFP=False;User Id=Admin;PASSWORD=;"

		Application("maxxschedule_ConnectionTimeout") = 15

		Application("maxxschedule_CommandTimeout") = 30

		Application("maxxschedule_CursorLocation") = 3

		Application("maxxschedule_RuntimeUserName") = "Admin"

		Application("maxxschedule_RuntimePassword") = ""

	'-- Project Data Environment

		'Set DE = Server.CreateObject("DERuntime.DERuntime")

		'Application("DE") = DE.Load(Server.MapPath("Global.ASA"),

"_private/DataEnvironment/DataEnvironment.asa")

	'==Visual InterDev Generated - endspan==

End Sub

</SCRIPT>



-----Original Message-----

From: Pritesh Badiani [mailto:pritesh_b@t...]

Sent: Wednesday, November 28, 2001 3:59 PM

To: Access ASP

Subject: [access_asp] RE: Insert Into error



No Ideas mate



I have this that works :



subtime=NOW



MyCols="[Name],[Compname],[Phone],[email],[Comments],[IPAddress],[Posted

]"



MyVals="'"&name&"',"

MyVals=MyVals & "'"&compname&"',"

MyVals=MyVals & "'"&phone&"',"

MyVals=MyVals & "'"&email&"',"

MyVals=MyVals & "'"&comments&"',"

MyVals=MyVals & "'"&Request.ServerVariables("REMOTE_ADDR")&"',"

MyVals=MyVals & "'"&subtime&"'"



'Response.Write ("The information you entered is :" + "<BR>")

'Response.Write (phone + email)





MySql = "INSERT INTO Visitors ("&MyCols&") VALUES ("&MyVals&")"



set conn = Server.CreateObject("ADODB.Connection")

conn.open "guest"

conn.execute (MYSQL)

conn.close

-----Original Message-----

From: lrosenzweig [mailto:rosenzl@o...]

Sent: 28 November 2001 20:37

To: Access ASP

Subject: [access_asp] RE: Insert Into error



It did not work. The Response.write shows nothing because it is in the

Global.asa file. In any event it just did the response.redirect

And went to that page. Why is it so complicated to store a record with 2

fields?



Thanks,



Larry





-----Original Message-----

From: Pritesh Badiani [mailto:pritesh_b@t...]

Sent: Wednesday, November 28, 2001 3:20 PM

To: Access ASP

Subject: [access_asp] RE: Insert Into error



Have you tried the following : -



strsql = "Insert Into tblLogons(userid, logon) VALUES ("'"& varuserid

&"'", "'" & varpassword & "'");"



Response.Write strsql





Please if this doesn't work don't execute the strsql just see what the

Response.Write function writes.



Please Advise



Thanks





-----Original Message-----

From: Larry Rosenzweig [mailto:rosenzl@o...]

Sent: 28 November 2001 19:37

To: Access ASP

Subject: [access_asp] Insert Into error



Can anyone tell me what's wrong with the Inert Into? I keep getting the

following error:   Syntax error in Insert Into statement



It's pointing to the cn.execute(strsql) statement. I am using M/S Access



2000 and Visual Interdev.



Please see code below! this code is part of a larger group of code and

is

contained in the global.asa. I assume that you can add a record in the

global.asa?



Thanks,



Larry





If strcheckbox1 = 1 Then



Session("varpassword") = strpassword

Session("varUserID") = struserid

session("varcheckbox1") = strcheckbox1

strsql = "Insert Into tblLogons(" &_

        "userid, " &_

        "logon) " & _

        "VALUES( "

                strsql = strsql & varuserid & ","

                strsql = strsql & varpassword & ""

                strsql = strsql & ")"

cn.Execute(strsql)

Response.Write strsql

cn.close

set cn=nothing

set rs=nothing

Response.Redirect "default.asp"




$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid

=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&catego

ryid=rn_ebooks










Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks

Message #6 by "Ken Schaefer" <ken@a...> on Thu, 29 Nov 2001 23:42:35 +1100

The reason the problem is occuring is because you have an SQL statement that

reads like this:



: strsql = "Insert Into tblLogons (userid, Logon) Values ('" & varuserid &

: "', '" & varpassword & "')"



But you assigned the user's username and password to variables called

strUserID and strPassword

The variables varuserid and varpassword are blank.



I know I've said this before, but I'll say it again - do not handle the

authentication in your global.asa - please post your form to another .asp

page and handle your authentication there. You can then troubleshoot your

problems. You could use Option Explicit to catch this type of problem. You

could Response.Write(strSQL) to see your actual SQL statement.



Cheers

Ken





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "lrosenzweig" <rosenzl@o...>

Subject: [access_asp] RE: Insert Into error





: Can someone please help? I am using the Global.asa file for authentication

: of userid and password. It works fine everywhere except where I check to

see

: if the user checked the checkbox. If the user did and the userid and

: password are not in the database, then it uses the BOLD code below and in

: particular the statement cn.Execute(strsql), is causing the problem. I am

: attempting to add the user to my table (tbllogons). The table has 2 fields

: to columns to store the data. One is userid and the other is logon, which

is

: the password. The error message is:



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Return to Index

P2P Archives