Solved

what is best way to search stringbuilder text for Is Nothing

Posted on 2010-11-18
7
379 Views
Last Modified: 2012-05-10
I am writing this code is my SSIS package.  I need to execute a stored procedure in the midst of it.  I know I can test each variable for Is Nothing but there has to be a shorter way and I don't know how to do it.  

MyFields(3).ToString) sometimes has Nothing for a value as well as some other fields, so when I try to insert this into the stringbuilder it errors out.  I'd like to use a vb.net build in function or build one to make my code shorter.  

any ideas?   thanks for your help

here is code:
 While (oneLine <> "")
                    oneLine.Split(txtDelimiter)
                    Dim MyFields(10) As String
                    MyFields = Split(oneLine, txtDelimiter)

                    '  CREATE Update STATEMENT - START
                    Dim sbQueryString As New StringBuilder

                    sbQueryString.Append(" exec sp_uTable1 ")
                    sbQueryString.Append(" @vchTrackingNumber='" & Trim(MyFields(0).ToString) & "', ")
                    sbQueryString.Append(" @dtActualDeliveryDate=" & (Trim(MyFields(2).ToString) & "', ")

                    sbQueryString.Append(" @vchSignature=" & (Trim(MyFields(3).ToString) & "', ")
                    sbQueryString.Append(" @vchStatusCode='" & Trim(MyFields(8).ToString) & "', ")
                    sbQueryString.Append(" @vchStatusDesc='" & Trim(MyFields(9).ToString) & "', ")
                    sbQueryString.Append(" @dtStatusDate='" & Trim(MyFields(10).ToString) & "', ")
                    sbQueryString.Append(" @vchToteId='" & Trim(MyFields(4).ToString) & "', ")
                    sbQueryString.Append(" @vchStoreInvoiceNumber='" & Trim(MyFields(6).ToString) & "', ")
                    sbQueryString.Append(" @Co_PONumber='" & Trim(MyFields(7).ToString) & "', ")
                    '  CREATE Update STATEMENT - END

                    sqlcmd.CommandText = sbQueryString.ToString()
                    sqlcmd.Connection = sqlcon
                    sqlcmd.ExecuteNonQuery()

                    oneLine = ReadFile.ReadLine()  ' Is there another row of data to process?

                    If oneLine IsNot Nothing Then
                        strInsertStatement = oneLine.ToString
                    End If

                End While
0
Comment
Question by:purdyra1
  • 4
  • 2
7 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 250 total points
ID: 34164906
One option is

Replace

MyField3.ToString

with

IFF(String.IsNullOrEmpty(MyField3), "", MyField3)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34164942
The other option is to add an extension method to a module in the project


<Runtime.CompilerServices.Extension()> _
    Public Function MyToString(ByVal s As String) As String
        If Not String.IsNullOrEmpty(s) Then
           Return s
        Else
            Return ""
        End If
    End Function

Open in new window

0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 34164987
Since MyFields is already a string array, you do not need .ToString to convert it.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34165157
That reminds me to correct myself

IFF(String.IsNullOrEmpty(MyField(3)), "", MyField(3))

or

MyField(3).MyToString()
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 34165183
@CodeCruiser, it's IIF not IFF. :=)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34165201
Oops. Yeah. Thanks for correction :-)
0
 

Author Comment

by:purdyra1
ID: 34168374
With the help of CodeCruiser I changed my code to do as below. thanks.

sbQueryString.Append(" @dtActualDeliveryDate=" & CStr(IIf(String.IsNullOrEmpty(MyFields(2)), "NULL,", "'" & MyFields(2) & "', ")))
sbQueryString.Append(" @vchSignature='" & CStr(IIf(String.IsNullOrEmpty(MyFields(3)), "',", MyFields(3) & "', ")))
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

830 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