help with Command - Stored Procedure

5 replies
558 views
Translate

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

Server side applications

This topic is closed to new replies. Start a new post to keep the conversation going.

Correct answer

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&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 from being updated with the correct values.

I hope a solution is forthcoming....

N

Newsgroup_User

Inspiring

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

Translate

N

Newsgroup_User

Inspiring

> 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??

Translate

G

GTN1707Author

Known Participant

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

Translate

N

Newsgroup_User

Inspiring

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

Translate

A

Anonymous

Correct answer

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&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 from being updated with the correct values.

I hope a solution is forthcoming....

Translate

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.