help with Command - Stored Procedure

[GTN1707]

Hi all,

I'm having a great deal of difficulty getting my head around an update command / stored procedure, I've pulled out all of my books and spent the last two days on Google which has made my confusion worse. In simple terms what I'm trying to do is update a field in a table with the value y, but based on certain criteria. So my table has the following fields -

JBAID
JBASiteID
JBADatePosted
JBAPostFor
JBANotified

What i want to do is update the value of field JBANotified on all records that meeting the following criteria to Y

The criteria is -

WHERE DATEADD(d,JBAPostFor,JBADatePosted) BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE() AND JBANotified = 'n' AND JBASiteID = MMColParam (where MMColParam is Session("SITEID"))

So I've just tried building a command / stored procedure - which reads (see attached code)(more than likely all wrong)

The things that I cant get my head around are -

Firstly getting the code right in the first place, running the command on page load and then when the command has run redirecting to the next page.

I really would appreciate some help with this -

thanks all

[Guest · Accepted solution]

Are you using CS3 with ASP/VBScript? There is a bug in the command implementation that has this function broken.

I asked about it in this forum several months ago, but now I can't find my original post, but I did find another post that talks about the same thing: http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=12&catid=263&threadid=1281539&e...

My solution is to drop back to DW8 for command editing, but use CS3 for everything else. I don't know what you're supposed to do if you don't have an earlier version.

It looks like your sql is correct, but the bug prevents the second code block from being updated with the correct values.

I hope a solution is forthcoming....

[Newsgroup_User]

Ed Stewart wrote:
> Are you using CS3 with ASP/VBScript? There is a bug in the command
> implementation that has this function broken.
>
> I asked about it in this forum several months ago, but now I can't find my
> original post, but I did find another post that talks about the same thing:
> http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=12&catid=26
> 3&threadid=1281539&enterthread=y
>
> My solution is to drop back to DW8 for command editing, but use CS3 for
> everything else. I don't know what you're supposed to do if you don't have an
> earlier version.
>
> It looks like your sql is correct, but the bug prevents the second code block
> is not being updated with the correct values.
>
> I hope a solution is forthcoming....
>

Create a stored procedure and then use a recordset to exectute it,
making sure the recordset has matching parameters that the stored
procedure expects.

Commands are broken, I reported it to Adobe and they said it might get
fixed in the next version.

Steve

[GTN1707]

Ed,

Thanks for the above, I pulled MX2004 and wrote the following code using the command function -

<%
if(Session("SITEID") <> "") then Command1__MMColParam = Session("SITEID")
%>
<%
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_recruta2_STRING
Command1.CommandText = "UPDATE dbo.JBAdvert SET JBANotified = 'y' WHERE JBASiteID = " + Replace(Command1__MMColParam, "'", "''") + " AND DATEADD(d,JBAPostFor,JBADatePosted) BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE() AND JBANotified = 'n'"
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
%>

And needless to say, it works a treat and strangely generates less code than CS3, I've got a few more (more complicated) procedures to write, but this one works beautifully thanks for the advise.

GTN

[Newsgroup_User]

> Create a stored procedure and then use a recordset to exectute it,
> making sure the recordset has matching parameters that the stored
> procedure expects.
>
> Commands are broken, I reported it to Adobe and they said it might get
> fixed in the next version.




Does Access support stored procedures??

[Newsgroup_User]

Ed Stewart wrote:
>> Create a stored procedure and then use a recordset to exectute it,
>> making sure the recordset has matching parameters that the stored
>> procedure expects.
>>
>> Commands are broken, I reported it to Adobe and they said it might get
>> fixed in the next version.
>
>
>
>
> Does Access support stored procedures??

I think it has its own implementation of it. Here is something I just
found on Google: http://www.devcity.net/Articles/18/msaccess_sp.aspx

Steve