如果文本框值为0，则不要插入表中 [英] If textbox value is 0 then dont insert into Table

问题描述

我想在下面的代码中添加一个条件，如果nudAdultBfast = 0和nudChildBfast = 0则不要插入早餐表。请帮助我。

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（））



                   如果totalRowsReservationNo> 0然后



                        '更新早餐桌

                        UpdateSTR =" UPDATE Breakfast SET"

                        UpdateSTR + =" Adult ='" &安培; nudAdultBfast.Text& "'，"

                        UpdateSTR + =" Child ='" &安培; nudChildBfast.Text& "'"

                        UpdateSTR + =" WHERE ReservationNo ='" &安培; txtResNo.Text& "'"

                        TransCMD =新的SqlCommand（UpdateSTR，TransCon）

                        TransCMD.Transaction = TransOperation

                        TransCMD.CommandTimeout = SqlTimeOut

                        TransCMD.ExecuteNonQuery（）



                   否则

                        '插入早餐桌

                        InsertSTR =" INSERT INTO BREAKFAST"

                        InsertSTR + ="（[ReservationNo]，[GuestName]，[ArrivalDate]，[DepartureDate]，[RoomNumber]，[RateCode]，          
      [Status]，[Adult]，[Child]）" b
                        InsertSTR + =" SELECT ReservationNo，GuestName，ArrivalDate，DepartureDate，RoomNumber，Rate.RateCode，                   
      Reservation.Status，"

                        InsertSTR + ="'" &安培; nudAdultBfast.Text& "'，"

                        InsertSTR + ="'" &安培; nudChildBfast.Text& "'"

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertSTR + ="来自预订< b $ b  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertSTR + =" LEFT OUTER JOIN RoomNo ON RoomNo.RoomID = Reservation.RoomID"

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertSTR + =" LEFT OUTER JOIN Rate ON Reservation.RateID = Rate.RateID"

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertSTR + ="在哪里Reservation.ReservationNo ='" &安培; txtResNo.Text& "'"

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; TransCMD =新的SqlCommand（InsertSTR，TransCon）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; TransCMD.Transaction = TransOperation

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; TransCMD.CommandTimeout = SqlTimeOut

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; TransCMD.ExecuteNonQuery（）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;结束如果

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; GlobalCon.Close（）

解决方案

嗨  Durgavalli26，

>>我想在下面的代码中添加一个条件，如果nudAdultBfast = 0和nudChildBfast = 0则不要插入早餐表。请帮助我。

根据你的描述，你查询早餐表存在ReservationNo = txtResNo.Text这条记录。如果有，  Adult = nudAdultBfast.Text 和孩子=  nudChildBfast.Text，无论这些值是什么，都可以执行 更新
操作。如果它不存在，请执行插入操作。

但是你也说过 如果nudAdultBfast = 0且nudChildBfast = 0，请勿插入早餐。我想如果nudAdultBfast = 0和nudChildBfast = 0，则表示有此记录 其中ReservationNo = txtResNo.Text，所以它不会插入任何
记录。

顺便说一下，请使用查询语句中的参数清楚地说明，如：

 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 from Id = @ Id" 
 Dim con As New SqlConnection（str）
 con.Open（）
使用cmd作为新的SqlCommand（sql，con）
 cmd.Parameters.AddWithValue（" @ Id" ，TextBox1.Text）
 
 rowcount = cmd.ExecuteScalar（）
结束使用
 
如果rowcount> 0然后
 
 Dim updaterow As String =" update Test1 set Adult = @ Adult，Child = @ Child，其中Id = @ Id" 
尺寸CMD1作为新的SqlCommand（updaterow，CON）
 cmd1.Parameters.AddWithValue（QUOT; @成人及QUOT ;, nudAdultBfast.Text）
 cmd1.Parameters.AddWithValue（QUOT; @儿童及QUOT ;, nudChildBfast.Text）
 cmd1.Parameters.AddWithValue（" @ Id"，TextBox1.Text）
 cmd1.ExecuteNonQuery（）
 
 Else 
 Dim insertrow As String ="插入Test1值（@ Id，@ Adult，@ Child）" 
尺寸CMD2作为新的SqlCommand（的InsertRow，CON）
 cmd2.Parameters.AddWithValue（QUOT; @成人及QUOT ;, nudAdultBfast.Text）
 cmd2.Parameters.AddWithValue（QUOT; @儿童及QUOT ;, nudChildBfast.Text）
 cmd2.Parameters.AddWithValue（" @ Id"，TextBox1.Text）
 cmd2.ExecuteNonQuery（）
 End if 
 con.Close（）
 End Sub 

 请在此处提供您的表格，以帮助我们了解你的问题。

最好的问候，

樱桃

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()

解决方案

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

这篇关于如果文本框值为0，则不要插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！