Update multiple SQL fields in a table as per condition

Question

In my asp.net+vb+sql web i have a table name LP_ION

in this table i have to update two fields as per condition

common id is Originator_ID

There will be multiple rows with Originator_ID

i want to update those

Table

i was using this query. but it works only when One originator_id row is there

in my case as you can see multiple rows row number 20 , 21 and 22

Answer 1

Well, assuming that your posted data, and your goal is to allow the user to edit those 2 columns (LPR_NO, Status)?

As noted, we not all clear as to what the issue is here.

However, assume we drop in a gridview, say like this:

        <div style="padding:35px">
            <asp:GridView ID="GridView1" runat="server" 
                AutoGenerateColumns="False" 
                DataKeyNames="ION_ID" 
                CssClass="table table-hover"
                width="55%">
                <Columns>
                    <asp:BoundField DataField="ION_ID" HeaderText="ION_ID"  />
                    <asp:BoundField DataField="Originator" HeaderText="Originator"  />
                    <asp:BoundField DataField="Originator_ID" HeaderText="Originator_ID" />
                    <asp:BoundField DataField="Date" HeaderText="Date" 
                        DataFormatString = "{0:yyyy-MM-dd}" HeaderStyle-Width="120px"   />
                    <asp:TemplateField HeaderText="LPR_NO">
                        <ItemTemplate>
                        <asp:TextBox ID="txtLPR" runat="server"
                            Text='<%# Eval("LPR_NO") %>' width="100px" >
                        </asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="LPR_Dt" HeaderText="LPR_Dt" 
                        DataFormatString = "{0:yyyy-MM-dd}" HeaderStyle-Width="120px"  />
                    <asp:BoundField DataField="Product" HeaderText="Product" />
                    <asp:BoundField DataField="Qty" HeaderText="Qty"  />
                    <asp:BoundField DataField="AU" HeaderText="AU"  />
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                            <asp:TextBox ID="txtStatus" runat="server"
                                Text='<%# Eval("Status") %>' width="60px"   >
                            </asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="UserID" HeaderText="UserID"  />
                </Columns>
            </asp:GridView>
            <br />
            <asp:Button ID="cmdSave" runat="server" Text="Save Edits" 
                CssClass="btn btn-info"
                OnClick="cmdSave_Click"
                />

            <asp:Button ID="cmdCancel" runat="server" Text="Cancel Edits" 
                CssClass="btn btn-danger"
                OnClick="cmdCancel_Click"
                style="margin-left:30px"
                />

And our code to load the Grid view is thus this:

    Dim rstData As DataTable
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            LoadGrid()
            Session("rstData") = rstData
        Else
            rstData = Session("rstData")
        End If
    End Sub

    Sub LoadGrid()

        rstData = MyRst("SELECT * FROM LP_ION")

        GridView1.DataSource = rstData
        GridView1.DataBind()


    End Sub

Ok, so the result is this, and since those 2 columns are text boxes, then I can tab around and edit them.

it looks like this:

so, only part left is to add the save code for the save button.

that looks like this:

    Protected Sub cmdSave_Click(sender As Object, e As EventArgs)

        'send edits back to table

        For Each gRow As GridViewRow In GridView1.Rows

            Dim RowData As DataRow = rstData.Rows(gRow.RowIndex)
            RowData("LPR_NO") = CType(gRow.FindControl("txtLPR"), TextBox).Text
            RowData("Status") = CType(gRow.FindControl("txtStatus"), TextBox).Text

        Next

        ' now send edits back to database.
        MySave(rstData)
        'MyToast(Me.Page, cmdSave.ClientID, "Save", "Data saved to database")

    End Sub

So, it not a lot of code. However, I don't see any particular "limitation" to edit or update each row. Even if you not using a gridview, then udpates via SQL as a general rule occur by the PK row id, not other rows that "might be" or "could have" the same values - that should not matter.

Also in above, I did use a simple routine that returns a data table to fill out the gridview, and a routine to save a data table back to the database.

those 2 code stubs were:

    Public Function MyRst(strSQL As String) As DataTable

        Dim rstData As New DataTable
        Using conn As New SqlConnection(strCon)
            Using cmdSQL As New SqlCommand(strSQL, conn)
                conn.Open()
                rstData.Load(cmdSQL.ExecuteReader)
                rstData.TableName = strSQL
            End Using
        End Using
        Return rstData

    End Function

And the table save code:

    Sub MySave(rstData As DataTable)

        Using conn As New SqlConnection(My.Settings.TEST6)
            Using cmdSQL As New SqlCommand("SELECT * FROM LP_ION", conn)
                Dim da As New SqlDataAdapter(cmdSQL)
                Dim daU As New SqlCommandBuilder(da)
                da.Update(rstData)
            End Using
        End Using

    End Sub