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

Question

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.

WARNING: Your code is dangerous, it is wide open to injection attacks. Always parametrise your statements. — Thom A
– Thom A ♦, Commented Nov 1, 2021 at 8:36

Andrew Morton · Accepted Answer · 2021-11-01 11:23:32Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related