Solved

VBscript Array Question

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query still returning duplicates 5 50
PHP Form Calculate Total Price 10 121
WordPress 8 54
calculate days away 11 24
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

749 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