Solved

Datagrid Update/Edit Command with Stored Procedure

Posted on 2004-09-22
10
3,088 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:luckyinc
Comment Utility
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
Comment Utility
I have a SP for update data /get data/delete data
0
 
LVL 28

Expert Comment

by:mmarinov
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now