[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:
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 ?
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")
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.
Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.
Quote:
Originally Posted by
jmcilhinney
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?
Re: Help Insert Update Delete Datagridview using MS Sql stored procedures.
Quote:
Originally Posted by
coolwater
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:
Quote:
'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.
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?
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. :-(
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.
