How to add "null" to a String object

[Guest]

I am building a SQL statement for Oracle and need one part of it to read:

, myvar = null, myvar2 = "1", myvar3 = "0", myvar4 = null,

etc.

I am constructing this string using the String object with the following code:

theSQL.Append(" , myvar = " & FormatValue(MyClass.DB_CHAR1, Me.MyVar))

The portion of the FormatValue function that returns the value is (outValue
is the passed variable):

If IsNumeric(outValue) OrElse outValue = Nothing Then
If outValue < 0 Then outValue = vbNull '****** this is the important
part
Else
outValue = "'" & UCase(valueValue) & "'"
End If

return outValue

Whether I use the statement above or "null" or "'null'" or "''" or ""
doesn't matter, outValue return "nothing" (without the quotes) and the string
looks like this:

, myvar = , myvar2 = "1", myvar3 = "0", myvar4 = ,

which, of course, caused the SQL statement in Oracle to fail.

Any ideas on how to get the string object to accept the word "null"?

Thanks,
Don

 

[Guest]

Try this:

If outValue Is Nothing OrElse (IsNumeric(outValue) AndAlso outValue < 0)
Then outValue = "null"

However a more standard approach would be to use the SqlParameters
collection; then you can assign DBNull.Value to the parameter value if the
object was nothing.
http://msdn2.microsoft.com/en-us/library/4wce29x9(en-US,VS.80).aspx

 

[DKode]

just a suggestion, you should try not to use a string to build your sql
query, this is a pretty big security problem. what happens if someone
injects a sql statement into one part of your variables. they could
then drop databases, delete databases, add themselves to access the
database.

its bad practice.

other than that, i dont know the answer to your question. sorry

maybe try <null>? just a thought

 

[DKode]

just a suggestion, you should try not to use a string to build your sql
query, this is a pretty big security problem. what happens if someone
injects a sql statement into one part of your variables. they could
then drop databases, delete databases, add themselves to access the
database.

its bad practice.

other than that, i dont know the answer to your question. sorry

maybe try <null>? just a thought

 

[DKode]

just a suggestion, you should try not to use a string to build your sql
query, this is a pretty big security problem. what happens if someone
injects a sql statement into one part of your variables. they could
then drop databases, delete databases, add themselves to access the
database.

its bad practice.

other than that, i dont know the answer to your question. sorry

maybe try <null>? just a thought

 

[Guest]

Thanks, but that doesn't work in this case:

Solution 1:
The problem is not in getting to the outValue = "null" code. It get there
fine. The problem is that even though it hits that statement, the value of
outValue (in the watch window) is still nothing. It is evaluating it as
nothing instead of a string that contains the word "null".

Solution 2:
I use this method in many places, but in this instance, the StringBuilder is
a better solution. However, I may have to revert to that if I can't find a
solution here.

Thanks for your input,
Don