Pass a null value to SQL in VB.NET 1

[amberlynn]

Hello,
I'm not sure if this question should go in the SQL forum or this one...

I'm using VB.NET and SQL Server.
I'm using stored procedures to update and create new records in my database.
On the form I'm working on, I have 2 date fields.
I want the user to have the option of leaving these blank.
I do not want default values.
I'm getting error messages not matter what I try.
What syntax can I use to get VB to pass this null value instead of a date, into SQL??

Thanks!
amber

 

[lizzieborden]

What are the error messages? Can you go into table design in sql and say except nulls on that date field?

 

[amberlynn]

The error message is:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

In SQL, the date fields are set to accept null values.
Null values already exist in the database, I just can't update a record or add a new record with a null value.

Thanks!
Amber

 

[RiverGuy]

Try DBNull.Value

 

[amberlynn]

If I use the code:
cLP.DateApproved = Convert.DBNull.value

I get the error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

Amber

 

[chiph]

What datatype is cLP ?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[RiverGuy]

It doesn't make sense to me. If you are using a stored procedure, you should be setting the value of its parameter. I have no idea what cLP is. Is it the name of your table? If it is, and you are just sending text as a sql statement, you can use the text NULL [/n] within the text of your sql statement.

 

[amberlynn]

sorry...I assumed it would be obvious...

cLP is a custom class I created.
cLP.dateApproved is the date field I'm trying to set to Null.

I'm using a stored procedure to pass a "cLP" into SQL.
All works great except the date issue.

amber

 

[RiverGuy]

That is the answer to your question right there. Your class is strongly-typed--it has to be a DateTime datatype if that is what you defined it as. I would set it to NOTHING if you are going to use a NULL value. Then, when you insert the value into your database, check to see if it is nothing, and pass the appropriate value into the database.

 

[dmorse]

I am doing something very similar in VB6. However, I have a date already in there and I need to be able to clear the date. Even setting it to nothing, I am getting a data mismatch. The field in the SQL table is date time and it is marked as nullable. Any suggestions?

and TIA

 

[RiverGuy]

Did you intend to ask this in the VB 6 forum? This is the VB.Net forum.

Anyways, we (or those in the VB 6 forum) would need the specifics of your situation: are you using a custom class like the poster above? Are you using an ADO command, or just setting field values in your ADO recordset?

 

[dmorse]

Sorry about that, I did a search in the SQL forum (I thought). I will repost. Thanks for your quick response.