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

Goran Bibic

8y1.2kLearn iOS Programming

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

18 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted 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;
+3
Goran Bibic8y ago
I am solve the problem....

In Third line just add vrijemeodjaveDateTimePicker == 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;

           }
       }
0
Goran Bibic8y ago
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";
0
Goran Bibic8y ago
I am now delete all data fropm sql table, but app again not start
0
Goran Bibic8y ago
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
0
Nitin Sontakke8y ago
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.
+1
Goran Bibic8y ago
What to do now?
0
Goran Bibic8y ago
What now? To replace in my code? Delete code for data table or?
0
Goran Bibic8y ago
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
+1
Goran Bibic8y ago
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
0
Nitin Sontakke8y ago
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?
+1
Goran Bibic8y ago
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
0
Nitin Sontakke8y ago
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!
+1
Goran Bibic8y ago
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() + "\t  Prekovremenih: " + columnData2.Sum() + "\t Ukupno: " + (columnData.Sum() + columnData2.Sum()).ToString();
           //sumColumnsLabel.Text = "Sati: " + sum.ToString() + "\t  Prekovremeni: " + overtimeSum.ToString() + "\t Ukupno: " + (sum + overtimeSum).ToString();
0
Nitin Sontakke8y ago
Also, if your data type issue (7, 8, 9, 10, 10, 10) is resolved, please accept my answer and close that thread.
+1
Nitin Sontakke8y ago
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.
+1
Goran Bibic8y ago
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
0
Nitin Sontakke8y ago
Please share the saveStaff query.
+1