Error converting data type varchar to numeric

Question

hi all. ope your allright i am inserting into my sql database data from a datagridview1 however some of the values are numeric and i keep on getting an error -  ERROR CONVERTING DATA TYPE VARCHAR TO NUMERIC funny though the data is inserted into the database table but the error remains below is the code i am using. foreach (DataGridViewRow row in dataGridView1.Rows) { string insertString =  insert into Treatmentpurchases (daydatemonthyearwknodrugiddrugnamedrugmeasurecostduration) values (' + row.Cells[Day].Value + '  ' + row.Cells[Date].Value + '  ' + row.Cells[Month].Value + '  ' + row.Cells[Year].Value + '  ' + row.Cells[Wkno].Value + '  ' + row.Cells[Drugid].Value + '  ' + row.Cells[Drug].Value + '  ' + row.Cells[Measure].Value + '  ' + row.Cells[Cost].Value + '  ' + row.Cells[Duration].Value + '); SqlCommand cmd = new SqlCommand(insertString conn); cmd.ExecuteNonQuery(); } the numeric values are wkno and year. Any help???

Vulpes · Answer

For DateTime you'll need: DateTime date = DateTime.Parse(row.Cells[Date].Value.ToString()); and then insert it into the database in whatever format you need surrounded by single quotes. For example if you want the month before the day and aren't interested in the time component: ' + date.ToString(MM/dd/yyyy) + '

Priya Linge · Answer

Hi Cast these two fields to varchar using CAST fuction CAST(wkno ) AS Varchar(10) CAST(year) As Varchar(10) Hope this will help you. Thanks.

Marvin kakuru · Answer

Hi Vulpes thankyou again i have been successful with the date value as well. However i inserted using a '+ date.ToString(MM/dd/yyyy) + ' and it has worked. thanks man

Marvin kakuru · Answer

Hai Vulpes thanks alot man my wkno and year are working perfectly well. however when i tried to use the same method for my date value int date = DateTime.Parse(row.Cells[ Date ].Value.ToString()); the program won't run but i get this error  cannot implicity convert type system.DateTime to int  what can do for this

Vulpes · Answer

My guess is that the null reference exception is coming when you reach the last row in the DGV because it's the 'new row' and therefore empty. Assuming that's so adding the highighted line should solve it: foreach (DataGridViewRow row in dataGridView1.Rows) { if (row.IsNewRow) break; int wkno = Int32.Parse(row.Cells[Wkno].Value.ToString()); int year = Int32.Parse(row.Cells[Year].Value.ToString()); // etc }

Marvin kakuru · Answer

Hi Vulpes. Thanks a lot for your help I have done just like you said however I have going the following ERROR [  System. NullReferenceException was unhandled - Object reference not set to an instance of an object.] This is my entire code. private void button3_Click( object sender EventArgs e) { SqlConnection conn = new SqlConnection ( Data Source=KATOTO-PC;Initial Catalog =thyfarm;User ID=sa;Password=Kyozi ); conn.Open(); foreach ( DataGridViewRow row in dataGridView1.Rows) { int wkno = Int32 .Parse(row.Cells[ Wkno ].Value.ToString()); int year = Int32 .Parse(row.Cells[ Year ].Value.ToString()); string insertString =  insert into Treatmentpurchases (daymonthyearwknodrugiddrugnamedrugmeasurecostduration) values (' + row.Cells[ Day ].Value + '  ' + row.Cells[ Month ].Value + '   + year.ToString() +    + wkno.ToString() +   ' + row.Cells[ Drugid ].Value + '  ' + row.Cells[ Drug ].Value + '  ' + row.Cells[ Measure ].Value + '  ' + row.Cells[ Cost ].Value + '  ' + row.Cells[ Duration ].Value + ') ; SqlCommand cmd = new SqlCommand (insertString conn); cmd.ExecuteNonQuery(); } conn.Close(); dataGridView1.SelectAll(); button3.Visible = false ; } I hope I haven't done anything the wrong way. i also have a date value and cash value i would want to convert as well Thanks again

Vulpes · Answer

Unlike strings numeric values shouldn't be surrounded by single quotes. So I'd try this: int wkno = Int32.Parse(row.Cells[Wkno].Value.ToString()); int year = Int32.Parse(row.Cells[Year].Value.ToString()); string insertString = insert into Treatmentpurchases (daydatemonthyearwknodrugiddrugnamedrugmeasurecostduration) values (' + row.Cells[Day].Value + '  ' + row.Cells[Date].Value + '  ' + row.Cells[Month].Value + '   + year.ToString() +    + wkno.ToString() +   ' + row.Cells[Drugid].Value + '  ' + row.Cells[Drug].Value + '  ' + row.Cells[Measure].Value + '  ' + row.Cells[Cost].Value + '  ' + row.Cells[Duration].Value + '); As Sam said you should also use parameters rather than the values themselves in the insert query but see if you can get it to work first.

Marvin kakuru · Answer

Hai Sam thanks for your patience with me. I am kindly requesting you to bear with me and show me from my code below how I can convert from numeric the data I am trying to insert. I am very new to C sharp so I can't make much out of the terms you are using . I tried to cast the numeric figures but I can't seem to solve the problem. below is the code im using { string insertString =  insert into Treatmentpurchases (daydatemonthyearwknodrugiddrugnamedrugmeasurecostduration) values (' + row.Cells[Day].Value + '  ' + row.Cells[Date].Value + '  ' + row.Cells[Month].Value + '  ' + row.Cells[Year].Value + '  ' + row.Cells[Wkno].Value + '  ' + row.Cells[Drugid].Value + '  ' + row.Cells[Drug].Value + '  ' + row.Cells[Measure].Value + '  ' + row.Cells[Cost].Value + '  ' + row.Cells[Duration].Value + '); Int32.TryParse(row.Cells[Wkno].Value); Int32.TryParse(row.Cells[Year].Value); } Please help if you can. THANKS

Sam Hobbs · Answer

First please understand that the green color you are using for the code is difficult for me to read. It is too bright so the contrast between the bright green and the white background makes it difficult to read. Many experienced SQL developers insist that we should always use parameterized queries. If you read the articles in this web site you will find many describing very useful techniques such as parameterized queries. The data type for the wkno and year parameters could be a numeric type and you could do the string to numeric conversion in your code. You can use Int32.TryParse to convert to integer and then you can be guaranteed that there is not a conversion problem in the insert command. John asked what the contents of insertString is. That is an important question; there is likely something wrong with the query. The problem might only be the data but a problem with the data is a problem. You could do the query in a try block with a catch. Then when there is an error the catch block could show an error message. Part of the error message could be the query string (insertString).

John Penn · Answer

Can you post the contents of insertString after it has been populated? i.e. set a breakpoint on the SqlCommand line and use the watch window to see the contents of insertString.

Marvin kakuru · Answer

Thanks Priya for your concern. Unfortunately i have no education on programming but merely a deep passion to learn through practise. kindly show using an example of how you would cast.those values thanks.