insert null value when the textBox1.Text=="" from c# to sql server

Article
2015-07-23

Question

_{Thursday, July 23, 2015 7:49 PM}

Hello!!

In sql server i have a column:

create table Piese(

.....

example decimal(9,2),

)

In C#:

cmd.CommandText = "Insert into Piese(example) values ('"+textBox1.Text+"');

Problem: the column example can be null sometimes. If textBox1.Text is empty ( textBox1.Text=="") it show the error:

Error converting data type varchar to numeric.

i want to insert null instead of ""...or any solution can you give me :D

All replies (9)

_{Thursday, July 23, 2015 9:32 PM | 1 vote}

You can write something like this:

cmd.CommandText = "Insert into Piese(example) values ('@textValue');
string textBox = textBox1.Text.Length>0 ? textBox1.Text : null;

cmd.Parameters.AddWithValue("@textValue", textBox.);

_{Friday, July 24, 2015 8:03 AM | 1 vote}

Hi George,

Please try below sample...

string textBox1Value = string.IsNullOrEmpty(textBox1.Text) ? string.Empty : textBox1.Text;

           cmd.CommandText = "Insert into Piese(example) values ('"+textBox1.Text+"');

String.IsNullOrEmpty method is significantly faster than using Equals.

It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

_{Monday, July 27, 2015 9:07 PM}

maybe my question is wrong, even if i put cmd.CommandText = "Insert into Piese(example) values ('"+ null +"');

it doesn t work too, the only solution i see is to insert '0' when the textbox is empty

_{Monday, August 3, 2015 1:11 PM}

@Raducu,

>>it doesn t work too, the only solution i see is to insert '0' when the textbox is empty

Do you mean only insert '0' from TextBox will get null in Piese(example)?

Regards,

kristin

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Monday, August 3, 2015 1:29 PM}

Hi,

Try This,

int ? value=Convert.ToInt32(textbox1.Text);

cmd.CommandText = "Insert into Piese(example) values ("+value+");

PS.Shakeer Hussain

_{Friday, August 7, 2015 9:24 AM}

@Raducu,

What's the problem now? Do you have any updates about Syed and Val10's suggestions?

Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question.

Best regards,

_{Thursday, March 9, 2017 6:47 PM}

dynamic txtvalue;

if(string.IsNullOrEmpty(textbox1.Text)

{

     txtvalue=0

}

else

{

  txtvalue=Convert.ToInt32(textBox1.Text)

}

cmd.CommandText = "Insert into Piese(example) values ("+txtvalue+");

PS.Shakeer Hussain

_{Friday, March 10, 2017 1:24 AM}

EDIT Well I just noticed this is an old thread, oh well :-)

Perhaps the following

CREATE TABLE [dbo].[PeopleDemo](
    [PK] [INT] IDENTITY(1,1) NOT NULL,
    [FullName] [VARCHAR](30) NOT NULL,
    [AccountNumber] [DECIMAL](9, 2) NULL DEFAULT (NULL),
PRIMARY KEY CLUSTERED 
(
    [PK] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Class

using System;
using System.Data.SqlClient;

public class Operations
{
    private string Server = "KARENS-PC";
    private string Catalog = "ForumExamples";
    private string ConnectionString = "";

    public bool HasError { get; set; }
    Exception classException;
    public Exception Exception { get { return classException; } }
    public Operations()
    {
        ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
    }
    public bool AddNewCustomer(string FullName, string AccountNumber, ref int NewIdentifier)
    {
        decimal accountNumber;
        using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (SqlCommand cmd = new SqlCommand { Connection = cn })
            {
                cmd.Parameters.AddWithValue("@FullName", FullName);


                if (!decimal.TryParse(AccountNumber, out accountNumber))
                {
                    cmd.CommandText = "INSERT INTO [dbo].[PeopleDemo] ([FullName],[AccountNumber]) " +
                                      "VALUES (@FullName,NULL); " +
                                      "SELECT CAST(scope_identity() AS int);"; ;
                }
                else
                {
                    cmd.Parameters.AddWithValue("@AccountNumber", accountNumber);
                    cmd.CommandText = "INSERT INTO [dbo].[PeopleDemo] ([FullName],[AccountNumber]) " +
                                      "VALUES (@FullName,@AccountNumber); " +
                                      "SELECT CAST(scope_identity() AS int);";
                }


                cn.Open();

                try
                {
                    NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                    return true;
                }
                catch (Exception ex)
                {
                    HasError = true;
                    classException = ex;
                    return false;
                }
            }
        }
    }
}

Add one row with account number while the other account number is empty.

int newId = 0;
var ops = new Operations();
if (ops.AddNewCustomer("Karen Payne", "400", ref newId))
{
    MessageBox.Show($"Success, id is {newId}");
}
if (ops.AddNewCustomer("Mary Payne", "", ref newId))
{
    MessageBox.Show($"Success, id is {newId}");
}

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Friday, March 10, 2017 1:26 AM}

In VB I would not be able to do that. It would need to be one or the other.

WRONG:

https://msdn.microsoft.com/en-us/library/system.string.isnullorempty(v=vs.110).aspx?f=255&MSPPError=-2147217396&cs-save-lang=1&cs-lang=vb#code-snippet-1

But remember, by deciphering both you will know more about it, in particular if it has been set.

Check out what I just posted, this is no different in VB.NET.

Share via

insert null value when the textBox1.Text=="" from c# to sql server

Question

All replies (9)

Additional resources