assigning null value to a variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

assigning null value to a variable

Author

Topic

alpoor
Starting Member

29 Posts

Posted - 2005-07-26 : 12:08:30

How can I assign a null value to a varaible which is declared as Dim var1 as String and then use that variable to insert into sql server database table

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-07-26 : 12:14:49

In VB.Net

Var1 = nothing

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-26 : 12:28:08

Use
DBNull.value
for inserting nulls into a db.

Go with the flow & have fun! Else fight the flow

alpoor
Starting Member

29 Posts

Posted - 2005-07-26 : 14:11:06

This is inserting spaces instead of NULL in the table.

Any ideas please?

quote:
Originally posted by MichaelP

In VB.Net

Var1 = nothing

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-26 : 14:13:40

DBNull.Value is used for inserting proper nulls into a database.

Go with the flow & have fun! Else fight the flow

alpoor
Starting Member

29 Posts

Posted - 2005-07-26 : 14:17:36

My requirement is to assign a null value to the vb.net variable which is declared as Dim st1 as String and pass this varible to a stored procedure which inserts a record with null value

Thanks
Alpoor Reddy

quote:
Originally posted by spirit1

Use
DBNull.value
for inserting nulls into a db.

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2005-07-26 : 14:31:49

String.Empty

Refers to an empty string in vb.net, alternatively you can set your parameter to null in your stored procedure.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-26 : 15:07:54

String variables cannot be Null in .NET. They can be equal to String.Empty, or "", as Jon mentions, but that is not the same thing as NULL.

If you do not have the need to store "" values in the DB, then you can take all strings equal to that value and pass it in to the DB as DBNull, but you will have to explicitly check for "" and explicitly set the parameter value in that case to DBNull.value.

If you do have the need to store both "" and NULL values in your columns (not recommended), then you may need to decide upon a standard value for the string that will be caught and translated into NULL, such as "<--NULL-->" or something like that.

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 01:36:08

When inserting var1, use this if possible

iif(trim(var1)="",Null,var1)

Madhivanan

Failing to plan is Planning to fail

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 02:48:42

>>String variables cannot be Null in .NET

Are you sure Jeff? I thought a string was a reference type (although the equality operator acts as if it is a value type) and therefore a variable of type string can be null. Value types (DateTime, Guid, int, structs etc..) cannot be null.


string s = null;//OK
int i = null;//BAD

DavidM

A front-end is something that tries to violate a back-end.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 09:01:15

Ah, yes -- you are correct! With datetimes or numeric datatypes, you cannot do this, but with Strings you can indeed set it to null. Of course, this isn't the same as a DBNull, so you will still need to explicitly check to see if the string variable is null (or I think that is the same as "Nothing" in VB.NET, as Michaeal points out) and then you will have to explicitly set the parameter to DBNull.value.

Of course, we have to remember that

string s = Null

really means that "the string variable s doesn't reference anything" rather than "the value of the string variable s is equal to Null". An imporant distinction, since code that accesses a method or property of the variable, such as:

s.ToString()

it will throw an exception if s is set to Null.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources