By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

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

P: 3
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
Share this Question
Share on Google+
1 Reply

P: 3
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.