Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

VBscript Array Question

This should be pretty simple, I'm trying to insert the contents of a tab delimited text file into a SQL database.  I read each line of the file into an array row, assign variables to each column, and then insert.  The problem is that if there's no value in the array column, it blows up.  Here's the chunk of code:

do while not objTextStream.AtEndOfStream
      strLine = objTextStream.ReadLine
      if blnFirstRow = false then
            'organize each line of text into an array column      
            arrColumns = Split(strLine, Chr(9))
            lngUbound = ubound(arrColumns)

            strLname = FormatCheck(arrColumns(0))
            strFname = FormatCheck(arrColumns(1))
            strAddress = FormatCheck(arrColumns(2))
            strCity = FormatCheck(arrColumns(3))
            strState = FormatCheck(arrColumns(4))
            strZip = FormatCheck(arrColumns(5))

            strSQL = "INSERT into tblemployees2(fldlname, "
            strSQL = strSQL & "fldfname, "
            strSQL = strSQL & "fldaddress, "
            strSQL = strSQL & "fldcity, "
            strSQL = strSQL & "fldstate, "
            strSQL = strSQL & "fldzip)"
            strSQL = strSQL & "VALUES( "
            strSQL = strSQL & "'" & strLname & "', "
            strSQL = strSQL & "'" & strFname & "', "
            strSQL = strSQL & "'" & strAddress & "', "
            strSQL = strSQL & "'" & strCity & "', "
            strSQL = strSQL & "'" & strState & "', "
            strSQL = strSQL & "'" & strZip & "')"

            objConn.execute(strSQL)
      end if
      blnFirstRow = false
loop

I've tried a bunch of different stuff but I can't figure out how to validate the array columns.  

Thanks in advance,

N
0
stretch73
Asked:
stretch73
  • 3
1 Solution
 
WebBillyCommented:
Humm i have had a bit of ecsperance with this sort of thing but not quite what you are after. Is it possable to lay it out in a different form
0
 
joeposter649Commented:
Wat error are you getting?
You could try something like this...

strSQL = strSQL & "VALUES( "
if len(strLname) > 0 then
  strSQL = strSQL & "'" & strLname & "', "
else
  strSQL = strSQL & "null, "
end if
.
.
.
0
 
stretch73Author Commented:
Sorry to take so long to respond, long morning meeting.  The error I'm getting is:

Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '[number: 3]'

and it is in regards to:

strCity = FormatCheck(arrColumns(3))

so I'm not even getting to the part where I can check the length of the variable.

N
0
 
stretch73Author Commented:
I know what the problem is and I don't know if it can be fixed.  When the array is created, it only fills the slots where there are actual values.  So if the text line only has last name, first name, and city, then the array will only have three values.  I had it stuck in my head that it would always have six.  Any thoughts?
0
 
stretch73Author Commented:
JP,

I found a workaround based on your suggestion:

            for x = 0 to lngUbound
                  if arrColumns(x) = " " then
                        arrColumns(x) = "N/A"
                  end if
            next

Thanks,

N
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now