469,356 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

OleDb Stored Procedure Call Problem

HI,

I am trying to figure out how to write a C# program to add parameter's "A" and "B" and return "C" in the following code:

I get an error at the commnd.ExecuteNonQuery(); statement below. The error states:

X2 Provider Error: <ExecuteHelper Failed>
X2 Provider Error: <Internal Error: Failed to get the IProc Interface>

The provider works with ADO.Net according to the developer.

Here's the code I have so far:

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data.OleDb;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Data;
  9. using System.Windows.Forms;
  10.  
  11.  
  12. namespace ADP_API
  13. {
  14.     public partial class Form1 : Form
  15.     {
  16.         public Form1()
  17.         {
  18.             InitializeComponent();
  19.         }
  20.  
  21.         private void button1_Click(object sender, EventArgs e)
  22.         {
  23.             string constr = "provider=ADPProv.ADPProv.1;host=" + Host.Text;
  24.             constr = constr + ";product=" + Product.Text;
  25.             constr = constr + ";server=" + Server.Text;
  26.             constr = constr + ";password=" + Password.Text;
  27.             constr = constr + ";pooling=TRUE";
  28.  
  29.             OleDbConnection con = new OleDbConnection(constr);
  30.  
  31.             con.Open();
  32.  
  33.             OleDbCommand commnd = new OleDbCommand();
  34.             commnd.Connection = con;
  35.             commnd.CommandType = CommandType.StoredProcedure;
  36.             commnd.CommandText = "proc=Addition";
  37.  
  38.             OleDbParameter paramReturnValue = new OleDbParameter();
  39.             paramReturnValue.ParameterName = "C";
  40.             paramReturnValue.OleDbType = OleDbType.Integer;
  41.             paramReturnValue.Direction = ParameterDirection.Output;
  42.  
  43.             OleDbParameter paramA = new OleDbParameter();
  44.             paramA.ParameterName = "A";
  45.             paramA.OleDbType = OleDbType.Integer;
  46.             paramA.Direction = ParameterDirection.Input;
  47.             paramA.Value = var1.Text;
  48.  
  49.             OleDbParameter paramB = new OleDbParameter();
  50.             paramB.ParameterName = "B";
  51.             paramB.OleDbType = OleDbType.Integer;
  52.             paramB.Direction = ParameterDirection.Input;
  53.             paramB.Value = var2.Text;
  54.  
  55.             commnd.Parameters.Add(paramReturnValue);                            commnd.Parameters.Add(paramA); // parameter 1
  56.             commnd.Parameters.Add(paramB); // parameter 2
  57.  
  58.  
  59.  
  60.            commnd.ExecuteNonQuery();
  61.  
  62.             int returnValue = (int)commnd.Parameters["C"].Value;
  63.  
  64.             Output.Items.Add(returnValue);
  65.  
  66.             con.Close();
  67.  
  68.         }
  69.     }
  70. }
  71.  

Thanks,

John
Nov 6 '08 #1
14 7485
OK I got it to return a value in my listbox with the following code but with 6 and 5 put in for var1 and var2 respectively I get a zero in the list box. Any ideas why commnd.Parameters["C"].Value is null? Could it be that ExecuteNonQuery is wrong for this??

Thanks

Here's my new more successful code:

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data.OleDb;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Data;
  9. using System.Windows.Forms;
  10.  
  11.  
  12. namespace ADP_API_Practice
  13. {
  14.     public partial class Form1 : Form
  15.     {
  16.         public Form1()
  17.         {
  18.             InitializeComponent();
  19.         }
  20.  
  21.         private void button1_Click(object sender, EventArgs e)
  22.         {
  23.             string constr = "provider=ADPProv.ADPProv.1;host=" + Host.Text;
  24.             constr = constr + ";product=" + Product.Text;
  25.             constr = constr + ";server=" + Server.Text;
  26.             constr = constr + ";password=" + Password.Text;
  27.             constr = constr + ";pooling=TRUE";
  28.  
  29.             OleDbConnection con = new OleDbConnection(constr);
  30.  
  31.             con.Open();
  32.  
  33.             OleDbCommand commnd = new OleDbCommand();
  34.             commnd.Connection = con;
  35.             commnd.CommandType = CommandType.StoredProcedure;
  36.             commnd.CommandText = "group=ADMIN;object=Procs;name=Addition";
  37.  
  38.             OleDbParameter paramA = new OleDbParameter();
  39.             paramA.ParameterName = "A";
  40.             paramA.OleDbType = OleDbType.Integer;
  41.             paramA.Direction = ParameterDirection.Input;
  42.             paramA.Value = Int32.Parse(var1.Text);
  43.  
  44.             OleDbParameter paramB = new OleDbParameter();
  45.             paramB.ParameterName = "B";
  46.             paramB.OleDbType = OleDbType.Integer;
  47.             paramB.Direction = ParameterDirection.Input;
  48.             paramB.Value = Int32.Parse(var2.Text);
  49.  
  50.             OleDbParameter paramReturnValue = new OleDbParameter();
  51.             paramReturnValue.ParameterName = "C";
  52.             paramReturnValue.OleDbType = OleDbType.Integer;
  53.             paramReturnValue.Direction = ParameterDirection.ReturnValue;
  54.  
  55.             commnd.Parameters.Add(paramA);
  56.             commnd.Parameters.Add(paramB);
  57.             commnd.Parameters.Add(paramReturnValue);
  58.  
  59.             commnd.ExecuteNonQuery();
  60.  
  61.             int returnValue = (int)commnd.Parameters["C"].Value;
  62.  
  63.             Output.Items.Add(returnValue);
  64.  
  65.             con.Close();
  66.  
  67.         }
  68.     }
  69. }
Nov 7 '08 #2
mldisibio
190 Expert 100+
I noticed in your first post, you added the return parameter to the parameter collection first, but you tagged it incorrectly as ParameterDirection.Output.

In the second post, you tagged it correctly as ParameterDirection.ReturnValue, but you add it to the collection last.

I believe MSDN states that ReturnValue parameters always need to be added to the ParameterCollection first, so try that again.

Also, not seeing your actual stored procedure, make sure it is indeed a ReturnValue and not an output paramater.

In SqlServer, for example, a ReturnValue is returned by the sql "RETURN" statement.
An output parameter is declared in the procedure definition, and the parameter is filled during the procedure. There may or may not also be a RETURN statement, but it is unrelated to the output parameter.

Also, SqlServer at least, has an implicit return value of 0 when successful, whether you declare it or not, so that may explain why you get a 0 in your text box.
Nov 9 '08 #3
Hi and thank you for your response. You are right it was an Output Parameter. I still only get a 0 when I make that change though.

I'm not understanding your last paragraph about 0 being returned when successful.

I had a look around on the server and there is another object called AdditionParams where I found all the attribute of each parameter and there is a place for their value. It looks like the Addition proc might look to that object for the A and B values and return the C value there??

Knowing that, how would I approach this? Write to the AdditionParams object, run the proc with no added params and read the C value???

Thanks for your help.
Nov 12 '08 #4
mldisibio
190 Expert 100+
Can you post the sql for both procedures?
Please enclose the procedures in CODE tags. Also, you can cut out any irrelevant statements if the procedures are long. Basically, we need to look at the procedure declarations (usually at the beginning), any calls to other procedures, and any statements that set the parameters.

Just for clarification, what database are you using?

What I mean by SQL Server returns an implicit zero is that if the procedure completes without error, and you do not have a RETURN statement, SQL Server implicitly returns a value of 0 to indicate success. So, even if you do not specify a RETURN statement in your sql procedure, if you create a Parameter with a ReturnValue direction in ADO.Net, it will be filled with a zero value.

The only reason I pointed that out is that if you mistakenly assign a ReturnValue parameter to your collection instead of an output parameter, and the value in your TextBox is always zero, it might be because it it simply reading the implicit return value of zero.
Nov 12 '08 #5
I really appreciate your help.

I don't have access to the procedures unfortunately. I just have API documentation which is based on the old ADO. The procs are in ENGLISH a query language used on the PICK OS.

The database is on a PICK system with multivariant fields and subvalues etc which is why I have to use the ADP provider. ADP is a computer system for car dealerships and they are very protective of their database. I have access to a training database.

I work for a chain of dealerships and I have to get proficient in the API before they will allow us access to our own data. The ADP chief software engineer really doesn't want to work with ADO.Net. Everytime I ask him a question he sends VB6 code and says it should work.I suppose I could work in VB6. Apparently I am not converting correctly to C# and .Net.

Here's his latest:

"John,

If it returns zero, then it is not working correctly (unless you added 0 + 0).

You do not need to give it the group or object values.

I made a VB 6 version of the Addition tester in a bare bones code to make it easier to see (using the ADO Tester code as a guide) and tested it against the TRAIN36 product. This should help you determine where your coding issues are.

Here is my code I highlighted the important sections of code. Yellow is supporting code and blue is the critical code in the API call itself."

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Dim PageConn As ADODB.Connection
  4. Dim PageCmd As ADODB.Command
  5. Dim StrConnData As String
  6. Dim StrCommand As String
  7. Dim sHost As String
  8. Dim sProd As String
  9. Dim sServer As String
  10. Dim sPasswd As String
  11. Dim iVarA As Integer
  12. Dim iVarB As Integer
  13. Dim iSum As Integer
  14.  
  15. Private Sub CmdClose_Click()
  16.     Unload Me
  17. End Sub
  18.  
  19. Private Sub cmdExecute_Click()
  20.     Dim ErrMsg As String
  21.     Dim ConnOpen As Boolean
  22.     Dim CmdOpen As Boolean
  23.     Dim ParamName As String
  24.     Dim ParamValue As String
  25.     Dim ParamDirection As String
  26.     Dim ParamType As String
  27.     Dim Param As ADODB.Parameter
  28.     On Error GoTo ExitExecute
  29.     '
  30.     ErrMsg = ""
  31.     ConnOpen = False
  32.     CmdOpen = False
  33.     '
  34.     ' Check values have been entered
  35.     '
  36.     If txtHost.Text = "" Then Exit Sub
  37.     If txtProd.Text = "" Then Exit Sub
  38.     If txtServer.Text = "" Then Exit Sub
  39.     If txtPasswd.Text = "" Then Exit Sub
  40.     If txtVarA.Text = "" Then Exit Sub
  41.     If txtVarB.Text = "" Then Exit Sub
  42.     '
  43.     ' Assign variables from the text boxes and check type
  44.     '
  45.     sHost = txtHost.Text
  46.     sProd = txtProd.Text
  47.     sServer = txtServer.Text
  48.     sPasswd = txtPasswd.Text
  49.     If IsNumeric(txtVarA.Text) Then
  50.         iVarA = CInt(txtVarA.Text)
  51.     Else
  52.         MsgBox "Variable A is not numeric", vbCritical
  53.         Exit Sub
  54.     End If
  55.     If IsNumeric(txtVarB.Text) Then
  56.         iVarB = CInt(txtVarB.Text)
  57.     Else
  58.         MsgBox "Variable B is not numeric", vbCritical
  59.         Exit Sub
  60.     End If
  61.     '
  62.     ' Open connection to DMS
  63.     '
  64.     ErrMsg = "Connection to " & sHost & ", " & sProd & "/" & sServer & " failed."
  65.     Set PageConn = CreateObject("ADODB.Connection")
  66.     PageConn.Provider = "ADPProv.ADPProv.1"
  67.     PageConn.CursorLocation = adUseClient
  68.     PageConn.ConnectionTimeout = 0
  69.     StrConnData = "host=" & sHost & "; product=" & sProd & "; server=" & sServer & "; productpassword=" & sPasswd & "; pooling=FALSE; port=10223"
  70.     PageConn.Open StrConnData, "", ""
  71.     ConnOpen = True
  72.     '
  73.     ' Execute Proc
  74.     '
  75.     ErrMsg = "Execute of Addition Proc failed."
  76.     Set PageCmd = CreateObject("ADODB.Command")
  77.     PageCmd.ActiveConnection = PageConn
  78.     PageCmd.CommandTimeout = 480
  79.     StrCommand = "proc=Addition"
  80.     PageCmd.CommandText = StrCommand
  81.     PageCmd.Parameters.Refresh
  82.     '
  83.     ' This for loop is for illustration only
  84.     '
  85.     For Each Param In PageCmd.Parameters
  86.         Select Case Param.Direction
  87.             Case 1
  88.                 ParamDirection = "In"
  89.             Case 2
  90.                 ParamDirection = "Out"
  91.             Case 3
  92.                 ParamDirection = "In/Out"
  93.             Case Else
  94.                 ParamDirection = "Unknown"
  95.         End Select
  96.         '
  97.         Select Case Param.Type
  98.             Case adBSTR
  99.                 ParamType = "String"
  100.             Case adDecimal
  101.                 ParamType = "Number"
  102.             Case adDate
  103.                 ParamType = "Date"
  104.             Case adDBTime
  105.                 ParamType = "Time"
  106.             Case Else
  107.                 ParamType = Param.Type
  108.         End Select
  109.         Select Case Param.Name
  110.             Case "A"
  111.                 If ParamType = "Number" And IsNumeric(iVarA) Then
  112.                     PageCmd.Parameters(Param.Name).Value = iVarA
  113.                 End If
  114.             Case "B"
  115.                 If ParamType = "Number" And IsNumeric(iVarB) Then
  116.                     PageCmd.Parameters("B").Value = iVarB
  117.                 End If
  118.         End Select
  119.     Next
  120.     '
  121.     ' Ignore above For loop and assign the values to add together
  122.     '
  123.     PageCmd.Parameters("A").Value = iVarA
  124.     PageCmd.Parameters("B").Value = iVarB
  125.     PageCmd.Parameters("C").Value = 0
  126.     PageCmd.Execute
  127.     CmdOpen = True
  128.     iSum = PageCmd.Parameters("C").Value
  129.     txtSum.Text = CStr(iSum)
  130.     '
  131.     ' Wrap Up and exit
  132.     '
  133.     On Error GoTo 0
  134.     DoEvents
  135.     If CmdOpen Then
  136.         Set PageCmd = Nothing
  137.         CmdOpen = False
  138.     End If
  139.     If ConnOpen Then
  140.         PageConn.Close
  141.         Set PageConn = Nothing
  142.         ConnOpen = False
  143.     End If
  144.     MsgBox "Done", vbInformation
  145.     Exit Sub
  146.     '
  147.     ' Handle any errors
  148.     '
  149. ExitExecute:
  150.     MsgBox ErrMsg & vbLf & Error, vbCritical
  151.     On Error GoTo 0
  152.     DoEvents
  153.     If CmdOpen Then
  154.         Set PageCmd = Nothing
  155.         CmdOpen = False
  156.     End If
  157.     If ConnOpen Then
  158.         PageConn.Close
  159.         Set PageConn = Nothing
  160.         ConnOpen = False
  161.     End If
  162. End Sub
  163.  
Again thanks for your help.
Nov 13 '08 #6
mldisibio
190 Expert 100+
I feel sorry for your situation, you are working against a black box.

Looking at his sample code, I would conclude he is calling "proc=Addition" directly, so I don't think you should change to the "AdditionParams" object.

From an ADO.Net perspective, your code looks like it should be correct. I do not see any "obvious" errors. Sometimes connections strings are different between legacy ADO and ADO.Net, but the connection would not be open if the connection string you are using is incorrect. And looking at his legacy connection code, I do not see any obvious reason why the ADO.Net should not work. At a minimum, it would throw some "Provider not found" error or such if there was an obvious showstopper.

- When you say you tried with "6" and "5", did you hard code those in? And did you try to display or view the result using the debugger to examime parameter "C" directly instead of relying on the form?

- I noticed his string uses "productpassword"; did you try keeping that instead of just "password"? Also, are you sure that by adding "group=ADMIN" you are addressing the correct stored procedure?

This is tough. I guess you can try using the debugger and making sure the connection is truly opened (although it would throw an exception if it wasn't) and then perhaps put a try/catch around the ExecuteNonQuery statement to make sure it is truly executing.

I am at a loss. I have never heard of this PICK database. One last try might be to use the System.Data.Odbc library instead of OleDb...but I'm just shooting in the dark.

Also...are there any tables you know about where you can test a very simple "Select" statement, just to confirm the database is capable of returning data?
Nov 13 '08 #7
mldisibio
190 Expert 100+
mmm...I came across one (count 'em - one) post regarding this ADPProv Provider;
This might even be your post, as it is so similar. However, I see one difference:


I figured it out. See the underlined changes below. I should not have used TableDirect but Text and then any string is cool.
Expand|Select|Wrap|Line Numbers
  1. private void button1_Click(object sender, EventArgs e)
  2. {
  3. string constr = "provider=ADPProv.ADPProv.1;host=" + Host.Text;
  4. constr = constr + ";product=" + Product.Text;
  5. constr = constr + ";server=" + Server.Text;
  6. constr = constr + ";password=" + Password.Text;
  7. constr = constr + ";pooling=TRUE";
  8.  
  9. OleDbConnection con = new OleDbConnection(constr);
  10.  
  11. OleDbCommand command = new OleDbCommand();
  12. command.CommandType = System.Data.CommandType.Text;
  13. command.CommandText = "group=ADMIN;object=Object";
  14. command.Connection = con;
  15. con.Open();
  16.  
  17. OleDbDataReader reader = command.ExecuteReader();
  18.  
  19. int columnCount = reader.FieldCount;
  20. while (reader.Read())
  21. {
  22. for (int i = 0; i < columnCount; i++)
  23. {
  24. Output.Items.Add(reader.GetName(i).ToString()+"= " + reader.GetValue(i).ToString());
  25. }
  26. }
  27.  
  28. reader.Close();
  29. con.Close();
  30. }
Did you try setting your CommandType to "Text" instead of "StoredProcedure"?
This PICK thingy may not have any concept of stored procedure.
Nov 13 '08 #8
These are great comments. I will try each one over the weekend.

That was my post on experts-exchange I believe when I needed help getting the connection to work and I solved it myself. But this is kind of crazy. I may just skip this exercise and move on.

If I ever needed to create a stored proc and use it I think I could get it to work. Just working against an unknown is hard.

I really appreciate you talking this through with me. The rest of the net is not only silent on PICK but they don't respond much on non SQL ADO.Net

I'll post soon. Thanks again.
Nov 14 '08 #9
I tried all your suggestions and none of them totally fixed the code. However I think I am now on the right track. Maybe you can confirm my thinking and the direction I think I need to pursue.

I ran a bunch of different commnd.CommandType.Text values and some gave me no error (but 0 still) and some gave me an error. I think "no error" is a dead end and I also think the IProc Interface error is the right direction. I believe this means that .Net is saying "Ok we are calling a Stored Procedure but I can't get the provider to hook me up."

As opposed to paramC is always zero because it is set to zero and never is acted on because .Net sees no procedure request????

Here's my Error list:

These did not give errors:
commnd.CommandText = "group=ADMIN;object=AdditionParams;name=Additi on";
commnd.CommandText = "group=ADMIN;object=Procs;name=Addition";
commnd.CommandText = "group=ADMIN;object=Procs;methodname=Addition" ;

These did give me errors:
commnd.CommandText = "group=ADMIN;MethodName=Addition"; X2Provider Error <Object not found>
commnd.CommandText = "group=ADMIN;object=Procs;proc=Addition"; X2Provider Error<Internal Error:Failed to get the IProc Interface>
commnd.CommandText = "proc=Addition"; X2Provider Error <Internal Error:Failed to get the IProc Interface>
commnd.CommandText = "group=ADMIN;object=ProcParams;name=Addition"; Could not find object <ProcParams>
X2Provider Error <Object not found>
commnd.CommandText = "group=ADMIN;object=AdditionParams"; Could not find object <AdditionParams ?, ?, ?> X2Provider Error <Object not found>
commnd.CommandText = "group=ADMIN;name=Addition"; Could not find object <> X2Provider Error <Object not found>
commnd.CommandText = "group=ADMIN;proc=Addition"; X2Provider Error <Internal Error:Failed to get the IProc Interface>

Do you have any idea what I could do to get "proc=Addition" to take?

Thanks so much!

John
Nov 25 '08 #10
mldisibio
190 Expert 100+
I am trying to be helpful, but I don't want to waste your time either, as I really don't see any obvious solution.
Honestly, if certain calls are giving you errors, then my conclusion is that the calls which do not give errors are syntactically correct. You are connecting, so that is not the issue, and when the syntax is incorrect, you get an exception, including "object not found" errors.

Just to be clear on all the tests:
- Set your Command object to CommandType.StoredProcedure and try the "commnd.CommandText = "group=ADMIN;object=Procs;name=Addition";"
- Set your Command object to CommandType.Text and try "commnd.CommandText = "group=ADMIN;object=Object;proc=Addition";"

One last suggestion: can you add a C# version of his "Illustration purposes loop". He calls cmd.Parameters.Refresh, which queries the database for the parameter types and names. You can then print the names and types to the Console or such. You would do this without adding the parameters yourself. It is a debugging procedure. Make sure they are the types you are expecting and the names you are using!

If all else fails, even if you don't have access to VB6, I believe you could write a classic ASP web page and make use of the old ADO libraries, to see if in fact you get a return value from your test environment using the old code. It would look very similar to his VB6 code.
Nov 25 '08 #11
This is not a waste of time for me. I am learning a ton about C# and ADO.Net

However I don't want to waste your time.

I did some foreach loops to gather parameter properties and during debugging the collection had no members so I'm not reaching the proc id on't believe.

I tried all execute methods ExecuteNonQuery, ExecuteScalar and ExecuteDataReader and eliminated the possiblity of the Execute method being the culprit in the failure to get IProc Interface.

I think I need to move on so don't bother responding unless you just have to tell me a new idea!

Thanks for all your help.
Nov 26 '08 #12
mldisibio
190 Expert 100+
Well, kudos to you for sticking in there and really trying. I feel frustrated too,that you couldn't solve it. But maybe if you drop it for a few weeks you may come across something else that sheds some light on what is missing here.

The cool part is that when anyone in the entire world does a Google on "ADPProv.ADPProv.1", they will find your post here. You're famous!

In any case, I hope eventually you get the breakthrough you need so that you can work with the language and ADO libraries of your choice.
Nov 26 '08 #13
Just wanted to post this because I gave up on C# and ADO.Net with this provider so I went at getting VS2008 to compile the VB6 code listed above.

I attached a screenshot of the references I had to add and the namespace I imported to make the project work. I also had to delete some error handling code with DoEvents() etc

I really wanted to use C# ADO.Net but I don't think the provider can handle it.

Oh well at least I got it to work!

Here's the code I ended up with:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit On
  2.  
  3. Imports Microsoft.VisualBasic
  4.  
  5. Public Class Form1
  6.  
  7.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  8.         Dim PageConn As ADODB.Connection
  9.         Dim PageCmd As ADODB.Command
  10.         Dim StrConnData As String
  11.         Dim StrCommand As String
  12.         Dim sHost As String
  13.         Dim sProd As String
  14.         Dim sServer As String
  15.         Dim sPasswd As String
  16.         Dim iVarA As Integer
  17.         Dim iVarB As Integer
  18.         Dim iSum As Integer
  19.         Dim ErrMsg As String
  20.         Dim ConnOpen As Boolean
  21.         Dim CmdOpen As Boolean
  22.         Dim ParamName As String
  23.         Dim ParamValue As String
  24.         Dim ParamDirection As String
  25.         Dim ParamType As String
  26.         Dim Param As ADODB.Parameter
  27.  
  28.         ' Check values have been entered
  29.  
  30.         If txtHost.Text = "" Then Exit Sub
  31.         If txtProd.Text = "" Then Exit Sub
  32.         If txtServer.Text = "" Then Exit Sub
  33.         If txtPasswd.Text = "" Then Exit Sub
  34.         If txtVarA.Text = "" Then Exit Sub
  35.         If txtVarB.Text = "" Then Exit Sub
  36.         '
  37.         ' Assign variables from the text boxes and check type
  38.         '
  39.         sHost = txtHost.Text
  40.         sProd = txtProd.Text
  41.         sServer = txtServer.Text
  42.         sPasswd = txtPasswd.Text
  43.         If IsNumeric(txtVarA.Text) Then
  44.             iVarA = CInt(txtVarA.Text)
  45.         Else
  46.             MsgBox("Variable A is not numeric", vbCritical)
  47.             Exit Sub
  48.         End If
  49.         If IsNumeric(txtVarB.Text) Then
  50.             iVarB = CInt(txtVarB.Text)
  51.         Else
  52.             MsgBox("Variable B is not numeric", vbCritical)
  53.             Exit Sub
  54.         End If
  55.         '
  56.         ' Open connection to DMS
  57.         '
  58.         ErrMsg = "Connection to " & sHost & ", " & sProd & "/" & sServer & " failed."
  59.         PageConn = CreateObject("ADODB.Connection")
  60.         PageConn.Provider = "ADPProv.ADPProv.1"
  61.         PageConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
  62.         PageConn.ConnectionTimeout = 0
  63.         StrConnData = "host=" & sHost & "; product=" & sProd & "; server=" & sServer & "; productpassword=" & sPasswd & "; pooling=FALSE; port=10223"
  64.         PageConn.Open(StrConnData, "", "")
  65.         ConnOpen = True
  66.         '
  67.         ' Execute Proc
  68.         '
  69.         ErrMsg = "Execute of Addition Proc failed."
  70.         PageCmd = CreateObject("ADODB.Command")
  71.         PageCmd.ActiveConnection = PageConn
  72.         PageCmd.CommandTimeout = 480
  73.         StrCommand = "proc=Addition"
  74.         PageCmd.CommandText = StrCommand
  75.         PageCmd.Parameters.Refresh()
  76.         '
  77.         ' This for loop is for illustration only
  78.         '
  79.         For Each Param In PageCmd.Parameters
  80.             Select Case Param.Direction
  81.                 Case 1
  82.                     ParamDirection = "In"
  83.                 Case 2
  84.                     ParamDirection = "Out"
  85.                 Case 3
  86.                     ParamDirection = "In/Out"
  87.                 Case Else
  88.                     ParamDirection = "Unknown"
  89.             End Select
  90.             '
  91.             Select Case Param.Type
  92.                 Case ADODB.DataTypeEnum.adBSTR
  93.                     ParamType = "String"
  94.                 Case ADODB.DataTypeEnum.adDecimal
  95.                     ParamType = "Number"
  96.                 Case ADODB.DataTypeEnum.adDate
  97.                     ParamType = "Date"
  98.                 Case ADODB.DataTypeEnum.adDBTime
  99.                     ParamType = "Time"
  100.                 Case Else
  101.                     ParamType = Param.Type
  102.             End Select
  103.             Select Case Param.Name
  104.                 Case "A"
  105.                     If ParamType = "Number" And IsNumeric(iVarA) Then
  106.                         PageCmd.Parameters(Param.Name).Value = iVarA
  107.                     End If
  108.                 Case "B"
  109.                     If ParamType = "Number" And IsNumeric(iVarB) Then
  110.                         PageCmd.Parameters("B").Value = iVarB
  111.                     End If
  112.             End Select
  113.         Next
  114.         '
  115.         ' Ignore above For loop and assign the values to add together
  116.         '
  117.         PageCmd.Parameters("A").Value = iVarA
  118.         PageCmd.Parameters("B").Value = iVarB
  119.         PageCmd.Parameters("C").Value = 0
  120.         PageCmd.Execute()
  121.         CmdOpen = True
  122.         iSum = PageCmd.Parameters("C").Value
  123.         txtSum.Text = CStr(iSum)
  124.         '
  125.         ' Wrap Up and exit
  126.         '
  127.         PageConn.Close()
  128.     End Sub
  129.  
  130.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  131.  
  132.         txtHost.Text = "207.109.69.61"
  133.         txtProd.Text = "TRAIN36"
  134.         txtServer.Text = "TEST1"
  135.         txtPasswd.Text = "TRAIN361"
  136.         txtVarA.Text = ""
  137.         txtVarB.Text = ""
  138.         txtSum.Text = ""
  139.  
  140.     End Sub
  141.  
  142.     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  143.         End
  144.     End Sub
  145.  
  146. End Class
Nov 27 '08 #14
I couldn't attach a jpg file. It kept telling me it was too big even at 8 kb!?!

I imported the namespace Microsoft.VisualBasic as you can see in the code.

I added these references in addition to the usual System ones used in Windows forms development including System.Data.

Reference Name Type Version
adodb .Net 7.0.3300.0
dao .Net 10.0.4504.0
Microsoft.VisualBasic.Compatibility .Net 8.0.0.0
Microsoft.VisualBasic.Compatibility.Data .Net 8.0.0.0

Thanks for all the help,

JD
Nov 27 '08 #15

Post your reply

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

Similar topics

reply views Thread by udo polder | last post: by
reply views Thread by Error while executing SP | last post: by
7 posts views Thread by Jeff Wang | last post: by
6 posts views Thread by Wojciech Wendrychowicz | last post: by
2 posts views Thread by Dino L. | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.