|
 |
access_asp thread: sql that works but won't run when generated by Ultradev
Message #1 by "Gianpiero Colagiacomo" <gp@1...> on Sun, 25 Aug 2002 14:46:44 +0200
|
|
I'm running a Ultradev command behaviour sql script from an asp/vbs page:
UPDATE members, memberships
SET accesslvl = 'member',
authtodate = IIf(authtodate<date(), date() + duration, authtodate +
duration),
netbanxref = varNetbanxRef,
netbanxdate = date()
WHERE username = varUsername AND ref = varMembership
It generates this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
If I run the exact same sql from within Access itself it works fine,
updating the row expected with the correct data. What's happening here? I
realise the 'Too few parameters' error suggests that one or more of the
column names is/are invalid but they obviously are not otherwise the query
wouldn't run within Access (cut and paste the query so it's not my
typing!)... The connection itself is fine as I can query/update tables
(including MEMBERS and MEMBERSHIPS) in the database with other pages in the
same site without fault. Only this script is causing me problems.
What does the 'expected 2' mean? 2 columns that are invalid? Is there
someway to debug this???
The ASP generated by Ultradev looks like this, note I've hardcoded values
for the vars for test purposes:
<%
if("abcd" <> "") then updateAuth__varNetbanxRef = "abcd"
if("0000001" <> "") then updateAuth__varMembership = "0000001"
if("test4" <> "") then updateAuth__varUsername = "test4"
%>
<%
set news = Server.CreateObject("ADODB.Recordset")
news.ActiveConnection = MM_toptipsracing_STRING
news.Source = "SELECT * FROM news"
news.CursorType = 0
news.CursorLocation = 2
news.LockType = 3
news.Open()
news_numRows = 0
%>
<%
set updateAuth = Server.CreateObject("ADODB.Command")
updateAuth.ActiveConnection = MM_toptipsracing_STRING
updateAuth.CommandText = "UPDATE members, memberships SET accesslvl
'member', authtodate = IIf(authtodate<date(), date() + duration,
authtodate + duration), netbanxref = " + Replace(updateAuth__varNetbanxRef,
"'", "''") + ", netbanxdate = date() WHERE username LIKE " +
ReplaceupdateAuth__varUsername, "'", "''") + " AND ref LIKE " +
Replace(updateAuth__varMembership, "'", "''") + ""
updateAuth.CommandType = 1
updateAuth.CommandTimeout = 0
updateAuth.Prepared = true
updateAuth.Execute()
%>
Please help because this is driving me crazy!
This is happening in Ultradev4 with Access2000 on Windows2000.
GP
gp@1...
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 26 Aug 2002 13:17:31 +1000
|
|
Hi,
Can you please print out (using Response.Write()) the literal SQL statement
you are sending to the database? If what is below is the literal statement,
then the problem is being caused because varUserName and varMembership are
variables in ASP (I think), they are not functions in Access.
Instead, when creating your SQL statement in ASP, you need to do something
like:
strSQL = _
"WHERE UserName = '" & varUserName & "' "
so that the literal text is concatenated with the variable.
http://www.adopenstatic.com/faq/80040e10.asp
(Not enough parameters: Scenario 1 explains how this might happen)
To output the literal SQL statement, amend your code to read:
: updateAuth.CommandText = "UPDATE members, memberships SET accesslvl
: 'member', authtodate = IIf(authtodate<date(), date() + duration,
: authtodate + duration), netbanxref = " +
Replace(updateAuth__varNetbanxRef,
: "'", "''") + ", netbanxdate = date() WHERE username LIKE " +
: ReplaceupdateAuth__varUsername, "'", "''") + " AND ref LIKE " +
: Replace(updateAuth__varMembership, "'", "''") + ""
: updateAuth.CommandType = 1
: updateAuth.CommandTimeout = 0
: updateAuth.Prepared = true
' --------------------------------------------------
' --- Two lines added here
' --------------------------------------------------
Response.Write(updateAuth.Commandext)
Response.End
' --------------------------------------------------
: updateAuth.Execute()
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Gianpiero Colagiacomo" <gp@1...>
To: "Access ASP" <access_asp@p...>
Sent: Sunday, August 25, 2002 10:46 PM
Subject: [access_asp] sql that works but won't run when generated by
Ultradev
: I'm running a Ultradev command behaviour sql script from an asp/vbs page:
:
: UPDATE members, memberships
: SET accesslvl = 'member',
: authtodate = IIf(authtodate<date(), date() + duration, authtodate +
: duration),
: netbanxref = varNetbanxRef,
: netbanxdate = date()
: WHERE username = varUsername AND ref = varMembership
:
: It generates this error:
:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
:
: If I run the exact same sql from within Access itself it works fine,
: updating the row expected with the correct data. What's happening here?
I
: realise the 'Too few parameters' error suggests that one or more of the
: column names is/are invalid but they obviously are not otherwise the query
: wouldn't run within Access (cut and paste the query so it's not my
: typing!)... The connection itself is fine as I can query/update tables
: (including MEMBERS and MEMBERSHIPS) in the database with other pages in
the
: same site without fault. Only this script is causing me problems.
:
: What does the 'expected 2' mean? 2 columns that are invalid? Is there
: someway to debug this???
:
: The ASP generated by Ultradev looks like this, note I've hardcoded values
: for the vars for test purposes:
:
: <%
:
: if("abcd" <> "") then updateAuth__varNetbanxRef = "abcd"
:
: if("0000001" <> "") then updateAuth__varMembership = "0000001"
:
: if("test4" <> "") then updateAuth__varUsername = "test4"
:
: %>
: <%
: set news = Server.CreateObject("ADODB.Recordset")
: news.ActiveConnection = MM_toptipsracing_STRING
: news.Source = "SELECT * FROM news"
: news.CursorType = 0
: news.CursorLocation = 2
: news.LockType = 3
: news.Open()
: news_numRows = 0
: %>
: <%
:
: set updateAuth = Server.CreateObject("ADODB.Command")
: updateAuth.ActiveConnection = MM_toptipsracing_STRING
: updateAuth.CommandText = "UPDATE members, memberships SET accesslvl
: 'member', authtodate = IIf(authtodate<date(), date() + duration,
: authtodate + duration), netbanxref = " +
Replace(updateAuth__varNetbanxRef,
: "'", "''") + ", netbanxdate = date() WHERE username LIKE " +
: ReplaceupdateAuth__varUsername, "'", "''") + " AND ref LIKE " +
: Replace(updateAuth__varMembership, "'", "''") + ""
: updateAuth.CommandType = 1
: updateAuth.CommandTimeout = 0
: updateAuth.Prepared = true
: updateAuth.Execute()
:
: %>
:
:
:
: Please help because this is driving me crazy!
:
: This is happening in Ultradev4 with Access2000 on Windows2000.
Message #3 by "Gianpiero Colagiacomo" <gp@1...> on Mon, 26 Aug 2002 20:11:08
|
|
Ken,
THANK YOU!
Yup, you guessed right the vars were being placed within the sql WITHOUT
being enclosed within quotes. I don't understand why Ultradev requires
you to enclose variables within quotes but having seen the literal output
it was obvious what was needed to correct it!
You've really helped...
Cheers,
GP
gp@1...
> Hi,
Can you please print out (using Response.Write()) the literal SQL statement
you are sending to the database? If what is below is the literal statement,
then the problem is being caused because varUserName and varMembership are
variables in ASP (I think), they are not functions in Access.
Instead, when creating your SQL statement in ASP, you need to do something
like:
strSQL = _
"WHERE UserName = '" & varUserName & "' "
so that the literal text is concatenated with the variable.
http://www.adopenstatic.com/faq/80040e10.asp
(Not enough parameters: Scenario 1 explains how this might happen)
To output the literal SQL statement, amend your code to read:
: updateAuth.CommandText = "UPDATE members, memberships SET accesslvl
: 'member', authtodate = IIf(authtodate<date(), date() + duration,
: authtodate + duration), netbanxref = " +
Replace(updateAuth__varNetbanxRef,
: "'", "''") + ", netbanxdate = date() WHERE username LIKE " +
: ReplaceupdateAuth__varUsername, "'", "''") + " AND ref LIKE " +
: Replace(updateAuth__varMembership, "'", "''") + ""
: updateAuth.CommandType = 1
: updateAuth.CommandTimeout = 0
: updateAuth.Prepared = true
' --------------------------------------------------
' --- Two lines added here
' --------------------------------------------------
Response.Write(updateAuth.Commandext)
Response.End
' --------------------------------------------------
: updateAuth.Execute()
Cheers
Ken
|
|
|
View Cart
