Results 1 to 8 of 8

Thread: [RESOLVED] Help Insert Update Delete Datagridview using MS Sql stored procedures.

  1. #1

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Resolved [RESOLVED] Help Insert Update Delete Datagridview using MS Sql stored procedures.

    Hello. I'm new to vb.net. Please bear with me. I would like to learn how to insert, update, delete data in a Datagridview. So far I learned the best approach to this is to bind the DatagridView to a Datatable? The requirement is to use stored procedures. I'm not allowed to directly access database tables.

    My public variables:

    Code:
        Public intDisbursementID As Long
        Dim CS As String = ConfigurationManager.ConnectionStrings("SimpleAccounting.My.MySettings.SimpleAcctgConnectionString").ConnectionString
        Dim cb As SqlCommandBuilder = Nothing
        Dim da As SqlDataAdapter = Nothing
        Dim ds As DataSet = Nothing
        Dim dv As DataView
        Dim dt As DataTable
        Dim bs As BindingSource
        Dim isDataLoaded As Boolean
    my code below for From Load:

    Code:
        Private Sub LoadDetailsData(ByVal mDisbursementID As Long)
    
            Using con As SqlConnection = New SqlConnection(CS)
                Try
                    da = New SqlDataAdapter("sp_NET_tblDisbursementDetails_CompanyID_DisbursementID", CS)
                    da.SelectCommand.CommandType = CommandType.StoredProcedure
                    da.SelectCommand.Parameters.AddWithValue("@CompanyID", CInt(ConfigurationManager.AppSettings("CompanyID")))
                    da.SelectCommand.Parameters.AddWithValue("@DisbursementID", CLng(mDisbursementID))
    
                    cb = New SqlCommandBuilder(da)
                    ''======== I have no idea how to make this work ===============
                    'da.InsertCommand = SqlCommand.GetInsertCommand()
                    'da.UpdateCommand = SqlCommand.GetUpdateCommand()
                    'da.DeleteCommand = SqlCommand.GetDeleteCommand()
                    '==============================================================
    
                    dt = New DataTable
                    bs = New BindingSource
                    da.Fill(dt)
                    bs.DataSource = dt
                    dgvDisbursementDetails.DataSource = bs
                    'dgvDisbursementDetails.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
    
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End Using
    
        End Sub
    My Button Save Code:

    Code:
    da.Update(dt)
    Question: I can't figure out a way how to make the sqlcommandbuilder to work. Is there a way for me to override the sqlcommandbuilder and use my existing insert, update, delete storedprocedures ?

  2. #2

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Question Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    I think I kinda found a solution: (But I really don't understand the side effects on the database side.) Since SQLCommandBuilder made the insert, update, delete commands for me, does it mean I no longer need my existing insert, update, delete stored procedures? Direct table access is not allowed in the company I work.

    Below my simplified code:

    Code:
    Private Sub LoadDetailsData(ByVal mDisbursementID As Long)
    
    
        Using con As SqlConnection = New SqlConnection(CS)
            Try
                'con.Open()
                da = New SqlDataAdapter("sp_NET_tblDisbursementDetails_CompanyID_DisbursementID", CS)
                da.SelectCommand.CommandType = CommandType.StoredProcedure
                da.SelectCommand.Parameters.AddWithValue("@CompanyID", CInt(ConfigurationManager.AppSettings("CompanyID")))
                da.SelectCommand.Parameters.AddWithValue("@DisbursementID", CLng(mDisbursementID))
    
                ds = New DataSet
                da.Fill(ds)
                ds.Tables(0).TableName = "Disbursements"
    
                dgvDisbursementDetails.DataSource = ds.Tables("Disbursements")
    
            Catch ex As Exception
                Throw ex
                'MessageBox.Show(ex.Message)
            End Try
        End Using
    
    End Sub
    MY cmdSaveDetails_Click Code:

    Code:
        cb = New SqlCommandBuilder(da)
        da.Update(ds, "Disbursements")

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    If you want to define your own commands then don't a command builder. Create the SqlCommand objects, assign the sproc names to their CommandText properties, set their CommandType properties to StoredPorcedure, add the appropriate parameters and then assign them to the InsertCommand, UpdateCommand and DeleteCommand. If you need it, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It includes an example similar to that, with the only real difference being that it uses inline SQL rather than sprocs. As I said, you simply set the CommandText and CommandType appropriately to make that change.

  4. #4

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    Quote Originally Posted by jmcilhinney View Post
    If you want to define your own commands then don't a command builder. Create the SqlCommand objects, assign the sproc names to their CommandText properties, set their CommandType properties to StoredPorcedure, add the appropriate parameters and then assign them to the InsertCommand, UpdateCommand and DeleteCommand. If you need it, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It includes an example similar to that, with the only real difference being that it uses inline SQL rather than sprocs. As I said, you simply set the CommandText and CommandType appropriately to make that change.
    Thanks for replying. I read your thread. Question: The code InitializeDataAdapter how can I implement this on a Datagridview? Sorry I'm new to .NET. How can I supply the parameters using datagridview values?

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    Quote Originally Posted by coolwater View Post
    Thanks for replying. I read your thread. Question: The code InitializeDataAdapter how can I implement this on a Datagridview? Sorry I'm new to .NET. How can I supply the parameters using datagridview values?
    You don't. The DataGridView is irrelevant. It is simply a way to display the data to the user and allow the user to edit that data. It's got absolutely zero to do with the data access. To retrieve the data you call Fill on the data adapter and populate the DataTable. To commit the changes you call Update on the data adapter and save the contents of the DataTable. What happens to the DataTable in between is of no consequence. It might be bound to a DataGridView or a series of TextBoxes and ComboBoxes or edited in code. It doesn't matter. The data adapter is still the data adapter and the DataTable is still the DataTable. You add the parameters is code exactly as I have demonstrated. The values of the parameters get set automatically by the data adapter from the DataTable. You don't have a hand in that part. You just do as I have shown. As I SPECIFICALLY stated in that code:
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.

  6. #6

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    I'm sorry I'm quite slow. When will I call the sub InitialiseDataAdapter()? On Form Load?

  7. #7

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Question Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    I 'm trying to duplicate the SQLCOMMANDBUILDER by creating my own insert, update, delete commands using storedprocedures. I'm stuck with this error on buttonSave_Click: sqladapter.Update(dtable) ERROR: "The connection string property has not been initialized."

    Below my updated code:

    Global Variables:
    Code:
    Public intDisbursementID as  Long
    Dim CS As String = ConfigurationManager.ConnectionStrings("SimpleAccounting.My.MySettings.SimpleAcctgConnectionString").ConnectionString
    Private sqladapter As SqlDataAdapter
    Dim dset As DataSet
    Private dtable As DataTable
    Form Load Code:
    Code:
    LoadDisbursementDetails(intDisbursementID)
    myownSqlCommandBuilder(intDisbursementID)
    myownSqlCommandBuilder Sub:
    Code:
        Private Sub LoadDisbursementDetails(ByVal mDisbursementID As Long)
    
    
            Using con As SqlConnection = New SqlConnection(CS)
    
                Try
                        sqladapter = New SqlDataAdapter("sproc_DisbursementDetailsSelectByDisbursementID", con)
                        sqladapter.SelectCommand.CommandType = CommandType.StoredProcedure
                        sqladapter.SelectCommand.Parameters.AddWithValue("@DisbursementID", CLng(mDisbursementID))
    
                        Dim dset As New DataSet
                        Dim dtable As New DataTable
    
                        sqladapter.Fill(dset)
                        sqladapter.Fill(dtable)
    
                        dgvDisbursementDetails.DataSource = dtable
    
                   
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub
    
    
    Private Sub myownSqlCommandBuilderCode(ByVal mDisbursementID As Long)
    
        Using con As SqlConnection = New SqlConnection(CS)
    
            Dim delete As New SqlCommand("sproc_DisbursementDetailsDelete", con)
            delete.CommandType = CommandType.StoredProcedure
            delete.Parameters.Add("@DisbursementDetailsID", SqlDbType.BigInt, 8, "DisbursementDetailsID")
    
            Dim insert As New SqlCommand("sproc_DisbursementDetailsInsert", con)
            insert.CommandType = CommandType.StoredProcedure
            insert.Parameters.Add("@DisbursementID", SqlDbType.BigInt, 8, "DisbursementID")
            insert.Parameters.Add("@CompanyID", SqlDbType.BigInt, 8, "CompanyID")
            insert.Parameters.Add("@DatePosted", SqlDbType.DateTime, 8, "DatePostedID")
            insert.Parameters.Add("@SLID", SqlDbType.BigInt, 8, "SLID")
            insert.Parameters.Add("@Amount", SqlDbType.BigInt, 8, "Amount")
            insert.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
    
            Dim update As New SqlCommand("sproc_DisbursementDetailsUpdate", con)
            update.CommandType = CommandType.StoredProcedure
            update.Parameters.Add("@DisbursementID", SqlDbType.BigInt, 8, "DisbursementID")
            update.Parameters.Add("@CompanyID", SqlDbType.BigInt, 8, "CompanyID")
            update.Parameters.Add("@DatePosted", SqlDbType.DateTime, 8, "DatePostedID")
            update.Parameters.Add("@SLID", SqlDbType.BigInt, 8, "SLID")
            update.Parameters.Add("@Amount", SqlDbType.BigInt, 8, "Amount")
            update.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
            update.Parameters.Add("@DisbursementDetailsID", SqlDbType.BigInt, 8, "DisbursementDetailsID")
    
            '==== Error: object reference not set to an instance of an object ====
            sqladapter.DeleteCommand = delete
            sqladapter.InsertCommand = insert
            sqladapter.UpdateCommand = update
            '======================================================================
    
            sqladapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
        End Using
    
    End Sub
    Button Save Code:
    Code:
    sqladapter.Update(dtable)
    Please help. I'm stuck. :-(
    Last edited by coolwater; Oct 30th, 2013 at 06:50 AM.

  8. #8

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.

    I think I got it. I removed the using statement on my myownSqlCommandBuilderCode sub. Thanks @jmcilhinney for the tutorials.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width