CODEWITHSUNDEEP
sqlvb.net
Ben
Ben

Reputation: 3

Validate SQL data and check for Null

I have a textbox(txtOrderNum) where you enter an 'order' number and update a 'userfield' based on a date that is generated in another textbox(txtDate), I want to validate that the 'order' number entered matches the 'Order' number in the database, then update 'UserField1' only if its NULL, if it doesn't match then do nothing.

My goal, I don't want to overwrite data that is already in the Userfield1, and I want to ensure I update the correct 'Order' number that exist.

****I've updated my code based on suggestion below, I just need to validate if 'Order' number exist AND matches the order number entered in the textbox, then run update query. (also need to parameterized the query, I have some ideas but could use some help)****

Public Sub executequery(ByVal query As String)
    Try
        Dim cmd As New SqlCommand(query, conn)
        conn.Open()
        If (conn.State = ConnectionState.Open) Then
            cmd.ExecuteNonQuery()
            conn.Close()
        Else
            MessageBox.Show("Check Connection")
            conn.Close()
        End If
         Catch ex As Exception
        MsgBox(ex.ToString)
        Return
    Finally
        conn.Close()
    End Try
End Sub

'My call Event via Enter Key within Textbox (txtOrdernum)

Dim conn As New SqlConnection("My data source")
    Try
        Dim updatequery As String = ("UPDATE [DATA].[dbo].[Order] SET [Userfield1] = '" & txtDate.Text.Trim() & "' WHERE  [order] ='" & txtOrdernum.Text.Trim() & "' AND [Userfield1] IS NULL")
        If e.KeyChar = Chr(13) Then 'Chr(13) 
            If txtOrdernum.Text.Length >= 8 Then
                'MessageBox.Show(updatequery)
                executequery(updatequery)
        Else
            MessageBox.Show("Invalid Order Number'")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
        Return
    End Try

Upvotes: 0

Views: 139

Answers (1)

I'm Hungry
I'm Hungry

Reputation: 201

As N0Alias stated, add "AND [UserField1] IS NULL".

Just be careful with the way you build your query. Building one like your example can allow SQL injection.

You should use the 'SqlCommand.Parameters' property to add values into your query.

Upvotes: 0

Related Questions