Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

what is best way to search stringbuilder text for Is Nothing

Posted on 2010-11-18
7
Medium Priority
?
382 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 1000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

661 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