Solved

Datagrid Update/Edit Command with Stored Procedure

Posted on 2004-09-22
10
3,096 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
  • 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
Industry Leaders: 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

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

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…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

763 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