Solved

Datagrid Update/Edit Command with Stored Procedure

Posted on 2004-09-22
10
3,104 Views
Last Modified: 2012-06-21
Can you use the dg_EditCommand with a stored procedure?  The reason I ask is because i previously used this code:

        dgBloodDrive.EditItemIndex = e.Item.ItemIndex

        ' Refill DataSets with information
        BindData()

        Dim MyADData As New MyData

        Dim strName As String = MyADData.First & " " & MyADData.Last
        CType(dgBloodDrive.Items(dgBloodDrive.EditItemIndex).FindControl("Name"), TextBox).Text = strName
        Dim strDept As String = MyADData.Dept
        CType(dgBloodDrive.Items(dgBloodDrive.EditItemIndex).FindControl("Dept"), TextBox).Text = strDept
        Dim strPhone As String = MyADData.Phone
        CType(dgBloodDrive.Items(dgBloodDrive.EditItemIndex).FindControl("Phone"), TextBox).Text = strPhone


     Private Sub BindData()

        Dim strSQL As String = "SELECT * FROM Table"
        Dim conn As New SqlConnection(glbConnection)
        Dim cmd As New SqlCommand(strSQL, conn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        dg.DataSource = ds 'Grab the new data            
        dg.DataBind()
        conn.Close() 'Close the connection    

    End Sub


Now, I want to know if I can get data from a stored procedure instead of binding to a dataset and filling a dataadapter

THANKS
0
Comment
Question by:luckyinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 28

Accepted Solution

by:
mmarinov earned 150 total points
ID: 12127152
Hi,

if you use the stored procedure for the select - it is not a problem
for the update operation you have to manually set the values to the stored procedure / sql update statement

also you can use the sqlcommandbuilder

Regards,
B..M
0
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 100 total points
ID: 12127167
This loops through all records:

  Private Sub BindData()

        Dim strSQL As String = "SELECT * FROM Table"
        Dim conn As New SqlConnection(glbConnection)
        Dim cmd As New SqlCommand(strSQL, conn)
        Dim dr As New SqlDataReader
        dr = cmd.ExecuteReader(commandbehavior.closeconnection)
        While dr.read        
        MyLabel.Text = dr.item("MyDatabaseFieldName") 'Repeat for each field as neccessary
        End While
        conn.Close() 'Close the connection    

    End Sub

Use this for only one record:

Private Sub BindData()

        Dim strSQL As String = "SELECT * FROM Table"
        Dim conn As New SqlConnection(glbConnection)
        Dim cmd As New SqlCommand(strSQL, conn)
        Dim dr As New SqlDataReader
        dr = cmd.ExecuteReader(commandbehavior.closeconnection)
        dr.read        
        MyLabel.Text = dr.item("MyDatabaseFieldName") 'Repeat for each field as neccessary
        conn.Close() 'Close the connection    

    End Sub

Is that what you were looking for?

Regards,

Aeros

Regards,

Aeros
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12127175
just change the sql string to a stored procedure and set your paramaters

  Private Sub dg_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.UpdateCommand
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim intQuantity As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE TempSession SET Quantity = " & intQuantity.ToString & " WHERE SessionString LIKE '" & Me.Session.SessionID.ToString & "' AND ProductName LIKE '" & e.Item.Cells(2).Text & "'"
        cmd.Connection = cnn
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
        dg.EditItemIndex = -1
        LoadProductData()
        CalculateTotal()
    End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:luckyinc
ID: 12127239
Well,

I have a stored procedure that returns my values to fill a datagrid.  But I need to edit the information on the datagrid, so I created the Edit and Delete button.  I have previously used this command with using datasets/adapters and manually entered a SQL string statement.  I already have stored procedures written for Update, Edit, and Delete, so I do not want to spend extra time writing the code when my producedures are already in place.  

When I select the Edit button, I need (in some cases) a drop down list selection from another Stored Procedure, and textboxes to edit the record within the datagrid.  Do I use this:

<asp:TemplateColumn HeaderText="Department">
   <ItemTemplate>
      <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Department") %>' ID="Label2">
      </asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" Width="75px" Text='<%# DataBinder.Eval(Container, "DataItem.Department") %>' ID="Dept">
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>


The only thing is this time, I am using a combo box where my previous code only used textboxes.  Now that I have stored procedures for this process, I want to pass the ID to the procedure and Update/Delete these Items.  My problem is understanding how to get the datagrid to maintain its state when the Edit Command runs....in which I was just explained by mmarinov that you can use the sqlcommandbuilder....never used that before.

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12127256
heres my entire routine to do this:

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            GetSelectedProductInfo()
            Me.lblProductID.Text = CStr(Session("ProductID")).ToString
            Me.lblQuantity.Text = CInt(Session("Quantity")).ToString
            Me.lblSessionID.Text = CInt(Session("SessionID")).ToString
            Me.lblWeight.Text = CStr(Session("Weight"))
            Session("SessionID") = Me.lblSessionID.Text.ToString
            LoadProductData()
            CalculateTotal()
        End If
    End Sub
    Private Sub LoadProductData()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT TempSession.SessionID, TempSession.SessionString, TempSession.ProductID, TempSession.ProductName,TempSession.ProductPrice, TempSession.Quantity FROM TempSession " & _
                "WHERE SessionString = '" & Me.Session.SessionID & "'"
        cmd.Connection = cnn
        cnn.Open()
        dg.DataSource = cmd.ExecuteReader
        dg.DataBind()
        Me.HyperLink1.NavigateUrl = "ProductDetails.aspx?ProductID=" & lblProductID.Text.ToString
        Me.HyperLink1.Text = "Continue Shopping"
        Me.HyperLink2.NavigateUrl = "Checkout.aspx?SID=" & Me.Session.SessionID
        Me.HyperLink2.Text = "Proceed To Checkout"
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
    Private Sub CalculateTotal()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        Dim intQuantity As Integer
        Dim decPrice, decTotal, decTotalTemp As Decimal
        decTotal = 0
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM TempSession WHERE SessionString = '" & Me.Session.SessionID.ToString & "';"
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While dr.Read()
            decPrice = CDec(dr.Item("ProductPrice"))
            intQuantity = CInt(dr.Item("Quantity"))
            decTotalTemp = decPrice * intQuantity
            decTotal = decTotalTemp + decTotal
        End While
        Me.lblOrderTotal.Text = CStr(decTotal).ToString
    End Sub
    Private Sub dg_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.DeleteCommand
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE * FROM TempSession WHERE ProductID = " & e.Item.Cells(5).Text & " AND SessionString = '" & Me.Session.SessionID & "'"
        cmd.Connection = cnn
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
        LoadProductData()
        CalculateTotal()
    End Sub
    Private Sub dg_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.EditCommand
        dg.EditItemIndex = e.Item.ItemIndex
        LoadProductData()
        CalculateTotal()
    End Sub
    Private Sub dg_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.UpdateCommand
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim intQuantity As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE TempSession SET Quantity = " & intQuantity.ToString & " WHERE SessionString LIKE '" & Me.Session.SessionID.ToString & "' AND ProductName LIKE '" & e.Item.Cells(2).Text & "'"
        cmd.Connection = cnn
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
        dg.EditItemIndex = -1
        LoadProductData()
        CalculateTotal()
    End Sub
    Private Sub dg_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.CancelCommand
        dg.EditItemIndex = -1
        LoadProductData()
        CalculateTotal()
    End Sub
    Private Sub GetSelectedProductInfo()
        Dim ProductID As String = Request.QueryString("ProductID")
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM Products WHERE ProductID = " & ProductID
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While dr.Read
            Session("ProductID") = ProductID
            Session("Name") = dr.Item("Name")
            Session("Price") = dr.Item("Price")
            Session("Weight") = dr.Item("Weight")
        End While
        dr.Close()
        cmd.Dispose()
        cnn.Close()
        cnn.Dispose()
    End Sub
0
 

Author Comment

by:luckyinc
ID: 12127367
Thank you for your Input...WOW!  I am trying to get away from using the cmd.commandtext = "Select * From Table"

I researched the SQLCommandBuilder and I am not sure...but I think it would be something like????



        Dim conn As New SqlConnection(glbConnection)
        Dim da As SqlDataAdapter = New SqlDataAdapter("MyStoredProcdure", conn)
        Dim cmd As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet
        da.UpdateCommand = cmd.GetUpdateCommand
        da.Fill(ds)
0
 

Author Comment

by:luckyinc
ID: 12127378
I have a SP for update data /get data/delete data
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12127406
something like this
but you have to set the select statemet so the sqlcommandbuilder to create the update/delete/insert statements


Regards,
B..M
0
 

Author Comment

by:luckyinc
ID: 12127496
Ok...I dont think I am explaining this good enough......

There is a SP  that is in a clFile.vb

Public Function GetDeptJoin(ByVal ID As String) As SqlDataReader

        Dim conn As New SqlConnection(glbConnection)
        Dim cmd As New SqlCommand("GetDocDeptJoin", conn)

        Try
            'set up the command object
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 30 '

            'set up the command parameters
            Dim parm As New SqlParameter("@ID", SqlDbType.Int, 4)
            parm.Direction = ParameterDirection.Input
            parm.Value = ID
            cmd.Parameters.Add(parm)

            'open the connection to the database and execute the command
            conn.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader()

            Return dr

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function


I then have my datagrid

 Private Sub LoadDatagrid()

        Dim dr As SqlClient.SqlDataReader
        Dim objGetData As New Intranet.clIntranet

        'Get data reader from function
        dr = objGetData.GetDeptJoin(cbo.SelectedItem.Value)

        If dr.HasRows Then
            dg.Visible = True
            dg.DataSource = dr
            dg.DataBind()
        Else
            dg.Visible = False
        End If

        dr.Close()
        dr = Nothing

        dg.Dispose()
        dg = Nothing

    End Sub


Now, I am using the Edit Command

Private Sub dg_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.EditCommand


I hope I am making sense = (
   
0
 
LVL 8

Assisted Solution

by:daffodils
daffodils earned 50 total points
ID: 12127532
What 'mmarinov' means is that set SelectCommand for the SqlDataAdpater.. so that SqlCommandBuilder can use that to create the other (Inser/Update/Delete) Commands for the SqlDataAdapter.

Somethign like this...
        Dim conn As New SqlConnection(glbConnection)

        Dim da As SqlDataAdapter = New SqlDataAdapter()
       myDataAdapter.SelectCommand = new SqlCommand("MyStoredProcdure", conn)

        Dim cmd As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet
        da.UpdateCommand = cmd.GetUpdateCommand
        da.Fill(ds)

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

635 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