Solved

what is best way to search stringbuilder text for Is Nothing

Posted on 2010-11-18
7
377 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
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: …

932 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

9 Experts available now in Live!

Get 1:1 Help Now