If statement to not allow insert statement to work because a cell is null-CodePudding

I want the code to not allow the complete button to work because the column of "StartTime" is null.

Attached is the code below:

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Public Class Etask
Dim con As SqlConnection
                Dim cmd As SqlCommand
                 Private Sub Etask_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Labelname.Text = login.mname
    Dim str As String = "Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true"
    Dim con As New SqlConnection(str)
    Dim com As String = "SELECT TaskID, Name, TaskAssigned, StartTime, FinishTime, Status
FROM     dbo.Tasks
WHERE  Name = '" & Labelname.Text & "'"
    Dim Adpt As New SqlDataAdapter(com, con)
    Dim ds As New DataSet()
    Adpt.Fill(ds, "PosTable")
    DataGridView1.DataSource = ds.Tables(0)

End Sub

Private Sub Etask_Resize(sender As Object, e As EventArgs) Handles Me.Resize
    Panel1.Left = (Me.Width - Panel1.Width) / 2
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    refreshDGV()
End Sub

Public Sub refreshDGV()
    Labelname.Text = login.mname
    Dim str As String = "Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true"
    Dim con As New SqlConnection(str)
    Dim com As String = "SELECT TaskID, Name, TaskAssigned, StartTime, FinishTime, Status
 FROM     dbo.Tasks
 WHERE  Name = '" & Labelname.Text & "'"
    Dim Adpt As New SqlDataAdapter(com, con)
    Dim ds As New DataSet()
    Adpt.Fill(ds, "PosTable")
    DataGridView1.DataSource = ds.Tables(0)
End Sub

'complete button
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim con As New SqlConnection("Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true")
    Dim query As String = "update Tasks set FinishTime=@FinishTime,Status=@Status where TaskID=@id"

    con.Open()
    cmd = New SqlCommand(query, con)
    cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = LabelID.Text
    cmd.Parameters.Add("@FinishTime", SqlDbType.VarChar).Value = Label1.Text
    cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = comboboxstatus.Text
    cmd.ExecuteNonQuery()
    con.Close()
    MsgBox("Successfully updated!")
    refreshDGV()
End Sub

Private Sub FillByToolStripButton_Click(sender As Object, e As EventArgs) 
    Try
        Me.TasksTableAdapter.FillBy(Me.RestaurantDatabaseDataSet2.Tasks)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
    Dim i As Integer
    i = DataGridView1.CurrentRow.Index
    Me.LabelID.Text = DataGridView1.Item(0, i).Value
End Sub

Private Sub btnstart_Click(sender As Object, e As EventArgs) Handles btnstart.Click
    Dim con As New SqlConnection("Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true")
    Dim query As String = "update Tasks set StartTime=@StartTime,Status=@Status where TaskID=@id"

    con.Open()
    cmd = New SqlCommand(query, con)
    cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = LabelID.Text
    cmd.Parameters.Add("@StartTime", SqlDbType.VarChar).Value = Label1.Text
    cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Working on it!"
    cmd.ExecuteNonQuery()
    con.Close()
    MsgBox("Successfully started!")
    refreshDGV()
End Sub

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Label1.Text = Date.Now.ToString("dd MMM yyyy  hh:mm:ss")
End Sub


    End Class

This is what the application looks like:

I want the code to check for null data in the StartTime column. If its null, then the complete button won't work. Button1 is the button to complete a task.

CodePudding user response：

ExecuteNonQuery returns an integer with the number of rows affected.

If you create the query so that it does not do an update if the column is NULL, then it will return 0, which you can check for.

Also, it is easier to put the connection string in just one place, so that if you need to change it you only have to do so once - it is too easy to miss an occurrence of the string and have to go and edit it again. Often, such data is stored in the settings for the program, but I've made it as a constant for this example:

Public Const CONNSTR As String = "Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true"

'....

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim query As String = "UPDATE Tasks
                             SET FinishTime = @FinishTime, Status = @Status
                             WHERE TaskID = @id
                             AND StartTime IS NOT NULL"

    Dim nRowsAffected = 0

    Using con As New SqlConnection(CONNSTR),
          cmd As New SqlCommand(query, con)

        cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = LabelID.Text
        cmd.Parameters.Add("@FinishTime", SqlDbType.VarChar).Value = Label1.Text
        cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = comboboxstatus.Text

        con.Open()
        nRowsAffected = cmd.ExecuteNonQuery()

    End Using

    If nRowsAffected = 0 Then
        MsgBox("Database not updated - check for empty StartTime.")
    Else
        MsgBox("Successfully updated!")
    End If

    refreshDGV()

End Sub

The Using statement makes sure that "unmanaged resources" are released when it is done.