Type mismatch --> String and object 1

[Jokada]

I want to create a script that reads all user info of all users in my domain server and then puts it in a table in my sql server. the problem is i have to modify the object that i get. now when i want to write strings back to the DB i get an error saying type mismatch. So i have to cast a string back to an object but how do you do that??
the strings that i get from the objects are from the format
"sirname, forname"
"Location, kostenstelle, abteilung"

here's the code

MyCommand.CommandText = "INSERT INTO [Employee2] ([Username], [VoorNaam], [Achternaam], [Location], [Kostenstelle], [Abteilung], [Description]) VALUES(?,?,?,?,?,?,?)"
MyCommand.CommandType = 1

imyVar = 0

For Each oADobject In oDomain
strFullName = oADobject.FullName
iFullNamePos = InStr(strFullName, ",")
if (iFullNamePos = 0) then
strVoorNaam = trim(StrFullName)
strnaam = ""
else
iFullNamePos = iFullNamePos-1

strNaam = Left(strFullName, iFullNamePos )
strNaam = Trim(strNaam)
strVoorNaam = Right(strFullName, (len(strFullName) - iFullNamePos-1))
strVoorNaam = Trim(strVoorNaam)
end if

strFullDescription = oADobject.Description
iDescPos1 = Instr(strFullDescription, ",")
if (iDescPos1=0) then
strDescr = strFullDescription
strLocation = ""
strKostenStelle = ""
strAbteilung = ""
else
strDescr =""
iDescPos1 = iDescPos1 - 1

iDescPos2 = InstrRev(strFullDescription, ",")
iDescPos2 = iDescPos2 - 1

strLocation = Left(strFullDescription , iDescPos1)
strKostenStelle = Mid (strFullDescription, iDescPos1 + 3, (iDescPos2 - iDescPos1 - 2) )
strAbteilung = Right (strFullDescription, (len(strFullDescription) - iDescPos2 -2))
End If

MyCommand.Parameters(0).Value = oADobject.Name
MyCommand.Parameters(1).Value = strVoorNaam
MyCommand.Parameters(2).Value = strNaam
MyCommand.Parameters(3).Value = strLocation
MyCommand.Parameters(4).Value = strKostenStelle
MyCommand.Parameters(5).Value = strAbteilung
MyCommand.Parameters(6).Value = strDescr
MyCommand.Execute
imyVar = imyVar+1
Next

any help would be very appreciated

 

[tsuji]

Hello Jokada,

I try simplify the parsing. It could make problem easier to spot.

MyCommand.CommandText = "INSERT INTO [Employee2] ([Username], [VoorNaam], [Achternaam], [Location], [Kostenstelle], [Abteilung], [Description]) VALUES(?,?,?,?,?,?,?)"
MyCommand.CommandType = 1

imyVar = 0

For Each oADobject In oDomain
    strFullName = oADobject.FullName
    arrFullName = split(strFullName,",")
    if ubound(arrFullName)= 0 then
        strVoorNaam = Trim(arrFullName(0))
        strNaam = ""
    else
        strNaam = Trim(arrFullName(0))
        strVoorNaam = Trim(arrFullName(1))
    end if

    strFullDescription = oADobject.Description
    arrFullDescription = split(strFullDescription, ",")
    if ubound(arrFullDescription) = 0 then
        strDescr = Trim(arrFullDescription(0))
        strLocation = ""
        strKostenStelle = ""
        strAbteilung = ""
    else 
        strDescr =""
        strLocation = Trim(arrFullDescription(0))
        on error resume next
        strKostenStelle = Trim(arrFullDescription(1))
        if err.number <> 0 then
            strKostenStelle = ""
            strAbteilung = ""
            err.clear
        end if
        strAbteilung = Trim(arrFullDescription(2))
        if err.number <> 0 then
             strAbteilung = ""
        end if
        on error goto 0
    End If

    MyCommand.Parameters(0).Value = oADobject.Name
    MyCommand.Parameters(1).Value = strVoorNaam 
    MyCommand.Parameters(2).Value = strNaam 
    MyCommand.Parameters(3).Value = strLocation 
    MyCommand.Parameters(4).Value = strKostenStelle 
    MyCommand.Parameters(5).Value = strAbteilung 
    MyCommand.Parameters(6).Value = strDescr 
    MyCommand.Execute
    imyVar = imyVar+1
Next

regards - tsuji

 

[Jokada]

The stings retun properly but the real problem is here :

'this goes good because it's of the type object
MyCommand.Parameters(0).Value = oADobject.Name

' All the string values are not from the type Object
' so i get a error 'not a valid Type'
MyCommand.Parameters(1).Value = strVoorNaam
MyCommand.Parameters(2).Value = strNaam
MyCommand.Parameters(3).Value = strLocation
MyCommand.Parameters(4).Value = strKostenStelle
MyCommand.Parameters(5).Value = strAbteilung
MyCommand.Parameters(6).Value = strDescr

i've also tried
MyCommand.Parameters(1).Value = (oADobject.FullName = strVoorNaam )
this syntax was correct but then my database gets populated with 0, 1, -1

greets
jokada

 

[tsuji]

Jokada,

The description of the problem is still not very clear. But I look at the other part of the script. I might now have an idea of why it went wrong.

The parameters are 1-based rather than 0-based. Hence, it is Parameters(1), Parameters(2),..., Parameters(7). Test it out.

- tsuji

 

[Jokada]

i've found it IT WORKS


If UBound(arrFullName)= -1 Then
strVoorNaam = ""
strNaam = ""
ElseIf UBound(arrFullName)= 0 Then
strVoorNaam = ""
strNaam = ""
strDescr = Trim(arrFullName(0))
Else
...
End If

If UBound(arrFullDescription) = -1 Then
strDescr = ""
strLocation = ""
strKostenStelle = ""
strAbteilung = ""
ElseIf UBound(arrFullDescription) = 0 Then
...
Else
...
End If

MyCommand.Parameters(0).Value = oADobject.Name
MyCommand.Parameters(1).Value = strVoorNaam
...
Next


If the object = "" ubound returns -1.... that was all...
Many thanks!!!!!!!!!!!!!!!!!!!!!!!

Now i have to convert it to an update script that runs every day... (every day I already found)
if any value is changed in the domain(new user, deleted user, changed information), it should do INSERT Record, DElETE Record Or UPDATE record Or do nothing
I haven't got a clue how to program this
Can anyone give me a start how I should write this??

many thanks

 

[tsuji]

Jokada,

So my last posting is wrong, afterall!? My documentation reads "Each parameter marker is referenced by its ordinal number, assigned to the parameters from left to right. The leftmost parameter marker in an SQL statement has an ordinal value of 1, the next one is ordinal 2, and so on." I've to look into it myself.

Glad you sort your problem out. Still wonder why a ubound()=-1 ever results!

- tsuji

 

[Jokada]

in my experience Ubound returns -1 when the Split Function was executed on an empty string
<code>
dim strtest
dim result
dim arrFullName

strtest =""
arrFullName = split(strtest,",")
result=ubound(arrFullName)

msgbox result
</code>

Cheers

 

[tsuji]

Jokada,

Yes, in that case would it not be empty for every account? as you have nowhere explicitly get fullname to cache. Your demo is good though.

- tsuji