Problems with using 'Insert Into' in FP 2002

G

Guest

I am attempting to create a user log so I can see who and when a user is
accessing a particular page in my FP 2002 generated web. To start with, I am
merely trying to post TeamLogin and Teampassword to an access database
'userstats' table 'Results' by doing a custom query using SQL in the
Frontpage DRW such as:

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
'::TeamPassword::')

When I attempt this, I receive an error

"Database Results Error
The operation failed. If this continues, please contact your server
administrator."

However, when I change the values to constants by changing the SQL code

FROM

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
'::TeamPassword::')

TO

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('mylogin',
'mypassword')

IT WORKS .....using the constants, and writes to the file.

Therefore there appears to be a problem in passing the data from the 'forms'
page (login.htm) to the 'add to data' page (add.asp)

I am using the same variables in the forms entry as in the database, and am
posting them using 'Send to other' to add.asp

I am able to display the variables passed from the login.htm on the add.asp
program, so they are getting there

I first go this idea from Commonly used SQL code at
http://www.frontpagehowto.com/sqlcode.htm, but their example does not work
either. Tried contacting them, but well, their contact email bounced back on
me invalid...

Any suggestions on what I might be doing wrong? Their example was using FP
2000 and I am using FP2002.

IF I get past this hurdle, I intend to add other variables to my file
including date and Page and IP, but until I can do the simple, how can I
expect to do anything....

and of course I expect to need help on the system variables also <grin>

Thanks
 
S

Stefan B Rusynko

How/where are you getting the 2 form fields?

Before the insert make sure you are using a

TeamLogin=Request.Form("TeamLogin")
TeamPassword=Request.Form("TeamPassword")

Then check for empty strings before the insert to prevent errors

IF TeamLogin<>"" AND TeamLogin<>"" THEN
INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('TeamLogin', 'TeamPassword')
END IF
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am attempting to create a user log so I can see who and when a user is
| accessing a particular page in my FP 2002 generated web. To start with, I am
| merely trying to post TeamLogin and Teampassword to an access database
| 'userstats' table 'Results' by doing a custom query using SQL in the
| Frontpage DRW such as:
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| '::TeamPassword::')
|
| When I attempt this, I receive an error
|
| "Database Results Error
| The operation failed. If this continues, please contact your server
| administrator."
|
| However, when I change the values to constants by changing the SQL code
|
| FROM
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| '::TeamPassword::')
|
| TO
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('mylogin',
| 'mypassword')
|
| IT WORKS .....using the constants, and writes to the file.
|
| Therefore there appears to be a problem in passing the data from the 'forms'
| page (login.htm) to the 'add to data' page (add.asp)
|
| I am using the same variables in the forms entry as in the database, and am
| posting them using 'Send to other' to add.asp
|
| I am able to display the variables passed from the login.htm on the add.asp
| program, so they are getting there
|
| I first go this idea from Commonly used SQL code at
| http://www.frontpagehowto.com/sqlcode.htm, but their example does not work
| either. Tried contacting them, but well, their contact email bounced back on
| me invalid...
|
| Any suggestions on what I might be doing wrong? Their example was using FP
| 2000 and I am using FP2002.
|
| IF I get past this hurdle, I intend to add other variables to my file
| including date and Page and IP, but until I can do the simple, how can I
| expect to do anything....
|
| and of course I expect to need help on the system variables also <grin>
|
| Thanks
 
G

Guest

Stefan B Rusynko said:
How/where are you getting the 2 form fields?
Click to expand...

---------------------------------------------------------------------------------

They come from a form (login.htm) which uses 'post' using 'Send to other' to
the program (add.asp) where I am trying to write to the database

There appears to be a problem in passing the data from the 'forms' page
(login.htm) to the 'add to data' page (add.asp) EXCEPT I can display the data
on the add.asp page without a problem. Mayhaps, the problem is in the syntax
I am using :

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
'::TeamPassword::')

where '::TeamLogin::' , etc. is supposed to pickup the value of the passed
variable TeamLogin, etc.

WHEN I use constants for the values, such as:

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('AnyLogin',
'MyPassword')

THEN add.asp works without a problem, except of course, it does not post the
login.htm variables to the database - only the constants AnyLogin, etc.

---------------------------------------------------------------------------------
Before the insert make sure you are using a

TeamLogin=Request.Form("TeamLogin")
TeamPassword=Request.Form("TeamPassword")
Click to expand...
---------------------------------------------------------------------------------

I added this, but it did not change anything.

ALSO, on add.asp, I am displaying the form input variables, and they show up
fine

---------------------------------------------------------------------------------
Then check for empty strings before the insert to prevent errors

IF TeamLogin<>"" AND TeamLogin<>"" THEN
INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('TeamLogin', 'TeamPassword')
END IF
--
Click to expand...

---------------------------------------------------------------------------------

THE above statement does NOT WORK within the DRW Custom Query

Appreciate any help you can give me

Thanks
 
S

Stefan B Rusynko

Once again if it is coming from a form you must first convert the form value to a usable variable to use in your update query per my
prior post

How are you displaying them?
- obviously w/ something like
<%=Request.Form("TeamLogin")%>
and
<%=Request.Form("TeamPassword")%>

So the insert needs to use the same values if you are not setting them as page variables above

INSERT INTO Results (TeamLogin, TeamPassword) VALUES (' " & Request.Form("TeamLogin") & " , " &
'Request.Form("TeamPassword") & " ')"

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|
|
| "Stefan B Rusynko" wrote:
|
| > How/where are you getting the 2 form fields?
|
| ---------------------------------------------------------------------------------
|
| They come from a form (login.htm) which uses 'post' using 'Send to other' to
| the program (add.asp) where I am trying to write to the database
|
| There appears to be a problem in passing the data from the 'forms' page
| (login.htm) to the 'add to data' page (add.asp) EXCEPT I can display the data
| on the add.asp page without a problem. Mayhaps, the problem is in the syntax
| I am using :
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| '::TeamPassword::')
|
| where '::TeamLogin::' , etc. is supposed to pickup the value of the passed
| variable TeamLogin, etc.
|
| WHEN I use constants for the values, such as:
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('AnyLogin',
| 'MyPassword')
|
| THEN add.asp works without a problem, except of course, it does not post the
| login.htm variables to the database - only the constants AnyLogin, etc.
|
| ---------------------------------------------------------------------------------
| > Before the insert make sure you are using a
| >
| > TeamLogin=Request.Form("TeamLogin")
| > TeamPassword=Request.Form("TeamPassword")
| >
| ---------------------------------------------------------------------------------
|
| I added this, but it did not change anything.
|
| ALSO, on add.asp, I am displaying the form input variables, and they show up
| fine
|
| ---------------------------------------------------------------------------------
|
| > Then check for empty strings before the insert to prevent errors
| >
| > IF TeamLogin<>"" AND TeamLogin<>"" THEN
| > INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('TeamLogin', 'TeamPassword')
| > END IF
| > --
|
| ---------------------------------------------------------------------------------
|
| THE above statement does NOT WORK within the DRW Custom Query
|
| Appreciate any help you can give me
|
| Thanks
 
G

Guest

Stefan B Rusynko said:
Once again if it is coming from a form you must first convert the form value to a usable variable to use in your update query per my
prior post

How are you displaying them?
- obviously w/ something like
<%=Request.Form("TeamLogin")%>
and
<%=Request.Form("TeamPassword")%
Click to expand...
------------------------------------------------------------------------------------------

I wasn't using your above code, but I inserted that code and it does also
display the variables' values on the add.asp page, as does
<%=Request("TeamLogin")%> within a textbox on the add.asp page, which is what
I have been using

------------------------------------------------------------------------------------------
So the insert needs to use the same values if you are not setting them as page variables above

INSERT INTO Results (TeamLogin, TeamPassword) VALUES (' " & Request.Form("TeamLogin") & " , " &
'Request.Form("TeamPassword") & " ')"
Click to expand...
------------------------------------------------------------------------------------------

Your above 'Insert into' code will not work - it does not pass the 'verify
query' for SQL code

The following is the code that I have been told SHOULD work, but doesn't,
although it passes the 'verify query' test

INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
'::TeamPassword::')
 
S

Stefan B Rusynko

As I pointed out originally the clean way is to get your variables at the top of the page and then you can use them anywhere in the
page
Below is base on updating an Access database but should be changable by you to SQL db if required

<%
'Get them and clean them of any ' to prevent errors
TeamLogin=Trim(Replace(Request.Form("TeamLogin"),"'", "''"))
TeamPassword=Trim(Replace(Request.Form("TeamPassword"),"'", "''"))
' Then display them
%>

<p>The Team Login is: <%=TeamLogin%> and the Team password is: <%=TeamPassword%>

<%
'Update them only if we have data to prevent errors
IF TeamLogin<>"" AND TeamPassword<>""THEN
adCmdText = 1
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open AddYourConnectionNameHere
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
strFields= "TeamLogin ,TeamPassword"
strValues= " ' " & TeamLogin & " ', " & TeamPassword & " ' "
strSql= "INSERT INTO Results (" & strFields & ") VALUES (" & strValues & " ')"
objCmd.CommandText = strSql
objCmd.CommandType = adCmdText
objCmd.Execute
END IF
%>

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|
|
| "Stefan B Rusynko" wrote:
|
| > Once again if it is coming from a form you must first convert the form value to a usable variable to use in your update query
per my
| > prior post
| >
| > How are you displaying them?
| > - obviously w/ something like
| > <%=Request.Form("TeamLogin")%>
| > and
| > <%=Request.Form("TeamPassword")%>
| ------------------------------------------------------------------------------------------
|
| I wasn't using your above code, but I inserted that code and it does also
| display the variables' values on the add.asp page, as does
| <%=Request("TeamLogin")%> within a textbox on the add.asp page, which is what
| I have been using
|
| ------------------------------------------------------------------------------------------
| >
| > So the insert needs to use the same values if you are not setting them as page variables above
| >
| > INSERT INTO Results (TeamLogin, TeamPassword) VALUES (' " & Request.Form("TeamLogin") & " , " &
| > 'Request.Form("TeamPassword") & " ')"
| >
| ------------------------------------------------------------------------------------------
|
| Your above 'Insert into' code will not work - it does not pass the 'verify
| query' for SQL code
|
| The following is the code that I have been told SHOULD work, but doesn't,
| although it passes the 'verify query' test
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| '::TeamPassword::')
|
|
|
 
G

Guest

Okay, now that we are departing from Frontpage and writing code, I will
probably really show my ignorance by asking what do I put in the
'AddYourConnectionNameHere'

objConn.Open AddYourConnectionNameHere

I am using an access database 'userstats.mdb' in the fpdb directory of my
website

And I have I have tried several things, but keep getting errors using your
code

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified




Stefan B Rusynko said:
As I pointed out originally the clean way is to get your variables at the top of the page and then you can use them anywhere in the
page
Below is base on updating an Access database but should be changable by you to SQL db if required

<%
'Get them and clean them of any ' to prevent errors
TeamLogin=Trim(Replace(Request.Form("TeamLogin"),"'", "''"))
TeamPassword=Trim(Replace(Request.Form("TeamPassword"),"'", "''"))
' Then display them
%>

<p>The Team Login is: <%=TeamLogin%> and the Team password is: <%=TeamPassword%>

<%
'Update them only if we have data to prevent errors
IF TeamLogin<>"" AND TeamPassword<>""THEN
adCmdText = 1
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open AddYourConnectionNameHere
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
strFields= "TeamLogin ,TeamPassword"
strValues= " ' " & TeamLogin & " ', " & TeamPassword & " ' "
strSql= "INSERT INTO Results (" & strFields & ") VALUES (" & strValues & " ')"
objCmd.CommandText = strSql
objCmd.CommandType = adCmdText
objCmd.Execute
END IF
%>

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|
|
| "Stefan B Rusynko" wrote:
|
| > Once again if it is coming from a form you must first convert the form value to a usable variable to use in your update query
per my
| > prior post
| >
| > How are you displaying them?
| > - obviously w/ something like
| > <%=Request.Form("TeamLogin")%>
| > and
| > <%=Request.Form("TeamPassword")%>
| ------------------------------------------------------------------------------------------
|
| I wasn't using your above code, but I inserted that code and it does also
| display the variables' values on the add.asp page, as does
| <%=Request("TeamLogin")%> within a textbox on the add.asp page, which is what
| I have been using
|
| ------------------------------------------------------------------------------------------
| >
| > So the insert needs to use the same values if you are not setting them as page variables above
| >
| > INSERT INTO Results (TeamLogin, TeamPassword) VALUES (' " & Request.Form("TeamLogin") & " , " &
| > 'Request.Form("TeamPassword") & " ')"
| >
| ------------------------------------------------------------------------------------------
|
| Your above 'Insert into' code will not work - it does not pass the 'verify
| query' for SQL code
|
| The following is the code that I have been told SHOULD work, but doesn't,
| although it passes the 'verify query' test
|
| INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| '::TeamPassword::')
|
|
|
Click to expand...
 
S

Stefan B Rusynko

Are you using the Database Results Wizard
- Explain how you are now connecting to the DB to display the 2 values

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| Okay, now that we are departing from Frontpage and writing code, I will
| probably really show my ignorance by asking what do I put in the
| 'AddYourConnectionNameHere'
|
| objConn.Open AddYourConnectionNameHere
|
| I am using an access database 'userstats.mdb' in the fpdb directory of my
| website
|
| And I have I have tried several things, but keep getting errors using your
| code
|
| Microsoft OLE DB Provider for ODBC Drivers error '80004005'
|
| [Microsoft][ODBC Driver Manager] Data source name not found and no default
| driver specified
|
|
|
|
| "Stefan B Rusynko" wrote:
|
| > As I pointed out originally the clean way is to get your variables at the top of the page and then you can use them anywhere in
the
| > page
| > Below is base on updating an Access database but should be changable by you to SQL db if required
| >
| > <%
| > 'Get them and clean them of any ' to prevent errors
| > TeamLogin=Trim(Replace(Request.Form("TeamLogin"),"'", "''"))
| > TeamPassword=Trim(Replace(Request.Form("TeamPassword"),"'", "''"))
| > ' Then display them
| > %>
| >
| > <p>The Team Login is: <%=TeamLogin%> and the Team password is: <%=TeamPassword%>
| >
| > <%
| > 'Update them only if we have data to prevent errors
| > IF TeamLogin<>"" AND TeamPassword<>""THEN
| > adCmdText = 1
| > Set objConn = Server.CreateObject("ADODB.Connection")
| > objConn.Open AddYourConnectionNameHere
| > Set objCmd = Server.CreateObject("ADODB.Command")
| > Set objCmd.ActiveConnection = objConn
| > strFields= "TeamLogin ,TeamPassword"
| > strValues= " ' " & TeamLogin & " ', " & TeamPassword & " ' "
| > strSql= "INSERT INTO Results (" & strFields & ") VALUES (" & strValues & " ')"
| > objCmd.CommandText = strSql
| > objCmd.CommandType = adCmdText
| > objCmd.Execute
| > END IF
| > %>
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > |
| > |
| > | "Stefan B Rusynko" wrote:
| > |
| > | > Once again if it is coming from a form you must first convert the form value to a usable variable to use in your update
query
| > per my
| > | > prior post
| > | >
| > | > How are you displaying them?
| > | > - obviously w/ something like
| > | > <%=Request.Form("TeamLogin")%>
| > | > and
| > | > <%=Request.Form("TeamPassword")%>
| > | ------------------------------------------------------------------------------------------
| > |
| > | I wasn't using your above code, but I inserted that code and it does also
| > | display the variables' values on the add.asp page, as does
| > | <%=Request("TeamLogin")%> within a textbox on the add.asp page, which is what
| > | I have been using
| > |
| > | ------------------------------------------------------------------------------------------
| > | >
| > | > So the insert needs to use the same values if you are not setting them as page variables above
| > | >
| > | > INSERT INTO Results (TeamLogin, TeamPassword) VALUES (' " & Request.Form("TeamLogin") & " , " &
| > | > 'Request.Form("TeamPassword") & " ')"
| > | >
| > | ------------------------------------------------------------------------------------------
| > |
| > | Your above 'Insert into' code will not work - it does not pass the 'verify
| > | query' for SQL code
| > |
| > | The following is the code that I have been told SHOULD work, but doesn't,
| > | although it passes the 'verify query' test
| > |
| > | INSERT INTO Results (TeamLogin, TeamPassword) VALUES ('::TeamLogin::',
| > | '::TeamPassword::')
| > |
| > |
| > |
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Adding Record with INSERT INTO 4
Inserting Video in FP 2003 1
Absolute positioning in FP 2002 3
Import Outlook vCard into FP 2003 2
FP won't let me insert code in code view 7
Graphics and Text using ...FP 2002 4
MS ACCESS Insert statement problem using Visual Basic 2010 0
Insert Multiple Records into a table with one form 3

Top