Solved

VBscript Array Question

Posted on 2004-09-28
5
651 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 150 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

636 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