Parameters.Refresh
I was looking for documentation as to what the Parameters.Refresh method
does. I suspect that it is somehow initializing the Parameter oject within the Command with the number of parameters which are in my stored procedure. Anyway, when I get to that line of code I get a "-2147217900 (80040e14)" and that message says, more or less that my SQL statement is invalid. The only SQL statement I have is what is calling the stored procedure. In the message box I seem to be getting message back from Oracle indicating that I have the wrong number of paramters and/or the wrong types. At this juncture, I have not loaded my parameters. Here is my code.... Dim cnn As New ADODB.Connection Dim Rs As ADODB.Recordset cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYYYY;User ID=XXXXXX;Data Source=ZZZZZZZZZZ;Persist Security Info=True" cnn.Open Dim cmd As New ADODB.Command cmd.ActiveConnection = cnn cmd.CommandType = adCmdStoredProc cmd.CommandText = "OPS_UI_QUERIES.QRY_PROD_Data" cmd.Parameters.Refresh cmd.Parameters(1).Name = "i_User_ID" cmd.Parameters(1).Type = adInteger cmd.Parameters(1).Value = 1 cmd.Parameters(2).Name = "i_Time_ID" cmd.Parameters(2).Type = adInteger cmd.Parameters(2).Value = 136 Set Rs = cmd.Execute Any ideas as to what is wrong? Thanks in advance for your assistance. |
Parameters.Refresh
Jim,
I don't think that is what you want. I don't have a database on this machine, so I cannot test it, but I think you want code along these lines Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim parm1 As ADODB.Parameter Dim parm2 As ADODB.Parameter Dim rs As ADODB.Recordset Set cnn = New ADODB.Connection Set cmd = New ADODB.Command cnn.ConnectionString = "Provider=MSDAORA.1;" & _ "Password=YYYYYY;" & _ "UserID=XXXXX;" & _ "Data Source=ZZZZZZZ;" & _ "Persist Security Info=True" cnn.Open cnn.ConnectionString With cmd .CommandText = "mySP" .CommandType = adCmdStoredProc .ActiveConnection = cnn.ConnectionString End With Set parm1 = cmd.CreateParameter( _ Name:="i_User_ID", _ Type:=adInteger, _ Direction:=adParamInput, _ Value:=1) Set parm2 = cmd.CreateParameter( _ Name:="i_Time_ID", _ Type:=adInteger, _ Direction:=adParamInput, _ Value:=136) cmd.Parameters.Append (parm1) cmd.Parameters.Append (parm2) Set rs = cmd.Execute -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jim Heavey" wrote in message ... I was looking for documentation as to what the Parameters.Refresh method does. I suspect that it is somehow initializing the Parameter oject within the Command with the number of parameters which are in my stored procedure. Anyway, when I get to that line of code I get a "-2147217900 (80040e14)" and that message says, more or less that my SQL statement is invalid. The only SQL statement I have is what is calling the stored procedure. In the message box I seem to be getting message back from Oracle indicating that I have the wrong number of paramters and/or the wrong types. At this juncture, I have not loaded my parameters. Here is my code.... Dim cnn As New ADODB.Connection Dim Rs As ADODB.Recordset cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYYYY;User ID=XXXXXX;Data Source=ZZZZZZZZZZ;Persist Security Info=True" cnn.Open Dim cmd As New ADODB.Command cmd.ActiveConnection = cnn cmd.CommandType = adCmdStoredProc cmd.CommandText = "OPS_UI_QUERIES.QRY_PROD_Data" cmd.Parameters.Refresh cmd.Parameters(1).Name = "i_User_ID" cmd.Parameters(1).Type = adInteger cmd.Parameters(1).Value = 1 cmd.Parameters(2).Name = "i_Time_ID" cmd.Parameters(2).Type = adInteger cmd.Parameters(2).Value = 136 Set Rs = cmd.Execute Any ideas as to what is wrong? Thanks in advance for your assistance. |
Parameters.Refresh
See if this gives a clue:
http://support.microsoft.com/kb/240205/EN-US/ PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized Query -- Regards, Tom Ogilvy "Jim Heavey" wrote in message ... I was looking for documentation as to what the Parameters.Refresh method does. I suspect that it is somehow initializing the Parameter oject within the Command with the number of parameters which are in my stored procedure. Anyway, when I get to that line of code I get a "-2147217900 (80040e14)" and that message says, more or less that my SQL statement is invalid. The only SQL statement I have is what is calling the stored procedure. In the message box I seem to be getting message back from Oracle indicating that I have the wrong number of paramters and/or the wrong types. At this juncture, I have not loaded my parameters. Here is my code.... Dim cnn As New ADODB.Connection Dim Rs As ADODB.Recordset cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYYYY;User ID=XXXXXX;Data Source=ZZZZZZZZZZ;Persist Security Info=True" cnn.Open Dim cmd As New ADODB.Command cmd.ActiveConnection = cnn cmd.CommandType = adCmdStoredProc cmd.CommandText = "OPS_UI_QUERIES.QRY_PROD_Data" cmd.Parameters.Refresh cmd.Parameters(1).Name = "i_User_ID" cmd.Parameters(1).Type = adInteger cmd.Parameters(1).Value = 1 cmd.Parameters(2).Name = "i_Time_ID" cmd.Parameters(2).Type = adInteger cmd.Parameters(2).Value = 136 Set Rs = cmd.Execute Any ideas as to what is wrong? Thanks in advance for your assistance. |
Parameters.Refresh
Thanks Tom for the article. But the article says that the indicates that the
possible reason for the error is that the wrong provided is being used. I am using the "MSDAORA" identified in the article (actually I was using "MSDAORA.1", but neither works). So I am still stuck |
Parameters.Refresh
Bob, I have tried several versions of what you had suggested. Seems
perfectly logical, but it does not work. When the append command is used, I get an error message about requiring an object. I have gone and seen KB article from Microsoft which shows the use of refresh method, and once that is used, the code begins to reference the parameters, so I am guessing that the Refresh initialized those parameters in the command object. But neither work for me. |
Parameters.Refresh
To me it says MSDAORA does not work (I assume MSDAORA.1 does not either)
with this command. Further it says you have use a different provider which it suggests which one to use. Your only stuck if you continue to use the OLE DB provider above. Then again, maybe I can't read. -- Regards, Tom Ogilvy "Jim Heavey" wrote in message ... Thanks Tom for the article. But the article says that the indicates that the possible reason for the error is that the wrong provided is being used. I am using the "MSDAORA" identified in the article (actually I was using "MSDAORA.1", but neither works). So I am still stuck |
Parameters.Refresh
So you are not getting the error that is shown in the article? Do you get
that error if you use Refresh? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jim Heavey" wrote in message ... Bob, I have tried several versions of what you had suggested. Seems perfectly logical, but it does not work. When the append command is used, I get an error message about requiring an object. I have gone and seen KB article from Microsoft which shows the use of refresh method, and once that is used, the code begins to reference the parameters, so I am guessing that the Refresh initialized those parameters in the command object. But neither work for me. |
Parameters.Refresh
I found a form of the Parameters.Append command which does work. That being:
cmd.Parameters.Append cmd.CreateParameter("i_User_ID", adInteger, adParamInput, 8, 1) Why this works and the other does not, beats me. I am guessing that I only need to use the "Refresh" method if I did not want to create my parms manually. So now I get through the code which allows me to add my parameters. My current problem is my last parameter "type" is an Oracle Ref Cursor. I'm not sure what type this translates to in ADO terms.... So I guess I will try a few and see if any work. |
Parameters.Refresh
Are you using a ref cursor as an OUT parameter from a procedure, or as the
return value from a function? I know that if a PLSQL function returns a ref cursor then in VBA you would set the return value to an ADO recordset... Example here... http://www.oracle-base.com/articles/...Recordsets.php Tim -- Tim Williams Palo Alto, CA "Jim Heavey" wrote in message ... I found a form of the Parameters.Append command which does work. That being: cmd.Parameters.Append cmd.CreateParameter("i_User_ID", adInteger, adParamInput, 8, 1) Why this works and the other does not, beats me. I am guessing that I only need to use the "Refresh" method if I did not want to create my parms manually. So now I get through the code which allows me to add my parameters. My current problem is my last parameter "type" is an Oracle Ref Cursor. I'm not sure what type this translates to in ADO terms.... So I guess I will try a few and see if any work. |
All times are GMT +1. The time now is 04:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com