Solved

what is best way to search stringbuilder text for Is Nothing

Posted on 2010-11-18
7
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

628 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