Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBscript Array Question

Posted on 2004-09-28
5
Medium Priority
?
652 Views
Last Modified: 2008-03-10
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
Comment
Question by:stretch73
[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
  • 3
5 Comments
 

Expert Comment

by:WebBilly
ID: 12169544
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
 
LVL 15

Accepted Solution

by:
joeposter649 earned 600 total points
ID: 12169549
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
 

Author Comment

by:stretch73
ID: 12172349
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
 

Author Comment

by:stretch73
ID: 12172431
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
 

Author Comment

by:stretch73
ID: 12172896
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

670 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