locked
SQL connection - resuse a selectstatement and update each row RRS feed

  • Question

  • User-60784728 posted

    Hi!

    I´m having some SQL connections with select statements in code behind. I would like to find a safe och good way to also update the selected rows.

    As for today I use a new SQLCommande with the update statement within a FOR Each drv as DatarovWiev. That only makes a update statement running for Each and not actually selecting that particular row, so I must add paramaters to make sure that that row is the only one updated.

    Can I somehow use for each row update "this row" SET column2 = "new value".

    Best regards Daniel

    Wednesday, June 15, 2011 7:04 AM

Answers

  • User-60784728 posted
        Dim conn As New SqlConnection("Data Source=SQL;Initial Catalog=TABLE;User Id=sa;Password=PASS;")
            conn.Open()
    
    'select statement and fill table
            Dim da As New SqlCommand("select whole_phone_number, agent_input39, agent_input1, agent_input2, agent_input3, agent_input4, agent_input5, BOKEN = ('I ditt välkomstpaket hittar du ' + agent_input26 + ' och ' + agent_input27+'.') from agent_input where agent_input39 like 'srk%' AND convert(varchar,entry_date,102)  >= '2011.06.13' AND convert(varchar,entry_date,102)  <= GETDATE()-1  and agent_input24 is null and agent_input22 is null ", conn)
    
            Dim mySqlDataAdapter As New SqlDataAdapter()
            mySqlDataAdapter.SelectCommand = da
    
            Dim ds As New DataSet
            mySqlDataAdapter.Fill(ds)
            Dim dt As DataTable = ds.Tables(0)
      
    
            rptList.DataSource = dt
            rptList.DataBind()
    
    'Here I want to update each selected (returned row in my selected statement). As for now I´m running a new update statement with a where clause. I want to update the already selected rows.
            For Each drv As DataRowView In dt.DefaultView
                Dim up As New SqlCommand("UPDATE agent_input SET agent_input24 = '1' where agent_input39 like 'srk%' AND convert(varchar,entry_date,102)  >='2011.06.13' and whole_phone_number= @nummer and agent_input39=@proj  AND convert(varchar,entry_date,102)  <= GETDATE()-1  and agent_input24 is null and agent_input22 is null ", conn)
    
                up.Parameters.Add("@nummer", SqlDbType.VarChar, 20).Value = drv("whole_phone_number")
                up.Parameters.Add("@proj", SqlDbType.VarChar, 20).Value = drv("agent_input39")
                up.ExecuteNonQuery()
    
    
    
            Next
    
    
            If IsPostBack Then
    
                rptList.DataSource = dt
                rptList.DataBind()
            End If
    
            conn.Close()
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 15, 2011 8:33 AM

All replies

  • User1315229986 posted

    Please post your code and explain the above things with it

    Wednesday, June 15, 2011 7:11 AM
  • User-60784728 posted
        Dim conn As New SqlConnection("Data Source=SQL;Initial Catalog=TABLE;User Id=sa;Password=PASS;")
            conn.Open()
    
    'select statement and fill table
            Dim da As New SqlCommand("select whole_phone_number, agent_input39, agent_input1, agent_input2, agent_input3, agent_input4, agent_input5, BOKEN = ('I ditt välkomstpaket hittar du ' + agent_input26 + ' och ' + agent_input27+'.') from agent_input where agent_input39 like 'srk%' AND convert(varchar,entry_date,102)  >= '2011.06.13' AND convert(varchar,entry_date,102)  <= GETDATE()-1  and agent_input24 is null and agent_input22 is null ", conn)
    
            Dim mySqlDataAdapter As New SqlDataAdapter()
            mySqlDataAdapter.SelectCommand = da
    
            Dim ds As New DataSet
            mySqlDataAdapter.Fill(ds)
            Dim dt As DataTable = ds.Tables(0)
      
    
            rptList.DataSource = dt
            rptList.DataBind()
    
    'Here I want to update each selected (returned row in my selected statement). As for now I´m running a new update statement with a where clause. I want to update the already selected rows.
            For Each drv As DataRowView In dt.DefaultView
                Dim up As New SqlCommand("UPDATE agent_input SET agent_input24 = '1' where agent_input39 like 'srk%' AND convert(varchar,entry_date,102)  >='2011.06.13' and whole_phone_number= @nummer and agent_input39=@proj  AND convert(varchar,entry_date,102)  <= GETDATE()-1  and agent_input24 is null and agent_input22 is null ", conn)
    
                up.Parameters.Add("@nummer", SqlDbType.VarChar, 20).Value = drv("whole_phone_number")
                up.Parameters.Add("@proj", SqlDbType.VarChar, 20).Value = drv("agent_input39")
                up.ExecuteNonQuery()
    
    
    
            Next
    
    
            If IsPostBack Then
    
                rptList.DataSource = dt
                rptList.DataBind()
            End If
    
            conn.Close()
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 15, 2011 8:33 AM
  • User3866881 posted

    Hello:)

    In fact you can use SqlCommandBuilder to automatically generate insert, update and delete commands and the next step that you can do is just to do inserting, deleting and updating in the DataTable, and in the end you can just call "Update" method instead of running SqlCommand again and again.

    In fact I did a simple demo for you, if you want it, please send me email at maledong@qq.com, with the title——Batch Excel-Like GridView Inserting, Deleting and Updating Wanted.

    Here's the codes——

    【aspx】

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="VBASPNETExcelLikeGridView._Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">

    <head id="Head1" runat="server">
        <title>VBExcelLikeGridView</title>
        <script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js" type="text/javascript">
        </script>
        <script type="text/javascript">

            //Function to read HidState (JSON) to keep the state color
            function ResetColors(color1, color2) {
                var contents = $(":hidden:last").val();
               
                // If not "[]", meaning something changed, reset colors.
                if (contents.toString() != "[]") {
                    // Convert to JSON object.
                    var objectc = eval(contents);

                    // To reset colors depending on whether it's changed
                    for (var i = 0; i < objectc.length; ++i) {
                        if (objectc[i].Color != '') {
                            $("td:eq(" + objectc[i].Index + ")").css("background-color", objectc[i].Color);
                        }

                        var checked = objectc[i].Deleted == "True";

                        if (parseInt(objectc[i].Index) % 2 == 0) {
                            $("td:eq(" + parseInt(objectc[i].Index - 2) + ")").find(":checkbox").attr("checked", checked);
                        }
                        else {
                            $("td:eq(" + parseInt(objectc[i].Index - 3) + ")").find(":checkbox").attr("checked", checked);
                        }

                        // If a checkbox checked, change the row color
                        if (checked) {
                            $("td:eq(" + objectc[i].Index + ")").parent().css("background-color", "red");
                        }
                    }
                }

            }

            //Validation for Save
            function SaveValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (Page_ClientValidate('Fill')) {
                    return confirm('Do you really want to save all these changes together?');
                }
                else {
                    alert("Attention! You cannot leave a name blank!");
                }
            }

            //Validation for Insert
            function InsertValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (!Page_ClientValidate('Insert')) {
                    alert("Attention! You cannot insert a blank name!");
                }
            }

            // Add dynamically events for all textboxes
            // except the footer one to turn the background color.
            function AddEvents() {

                var rowarray = $("tr");
                for (var i = 0; i < rowarray.length - 1; ++i) {
                    $(rowarray[i]).find(":text").change(function () {
                        $(this).parent().css("background", "blue");
                    });
                }

            }

            $(function () {

                //Keep the original color row for odd
                var color1 = $("tr:eq(1)").css("background-color").valueOf();
                var color2 = $("tr:eq(2)").css("background-color").valueOf();
                var headercolor = $("tr:first").css("background-color").valueOf();
                var footercolor = $("tr:last").css("background-color").valueOf();

                AddEvents();

                // Header checkbox's cascading effect:
                $("#chkAll").click(function () {

                    $(":checkbox").attr("checked", $(this).attr("checked"));

                    if ($(this).attr("checked")) {
                        $(":checkbox").parent().parent().css("background-color", "red");
                        //Reset the color of header
                        $("tr:first").css("background-color", headercolor);
                    }
                    else {
                        $("tr:odd").css("background-color", color1);
                        $("tr:even").css("background-color", color2);

                        //Reset the color of header and footer
                        $("tr:first").css("background-color", headercolor);
                        $("tr:last").css("background-color", footercolor);
                    }
                });

                //Single checkbox checked event
                $(":checkbox").click(function () {
                    if ($(this).attr("checked")) {
                        $(this).parent().parent().css("background-color", "red");
                    }
                    else {
                        if ($(this).parent().parent().index() % 2 == 0) {
                            $(this).parent().parent().css("background-color", color2);
                        }
                        else {
                            $(this).parent().parent().css("background-color", color1);
                        }
                    }

                    //Reset the header color
                    $("tr:first").css("background-color", headercolor);
                });

                ResetColors(color1, color2);
            })

        </script>
    </head>
    <body>
        <form id="form1" runat="server">
        <h1>
            Demo for Batching Actions</h1>
        <span style="color: Red">red row to be deleted</span>
        <br />
        <span style="color: green">green row to be added</span>
        <br />
        <span style="color: blue">blue cell to be modified</span>
        <br />
        <hr />
        <div>
            <asp:GridView ID="GridView1" runat="server" Width="70%" Height="50%" AutoGenerateColumns="False"
                CellPadding="4" ForeColor="#333333" GridLines="None" ShowFooter="True">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:TemplateField HeaderText="Delete State">
                        <HeaderTemplate>
                            <input id="chkAll" type="checkbox" />
                            Delete
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="chkDelete" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Id">
                        <ItemTemplate>
                            <%#Eval("Id") %>
                        </ItemTemplate>
                        <FooterTemplate>
                            Name:<asp:TextBox ID="tbNewName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="tbNewName"
                                ErrorMessage="You cannot insert a balnk name!" ForeColor="#FFFF66" ValidationGroup="Insert"></asp:RequiredFieldValidator>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PersonName") %>'>
                            </asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="tbName"
                                ErrorMessage="You cannot leave a name blank!" ValidationGroup="Fill"></asp:RequiredFieldValidator>
                        </ItemTemplate>
                        <FooterTemplate>
                            Address:<asp:TextBox ID="tbNewAddress" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Address">
                        <ItemTemplate>
                            <asp:TextBox ID="tbAddress" runat="server" Text='<%#Eval("PersonAddress") %>'>
                            </asp:TextBox>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="btnAdd" runat="server" Text="Add a new row" OnClick="btnAdd_Click"
                                ValidationGroup="Insert" OnClientClick="InsertValidate()" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" HorizontalAlign="Center" VerticalAlign="Middle" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            </asp:GridView>
        </div>
        <asp:Button ID="btnSaveAll" runat="server" Height="30px" Text="Save All Changes"
            Width="149px" OnClick="btnSaveAll_Click" OnClientClick="SaveValidate()" ValidationGroup="Fill" />
        <asp:HiddenField ID="HidState" runat="server" Value="[]" />
        </form>
    </body>
    </html>

    【cs codes】

    '***************************** Module Header ******************************\
    '* Module Name:    DBProcess.vb
    '* Project:        CSExcelLikeGridView
    '* Copyright (c) Microsoft Corporation
    '*
    '* This is a UI module which lets which lets you do a batch inserting, updating
    '* as well as deleting.
    '*
    '* This source is subject to the Microsoft Public License.
    '* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    '* All other rights reserved.
    '\****************************************************************************


    Imports System.Web.UI.WebControls
    Imports System.Data
    Imports System.Text

    Partial Public Class _Default
        Inherits System.Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            If Not IsPostBack Then
                Dim p As New DBProcess()
                Dim dt As DataTable = p.GetDataTable()
                ViewState("dt") = dt
                GridView1.DataSource = dt
                GridView1.DataBind()
            End If
        End Sub

        ''' <summary>
        ''' This function will confirm the last modification
        ''' and do a batch save.
        ''' </summary>
        Protected Sub btnSaveAll_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSaveAll.Click

            'Default the value is false, meaning that the db isn't saved
            Dim flag As Boolean = False

            Dim p As New DBProcess()
            Dim dt As DataTable = CType(ViewState("dt"), DataTable)

            ' Change states and do a batch update
            For i As Integer = 0 To GridView1.Rows.Count - 1
                If TryCast(GridView1.Rows(i).FindControl("chkDelete"), CheckBox).Checked Then
                    dt.Rows(i).Delete()
        flag = True
                Else
                    If dt.Rows(i)("PersonName").ToString() <> TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox).Text Then
                        If dt.Rows(i).RowState = DataRowState.Unchanged Then
                            dt.Rows(i).BeginEdit()
                        End If
                        dt.Rows(i)("PersonName") = TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox).Text
                        If dt.Rows(i).RowState = DataRowState.Unchanged Then
                            dt.Rows(i).EndEdit()
                        End If
                        flag = True
                    End If
                    If dt.Rows(i)("PersonAddress").ToString() <> TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox).Text Then
                        If dt.Rows(i).RowState = DataRowState.Unchanged Then
                            dt.Rows(i).BeginEdit()
                        End If
                        dt.Rows(i)("PersonAddress") = TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox).Text
                        If dt.Rows(i).RowState = DataRowState.Unchanged Then
                            dt.Rows(i).EndEdit()
                        End If
                        flag = True
                    End If
                End If
            Next

            p.BatchSave(dt)

            ' Save data into db, so no need to maintain the state colors
            HidState.Value = "[]"

            dt = p.GetDataTable()
            GridView1.DataSource = dt
            GridView1.DataBind()

            If (flag) Then
                ClientScript.RegisterStartupScript([GetType](), "js", "alert('Save All Changes successfully!');", True)
            End If

        End Sub

        ''' <summary>
        '''  This function processes with the following aspect:
        '''  Record every row and every cell's state into HidState.
        ''' </summary>
        Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim i As Integer = 0
            Dim p As New DBProcess()
            Dim dt As DataTable = CType(ViewState("dt"), DataTable)
            Dim tb1 As TextBox = Nothing
            Dim tb2 As TextBox = Nothing
            Dim sbu As New StringBuilder()
            Dim chkbox As CheckBox = Nothing

            ' To maintain the JSON state.
            sbu.Append("[")

            While i < dt.Rows.Count
                ' Loop the cells
                For j As Integer = 2 To GridView1.HeaderRow.Cells.Count - 1
                    tb1 = TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox)
                    tb2 = TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox)

                    sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + j))

                    'Not added, meaning unchanged or added
                    If dt.Rows(i).RowState <> DataRowState.Added Then
                        ' Decide whether the 1st cell in db changed or not
                        If j = 2 Then
                            If Not tb1.Text.Equals(dt.Rows(i)(j - 1, DataRowVersion.Original)) Then
                                dt.Rows(i).BeginEdit()
                                sbu.Append("','Color':'blue',")
                                dt.Rows(i)(j - 1) = tb1.Text
                            Else
                                sbu.Append("','Color':'',")
                            End If
                        Else
                            ' Decide whether the 2nd cell in db changed or not
                            If Not tb2.Text.Equals(dt.Rows(i)(j - 1, DataRowVersion.Original)) Then
                                dt.Rows(i).BeginEdit()
                                sbu.Append("','Color':'blue',")
                                dt.Rows(i)(j - 1) = tb2.Text
                            Else
                                sbu.Append("','Color':'',")
                            End If
                        End If
                        dt.Rows(i).EndEdit()
                    Else

                        ' Add row should be marked as green
                        If dt.Rows(i).RowState = DataRowState.Added Then
                            sbu.Append("','Color':'green',")
                        Else
                            ' Other rows should keep the original color
                            sbu.Append("','Color':'',")
                        End If
                    End If

                    ' Keep the Delete Statement
                    chkbox = TryCast(GridView1.Rows(i).FindControl("chkDelete"), CheckBox)
                    sbu.Append("'Deleted':'" & chkbox.Checked & "'},")
                Next
                i += 1
            End While

            Dim r As DataRow = dt.NewRow()
            r("PersonName") = TryCast(GridView1.FooterRow.FindControl("tbNewName"), TextBox).Text
            r("PersonAddress") = TryCast(GridView1.FooterRow.FindControl("tbNewAddress"), TextBox).Text
            dt.Rows.Add(r)
            sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + 2) & "','Color':'green','Deleted':'false'},")
            sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + 3) & "','Color':'green','Deleted':'false'}")
            sbu.Append("]")
            HidState.Value = sbu.ToString()
            GridView1.DataSource = dt
            GridView1.DataBind()
        End Sub
    End Class

    '***************************** Module Header ******************************\
    '* Module Name:    DBProcess.vb
    '* Project:        VBASPNETExcelLikeGridView
    '* Copyright (c) Microsoft Corporation
    '*
    '* This module is managing the Connection,adapter as well as datatable
    '* instances.
    '*
    '* This source is subject to the Microsoft Public License.
    '* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    '* All other rights reserved.
    '\****************************************************************************


    Imports System.Data.SqlClient
    Imports System.Configuration
    Imports System.Data
    Imports System.IO
    Imports System.Runtime.Serialization.Formatters.Binary

    ''' <summary>
    ''' This is a class that manages the whole db's connection, create a
    ''' memory-based datatable, maintaince the state and do a batch save
    ''' things together in different functions.
    ''' </summary>
    Public NotInheritable Class DBProcess
        Private Shared conn As SqlConnection = Nothing
        Private Shared adapter As SqlDataAdapter = Nothing
        Private dt As DataTable = Nothing

        ''' <summary>
        ''' This static constructor will read out the whole connect string
        ''' from defined web.config. The connection and adapter are both
        ''' pointing to the same db, so only create once.
        ''' </summary>
        Shared Sub New()
            Dim constr As String = ConfigurationManager.ConnectionStrings("MyConn").ConnectionString
            conn = New SqlConnection(constr)
            Dim command As String = "select * from tb_personInfo"
            adapter = New SqlDataAdapter(command, conn)
            Dim builder As New SqlCommandBuilder(adapter)
            builder.GetDeleteCommand(True)
            builder.GetInsertCommand(True)
            builder.GetUpdateCommand(True)
        End Sub

        ''' <summary>
        ''' This function will create a datatable to reload all the data
        ''' from the db.
        ''' </summary>
        Public Function GetDataTable() As DataTable
            dt = New DataTable()
            adapter.Fill(dt)
            dt.Columns(0).AutoIncrement = True
            dt.Columns(0).AutoIncrementStep = 1
            dt.Columns(0).AutoIncrementSeed = dt.Rows.Count
            Return dt
        End Function

        ''' <summary>
        ''' Update the DataTable and delete the serialized file.
        ''' </summary>
        Public Sub BatchSave(ByVal dt As DataTable)
            adapter.Update(dt)
        End Sub
    End Class

    Thursday, June 16, 2011 10:37 PM