PMH4514
asked on
How to insert the string 'NULL' using Replace
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
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
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.
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.
ASKER
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
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
ASKER
if it matters, the parameters are coming across from an ASP page, where they are "" in the HTML form
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.
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 & "'", "")
ASKER
what is IIF ?
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.
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 & "'"
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.
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
ASKER
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.
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.
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")
ASKER
I'm using the shortened version and I end up with the word NULL enclosed in single quotes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok got it thanks! I forgot to pull the Replace() call
so, you say that sVal, after the replace line, does not have the right data?
Open in new window