Solved

Run Time Error'-2147217839(80040e51)':The provider cannot derive parameter info and SetParameterInfo has not been called.

Posted on 2003-11-08
6
1,413 Views
Last Modified: 2007-12-19
Hi there,
            I am developping a datbase application in VB.I am using stored procedure for accesiing some data from database.The database is in MS Access
& using a DSN.
            Iam using command & recordset object of adodc.I have an Item_Master table from where I want some details through stored procedure.The stored proc name is "Get_Max_Item_Code" & it has 3 parameters named SOBID,CATCODE,SUBCATCODE .When I set value for these parameters in a sub a got the above mentioned error message.This stored proc was called after another stored procedure which has only one parameter & it was executed without any peroblem.The code segment inwhich problem arrived is:

Public Sub Gen_New_Item_Code()
    Dim Rs As New ADODB.Recordset
    Cmd.CommandType = adCmdStoredProc
    Cmd.CommandText = "Get_New_Item_Code"   'STORED PROCEDURE NAME


    Cmd.Parameters("SOBID") = (Txt_Item(0).Text) 'SOB ID (here is the problem)
    Cmd.Parameters("CATCODE") = Cmb_Item(3).Text  'CATEGORY CODE
    Cmd.Parameters("SUBCATCODE") = Cmb_Item(4).Text  'SUB CATEGORY CODE
    Set Rs = Cmd.Execute()
 
I don't know what to do with this problem.If any one has answer to it ,pls send as soon as possible.
0
Comment
Question by:prem_anish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 30

Accepted Solution

by:
Mayank S earned 50 total points
ID: 9712693
Try using the CreateParameter method of the Command object, as:

Parameter adParam = Cmd.CreateParameter ("SOBID", ....) ' other arguments include parameter-type, size, etc - you can refer to any documentation (or the MSDN help) for this
Cmd.Parameters.Append adParam

Hope that helps.

Mayank.
0
 
LVL 2

Assisted Solution

by:diarmaid
diarmaid earned 50 total points
ID: 9721580
Try adding a .refresh on the commamd object before trying to set the parameters. (I take it the cmd object is defined and set before this procedure as its not set here.) This works with VB + SQL but im not 100% sure about Access.

Public Sub Gen_New_Item_Code()
   Dim Rs As New ADODB.Recordset
   Cmd.CommandType = adCmdStoredProc
   Cmd.CommandText = "Get_New_Item_Code"   'STORED PROCEDURE NAME

   Cmd.Refresh 'This should retrieve the parameter info from the store proc.

   Cmd.Parameters("SOBID") = (Txt_Item(0).Text) 'SOB ID (here is the problem)
   Cmd.Parameters("CATCODE") = Cmb_Item(3).Text  'CATEGORY CODE
   Cmd.Parameters("SUBCATCODE") = Cmb_Item(4).Text  'SUB CATEGORY CODE
   Set Rs = Cmd.Execute()
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10751672
Please proceed with that recommendation.
0
 
LVL 2

Expert Comment

by:diarmaid
ID: 10755993
ok by me
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question