How to check and update existing record date when new row is entered else add new row

Question

enter code hereSql table columns ( Clientid, Client_Status, Notes, Startdt,Enddt, Entrydt, Entryid) My current code adds data rows from UI, user enters Start date, Status and notes with Enddt defaulting to 9/9/9999. I need to change my code to -whenever a new reord/Status is enrtered check for existing record/Status with that Clientid, If records exists update the EndDt of existing record from 9/9/9999 to StartDt-1( new record StartDt) which is entered from Interface. Else enter as new client.

Private Sub BtnAddStatus_Click(sender As System.Object, e As System.EventArgs) Handles BtnAddStatus.Click

    Clientid = txtboxClid.Text
    Client_Status = cbboxStatus.Text
    StartDt = txtStartDt.Text
            notes = txtnote.Text


               conn = New SqlClient.SqlConnection("conneting string")
        Dim theQuery As String = "select * from Table name where Clientid = @Clientid and EndDt = '9/9/9999'"
        Dim cmd2 As SqlCommand = New SqlCommand(theQuery, conn)
        cmd2.Parameters.AddWithValue("@Clientid", txtboxClid.Text)

        conn.Open()
        If txtboxClid.Text.Trim <> "" And txtStartDt.Text.Trim <> "" Then
            Using reader As SqlDataReader = cmd2.ExecuteReader()
                If reader.HasRows Then
                    Dim query2 As String = "UPDATETable name SET ([EndDt] = SELECT (todate(StartDt)-1) FROM Table name WHERE Clientid = @Clientid and EndDt ='9/9/9999')"
                    reader.Close()

                End If
            End Using

            Dim query As String = "INSERT INTO Table name (Clientid, Client_Status, Notes, Startdt,Enddt, Entrydt, Entryid) VALUES ('" & Clientid & "','" & Client_Status & "','" & Notes & "','" & StartDt & "',getdate(),'" & UName & "');"
            Dim command = New SqlCommand(query, myconn)
            command.ExecuteNonQuery()

            MsgBox("Status Added ")

            conn.Close()

            Call GetInfoClientid()
    End If
    End If
End Sub

Answer 1

The simple reason is the fact that you are not executing the command stored in query2, but your code has other errors and someone potentially catastrophic.

First, you should always use parameters and never concatenate strings to build sql commands. If you concatenate strings you enable a simple trick called Sql Injection that allows anyone to hack your database.

Second you could directly call the Update without checking for the presence of a previous related record. The update will simply return with 0 record changed if the record doesn't exist.

Finally, the disposable objects like the connection should be created when needed and disposed as soon as possible. The Using Statement serves for this purpose.

Dim Client_Status As String = cbboxStatus.Text
Dim notes As String = txtnote.Text

' Suppose Clientid is a number not a string
Dim Clientid as Integer = Convert.ToInt32(txtboxClid.Text)

' Suppose you have a date in your database, not a string
Dim StartDt as DateTime = Convert.ToDateTime(txtStartDt.Text)
' Calculate here the previous ending date
Dim PrevEnd As DateTime = StartDt.AddDays(-1)

' Conventional max end date
Dim maxEndDate as DateTime = new DateTime(9999,9,9)

If txtboxClid.Text.Trim <> "" And txtStartDt.Text.Trim <> "" Then

    ' Create here the connection to dispose on exit from the using statement
    Using conn As SqlConnection = New SqlClient.SqlConnection("conneting string")
        conn.Open()
        ' USE PARAMETERS EVERYWHERE. DO NOT USE STRINGS TO FIND A DATE
        Dim query2 As String = "UPDATE [Table name] SET [EndDt] = @newEnd 
                                WHERE Clientid = @Clientid 
                                  AND EndDt = @maxEnd"
        Dim command = New SqlCommand(query2, conn)
        command.Parameters.Add("@Clientid", SqlDbType.Int).Value = Clientid
        command.Parameters.Add("@newEnd", SqlDbType.Date).Value = newEnd
        command.Parameters.Add("@maxEnd", SqlDbType.Date).Value = maxEndDate
        command.ExecuteNonQuery()

        ' Prepare the insert. 
        Dim query As String = "INSERT INTO [Table name] 
              (Clientid, Client_Status, Notes, Startdt,Enddt, Entrydt, Entryid) 
               VALUES 
              (@Clientid, @status,@Notes,@StartDt,@maxDate,getdate(), @UName);"
        command.Parameters.Clear()
        command.Parameters.Add("@Clientid", SqlDbType.Int).Value = Clientid
        command.Parameters.Add("@status", SqlDbType.NVarChar).Value = Client_Status
        command.Parameters.Add("@notes", SqlDbType.NVarChar).Value = notes
        command.Parameters.Add("@startdt", SqlDbType.Date).Value = StartDt
        command.Parameters.Add("@maxDate", SqlDbType.Date).Value = maxEndDate
        command.Parameters.Add("@uname", SqlDbType.NVarChar).Value = uname
        command.CommandText = query
        command.ExecuteNonQuery()
    End Using
    Call GetInfoClientid()
End If

Notice that I pass parameters of the appropriate type for what I suppose is the type of your columns. It is common error to think that a string like '9/9/9999' is a date. But for a computer program this is a string and if you want to use as a date we need to convert it to a proper date. This conversion more often than not results in wrong data passed to the database engine.

Answer 2

This should have been handled in stored procedure. But, since you have done most of the things here, I would suggest a minor change on this which would work. First, Remove the check before update and change the update query to:

Dim query2 As String = "UPDATE Table name SET [EndDt] = todate(@StartDt)-1 WHERE Clientid = @ClientId and EndDt ='9/9/9999'"
Dim cmd As SqlCommand = new SqlCommand(query2, vbConn);
cmd.AddParam(“@StartDt”,StartDt)
cmd.AddParam("@Clientid",ClientId)

(assuming clientid to be varchar, since you have used single quotes on insert statement)

Also, write executenonquery() statement for query2.