System.dbnull.value null? APP not start if column null

Question

If is Column 1, Column 3 or Column 4 null app not start...

How tu insert in this code for system db null or recomandition???

openCon.Open();
using (SqlDataAdapter querySaveStaff = new SqlDataAdapter(saveStaff, cs))
{
DataTable dt = new DataTable();
querySaveStaff.Fill(dt);
prijava_radnikaDataGridView.DataSource = dt;
prijava_radnikaDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
prijava_radnikaDataGridView.Columns[0].Visible = false; //id
prijava_radnikaDataGridView.Columns[1].FillWeight = 80; //redni broj
prijava_radnikaDataGridView.Columns[2].FillWeight = 250; //radnik
prijava_radnikaDataGridView.Columns[3].FillWeight = 180; //vrijeme prijave
prijava_radnikaDataGridView.Columns[4].FillWeight = 180; //vrijeme odjave

Nitin Sontakke · Answer

Can you please execute the following query in SQL Server Management Studio and show me the output as an image? Should show ALL COLUMNS and at least 10-20 records. SELECT a.IdPrijava ,a.redni_broj ,a.radnik ,a.vrijemeodjave as vrijemeodjave ,b.vrijemeprijave as vrijemeprijave , CASE WHEN (DATEDIFF( SECOND , b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE (DATEDIFF( SECOND , b.vrijemeprijave, a.vrijemeodjave) / 3600) END AS radni_sati , CASE WHEN (DATEDIFF( SECOND , b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN (DATEDIFF( SECOND , b.vrijemeprijave, a.vrijemeodjave) / 3600) - 8 ELSE NULL END AS prekovremeni FROM ( SELECT IdPrijava ,redni_broj ,radnik ,vrijemeodjave ,ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum FROM dbo.prijava_radnika) a INNER JOIN ( SELECT IdPrijava ,redni_broj ,radnik ,vrijemeprijave ,(ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava;

Goran Bibic · Answer

I think is now ok this code.... this is result from sql and result is ok... 1441 1 Gojko Bibic 2018-03-14 22:09:18.043 2018-03-14 12:09:16.190 8 2 1442 2 gfsdghsdfhdgh 2018-03-14 22:24:16.717 2018-03-14 12:24:15.500 8 2 1443 3 Gojko Bibic 2018-03-14 22:24:19.413 2018-03-14 12:24:18.260 8 2 1444 4 Gojko Bibic 2018-03-14 22:24:21.910 2018-03-14 12:24:20.920 8 2 1445 5 Gojko Bibic 2018-03-14 22:39:38.673 2018-03-14 13:39:34.713 8 1 1446 6 Stevo Stevic 2018-03-14 22:39:40.387 2018-03-14 05:39:37.043 8 9 1447 15 gfsdghsdfhdgh 2018-03-14 22:50:45.400 2018-03-14 17:42:51.773 5 NULL 1448 7 Test123456 2018-03-14 22:49:26.003 2018-03-14 08:49:24.980 8 6 1449 8 Test bbb 2018-03-14 22:49:29.910 2018-03-14 19:49:27.697 3 NULL 1450 9 Radnik_test 2018-03-14 22:49:32.677 2018-03-14 11:49:31.443 8 3 1451 10 Radnik_test 2018-03-14 22:49:35.193 2018-03-14 14:49:33.973 8 NULL 1452 13 Radnik_test 2018-03-14 22:50:31.887 2018-03-14 13:49:36.967 8 1 1453 11 Gojko Bibic 2018-03-14 22:49:43.150 2018-03-14 12:49:41.887 8 2 1454 12 Goran Panic 2018-03-14 22:50:27.277 2018-03-14 12:50:25.853 8 2 1455 14 sfdgsdfgsfg 2018-03-14 22:50:42.810 2018-03-14 09:50:40.433 8 5 1456 15 gfsdghsdfhdgh 2018-03-14 22:50:45.400 2018-03-14 22:50:44.060 0 NULL 1457 16 fjhjff 2018-03-14 22:50:48.713 2018-03-14 22:50:47.120 0 NULL 1458 17 Mitar Miric 2018-03-14 22:50:51.117 2018-03-14 22:50:49.853 0 NULL 1459 18 Goran Panic 2018-03-14 22:50:55.407 2018-03-14 22:50:53.907 0 NULL 1460 19 Test123456 2018-03-14 22:51:00.487 2018-03-14 22:50:59.120 0 NULL 1461 20 Radnik_test 2018-03-14 22:51:10.620 2018-03-14 22:51:09.143 0 NULL 1462 21 Test1 2018-03-14 22:51:21.747 2018-03-14 22:51:20.307 0 NULL 1463 22 Goran Panic 2018-03-14 22:51:24.423 2018-03-14 22:51:23.070 0 NULL 1464 23 Test bbb 2018-03-14 22:51:28.000 2018-03-14 22:51:26.257 0 NULL 1465 24 Test bbb 2018-03-14 22:51:45.087 2018-03-14 22:51:43.480 0 NULL 1467 25 Bibic Goran2 2018-03-15 07:21:14.287 2018-03-15 07:20:58.173 0 NULL

Goran Bibic · Answer

I am solve the problem.... In Third line just add v rijemeodjaveDateTimePicker == null...now work Thank you private void prijava_radnikaDataGridView_SelectionChanged(object sender, EventArgs e) { if (prijava_radnikaDataGridView.SelectedRows.Count > 0 && vrijemeodjaveDateTimePicker == null) // make sure user select at least 1 row { string id = prijava_radnikaDataGridView.CurrentRow.Cells[ "dataGridViewTextBoxColumn1" ].FormattedValue.ToString(); string radnik = prijava_radnikaDataGridView.CurrentRow.Cells[ "dataGridViewTextBoxColumn9" ].FormattedValue.ToString(); string redni_broj = prijava_radnikaDataGridView.CurrentRow.Cells[ "redni_broj" ].FormattedValue.ToString(); radnikComboBox.Text = radnik; vrijemeprijaveDateTimePicker.Value = Convert.ToDateTime(prijava_radnikaDataGridView.SelectedRows[0].Cells[ "dataGridViewTextBoxColumn2" ].Value); vrijemeodjaveDateTimePicker.Value = Convert.ToDateTime(prijava_radnikaDataGridView.SelectedRows[0].Cells[ "dataGridViewTextBoxColumn3" ].Value); redni_brojTextBox.Text = redni_broj; idPrijavaTextBox.Text = id; } }

Goran Bibic · Answer

Look ---here is ok? This is your code for sql and in sql work string saveStaff = "SELECT a.IdPrijava,a.redni_broj ,a.radnik ,a.vrijemeodjave as vrijemeodjave ,b.vrijemeprijave as vrijemeprijave ," + "CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END AS radni_sati," + " CASE WHEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) - 8 ELSE 0 END AS prekovremeni" + " FROM ( SELECT IdPrijava , redni_broj , radnik , vrijemeodjave , ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum FROM dbo.prijava_radnika) " + " a INNER JOIN ( SELECT IdPrijava , redni_broj , radnik , vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne" + "FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava" ;

Goran Bibic · Answer

I am now delete all data fropm sql table, but app again not start

Goran Bibic · Answer

I am put here in your query instead of NULL put 0 An dnow ina last column is not null...its 0, but app not start...what I mising? 1441 1 Gojko Bibic 2018-03-14 22:09:18.043 2018-03-14 12:09:16.190 8 2 1442 2 gfsdghsdfhdgh 2018-03-14 22:24:16.717 2018-03-14 12:24:15.500 8 2 1443 3 Gojko Bibic 2018-03-14 22:24:19.413 2018-03-14 12:24:18.260 8 2 1444 4 Gojko Bibic 2018-03-14 22:24:21.910 2018-03-14 12:24:20.920 8 2 1445 5 Gojko Bibic 2018-03-14 22:39:38.673 2018-03-14 13:39:34.713 8 1 1446 6 Stevo Stevic 2018-03-14 22:39:40.387 2018-03-14 05:39:37.043 8 9 1448 7 Test123456 2018-03-14 22:49:26.003 2018-03-14 08:49:24.980 8 6 1449 8 Test bbb 2018-03-14 22:49:29.910 2018-03-14 19:49:27.697 3 0 1450 9 Radnik_test 2018-03-14 22:49:32.677 2018-03-14 11:49:31.443 8 3 1451 10 Radnik_test 2018-03-14 22:49:35.193 2018-03-14 14:49:33.973 8 0 1452 13 Radnik_test 2018-03-14 22:50:31.887 2018-03-14 13:49:36.967 8 1 1453 11 Gojko Bibic 2018-03-14 22:49:43.150 2018-03-14 12:49:41.887 8 2 1454 12 Goran Panic 2018-03-14 22:50:27.277 2018-03-14 12:50:25.853 8 2 1455 14 sfdgsdfgsfg 2018-03-14 22:50:42.810 2018-03-14 09:50:40.433 8 5 1456 15 gfsdghsdfhdgh 2018-03-14 22:50:45.400 2018-03-14 22:50:44.060 0 0 1457 16 fjhjff 2018-03-14 22:50:48.713 2018-03-14 22:50:47.120 0 0 1458 17 Mitar Miric 2018-03-14 22:50:51.117 2018-03-14 22:50:49.853 0 0 1459 18 Goran Panic 2018-03-14 22:50:55.407 2018-03-14 22:50:53.907 0 0 1460 19 Test123456 2018-03-14 22:51:00.487 2018-03-14 22:50:59.120 0 0 1461 20 Radnik_test 2018-03-14 22:51:10.620 2018-03-14 22:51:09.143 0 0 1462 21 Test1 2018-03-14 22:51:21.747 2018-03-14 22:51:20.307 0 0 1463 22 Goran Panic 2018-03-14 22:51:24.423 2018-03-14 22:51:23.070 0 0 1464 23 Test bbb 2018-03-14 22:51:28.000 2018-03-14 22:51:26.257 0 0 1465 24 Test bbb 2018-03-14 22:51:45.087 2018-03-14 22:51:43.480 0 0 1467 25 Bibic Goran2 2018-03-15 07:21:14.287 2018-03-15 07:20:58.173 0 0 1468 26 Test1 2018-03-15 22:38:11.723 2018-03-15 22:38:10.327 0 0

Nitin Sontakke · Answer

In the result supplied by you ONLY LAST column is null all other column have value. Your original question : If is Column 1, Column 3 or Column 4 null app not start... Where are the null values? Unless we identify the problem, we cannot solve it.

Goran Bibic · Answer

What to do now?

Goran Bibic · Answer

What now? To replace in my code? Delete code for data table or?

Goran Bibic · Answer

Column name are same like in save and insert query.... column name prijava_radnikaDataGridView.Columns[0].Visible = false; IdPrijava prijava_radnikaDataGridView.Columns[1].FillWeight = 80; redni_broj prijava_radnikaDataGridView.Columns[2].FillWeight = 250; radnik prijava_radnikaDataGridView.Columns[3].FillWeight = 180; vrijemeprijave prijava_radnikaDataGridView.Columns[4].FillWeight = 180; vrijemeodjave

Nitin Sontakke · Answer

Can we just talk about JUST ONE COLUMN. That will be better. Please tell me ONE COLUMN NAME. Do not say 1, 2, 3, etc. After i give you the solution, you can do it for other columns. Is that okay?

Goran Bibic · Answer

Data prijava_radnikaDataGridView.Columns[0].Visible = false; //id insert prijava_radnikaDataGridView.Columns[1].FillWeight = 80; //redni broj update prijava_radnikaDataGridView.Columns[2].FillWeight = 250; //radnik insert prijava_radnikaDataGridView.Columns[3].FillWeight = 180; //vrijeme prijave insert prijava_radnikaDataGridView.Columns[4].FillWeight = 180; //vrijeme odjave update That is all...but when is 2 and 4 empty app not start...is have same value working fine

Nitin Sontakke · Answer

Now that's better. Now you tell me you mentioned column 1, 3, 4. 1 = Columns[0] or Columns[1]? So number you have mentioned human terms or the ordinal (computer) terms. In any case assume column a.IdPrijava is getting null and you want it 0 or 16 or 80, all you have to is, select isnull(a.[IdPrijava], 0) [IdPrijava], .... Now you can do this for all column as above. Hope it helps!

Goran Bibic · Answer

String cs = "Data Source=.\SQLEXPRESS;Initial Catalog=bss_prijava_radnika;Integrated Security=True" ; using (SqlConnection openCon = new SqlConnection(cs)) { string saveStaff = "SELECT a.IdPrijava, a.redni_broj , a.radnik, a.vrijemeodjave as vrijemeodjave, b.vrijemeprijave as vrijemeprijave, " + "CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END AS radni_sati, " + "CASE WHEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) - 8 ELSE NULL END AS prekovremeni " + "FROM(SELECT IdPrijava, redni_broj, radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum " + "FROM dbo.prijava_radnika) a INNER JOIN(SELECT IdPrijava, redni_broj, radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava" ; openCon.Open(); using (SqlDataAdapter querySaveStaff = new SqlDataAdapter(saveStaff, cs)) { DataTable dt = new DataTable(); querySaveStaff.Fill(dt); prijava_radnikaDataGridView.DataSource = dt; prijava_radnikaDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; prijava_radnikaDataGridView.Columns[0].Visible = false ; //id prijava_radnikaDataGridView.Columns[1].FillWeight = 80; //redni broj prijava_radnikaDataGridView.Columns[2].FillWeight = 250; //radnik prijava_radnikaDataGridView.Columns[3].FillWeight = 180; //vrijeme prijave prijava_radnikaDataGridView.Columns[4].FillWeight = 180; //vrijeme odjave //podesavanje sirine redova tabele prijava_radnikaDataGridView.Columns[5].Visible = false ; //status prijava_radnikaDataGridView.Columns[6].Visible = false ; //vrijeme dolaska prijava_radnikaDataGridView.Columns[7].Visible = false ; //vrijeme odlaska prijava_radnikaDataGridView.Columns[8].FillWeight = 110; //radni sati prijava_radnikaDataGridView.Columns[9].FillWeight = 110; //prekovremeni prijava_radnikaDataGridView.Columns[10].Visible = false ; //org jedinica prijava_radnikaDataGridView.Update(); prijava_radnikaDataGridView.Refresh(); } if (!string.IsNullOrEmpty(textBox1.Text)) { saveStaff += "AND a.radnik LIKE \'%" + textBox1.Text + "%\' " ; } if (checkBox1.Checked == true ) { if (comboBox1.SelectedIndex == 0 || comboBox1.SelectedIndex == -1) { saveStaff += "AND b.vrijemeprijave>\'" + dateTimePicker1.Value.ToString( "yyyy-MM-dd 00:00:00" ) + "\' AND a.vrijemeodjave + dateTimePicker2.Value.ToString( "yyyy-MM-dd 23:59:59" ) + "\' " ; } else { saveStaff += "AND b.vrijemeprijave>\'" + dateTimePicker1.Value.ToString( "yyyy-MM-dd 00:00:00" ) + "\' AND a.vrijemeodjave + dateTimePicker2.Value.ToString( "yyyy-MM-dd 23:59:59" ) + "\' " ; } } } this .prijava_radnikaDataGridView.Sort( this .prijava_radnikaDataGridView.Columns[ "redni_broj" ], ListSortDirection.Ascending); double [] columnData = new double [prijava_radnikaDataGridView.Rows.Count]; columnData = (from DataGridViewRow row in prijava_radnikaDataGridView.Rows where row.Cells[ "radni_sati" ].FormattedValue.ToString() != string.Empty select Convert.ToDouble(row.Cells[ "radni_sati" ].FormattedValue)).ToArray(); sumColumnsLabel.Text = "Radnih sati: " + columnData.Sum().ToString(); // iznos_sa_pdvTextBox1.Text = columnData.Sum().ToString("#,0.00"); double [] columnData2 = new double [prijava_radnikaDataGridView.Rows.Count]; columnData2 = (from DataGridViewRow row in prijava_radnikaDataGridView.Rows where row.Cells[ "prekovremeni" ].FormattedValue.ToString() != string.Empty select Convert.ToDouble(row.Cells[ "prekovremeni" ].FormattedValue)).ToArray(); label7.Text = "Prekovremeni: " + columnData2.Sum().ToString(); // iznos_sa_pdvTextBox1.Text = columnData.Sum().ToString("#,0.00"); sumColumnsLabel.Text = "Radnih sati: " + columnData.Sum() + "	 Prekovremenih: " + columnData2.Sum() + "	 Ukupno: " + (columnData.Sum() + columnData2.Sum()).ToString(); //sumColumnsLabel.Text = "Sati: " + sum.ToString() + "	 Prekovremeni: " + overtimeSum.ToString() + "	 Ukupno: " + (sum + overtimeSum).ToString();

Nitin Sontakke · Answer

Also, if your data type issue (7, 8, 9, 10, 10, 10) is resolved, please accept my answer and close that thread.

Nitin Sontakke · Answer

Confusing.... The reason I asked for saveStaff because I was expecting that it will be a select query. Because it is the select query that will be used to fill the dataset, correct? Now how come you are using a INSERT query and then filling the dataset? See your code below: openCon.Open(); using (SqlDataAdapter querySaveStaff = new SqlDataAdapter(saveStaff, cs)) { DataTable dt = new DataTable(); querySaveStaff.Fill(dt); prijava_radnikaDataGridView.DataSource = dt; I really don't understand this. Questions....at line 6 can you check if your dt is populated? can you check how rows does it have, please? Please tell me. To be very honest with I NEVER used data table, data adaptor, .Fill, etc. Not a very good practice. Always use DataReader.

Goran Bibic · Answer

On terminal app i use insert into and update in table dbo.prijava_radnika Insert String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)" ; Update String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET" + " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and[status] = 'prijavljen'" ; In users app read data from same table dbo.prijava radnika, but openCon.Open(); using (SqlDataAdapter querySaveStaff = new SqlDataAdapter(saveStaff, cs)) { DataTable dt = new DataTable(); querySaveStaff.Fill(dt); prijava_radnikaDataGridView.DataSource = dt; prijava_radnikaDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; prijava_radnikaDataGridView.Columns[0].Visible = false ; //id prijava_radnikaDataGridView.Columns[1].FillWeight = 80; //redni broj prijava_radnikaDataGridView.Columns[2].FillWeight = 250; //radnik prijava_radnikaDataGridView.Columns[3].FillWeight = 180; //vrijeme prijave prijava_radnikaDataGridView.Columns[4].FillWeight = 180; //vrijeme odjave If column [1] (//redni broj) or column[4] (vrijeme odjave) are null , app not start, just close...if this two column have value...working fine... System.dbnull.value null? APP not start if column null I find that error...in debuger...or I wrong

Nitin Sontakke · Answer

Please share the saveStaff query.