Solved

asp.net - pull rows from ms sql svr 05 DB...insert into ...with a button for the user to remove...

Posted on 2011-03-10
11
208 Views
Last Modified: 2012-05-11
I need to pulls rows off db..and insert into a gridview...or label controls..doesnt matter....but on each row..I need to have a button ..so if they want to delete that row..they can press it...and only that row will be affected...I provided code..below..but its got a long way to go...
public sub page_load () handles me.load

  	 
Try
            Dim MyDropDown As DropDownList
           
            Dim myArray As New ArrayList()
            Dim dataReader As SqlDataReader
            Dim x As Integer = 0
            Dim sqlConnection As String
            
            TC_ID = Session("SessionTC_ID")
            MyDropDown = DropDownList1
            
               
            Using sqlConn As New SqlConnection
                sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Terrrnert;Trusted_connection=true;"
                sqlConn.ConnectionString = sqlConnection
                Dim SQLstring As String = "SELECT distinct(campaign_name) FROM campaign WHERE TC_ID = '" & TC_ID & "'"
          
                sqlConn.Open()
                Using command As New SqlCommand(SQLstring, sqlConn)
                    dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
                    If dataReader.HasRows Then
                        Do While dataReader.Read()
                            Session("sessionTO_NUMBER") = dataReader("TO_NUMBER").ToString()
                        Loop
                        
                    End If
                    dataReader.Close()
                    sqlConn.Close()
                End Using
            End Using
            
            myArray.Sort()

            MyDropDown.DataSource = myArray
            MyDropDown.DataBind()
        Catch ex As Exception
           
        End Try




        '--------------------------------

        ' POPULATE THE GRIDVIEW CONTROL..ON SELECTION FROM DROPDOWNLIST

        '--------------------------------

        Try

            Dim Go_Message As TextBox

            Dim sqlConnection As String

            Go_Message = TextBox1

            

            Using sqlConn As New SqlConnection

                sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Mycomp;Trusted_connection=true;"

                sqlConn.ConnectionString = sqlConnection

                Dim SQLstring As String = "SELECT * FROM  camp WHERE TC_ID = '" & tc_id

                Dim TblAdaptar As New SqlDataAdapter(SQLstring, sqlConn)

                Dim Tbl As New DataSet

                Tbl.Clear()

                Try

                    TblAdaptar.Fill(Tbl)

                    Me.GridView1.DataSource = Nothing

                    Me.GridView1.DataSource = Tbl

                    Me.GridView1.DataBind()

                Catch ex As Exception

                End Try

            End Using

        Catch ex As Exception

        End Try

End sub

Open in new window

0
Comment
Question by:GlobaLevel
  • 5
  • 5
11 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35099740
Hi,

You have to add a DELETE command column to your grid, that will show a delete button to each row of your grid, after that you add your delete statment in the RowDeleting event of your grid, it will looks like this


Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting

        'Get the selected row
        Dim row As GridViewRow
        row = GridView1.Rows(e.RowIndex)

        'Obtain the ID of the record to delete just that one
        'Assuming yout Record ID is in the first column
        Dim strID As String
        strID = row.Cells(0).Text

        Using sqlConn As New SqlConnection

            sqlConn.ConnectionString = "Data Source=xx.xx.xx.xx;Initial Catalog=Terrrnert;Trusted_connection=true;"

            'HERE YOU PUT THE REAL DELETE STATEMENT
            Dim SQLstring As String = "DELETE campaign WHERE TC_ID = '" & strID & "'"

            sqlConn.Open()
            Using command As New SqlCommand(SQLstring, sqlConn)
                Try
                    command.ExecuteNonQuery()
                Catch ex As Exception
                    'Some code handling the error
                End Try
                sqlConn.Close()
            End Using
        End Using

    End Sub

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35099861
..looks good...fyi...I am trying to do this all manually...trying to stay away from the wizards...if it makes a difference...
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35099936
Yes, do it manually will give you full control over the process, and in many cases gives you better performance since you do not use nothing you do not need.

The example I send you works manually since you are using an Event handled by you.

Adding the Delete Command Column to your grid also can be done manually, adding this line to your gridview markup

<asp:CommandField ShowDeleteButton="True" />
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 10

Author Comment

by:GlobaLevel
ID: 35099987
GridView1_RowDeleting

...just so I understand you correctly...in order to call the event...

<asp:CommandField ShowDeleteButton="True" onClick="GridView1_RowDeleting" />

...correct..?
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35100047
Nope, usually you do not need to asign the event handler to the column, but if the button does not call the event, you have to add it, but, to the gridview markup

<asp:GridView ID="GridView1" runat="server"
        ...
        OnRowDeleting="GridView1_RowDeleting"
        ...

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35108242
got an error, hen I tried to attach the delete...see below..
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" 
            GridLines="None">
            <RowStyle BackColor="#EFF3FB" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
            <asp:CommandField ShowDeleteButton="True" />

        </asp:GridView>

Open in new window

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35108465
Ok, the error is because the commandField has to be inside the <Columns> Tag, but based in your gridview markup, you  are not declaring the columns manually, and you have the AutoGenerateCoumns Property of the gridview ON.

So, if this is true, remove the  <asp:CommandField ShowDeleteButton="True" />, and set the property AUTOGENERATEDELETEBUTTON  On, that will show you the Delete Column, and the event handler for RowDeleting will work with no problem
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35109663
okay...a little confused...is this what you mean...

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
            GridLines="None">
            <RowStyle BackColor="#EFF3FB" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
            <AUTOGENERATEDELETEBUTTON="On" />

        </asp:GridView>
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35109857
No but almost ;)

The AutoGenerateDeleteButton="true" has to be inside the Gridview like this

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
            AutoGenerateDeleteButton="true" GridLines="None" >
            <RowStyle BackColor="#EFF3FB" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />

        </asp:GridView>
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35217757
I apologize, I will finish up the weekend...
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question