Solved

How to insert the string 'NULL' using Replace

Posted on 2008-10-27
13
413 Views
Last Modified: 2012-06-27
In Visual Basic 6.0

I have a String:

Dim sVal as String

sVal = "call_proc '" & sParam1 & "','" & sParam2 & "','" & sParam3

each param is also a string passed to the function

some of the string parameters might be empty, which might produce a value like this if, for example, sParam2 was empty:

result of concatenation:

call_proc 'value1', '', 'value2'

so far so good.

I want to then replace any resulting ,'', with ,NULL, so that the output would read like this:

call_proc 'value1', NULL, 'value2'

and I tried doing so as follows:

    Dim sNew As String
    sNew = Replace(sVal, "''", "NULL")

I tested this using a dummy string:

    sNew = Replace(sVal, "''", "HELLO WORLD")

and the replace happened as expected. However, when trying to replace with the actual string that reads NULL, I end up with no conversion, and the result reads:

call_proc 'value1', '', 'value2

So how do I use VB replace() method and actually insert the string that reads NULL, that is, the word, not any kind of VB Constant?

thanks



0
Comment
Question by:PMH4514
  • 6
  • 6
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22815175
your code seems right, though
so, you say that sVal, after the replace line, does not have the right data?

Dim sVal as String
sVal = "call_proc '" & sParam1 & "','" & sParam2 & "','" & sParam3
sVal = replace(sVal , "''", "NULL")

Open in new window

0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22815270
The only thing I see wrong with the code is that you have left off "'" at the end of the sVal assignment (and that way, if the third parameter is the NULL on, you don't get '' to replace).

Here's an example that is wording perfectly.  Create a test application and throw a commnad button and a Text box of the form and try the following code.
0
 

Author Comment

by:PMH4514
ID: 22815315
Yah I went and edited, incompletely, my post. That ending single quote is properly in place.  Sorry about that.

This is what I am seeing:

sParam1 = "one"
sParam2 = ""
sParam3 = "three"

Dim sVal as String
sVal = "call_proc '" & sParam1 & "','" & sParam2 & "','" & sParam3 & "'"
sVal = replace(sVal , "''", "NULL")

result:
sVal = "call_proc 'one', '', 'three'

however, if I do this instead:

sVal = replace(sVal , "''", "NULL2")

and all else is the same, I get:

sVal = "call_proc 'one', NULL2, 'three'

which is what I'd want, only NULL2 should read NULL






0
 

Author Comment

by:PMH4514
ID: 22815423
if it matters, the parameters are coming across from an ASP page, where they are "" in the HTML form
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22815647
I'm not experienced at getting data from ASP, but as a guess, perhaps you're getting some strange value that is perhaps functionally equivilent to a null character inside the single quotes and therefore the real string isn't two tick marks but two tick marks with "invisible" characters in between.

One suggestion that might work if the data coming back from ASP is effectively a NULL value should be the code snippet shown.

If the code snippet below doesn't work, try adding some debug code where you print out the ASC code number for every character in the string and see if there isn't something between the pair of ticks.
    sVal = "call_proc " & IIf(Len(sParam1), "'" & sParam1 & "'", "") & _

                          IIf(Len(sParam2), "'" & sParam2 & "'", "") & _

                          IIf(Len(sParam3), "'" & sParam3 & "'", "")

Open in new window

0
 

Author Comment

by:PMH4514
ID: 22815724
what is IIF ?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22815841
OK, so I'm showing examples from VB6.

IIF is a function where the 1st parameter is evaluated as true or false, if true, the 2nd parameter is returned, if false, the 3rd parameter is returned.  It's basically an IF as a function.

You can functionally do the same thing with three IF statements.
    sVal = "call_proc " 

    If Len(sParam1) then sVal = sVal & "'" & sParam1 & "'"

    If Len(sParam2) then sVal = sVal & "'" & sParam2 & "'"

    If Len(sParam3) then sVal = sVal & "'" & sParam3 & "'"

Open in new window

0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22815924
Opps, forgot to deal with commas AND the fact you want either the parameter or the word NULL.

So let's try this one more time.
sVal = "call_proc "
 

If Len(sParam1) Then

    sVal = sVal & "'" & sParam1 & "',"

Else

    sVal = sVal & "NULL,"

End If
 

If Len(sParam2) Then

    sVal = sVal & "'" & sParam2 & "',"

Else

    sVal = sVal & "NULL,"

End If
 

If Len(sParam3) Then

    sVal = sVal & "'" & sParam3 & "'"

Else

    sVal = sVal & "NULL"

End If

Open in new window

0
 

Author Comment

by:PMH4514
ID: 22815937
When I include this:

IIf(Len(sParam1), "'" & sParam1 & "'", "test")

The word test gets replaced.

if instead
IIf(Len(sParam1), "'" & sParam1 & "'", "NULL")

the Word NULL gets replaced, however it is enclosed in single quotes

I want the word NULL with no single quotes around it.


0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22815945
The shortened form using IIF() function would be as follows:
sVal = "call_proc " & IIf(Len(sParam1), "'" & sParam1 & "',", "NULL,") & _

                      IIf(Len(sParam2), "'" & sParam2 & "',", "NULL,") & _

                      IIf(Len(sParam3), "'" & sParam3 & "'", "NULL")

Open in new window

0
 

Author Comment

by:PMH4514
ID: 22816001
I'm using the shortened version and I end up with the word NULL enclosed in single quotes
0
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 125 total points
ID: 22816046
Make sure you are NOT using the Replace() function anymore, and only include the single quotes when appending sParam#.
The previous example I just did takes all of that, plus the need for commas, into account.
If a Parameter is NOT zero length, then the parameter is appended with single quotes.  If the Parameter IS zero length, NULL is appended withOUT single quotes.  And finally, for sParam1 and sParam2, a comma also gets appended.

The snippet below shows each piece of this broken out for you, but should be functionally the same as the previous example with the IIF to build the string in one long line of code.
sVal = "call_proc "
 

If Len(sParam1) Then

  sVal = sVal & "'" & sParam1 & "'"

Else

  sVal = sVal & "NULL"

End If
 

sVal = sVal & ","
 

If Len(sParam2) Then

  sVal = sVal & "'" & sParam2 & "'"

Else

  sVal = sVal & "NULL"

End If
 

sVal = sVal & ","
 

If Len(sParam3) Then

  sVal = sVal & "'" & sParam3 & "'"

Else

  sVal = sVal & "NULL"

End If

Open in new window

0
 

Author Comment

by:PMH4514
ID: 22816095
ok got it thanks! I forgot to pull the Replace() call
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 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