| Author |
 Topic  |
|
Carefree
Advanced Member
Philippines
4222 Posts |
 Posted - 05 June 2008 : 09:43:33
|
I hope one of you guys is still checking this topic lol.
How can I change this line so the necessary quotation marks are in the sql?
"WHERE M_NAME = " & Request.Form("RPrisoner") & ""
< |
 |
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 09:52:54
|
Just in case I needed the Members. in front of the members table values, I tried this:
Resulting in the predictable:
quote:
SELECT MEMBERS.MEMBER_ID FROM FORUM_MEMBERS WHERE MEMBERS.M_NAME = Testing123
and
quote:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
line 85
< |
Edited by - Carefree on 05 June 2008 09:53:23 |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 05 June 2008 : 10:17:46
|
"WHERE M_NAME = '" & Request.Form("RPrisoner") & "'"< |
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 10:36:25
|
That gives me apostrophes (not quotation marks) around the name, and the same error.
quote:
SELECT MEMBERS.MEMBER_ID FROM FORUM_MEMBERS WHERE MEMBERS.M_NAME = 'Testing123'
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
line 85
< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 05 June 2008 : 10:40:33
|
That's correct; SQL uses single quotation marks. Just having a look at the rest of the query for any problems ...
<edit>OK, I'm going to combine the 2 queries into one for you, save you a db hit but I'll need to know the structure of the usergroups table.</edit>
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
Edited by - Shaggy on 05 June 2008 10:44:11 |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 10:53:01
|
Thanks, Shaggy. I started drinking but coffee's not making me relax.
Only four fields, all of which are integers with zero decimal places:
USERGROUP_ID
MEMBER_ID
MEMBER_TYPE
ORIGINAL_ID
< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 05 June 2008 : 12:04:17
|
Give this a whirl:if request.form("Method_Type")="Release" then
strSql= "SELECT U.USERGROUP_ID,M.MEMBER_ID,U.MEMBER_TYPE,U.ORIGINAL_ID "&_
"FROM "&strTablePrefix&"USERGROUP_USERS U,"&strMemberTablePrefix&"MEMBERS M "&_
"WHERE M.M_NAME='"&chkstring(request.form("RPrisoner"),"sqlstring")&"' "
"AND U.MEMBER_ID=M.MEMBER_ID"
set objRs=my_conn.execute(strSql)
'do stuff
objRs.close:set objrs=nothing
'do more stuff
end if< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 13:18:53
|
I didn't get any values on Member_ID, so I added in some Responses
quote:
MEMBER_ID =
SELECT U.USERGROUP_ID,M.MEMBER_ID,U.MEMBER_TYPE,U.ORIGINAL_ID FROM FORUM_USERGROUP_USERS U,FORUM_MEMBERS M WHERE M.M_NAME='Testing123' AND U.MEMBER_ID=M.MEMBER_IDUPDATE FORUM_USERGROUP_USERS SET USERGROUP_ID = '', MEMBER_ID = '', MEMBER_TYPE = 1 WHERE MEMBER_ID = ''
No error message - but no value to MEMBER_ID, same thing I was getting earlier. Dead in the water again.< |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 13:28:04
|
That last one was hard to read, so I redid a couple lines:
quote:
SELECT U.USERGROUP_ID,M.MEMBER_ID,U.MEMBER_TYPE,U.ORIGINAL_ID FROM FORUM_USERGROUP_USERS U,FORUM_MEMBERS M WHERE M.M_NAME='Testing123' AND U.MEMBER_ID=M.MEMBER_ID
MEMBER_ID =
UPDATE FORUM_USERGROUP_USERS U, SET U.USERGROUP_ID = U.ORIGINAL_ID, U.MEMBER_ID = RPRISON_ID, U.MEMBER_TYPE = 1 WHERE U.MEMBER_ID =
No value being passed to the Member IDs.< |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 13:40:40
|
I figured out what I missed.
RPRISON_ID = objRs("MEMBER_ID")
So it's giving me a member ID and maybe I can finally finish this thing.
Thanks again, Shaggy< |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 05 June 2008 : 13:44:49
|
you don't get a value for MEMBER_ID because you never give it one
try
< |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 June 2008 : 15:08:26
|
I have it working 100% now, Huwr; thanks to everyone who helped out.
If anyone's interested in the project, I'll package it for SB.< |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 06 June 2008 : 04:27:38
|
You could save yourself a chunk of code and another db hist by using the following code instead:strSQL= "UPDATE "&strTablePrefix&"USERGROUP_USERS "&_
"SET USERGROUP_ID=ORIGINAL_ID, MEMBER_TYPE=1 "&_
"WHERE MEMBER_ID=(SELECT MEMBER_ID FROM "&strMemberTablePrefix&"MEMBERS "&_
"WHERE M_NAME='"&chkstring(request.form("RPrisoner"),"sqlstring")&"')"
my_conn.execute(strSql),,adCmdText+adExecuteNoRecords< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 06 June 2008 : 06:15:33
|
| Nice clean code, Shaggy. Now if I can avoid making it too cumbersome when I add Email selection to that routine since there are two automatic Emails generated by either action.< |
|
|
|
Topic |
|
Topic Locked
