Write Null in Database if the TextBoxes are Empty

Question

All I want to do is to insert Null with the database when the textbox is empty.

 --ACCOUNT

 AS
 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET IDENTITY_INSERT account ON


  INSERT INTO dbo.account
      ( 
        AccntID                   ,
        managedby                 ,
        AccountHolder             ,
        Description               ,
        AccountType               ,
        ContactPerson             ,
        ContactNumber             ,
        EmailAddress              ,
        Address                   ,
        ClientTYpe                ,
        SchemeType                ,
        SalesManager              ,
        DateCreated               ,
        Login                     ,
        Password                  ,
        Balance                   ,
        FilterOption              ,
        Enable22                  ,
        AllowExtendedConfig                  
      ) 
-- Insert statements for procedure here

 VALUES (@AccntID, @managedby, @AccountHolder, @Description, @AccountType, @ContactPerson, @ContactNumber, @EmailAddress, @Address, @ClientType, @SchemeType, @SalesManager, @DateCreated, @Login, @Password, @Balance, @FilterOption, @Enable22, @AllowExtendedConfig)
SET IDENTITY_INSERT account OFF



END

What is the best way to add NULL values to the database when my textboxes are empty

What are you using, ADO.NET, Entity framework, Linq-To-Sql, etc?? Can you show the code where aou're trying to insert the record? — Tim Schmelter
– Tim Schmelter, Commented Oct 22, 2012 at 7:51
vb.net,t-sl INSERT INTO dbo.account ( AccntID ,managedby,accountholder,) — Kristian Hernan C. Manuel
– Kristian Hernan C. Manuel, Commented Oct 22, 2012 at 7:59
Provide scema of dbo.account table, mention Nullable fields. Also, provide to which columns you need to set DB Null. — Parag Meshram
– Parag Meshram, Commented Oct 22, 2012 at 8:39

Parag Meshram · Accepted Answer · 2012-10-22 08:46:07Z

1

Set up helper functions such as:

Public Function DbNullOrStringValue(ByVal value As String) As Object
    If String.IsNullOrEmpty(value) Then
        Return DBNull.Value
    Else
        Return value
    End If
End Function

And so the first code block would be simplified by calling it thus:

Cmd.Parameters.AddWithValue("@AccntID", DbNullOrStringValue(TextBox1.Text)) 
Cmd.Parameters.AddWithValue("@managedby", DbNullOrStringValue(TextBox2.Text)) 
Cmd.Parameters.AddWithValue("@AccountHolder", DbNullOrStringValue(TextBox3.Text)) 
Cmd.Parameters.AddWithValue("@Description", DbNullOrStringValue(TextBox4.Text)) 
Cmd.Parameters.AddWithValue("@AccountType", DbNullOrStringValue(TextBox5.Text))

Cmd is your SqlCommanad object

edited Oct 22, 2012 at 8:46

answered Oct 22, 2012 at 8:18

Parag Meshram

8,52110 gold badges57 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kristian Hernan C. Manuel Over a year ago

this is my parameter looks like..how can i connect my code with your code.. .Parameters.AddWithValue("@AccntID", TextBox1.Text) .Parameters.AddWithValue("@managedby", TextBox2.Text) .Parameters.AddWithValue("@AccountHolder", TextBox3.Text) .Parameters.AddWithValue("@Description", TextBox4.Text) .Parameters.AddWithValue("@AccountType", TextBox5.Text)

Kristian Hernan C. Manuel Over a year ago

this errors appear when i execute this code of yours. Overload resolution failed because no Public Add can be called with these arguments: 'Public Function Add(text As String )As System.Windows.Forms.Listviewitem.Listview.Subitems)As System.Windows.Forms.Listviewitem.Listview.Subitem': Argument matching parameter item cannot convert from DBNull to ListViewSubitems

Parag Meshram Over a year ago

@KristianHernanC.Manuel - This is a different issue. Your DataSet/DataTable has DBNull values. Please check if your Database call is returning any DBNull values. I believe your this problem has resolved and you are now dealing with other promblem now. Add a new post with this issue under Winforms and ADO.NET.

Minus · Accepted Answer · 2012-10-22 15:36:39Z

1

Consider something lighter than a new method :

    Cmd.Parameters.AddWithValue("@AccntID", IIF(String.IsNullOrEmpty(TextBox1.Text),  DBNull.Value, TextBox1.Text))

But for the rest, use a SqlDataAdapter as suggested before.

answered Oct 22, 2012 at 15:36

Minus

7298 silver badges20 bronze badges

Comments

Jack Gajanan · Accepted Answer · 2012-10-22 13:20:18Z

The best way will be to inherited text box and add additional functionality to it

Public Class MyText
    Inherits TextBox

    'Either change default text property (but this will bug you while comparing text property)
    Public Overrides Property Text() As String
        Get
            Return MyBase.Text
        End Get
        Set(ByVal value As String)
            MyBase.Text = value
        End Set
    End Property

    'Best way will be to write new property to use in sql query
    Public ReadOnly Property TextSQL() As String
        Get
            If Me.Text = String.Empty Then
                Return "NULL"
            Else
                Return Me.Text
                'Return "'" & Me.Text & "'" 'or this
            End If
        End Get
    End Property
End Class

now use this text box in your application

cmd.Parameters.AddWithValue("@accountholder", txt_accHolder.TextSQL)

Farfarak · Accepted Answer · 2012-10-23 10:59:06Z

0

In the stored procedure you can use following:

Assumption: parameter name is @TextData

 NULLIF(LTRIM(RTRIM(@TextData)),'')

It will return NULL if @TextData = ''.

answered Oct 23, 2012 at 10:59

Farfarak

1,5471 gold badge8 silver badges8 bronze badges

Collectives™ on Stack Overflow

Write Null in Database if the TextBoxes are Empty

4 Answers 4

3 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Related