468,469 Members | 2,200 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,469 developers. It's quick & easy.

Problem with .executing a stored procedure that is an append query

Hi All,

I am at my wits end. I would consider myself an intermediate programmer and have been managing a web application that I wrote in .ASP, VBScript, and JavaScript for years. There is one task I cannot seem to get to work and that is to have ASP execute a stored procedure that is is an append query living on an MS Access backend.

I've worked around this issue by just opening the table then executing .AddNew, feeding in the data, and updating. However, now I need this to work as an Append Query because the records to be appended number anywhere from 1 to 70. So I'm back at it. And stumped.

The .execute command actually runs and I receive no errors anymore (fixed all those) but I do not have the new data in the table.

The query runs fine if I'm in Access, and I did go in and tell it not to prompt confirmation on Action Queries. I've verified my parameters are being sent in in the proper order. (At least, in the order the query asks for them run I run it from within Access).

Again, no errors, but no data. Here's my (relevant) code. From everything I've researched, it should be working. Any help is so greatly appreciated. I'm hoping it is something as stupid as I've been looking at it too long and just don't see the obvious mistake. Thanks in advance!!!

Two notes:
1) I use the below listed parameters and method to append the parameters in all my other stored procedure queries (which are all select queries) and they work fine.
2) The active connection (objConn) is established and working fine in other areas of this same page so that is not an issue. I won't waste space showing that information unless it is requested.

Expand|Select|Wrap|Line Numbers
  1. Dim objCmd
  2. set objCmd=server.createobject("adodb.command")
  3. objcmd.activeconnection=objConn
  4. objcmd.commandtype=adCmdStoredProc
  5. objCmd.parameters.append objcmd.CreateParameter("vndr", adBigInt, adparaminput, 10, vVndrID)
  6. objCmd.parameters.append objcmd.CreateParameter("dlr", adBigInt, adparaminput, 10, vDlrID)
  7. objCmd.parameters.append objcmd.CreateParameter("changed", adVarChar, adparaminput, 10, vChgBy)
  8. objCmd.commandtext="qryAddNewCS"
  9. objCmd.execute
Oct 6 '14 #1
1 1553
Hi again,
I have figured out that my problem was not with the code above. It is due to the fact that the append query stored in Access contains the "*" wildcard on a string field.

So, now I am figuring out how to modify the wildcard so it can stay in the query, or conversely, how to successfully pass it in. I know I need to use the "%" so at least I'm part way there. If I get stumped on that, I will post to a new question.
Oct 7 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Thomas R. Hummel | last post: by
1 post views Thread by Belee | last post: by
1 post views Thread by Justin | last post: by
6 posts views Thread by Scott McNair | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.