Solved

Datagrid Update/Edit Command with Stored Procedure

Posted on 2004-09-22
10
3,091 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

18 Experts available now in Live!

Get 1:1 Help Now