Solved

what is best way to search stringbuilder text for Is Nothing

Posted on 2010-11-18
7
374 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
Comment Utility
One option is

Replace

MyField3.ToString

with

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

Expert Comment

by:CodeCruiser
Comment Utility
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
Comment Utility
Since MyFields is already a string array, you do not need .ToString to convert it.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
That reminds me to correct myself

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

or

MyField(3).MyToString()
0
 
LVL 17

Expert Comment

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

Expert Comment

by:CodeCruiser
Comment Utility
Oops. Yeah. Thanks for correction :-)
0
 

Author Comment

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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:…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

10 Experts available now in Live!

Get 1:1 Help Now