?
Solved

MID fuction difference form VB to VBScript

Posted on 2004-11-05
7
Medium Priority
?
781 Views
Last Modified: 2012-05-05
Below are 4 functions. ParseFname, ParseMName, and ParseLName came from an excel spread sheet where I parse out the name from one field that has all three names in it. It works fine In Excel. I have taken this code and put it into a DTS packeage and modified the function Main but I get an error saying my mid function is incorrect or the wrong amount of arguments.
HELP




Function Main()
     
     DTSDestination("FirstName") = ParseFName(DTSSource("NAME"))
     DTSDestination("MiddleName") = ParseMName(DTSSource("NAME"))
     DTSDestination("LastName") = ParseLName(DTSSource("NAME"))
     Main = DTSTransformStat_OK
End Function

Function ParseFName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseFName = Trim(FName)


Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseFName = Trim(FName)

End If

End Function

Function ParseLName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseLName = Trim(LName)

Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseLName = Trim(LName)


End If

End Function


Function ParseMName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseMName = Trim(MName)

Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseMName = Trim(MName)


End If

End Function
0
Comment
Question by:jmoneilthe2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12507476
The length in the Mid function is not optional.  In other words it should be:
Mid(Data, Offset, Length)
0
 

Author Comment

by:jmoneilthe2
ID: 12507520
Yes. I have three arguments in my mid. I just can't seem to see why this works in VB but not in my VBScript (DTS)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12510133
Please see here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Accepted Solution

by:
ala_frosty earned 2000 total points
ID: 12516145
In SQL, you need to use 'substring' rather than 'mid'. It doesn't look like you're using any SQL here though, so maybe this has nothing to do with it...

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12517645
>>It doesn't look like you're using any SQL here though<<
Correct.  The questioner is using VBScript in a DTS package.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12519339
If you pass a string containing a space before the comma into your ParseMName function, you get an "Invalid procedure call or argument" error. You need to be a bit more careful with the possible strings that can be passed in. Consider using the optional first parameter with InStr to specify the start of the search, so that:

SpacePos = InStr(FullName, " ")

becomes:

SpacePos = InStr(CommaPos, FullName, " ")
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12587646
Sorry, I don't quite understand why you chose the answer you did. I apologise if I misread your question, but I don't think you were asking about SQL, rather VBScript in a DTS package?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

621 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