Set a NULL value within a string declaration?

  • Thread starter Thread starter Rich Wallace
  • Start date Start date
R

Rich Wallace

Hi all,

I have the following string declaration/build that I need to pass to a SQL
Server 7.0 system.

Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date " & _
"'" & sLot & "', '" & sCostCenter & "', '" & _
dEstComp & "', '" & dTrenchDt & "', '" & dSalesRslDt & "'"

Sometimes I will receive a NULL value as one of my dates. I've looked over
the DBNull.Value but it looks like I would have an issue setting a NULL
value inside a string like I'm dong here.

I'm already in production so I can't make any major changes at this point so
can anyone give me an idea of how to do this??

TIA
-Rich
 
Rich Wallace said:
Hi all,

I have the following string declaration/build that I need to pass to a SQL
Server 7.0 system.

Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date " & _
"'" & sLot & "', '" & sCostCenter & "', '" & _
dEstComp & "', '" & dTrenchDt & "', '" & dSalesRslDt & "'"

Sometimes I will receive a NULL value as one of my dates. I've looked over
the DBNull.Value but it looks like I would have an issue setting a NULL
value inside a string like I'm dong here.

I'm already in production so I can't make any major changes at this point so
can anyone give me an idea of how to do this??
Click to expand...

This is one of the many reasons you should never concatanate parameter
values into SQL statements.

It should look something like this:

Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date @Lot, @CostCenter,
@EstComp, @TrenchDt, @SalesRslDt"
dim cmd as new SQLCommand(sInsUpdLotDate, conn)
cmd.parameters.add("@Lot",sLot)
cmd.parameters.add("@CostCenter",sCostCenter)
cmd.parameters.add("@EstComp",dEstComp)
cmd.parameters.add("@TrenchDt",dTrenchDt)
cmd.parameters.add("@SalesRslDt",dSalesRslDt)
cmd.ExecuteNonQuery()


David
 
Understand and thank you , but as stated, I can't go in and make this change
at the current phase of development. Do I just have to do some ugly
IF...Else junk to do this for now? I'll definitely keep your suggestion in
mind for 'Version 2' but kind of at a dead end for now.
 
Also, where would I put in the logic to provide the DBNull.Value for the
parameter if I need to pass the NULL?

Thanks again.
 
Thanks for the reply. Here's a mock up of what I think would need to
happen, so if you can tell me if this looks right, I'd appreciate it.

Based on earlier code provided by David Browne:
Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date @Lot, @CostCenter,
@EstComp, @TrenchDt,
@SalesRslDt"
dim cmd as new SQLCommand(sInsUpdLotDate, conn)
cmd.parameters.add("@Lot",iif(IsDBNull(sLot), DBNull.Value, sLot)
....
cmd.ExecuteNonQuery()

Is this enough or should I attack it differently?
 
Rich Wallace said:
Thanks for the reply. Here's a mock up of what I think would need to
happen, so if you can tell me if this looks right, I'd appreciate it.

Based on earlier code provided by David Browne:
Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date @Lot, @CostCenter,
@EstComp, @TrenchDt,
@SalesRslDt"
dim cmd as new SQLCommand(sInsUpdLotDate, conn)
cmd.parameters.add("@Lot",iif(IsDBNull(sLot), DBNull.Value, sLot)
Click to expand...

Rich, how did you get the value into sLot? How is it declared? Are you
running with Option Strict On?

You should try it without the iif first. Just:

cmd.parameters.add("@Lot",sLot)
 
Rich Wallace said:
Thanks for the reply. Here's a mock up of what I think would need to
happen, so if you can tell me if this looks right, I'd appreciate it.

Based on earlier code provided by David Browne:
Dim sInsUpdLotDate as string = "EXEC ins_upd_lot_date @Lot, @CostCenter,
@EstComp, @TrenchDt,
@SalesRslDt"
dim cmd as new SQLCommand(sInsUpdLotDate, conn)
cmd.parameters.add("@Lot",iif(IsDBNull(sLot), DBNull.Value, sLot)
...
Click to expand...

if sLot is DBNull.Value, then you can just add it.

cmd.parameters.add("@Lot",sLot)

if sLot is the empty string or something then you have to use IIF

cmd.parameters.add("@Lot",iif(sLot= "", DBNull.Value, sLot))

if sLot is declared as a string, then it can never be a DBNull, same with
the dates.

David
 
Thank you both...

Just out of curiousity, for future reference. If the value is a string data
type, but it comes in as an empty string "" and I need to pass a NULL if it
is empty, what do I do then?

-Rich
 
But according to David, a string can never be a NULL, or did I misunderstand
what he was saying?
 
Rich Wallace said:
But according to David, a string can never be a NULL, or did I misunderstand
what he was saying?
Click to expand...

You never showed us the declarations for your local variables.

Dim s as String
s = dbNull.value 'won't work

DBNull is a type, String is a different type, not related to or convertable
to DBNull.

dim s as Object
s = dbNull.Value 'will work

David
 
Rich Wallace said:
But according to David, a string can never be a NULL, or did I misunderstand
what he was saying?
Click to expand...

David may have been assuming you were programming with "Option Strict On". I
was assuming you were not.

It wouldn't surprise me to find you had managed to get a DBNull.Value into
sLot. That's why I asked you how it was declared and how you got the value
into it.
 
Rich,

Here's a suggestion that may work while you're waiting to
move to parameterized queries. If you want to pass a Null value
to a stored procedure in a simple T-SQL query, your query would
look like:

EXEC MySproc 'SomeValue', NULL, 1

Perhaps you could re-write your string concatenation code to
take valid date values and surround them with the ' delimiters,
but return just "NULL" for null values.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top