none
If textbox value is 0 then dont insert into Table RRS feed

  • Question

  • Hi,

    I want to add a condition in the below code which is if nudAdultBfast = 0 and nudChildBfast = 0 the dont insert into breakfast table. Kindly help me.

    SelectSTR = "SELECT count(*) FROM Breakfast "
    SelectSTR += "WHERE ReservationNo = '" & txtResNo.Text & "' "

    Dim CountCMD2 As SqlCommand = New SqlCommand(SelectSTR, GlobalCon)

    Dim totalRowsReservationNo As Integer = CInt(CountCMD2.ExecuteScalar())

                        If totalRowsReservationNo > 0 Then

                            'update breakfast table
                            UpdateSTR = "UPDATE Breakfast SET "
                            UpdateSTR += "Adult = '" & nudAdultBfast.Text & "',"
                            UpdateSTR += "Child = '" & nudChildBfast.Text & "'"
                            UpdateSTR += " WHERE ReservationNo = '" & txtResNo.Text & "' "
                            TransCMD = New SqlCommand(UpdateSTR, TransCon)
                            TransCMD.Transaction = TransOperation
                            TransCMD.CommandTimeout = SqlTimeOut
                            TransCMD.ExecuteNonQuery()

                        Else
                            'insert into breakfast table
                            InsertSTR = "INSERT INTO BREAKFAST"
                            InsertSTR += "([ReservationNo],[GuestName],[ArrivalDate],[DepartureDate],[RoomNumber],[RateCode],                              [Status],[Adult],[Child])"
                            InsertSTR += " SELECT ReservationNo,GuestName,ArrivalDate,DepartureDate, RoomNumber, Rate.RateCode,                          Reservation.Status, "
                            InsertSTR += "'" & nudAdultBfast.Text & "',"
                            InsertSTR += "'" & nudChildBfast.Text & "'"
                            InsertSTR += " FROM Reservation"
                            InsertSTR += " LEFT OUTER JOIN RoomNo ON RoomNo.RoomID = Reservation.RoomID"
                            InsertSTR += " LEFT OUTER JOIN Rate ON Reservation.RateID = Rate.RateID"
                            InsertSTR += " WHERE Reservation.ReservationNo = '" & txtResNo.Text & "' "
                            TransCMD = New SqlCommand(InsertSTR, TransCon)
                            TransCMD.Transaction = TransOperation
                            TransCMD.CommandTimeout = SqlTimeOut
                            TransCMD.ExecuteNonQuery()
                        End If
                        GlobalCon.Close()


    Durga

    Friday, December 22, 2017 3:54 AM

All replies

  • Hi  Durgavalli26,

    >>I want to add a condition in the below code which is if nudAdultBfast = 0 and nudChildBfast = 0 the dont insert into breakfast table. Kindly help me.

    According to your description, you query breakfast table exists ReservationNo = txtResNo.Text this record. If there is, Adult =nudAdultBfast.Text  and Child =  nudChildBfast.Text , no matter what these values are, to do the update operation. If it does not exist, do insert operation.

    But you also said  if nudAdultBfast = 0 and nudChildBfast = 0, don't insert into breakfast. I think if nudAdultBfast = 0 and nudChildBfast = 0, it means that there is this record  where ReservationNo = txtResNo.Text , so it will not insert any record.

    By the way, please use parameter in your query sentence to make it clear, like this:

    Dim rowcount As Integer = 0
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Integrated Security=True"
            Dim sql As String = "select count(*) from Breakfast where Id=@Id"
            Dim con As New SqlConnection(str)
            con.Open()
                Using cmd As New SqlCommand(sql, con)
                cmd.Parameters.AddWithValue("@Id", TextBox1.Text)
    
                rowcount = cmd.ExecuteScalar()
                End Using
    
            If rowcount > 0 Then
    
                Dim updaterow As String = "update Test1 set Adult =@Adult, Child =@Child  where Id=@Id"
                Dim cmd1 As New SqlCommand(updaterow, con)
                cmd1.Parameters.AddWithValue("@Adult", nudAdultBfast.Text)
                cmd1.Parameters.AddWithValue("@Child", nudChildBfast.Text)
                cmd1.Parameters.AddWithValue("@Id", TextBox1.Text)
                cmd1.ExecuteNonQuery()
    
            Else
                Dim insertrow As String = "insert into Test1 values(@Id,@Adult,@Child)"
                Dim cmd2 As New SqlCommand(insertrow, con)
                cmd2.Parameters.AddWithValue("@Adult", nudAdultBfast.Text)
                cmd2.Parameters.AddWithValue("@Child", nudChildBfast.Text)
                cmd2.Parameters.AddWithValue("@Id", TextBox1.Text)
                cmd2.ExecuteNonQuery()
            End If
            con.Close()
        End Sub

     and please provide your table here to help us to understand your issue.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, December 27, 2017 1:20 AM
    Friday, December 22, 2017 6:46 AM
    Moderator