Error Got solved Thank you every one

Question

2.67/5 (3 votes)

See more:

DateTime date1 = Convert.ToDateTime(dateTimePicker1.Text);
SqlConnection cnn = new SqlConnection(@"Server=NGENIOUS-WSSDEV\SQLEXPRESS;Database=MIC;Trusted_Connection= True");

Change done on this line

C#

SqlCommand cmd = new SqlCommand("insert into subproduct(productcategory,productname,productcode,price,munits,date)values('" + cmdproduct.SelectedValue.ToString() + "','" + txtsub.Text + "','" + txtcode.Text + "'," + txtprice.Text + ",'" + cmdmunits.SelectedValue.ToString() + "','" + date1.ToString() + "')", con);

 cnn.Open();

Comment all this code

C#

 //cmd.Parameters.AddWithValue("@productcategory", cmdproduct.SelectedItem.ToString());
 //cmd.Parameters.AddWithValue("@productname", txtsub.Text);
 //cmd.Parameters.AddWithValue("@productcode", txtcode.Text);
 //cmd.Parameters.AddWithValue("@price", txtprice.Text);
 //cmd.Parameters.AddWithValue("@munits", cmdmunits.SelectedItem.ToString());
 //cmd.Parameters.AddWithValue("@date", date1);

// string dt = (string)dateTimePicker1.Text;
 cnn.Close();

 MessageBox.Show("Record Successfully Generated");

This is the code which i have written on submit button and the error i am getting the exception "String or binary "data would be truncated. The statement has been terminated.

My sql query is

create table subproduct
(
productcategory varchar(50),
productname varchar(50),
productcode varchar(20),
price int,
munits varchar(20),
date datetime
)

Yupiiiiiiii Error Solved Party :laugh: :laugh: :-O :-O :rolleyes: :rolleyes:

Thanks in Advance:rose::rose::rose:

Posted 21-Oct-10 0:27am

aayu

Updated 22-Oct-10 3:30am

Henry Minute

v7

Add a Solution

Comments

Sunasara Imdadhusen 21-Oct-10 8:25am

Please provide value that you have been passed to the procedure.

@nuraGGupta@ 22-Oct-10 1:02am

Aarti, did u got a solution?

aayu 22-Oct-10 2:36am

nope :(

@nuraGGupta@ 22-Oct-10 3:35am

my god, just an insert statement creating a problem for more than 24 hours, why dont you try it with stored procedure.......give it a last try.

aayu 22-Oct-10 5:08am

I even did that at last i deleted that form and even drop the table from SQL created new form and table but then also same problem i am banging my hand on the PC got so frustrated with this error tried everything but no result god.

Pawan Kiran 22-Oct-10 6:23am

sorry Aarti, same i have tried at my side by creating DB and Form..
everything ok for me. i tested by taking combo values as well as static values also.

@nuraGGupta@ 22-Oct-10 7:05am

now I am really dying to see that problem in front.......you may be working from your office.....dont you have any seniors or TL to guide you?

Pawan Kiran 22-Oct-10 7:24am

Hi Anurag, This kind of situation mostly occurs, while passing string size is more than DB Column size.. what i suggests her is, First Increase the column Size and Test it by giving Old Values only.

and i have found One more Link, it may be useful or not I am not aware.
http://blogs.msdn.com/b/buckh/archive/2005/10/12/480057.aspx

aayu 22-Oct-10 7:30am

@ Aunrag this three rose are mine only coz i only solve this error hahahahahhaha
@ venkatesh sorry for mis Communication for yesterday was really frustrated so i think it was not possible to get you. really sorry and thank you

Pawan Kiran 22-Oct-10 7:33am

let us know where u did the mistake?

Tarun.K.S 22-Oct-10 7:40am

PHEWW!!

@nuraGGupta@ 22-Oct-10 8:15am

my my.....you r getting happy when you solved this small issue spending more than 24 hours. Anyways, there is a reason to party today and that bcoz its friday, weekend has arrived. Anyways, do tell me that wht was the problem, and yeah plz keep ur roses wid u, coz I m looking for some intelligent girl to hand me over some. [;p] [;p] [;p]

@nuraGGupta@ 22-Oct-10 16:54pm

Hey...........i can see, you made changes in the insert command.............this is wht I was suggesting the whole time...now I will snatch those red roses !!!

aayu 23-Oct-10 6:49am

@Aunrag you can't do that coz you only told that you are looking for some intelligent girl who give you rose hahahahahaha.(jokes apart) you told me to do the changes but even there were more change to be done so that this insert statement should work properly.

@nuraGGupta@ 23-Oct-10 10:32am

Hey, whatever you say, but as already I requested that please have a habit of using stored procedures, thats very structured way to avoid such problems, and also if you have to use the same query again n again then u can save many code line that way !!!

9 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

@nuraGGupta@ · Answer 1 · 2010-10-21T01:35:00

Solution 6

Try running the sql insert query from your SQL server query analyzer window. See if the values get inserted to the database, then try with the same set of value from your code...........hope this will let you solve the problem. As you are not using any DAL or BLL, the problem lies in your presentation layer only, so it should not be hard to track it out.

Posted 21-Oct-10 1:35am

@nuraGGupta@

Updated 21-Oct-10 1:36am

v2

Comments

aayu 21-Oct-10 7:41am

i can insert the data in sql by insert statement in same format i have written in C# its giving me error

@nuraGGupta@ 21-Oct-10 7:51am

Then the problem must be like string instead of int or vice versa, passing to sql server from your code, this problem must be related to '' sort of thing. Why dont you write a store procedure and then execute the stored procedure from your code. Dont forget to write: cmd.CommandType=CommandType.StoredProcedure. Infact, this method must be always followed to avoid such type of situation as I can see, the problem lies somewhere in the insert query only....

Venkatesh Mookkan 21-Oct-10 8:08am

Anurag, I give up man. You will never make her understand.

@nuraGGupta@ 21-Oct-10 8:14am

Hey venkatesh, dont give up like this yaar, she had already offered 3 red roses whoever helps her. I am trying my level hard to have those roses, anyways (jokes apart), I have a very hard feeling that the problem lies in the insert query somewhere, and if she can give it try with stored procedure, then I will really be glad....Aarti you stop making those crying faces in blue color.....[:D]

abdul kamaal naser · Answer 2 · 2010-10-21T00:36:00

Solution 1

change the following line and try it

  SqlCommand cmd = new SqlCommand("insert into subproduct(productcategory ,productname ,productcode,price ,munits,date )values(@productcategory,@productname ,@productcode,@price ,@munits,#date )", cnn);

regards
ak.naser

Posted 21-Oct-10 0:36am

abdul kamaal naser

Updated 21-Oct-10 1:59am

v2

Comments

aayu 21-Oct-10 6:40am

nope it is giving me error for #

Venkatesh Mookkan · Answer 3 · 2010-10-21T00:36:00

Solution 2

You code works perfectly for me.
Please check,

- textBox1.Text returns only numerical values
~~- cmdmunits.SelectedItem.ToString() return only numerical values~~
- date1 returns only DateTime (from the code I believe it is numerical only)

I suspect textBox1.Text ~~and cmdunits.SelectedItem~~

Check if the length of the string exceeds.

Please refer this link http://www.dotnetspider.com/resources/30543-SQL-Error-String-or-binary-data-would-be.aspx[^]

The problem should be in this line.

C#

cmd.Parameters.AddWithValue("@price", textBox1.Text);

price is a int field. Parameters you send using Command are defaulted string datatype. You need to specify the data type for the above line.

Option1:

C#

cmd.Parameters.AddWithValue("@price", Convert.ToInt16(textBox1.Text));

~~Option 2:~~

C#

cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Int)); cmd.Parameters["@price"].Value = Convert.ToInt16(textBox1.Text);

This applies to the date field also.

Posted 21-Oct-10 0:36am

Venkatesh Mookkan

Updated 21-Oct-10 3:40am

v5

Comments

aayu 21-Oct-10 6:44am

nope i have tried both the option same error

Venkatesh Mookkan 21-Oct-10 6:46am

Can you give the full trace of your exception? And also please tell me at which line of code you are having the problem?

Venkatesh Mookkan 21-Oct-10 6:55am

Updated the answer. Please take a look.

aayu 21-Oct-10 6:58am

the line which i am getting error is cmd.ExecuteNonQuery();

aayu 21-Oct-10 7:01am

no no no no no no cmdmunits.SelectedItem.ToString() will not return only numerical values

Venkatesh Mookkan 21-Oct-10 7:05am

Updated the answer.

aayu 21-Oct-10 7:09am

well the length is not exceeding coz the data it is taking from master page on munit. over there no problem only this page having problem

Venkatesh Mookkan 21-Oct-10 7:12am

You need to check all the fields. txtproductname, txtpc, etc, length should be less than or equal to the database field size.

Put a break point in the first line and go step by step. Find the length.

aayu 21-Oct-10 7:20am

well i am using first combo box retrieving data from sql in combo, then three text where user will put the data itself and then again one combo to retrieve the data from sql then date
this is my form structure i don't know from where this string or binary came as an exception in cmd.ExecuteNonQuery();

Venkatesh Mookkan 21-Oct-10 7:23am

string or binary mean the data you pass to be SqlCommand as Parameter. Did you checked the length that user feeded? And this is my code (you will not find any difference except the values) which works fine.

con.Open();

SqlCommand cmd = new SqlCommand("insert into subproduct(productcategory ,productname ,productcode,price ,munits,date )values(@productcategory,@productname ,@productcode,@price ,@munits,@date )", con);
cmd.Parameters.AddWithValue("@productcategory", "Test");
cmd.Parameters.AddWithValue("@productname", "Test");
cmd.Parameters.AddWithValue("@productcode", "Test");
cmd.Parameters.AddWithValue("@price", "56");
cmd.Parameters.AddWithValue("@munits", "2");
cmd.Parameters.AddWithValue("@date", "10/10/2010");
cmd.ExecuteNonQuery();
con.Close();

aayu 21-Oct-10 7:26am

this is my SQL Query
create table subproduct
(
productcategory varchar(50),
productname varchar(50),
productcode varchar(10),
price int,
munits varchar(10),
date datetime
)

you only tell where i have given less parameter

Venkatesh Mookkan 21-Oct-10 7:29am

Provide the value of,

cmdproduct.SelectedItem.ToString()
txtproductname.Text
txtpc.Text
textBox1.Text
cmdmunits.SelectedItem.ToString()
date1

I will tell you where is the problem. FYI, I have created the same table in my machine.

aayu 21-Oct-10 7:42am

did you got where is my error

Venkatesh Mookkan 21-Oct-10 7:44am

I asked you the following

cmdproduct.SelectedItem.ToString()
txtproductname.Text
txtpc.Text
textBox1.Text
cmdmunits.SelectedItem.ToString()
date1

aayu 21-Oct-10 7:47am

ya

Venkatesh Mookkan 21-Oct-10 7:52am

What "ya"? Fill the question mark with the your values and post it again.

cmdproduct.SelectedItem.ToString() = ?
txtproductname.Text = ?
txtpc.Text = ?
textBox1.Text = ?
cmdmunits.SelectedItem.ToString() = ?
date1 = ?

@nuraGGupta@ 21-Oct-10 7:54am

:) :) :D .....Venkatesh is having a tough time here, solving the query.... [:D]

aayu 21-Oct-10 7:57am

ok you want the label name sorry

cmdproduct.SelectedItem.ToString()= ProductCategory like ice cream, Shrikhand
txtproductname.text= productname like Better Scotch, White Shrikhand
txtpc.text = productcode like BS, WS
textbox1.text = price like 150, 120
cmdmunits.SelectedItem.ToString() = MUnits like KG, Liter
date1 = datetimepicker1

Venkatesh Mookkan 21-Oct-10 8:00am

Aarthi, is this error happens for any values or particularly some values. Because, the above values you provided got inserted without any problem.

aayu 21-Oct-10 8:04am

in SQL when i am inserting it is getting insert with out any problem but when i am trying to insert this same data from C# it is giving my String or binary error

Venkatesh Mookkan 21-Oct-10 8:07am

You know what, I give up.

Venkatesh Mookkan 21-Oct-10 9:41am

Updated the answer. Take a look

Goutam Patra · Answer 4 · 2010-10-21T01:01:00

Solution 3

The error exactly suggests the problem. Some of your field length is less than the data you are trying to insert. That’s all. Check all the data length and set max length of the text boxes up to the length of the field.

Posted 21-Oct-10 1:01am

Goutam Patra

Comments

aayu 21-Oct-10 7:06am

i have checked but same error i not getting where this string or binary is getting problem

Goutam Patra 21-Oct-10 7:09am

Remove one field from your query and try to execute it. If does not run correctly then remove another field and try again. This way you will find the exact field where you are getting the error.

abdul kamaal naser · Answer 5 · 2010-10-21T01:07:00

Solution 4

change the first line and try it

DateTime date1 = Convert.ToDateTime(dateTimePicker1.Value);

Posted 21-Oct-10 1:07am

abdul kamaal naser

Pawan Kiran · Answer 6 · 2010-10-21T01:08:00

Try To Increase Your DB Column Size's First

Later Try This..

cmd.Parameters.AddWithValue("@price", string.IsNullOrEmpty(textBox1.Text)?0:Convert.ToInt32(textBox1.Text));
cmd.Parameters.AddWithValue("@productcategory",
string.IsNullOrEmpty(cmdproduct.SelectedItem.ToString())?"":cmdproduct.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@munits",
string.IsNullOrEmpty(cmdmunits.SelectedItem.ToString())?"":cmdmunits.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@productname", string.IsNullOrEmpty(txtproductname.Text)?"":txtproductname.Text);
cmd.Parameters.AddWithValue("@productcode", string.IsNullOrEmpty(txtpc.Text)?"":txtpc.Text);

cmd.Parameters.AddWithValue("@price", string.IsNullOrEmpty(textBox1.Text)?"":textBox1.Text);
         cmd.Parameters.AddWithValue("@date", string.IsNullOrEmpty(date1)? Convert.ToDateTime(Datetime.Now):date1);

nagendrathecoder · Answer 7 · 2010-10-21T02:09:00

Solution 7

Your Price field is int, are you sure with that?
Are you sure value going is int and not a decimal?

Posted 21-Oct-10 2:09am

nagendrathecoder

Comments

aayu 21-Oct-10 8:11am

yes its int i am not putting and decimal value

nagendrathecoder 21-Oct-10 8:45am

can you give the query which is executing with original values.

@mitDeshpande · Answer 8 · 2010-10-21T02:37:00

Solution 8

cmd.Parameters.AddWithValue("@price", textBox1.Text);

Replace above line with following one

cmd.Parameters.AddWithValue("@price",Convert.ToInt32(textBox1.Text));

Try this

Posted 21-Oct-10 2:37am

@mitDeshpande

Mohd Wasif · Answer 9 · 2010-10-21T02:43:00

DateTime date1 = Convert.ToDateTime(dateTimePicker1.Text);
SqlConnection cnn = new SqlConnection(@"Server=NGENIOUS-WSSDEV\SQLEXPRESS;Database=MIC;Trusted_Connection= True");
SqlCommand cmd = new SqlCommand("insert into subproduct(productcategory ,productname ,productcode,price ,munits,date )values(@productcategory,@productname ,@productcode,@price ,@munits,@date )", cnn);

cmd.Parameters.AddWithValue("@productcategory", cmdproduct.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@productname", txtproductname.Text);
cmd.Parameters.AddWithValue("@productcode", txtpc.Text);
cmd.Parameters.AddWithValue("@price", textBox1.Text);
cmd.Parameters.AddWithValue("@munits", cmdmunits.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@date", date1);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

MessageBox.Show("Record Successfully Generated
");

Error Got solved Thank you every one

9 solutions

Solution 6

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Solution 7

Solution 8

Solution 9

Add your solution here

Preview 0