Solved

VBscript Array Question

Posted on 2004-09-28
5
645 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
  • 3
5 Comments
 

Expert Comment

by:WebBilly
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem to be resolved in this article Currently, development of website and web application can be done without writing thousands of lines of programming code by hand. Description This can be done through by using a open source framework such …
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now