January 7, 2009 at 12:41 pm
I am currently using SQL Server 2008 and VB .Net as my front end. The issue that I am encountering could be simple to resolve but I have been struggling to find the correct answer. In my VB app I have several date and numeric fields that I am not able to get them to save at the DB level as NULLS. In my application I am using the datetime picker for the date fields the min value is '01/01/1753' so when I set the value to 'nothing' I get an exception that I am outside of the min or max range. What I have done is set the text property to an empty string and I am checking for the empty string and then set my business object property for the particular date to 'nothing', I still get an exception.
bottom line I would like the DB column to end up NULL if the user does not enter a date.
I have a similar issue with numeric values -- the DB column always ends up with a '0' (zero) I would like this to be NULL instead of zero if the user does not enter a value. Setting it to 'nothing' in the vb code does not create a NULL on the DB side.
I am inserting and updating the rows with stored procedures -- If I need to put some code in the SP that would also work for me.
I really DID NOT want to do some kludge and test for the 01/01/1753 or something along these lines.
Can someone tell me what I am doing wrong?
Thanks
Tom
January 7, 2009 at 1:04 pm
This is a client code problem. Show us the VB that writes the values to the database (executes the SP...).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 1:26 pm
I have attached 3 files:
1 file is the code behind the form - VB Code in Form.txt
1 file is the DataAdapter code
The 3rd file is the businessobject class that is used to pass the data to the dataadapter class
in the businessobject I have tried different ways to fix my problem but none of them work. The same goes for the code in the Form.
Thanks
Tom
January 7, 2009 at 2:05 pm
I do not see any files.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 2:17 pm
Lets try this again
January 7, 2009 at 2:27 pm
Can you validate that any of those values in the mFunerals(i) object are actually NULL? .NET seems to "hate" NULLs, so you tend to have to work extra hard to make it give you a NULL instead of, say, a 0 or "".
Also - As I recall, the Parameter object has an optional flag to the efffect of "Pass a NULL if you encounter the empty string" which may need to be set.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 7, 2009 at 3:26 pm
What version of VS is this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 3:44 pm
VS 2008
When the data gets to the mFuneral the value is never Null the best that I can get it is: a time value of 12:00:00 and the date portion is null. When the query tries to process it seems to do a validation on the date and converts the date that is null to '0001-01-01' and it finds that it is not in the min range for a date value and causes an execption. The last thing I was going to try is set the date to something old like '1800-01-01' and in the SP check for this date and do a set to null. I really don't like this method but I have tried just about everything I can think of. The thing that I find funny is setting a date to null is a everyday NEED. If you are doing an order system you may not know a ship date so instead of putting some date into a column you would put a NULL date into that column.
Let me know if you have another solution. By the way how do I set a null value to a numeric column. This is going to be the same problem I am having with the dates?
Thanks
Tom
January 7, 2009 at 3:49 pm
OK, I would recommend that you rewrite the properties of your BusinessObject like so:
Imports Microsoft.SqlServer
Imports system.Data
Public Class Funerals
'...
Private mFirstName As Data.SqlTypes.SqlString = Data.SqlTypes.SqlString.Null
Public Property FirstName() As String
Get
If mFirstName.IsNull Then
Return ""
Else
Return mFirstName.ToString
End If
End Get
Set(ByVal value As String)
If Not value Is Nothing Then
mFirstName = value
Else
mFirstName = Data.SqlTypes.SqlString.Null
End If
End Set
End Property
Public ReadOnly Property FirstName_SQL() As SqlTypes.SqlString
Get
Return mFirstName
End Get
End Property
Then have the dataAdapter class that calls the proc use the {property}_SQL methods in your BusinessObject.
(edit: added vb formatting tag. Nice!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 5:07 pm
RBarryYoung,
Just curious why you don't use DBNull.Value? The other thing would be to set a default value in the SP to null as in:
create procedure insertproc
(
@datetimevar datetime = null
)
and then in the business object leave that parameter off if it has the value that equates to null.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 7, 2009 at 6:02 pm
Jonathan Kehayias (1/7/2009)
RBarryYoung,Just curious why you don't use DBNull.Value? The other thing would be to set a default value in the SP to null as in:
create procedure insertproc
(
@datetimevar datetime = null
)
and then in the business object leave that parameter off if it has the value that equates to null.
Jonthan: The BusinessObject does not execute the command in the provided code, the DataAdapter does, processing the propeties of the BusinessObject. I didn't want to have to change both for the OP (confusing and error-prone for me to do that) so I just stuck with the BusinessObject changes.
When I tried to assign DBNull.Value to the SQLString private property, I got the error:Value of type 'System.DBNull' cannot be converted to 'System.Data.SqlTypes.SqlString'.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 6:11 pm
Something happened to your reply. All I see is a quote of my post.
EDIT:
Nevermind I see it now. I understand what you were after now.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 7, 2009 at 6:12 pm
Sorry, hit "Post" accidentally. fixed now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 7:42 pm
Thanks I will try your suggestions for the BO
As far as the SP goes, I currently code my SP to have the parameters set to null. What this does for me is the following. If I do not pass a parameter into the SP it will skip that parameter, but it will not insert or update the column to NULLS.
I am hoping that the code you supplied for the BO will take care of my NULLS, but if not I am going to test the values in the SP and then set them to NULL there. I will set the dates to 1800-01-01 and the numerics to 999999999.99. for money columns and 999999999 for integer columns and test for these values.
I would think Microsoft would provide a function in VB .Net to set fields to NULL and be done with it.
Thank you for all your assistance
Tom
January 7, 2009 at 7:57 pm
FYI, don't forget, you will need to change the DataAdapter to use the *_SQL methods of the BO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply