Interger changes to character in ADODB command statement

Hello,
I am working with a Classic ASP and the ADODB command that I am trying to use with an integer always returns a character with the value of '20' no matter what integer value I use.

We have installed a new Oracle 11G Client web server and are using an Oracle 10G database.

For example in the code snippet the integer 38 returns as a character with the value 20.

Thanks for the help

cmd.Parameters.Append cmd.CreateParameter("p_user_id", 20, 1, 10 , 38)

Open in new window

LVL 1
capturetheflagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Wrap the 38 in quotes.  That data type is not a number.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I meant to post this link with my answer for reference.  http://msdn.microsoft.com/en-us/library/windows/desktop/ms677209(v=vs.85).aspx

The "38" is a variant so it can be a number or character, therefor, wrap in quotes.
0
capturetheflagAuthor Commented:
Hello padas,

The quotes did not work, and this line of code fails too.

cmd.Parameters.Append cmd.CreateParameter("p_user_id", Application("adInteger"), Application("adParamInput"), 10, clng(request.form("userID")))

But it works on the old web server. Hmmm?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
That line may not have worked because you do not have adovbs included in your file.

I do not know Oracle.  Is that compatible with classic asp?  64bit vs 32bit?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Let's go back to what you did here http:Q_28258510.html#a39547703

For each version you tried please let us know the exact error message.  "It did not work" is hard to go by.  Also, if you are getting generic 500 error, you need to turn on asp errors.

http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default

I would also like to see more of your code.  Can you sprinkle some response.writes through different points in your code to help see where it is breaking.  Are you 100% sure it is that line?   It could also have something to do with your provider.  Have you always used oracle or did you use ms sql in the past?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Looking at some of my own code, where you have

cmd.Parameters.Append cmd.CreateParameter("p_user_id", 20, 1, 10 , 38)

Shouldn't that be

cmd.Parameters.Append cmd.CreateParameter("p_user_id", 200, 1, 100 , 38)
or
cmd.Parameters.Append cmd.CreateParameter("p_user_id", 200, 1, 100 , "38")
0
capturetheflagAuthor Commented:
Hello padas,

Thanks for the help.  Yes,

cmd.Parameters.Append cmd.CreateParameter("p_user_id", 200, 1, 100 , 38)

will work but, that makes it a character and it must be an integer.  
Another factor may be that we are using a new Oracle web server because the code works on the old web server.  I would not know where to look on the new web server

Here is the complete code statement.


Set cmd = Server.CreateObject("ADODB.Command")
                cmd.CommandType = 4
                cmd.ActiveConnection = conn
                cmd.CommandText = "APP_API.UPD_USER_BY_USER_ID"
                
                'cmd.CommandText = "UPDATE personnel SET email = lower(p_email), first_name = upper(p_first_name),last_name = upper(p_last_name) WHERE user_id = p_user_id"

                cmd.Properties("PLSQLRSet") = TRUE

                cmd.Parameters.Append cmd.CreateParameter("p_user_id", 20, 1, 10 , 38)
                cmd.Parameters.Append cmd.CreateParameter("p_first_name", 200, 1, 30, "xxxxx")
                
                cmd.Parameters.Append cmd.CreateParameter("p_last_name", 200, 1, 30, "yyyyy")
                cmd.Parameters.Append cmd.CreateParameter("p_email", 200, 1, 50, "zzzzzz@rrrrrr.ggg")

                for each x in cmd.Parameters
                                Response.Write("<p>" & x.Name & " = " & x.Value & "</p>")
                next

Open in new window

0
capturetheflagAuthor Commented:
Hello padas,

It appears that a bigint value is not supported in the version of the OLE DB driver that we use to connect our apps to the DB.

Mike
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I'm sorry, I am not familiar with Oracle to be much help.  I know in the past when I had a similar issue with ms sql, I just changed my connection string and all was good.  

Let's see if I can track down somebody else to help out.
0
slightwv (䄆 Netminder) Commented:
Oracle should be able to do an implicit data type conversion if you pass in a string.

Can you describe the procedure APP_API.UPD_USER_BY_USER_ID?

I would like to see what the parameters are that it is expecting.

Maybe I can then try to mock something up.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
capturetheflagAuthor Commented:
Thanks for the help but, I will just have to call Oracle about this problem.
0
slightwv (䄆 Netminder) Commented:
Although I appreciate the points.  My post didn't do anything to solve the issue.

If you did not get an answer to the question, you should just ask to have the question deleted.

>> I will just have to call Oracle about this problem.

We can probably answer the question faster than Oracle but you will need to provide us with enough information to be able to do so.  Even Oracle will ask for similar information to be provided.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Yes, I agree.  The solution selected should also  help the next person viewing the thread.  


Just another thought.  I typically use adDouble for all number values.  Can you try changing you code to:
cmd.Parameters.Append cmd.CreateParameter("p_user_id", 5, 1, 10 , 38)

Open in new window


However, I don't think that is the problem.  I don't use Oracle, I can't believe there is something that changes the value of your input value.  I wonder if we are looking at the wrong thing to figure out  your issue.

As slightwv has asked, viewing the code from your stored procedure is most likely the issue. it is probably something in your SP that is accepting the wrong value from something other then what you think it is.  

In your SP, do you have a fixed value set perhaps for testing you forgot to change?  Do you have the wrong variable specified in your SP for that item?  Slight spelling variation?  Or calculation?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.