If陳述句不允許插入陳述句作業，因為單元格為空

我希望代碼不允許完整按鈕作業，因為“StartTime”列為空。

附上代碼如下：

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

這是應用程式的樣子：

我希望代碼檢查 StartTime 列中的空資料。如果它為空，則完成按鈕將不起作用。Button1 是完成任務的按鈕。

uj5u.com熱心網友回復：

ExecuteNonQuery 回傳一個包含受影響行數的整數。

如果您創建查詢使其在列為 NULL 時不進行更新，則它將回傳 0，您可以檢查該值。

此外，將連接字串放在一個地方更容易，這樣如果您需要更改它，您只需要做一次 - 很容易錯過字串的出現而不得不再次編輯它. 通常，此類資料存盤在程式的設定中，但我已將其作為此示例的常量：

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

該Using陳述句確保在完成后釋放“非托管資源”。

標籤：sql-server 网络

上一篇：使用StreamReadervb.net的行文本檔案索引

下一篇：如何獲取一系列值-Visualbasic