INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

"Expected procedure not variable" error when trying to call a procedure from a database lo

"Expected procedure not variable" error when trying to call a procedure from a database lo

"Expected procedure not variable" error when trying to call a procedure from a database lo

(OP)
We receive a number of similar but slightly different files from our vendors that need to be massaged into a common format for transmission to yet another vendor for processing. My predecessor wrote a new VB6 program each time a new input file format was encountered, with the expected maintenance nightmare as a result. I have been asked to combine all these programs. I thought it would be simple since they all stem from the same root with all of the actual processing done within a single module. These modules are all very similar with the differences being mainly to handle differences in the order of the columns in the input files and the account numbers needed in the output. I was able to condense the various processing modules down into just three. My plan was to build a drop-down from which to select the vendor the input file is from. This triggers a database lookup where the account parameters and the name of the correct processing module are read into variables. I then want to call the processing module and pass it the account number variables. My problem is that this returns an error "expected procedure not variable". I typed my variable as Procedure but this did not work. Is there any way I can do what I want? It seemed such a good idea at the time.

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

You might want to have a look at VB's CallByName function.

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

On average, what is the same of the files you are importing? Also, what database engine are you using?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

Maybe you have inadvertently used the same name for a variable and a subroutine?
Eg Sub GetFirstName(Index as Integer)
then and later on used Dim GetFirstName as String: GetFirstName = "Smith"

Or else used a VB reserved word for a Variable name like Function = 200 ?

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

You could also get it to automatically do it by finding what was unique to each type then trying a write that would cause errors then use the errors to determine which type it was.
You can read myRec!CustomerName irrespective of the order if the fields.
If the input field names are spelt differently or don't exist, trying to read each type possibility with each of the known possibilities will soon tell you which is the correct type, giving you a trappable error on the unmatched ones.

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

What the OP is asking is to be able to call a procedure using a name contained in a variable. Not quite sure what question the rest of you are answering ...

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

I understood the question.

In my experience, it is usually many times faster (and in my opinion, easier) to handle this sort of thing within the database. Basically, import to a generic table and then parse in to individual table(s) based on the data you find.

For example, suppose you are trying to import "people". You have Id, Name, ShoeSize and EyeColor. Someone gives you a file with the data in that order, but someone else gives you a file with id, name, EyeColor, ShoeSize (the last two columns are transposed).

You could create a table with 4 string columns, import the data, do data type validation, and then copy the data to the real table.

I asked about the size of the file because at 100's of megabytes the database option starts to become slower because of the dynamic sql executions. I also asked about the database engine, because if it was Microsoft SQL Server, I could probably help a lot.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

All that may be true, George, but that's not an answer to the question being asked, and your response gave no indication that you had decided to answer a completely different question, which would boil down to 'what is the best way for me to massage my data into a common format?'.

RE: "Expected procedure not variable" error when trying to call a procedure from a database lo

How about usiong the name from the dropdown list to directly select the appropriate procedure for each database type
Eg
Sub Dropdownlist_Change() or whatever

Select Case DropDownValue
Case "Blogs&Co"
Subroutine1

Case "SmithIndustries"
Subroutine2

Case "OurDatabase"
Subroutine3

End Select

End sub

If you can derive a different number interrogating each database (using error trapping as I previously suggested) you could use the number produced instead of Dropdownvalue text to avoid the dropdown list. (Case 1, Case 2, Case 3 etc)

To enable future different database formats to be added without future modifications to software being necessary, you could save the 'formats' on a separate text file and create the text Criteria for your queries from that text file.
Then instead of different procedures, use the same procedure for all (only select or change the query criteria text file instead).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close