Solved

Datagrid Update/Edit Command with Stored Procedure

Posted on 2004-09-22
10
3,089 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Javascript in a user control not working 17 69
Multiple file Upload asp.net 2 41
C# GridRow get Old/New Value 1 55
SQL Login 17 41
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

864 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now