Search/Add/Edit/Delete SQL Records in Datagrid

Please elaborate on the BIG picture.  Visual studio provides add/change/delete functionality right in the datagrid.  I don't know what you have in mind for search, but there is also a criteria pane that allows you to filter the datagrid.

sorry if the description was vague. i do not need anything fancy, the simplest way will do; please excuse my lack of knowledge on this area. i just want the program to retreive all the records from that table and allow add/update/delete. i did add the built in toolstrip however that did not link itself to the datagrid. i will have to do some more searching on how to do this.

The simplest way is to open a table from visual studio (or sql management studio).   From the datagrid that appears, you can add, delete, insert, or filter the records. No programming required.

Can you post the code for retriving?

dgmg - ive started over with a blank project and dragged a datagrid from the toolbox, then i set the connection string to bring in the data, which works. how do i get it to automatically have the add/update/delete buttons up top? i added a toolstrip but only add and delete show and they dont work.

Hi psuscott;

From the table definition I noticed that you have not provided a primary key to the table. A primary key is needed for SQL commands to be generated for the update, insert.

Please post the code that you are using the load the data grid with from SQL server.

Fernando

fernando, i thought i had it going but something seems to have changed? or i did not save my recent changes last week because today i am getting a null reference exception at line dgvVendor.Columns("vendor").HeaderText = "Vendor". i will try to work on this more this afternoon and get it going again. sorry.

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindContractDataGridView()
    End Sub
 
    Private Function RetrieveVendorDataSet() As DataSet
        Dim ds As New DataSet("Vendor")
        Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
            connection.Open()
            Dim command As New SqlCommand("GetVendors", connection)
            command.CommandType = CommandType.StoredProcedure
            Using drContract As SqlDataReader = command.ExecuteReader()
                ds.Load(drContract, LoadOption.OverwriteChanges, New String() {"Contract"})
            End Using
            connection.Close()
        End Using
        Return ds
    End Function
 
    Private Sub BindContractDataGridView()
        Dim sprodid As String = ""
        Dim ds As DataSet = RetrieveVendorDataSet()
        If ds IsNot Nothing Then
            bsVendor.DataMember = "Vendor"
            bsVendor.DataSource = ds
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
            Me.bsVendor.DataSource = ds
            Me.bsVendor.DataMember = "Vendor"
        End If
    End Sub
 
End Class

Select allOpen in new window

the primary key is going to  be a composite key made up of the vendor and inv_num fields

Hi psuscott;

See code snippet for changes.

Fernando

Private Sub BindContractDataGridView()
    Dim sprodid As String = ""
    Dim ds As DataSet = RetrieveVendorDataSet()
    If ds IsNot Nothing Then
        bsVendor.DataMember = "Vendor"
        '==================== Change here ==============================
        ' Tell the Binding Source which table in the DataSet to use as the source of the data    <=============
        bsVendor.DataSource = ds.Table(0)  ' Because there is one table in the DataSet 0 is its index
        '===============================================================
        dgvVendor.DataSource = bsVendor
        dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
        If dgvVendor.RowCount > 0 Then
            dgvVendor.Rows.Item(0).Selected = True
        End If
        dgvVendor.Columns("vendor").HeaderText = "Vendor"
        dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
        dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
        dgvVendor.Columns("po").HeaderText = "Purchase Order"
        dgvVendor.Columns("product").HeaderText = "Product"
        dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
        dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
        dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
        dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
        dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
        dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
        dgvVendor.Columns("due_date").HeaderText = "Due Date"
        dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
        dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
        dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
        dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
        dgvVendor.Columns("paid").HeaderText = "Paid"
 
        '==================== Change here ==============================
        ' Rmove these two lines they are not need already done at top of IF ststement
        ' Me.bsVendor.DataSource = ds
        ' Me.bsVendor.DataMember = "Vendor"
        '===============================================================
 
    End If
End Sub

Select allOpen in new window

To your question, "the primary key is going to  be a composite key made up of the vendor and inv_num fields",  Yes.

thank you for your help
the name of the table is vendor payments but that is underlining itself and saying it is not a member of system.data.dataset?

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindVendorDataGridView()
    End Sub
 
    Private Function RetrieveVendorDataSet() As DataSet
        Dim ds As New DataSet("Vendor")
        Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
            connection.Open()
            Dim command As New SqlCommand("GetVendors", connection)
            command.CommandType = CommandType.StoredProcedure
            Using drContract As SqlDataReader = command.ExecuteReader()
                ds.Load(drContract, LoadOption.OverwriteChanges, New String() {"Contract"})
            End Using
            connection.Close()
        End Using
        Return ds
    End Function
 
 
    Private Sub BindVendorDataGridView()
        Dim sprodid As String = ""
        Dim ds As DataSet = RetrieveVendorDataSet()
        If ds IsNot Nothing Then
            bsVendor.DataMember = "Vendor"
            '==================== Change here ==============================
            ' Tell the Binding Source which table in the DataSet to use as the source of the data    <=============
            bsVendor.DataSource = ds.VendorPayments(0)  ' Because there is one table in the DataSet 0 is its index
            '===============================================================
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
 
            '==================== Change here ==============================
            ' Rmove these two lines they are not need already done at top of IF ststement
            ' Me.bsVendor.DataSource = ds
            ' Me.bsVendor.DataMember = "Vendor"
            '===============================================================
 
        End If
    End Sub
 
End Class

Select allOpen in new window

' This creates a DataSet with the name property set to Vendor
Dim ds As New DataSet("Vendor")

' This fills the DataSet Vendor with one DataTable called Contract
Using drContract As SqlDataReader = command.ExecuteReader()
    ds.Load(drContract, LoadOption.OverwriteChanges, New String() {"Contract"})
End Using

' This assigns the DataTable Contract to the bsVendor.DataSource
bsVendor.DataSource = ds.Table("Contract")
' Because the DataSet only contains one table in it you could use this as well
bsVendor.DataSource = ds.Table(0)

' This looks like to be to be a DataSet with a strongly typed DataTable called
' VendorPayments which is not the case with the code posted. An example of a
' Strongly typed table would be a TableAdapter.
bsVendor.DataSource = ds.VendorPayments(0)

Use one of the other two to assign the DataSource to bsVendor

if i were to choose the tableadapter method you are referring to what is the best way to go about that? the program will ultimately just take everything in the table VendorPayments, put it in a datagrid and allow me to change any of the fields and save them. is there an easier or perhaps better way to go about this than the code i posted? i will attach the new screenshot of the table below with primary keys.
VENDOR.jpg

Your code is already set up to use one of the below line, Is there a reason you want to use a TableAdaptor?

bsVendor.DataSource = ds.Table("Contract")

Or

bsVendor.DataSource = ds.Table(0)

i asked about the table adapter because you mentioned it did not appear the code matched the stronly typed data in the table. i guess i am just confused now where to put the new dataset created since the one i was using was to call the stored procedure and retrieve the information it doesnt seem to be making a connection now and bringing back data? sorry for the confusion here i am just inexperienced with this type of programming still i guess...

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindVendorDataGridView()
    End Sub
 
    Private Function RetrieveVendorDataSet() As DataSet
        Dim ds As New DataSet("Vendor")
        Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
            connection.Open()
            Dim command As New SqlCommand("GetVendors", connection)
            command.CommandType = CommandType.StoredProcedure
            Using drContract As SqlDataReader = command.ExecuteReader()
                ds.Load(drContract, LoadOption.OverwriteChanges, New String() {"Vendor"})
            End Using
            connection.Close()
        End Using
        Return ds
    End Function
 
 
    Private Sub BindVendorDataGridView()
 
        Dim ds As New DataSet("Vendor")
        'Using drVendor As SqlDataReader = Command.ExecuteReader()
        '    ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"Vendor"})
        'End Using
        'Dim ds As DataSet = RetrieveVendorDataSet()
 
        bsVendor.DataSource = ds.Tables("Vendor")
 
        If ds IsNot Nothing Then
            bsVendor.DataMember = "Vendor"
            bsVendor.DataSource = ds.Tables()
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
 
        End If
    End Sub
 
End Class

Select allOpen in new window

See the comments between
' ==========
and
'==========

In code snippet

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindVendorDataGridView()
    End Sub
 
Private Function RetrieveVendorDataSet() As DataSet
		' ============================================================
		' This creates the DataSet and you assigned it the name Vendor
    Dim ds As New DataSet("Vendor")    
		' ============================================================
    Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
        connection.Open()
        Dim command As New SqlCommand("GetVendors", connection)
        command.CommandType = CommandType.StoredProcedure
        Using drContract As SqlDataReader = command.ExecuteReader()
        		' ============================================================
        		' This loads the DataSet Vendor with one table called Vendor, the 
        		' table name is assigned with the third parameter in the next line.
        		' This command returns a New Datable that is put into the DataSet
            ds.Load(drContract, LoadOption.OverwriteChanges, New String() {"Vendor"})
            ' ============================================================
        End Using
        connection.Close()
    End Using
    Return ds
End Function
 
 
    Private Sub BindVendorDataGridView()
 
        '==========================================================
        ' This statement was not here before and creates an empty
        ' DataSet with NO Tables. I am removing this statement to
        ' correct the code.
        ' Dim ds As New DataSet("Vendor")   RMOVED
        '==========================================================
        '==========================================================
        ' These statemenst were not here before but you have 
        ' commented them out and not causing any problems
        'Using drVendor As SqlDataReader = Command.ExecuteReader()
        '    ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"Vendor"})
        'End Using
        'Dim ds As DataSet = RetrieveVendorDataSet()
        '==========================================================
        '==========================================================
        ' This statement was not here before and will cause an 
        ' exception because there is no tables in it. See first statement
        ' I am removing this statement to correct the code
        ' bsVendor.DataSource = ds.Tables("Vendor")   REMOVED
        '==========================================================
 
        Dim sprodid As String = ""    ' This statement was here before but not used
        '==========================================================
				' This statement was in the original code and added back by 
				' me to correct the code. This gets the DataSet with one table
				' that has data in it.
        Dim ds As DataSet = RetrieveVendorDataSet()
        '==========================================================      
 
        If ds IsNot Nothing Then
            bsVendor.DataMember = "Vendor"
            '==========================================================      
            ' This statement needs to be either this
            ' bsVendor.DataSource = ds.Tables(0) or this
            ' bsVendor.DataSource = ds.Tables("Vendor") but not this
            ' because you need to identify the table by index or name
            ' bsVendor.DataSource = ds.Tables()
            bsVendor.DataSource = ds.Tables("Vendor")
            '==========================================================      
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
 
        End If
    End Sub
 
End Class

Select allOpen in new window

i have made the changes and tried both
bsVendor.DataSource = ds.Tables(0)
and
 bsVendor.DataSource = ds.Tables("Vendor")
the program executes but does not return any values. i have stepped through and the connection is made everything seems to be executing just fine? the table is named VendorPayments. is it wrong to be referred to as "Vendor" could that be why i am not getting anything back?
thank you for taking the time to help with this.

here is the code updated with all current changes:

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindVendorDataGridView()
    End Sub
 
    Private Function RetrieveVendorDataSet() As DataSet
        Dim ds As New DataSet("Vendor")
        Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
            connection.Open()
            Dim command As New SqlCommand("GetVendors", connection)
            command.CommandType = CommandType.StoredProcedure
            Using drVendor As SqlDataReader = command.ExecuteReader()
                ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"Vendor"})
            End Using
            connection.Close()
        End Using
        Return ds
    End Function
 
 
    Private Sub BindVendorDataGridView()
        Dim ds As DataSet = RetrieveVendorDataSet()
        If ds IsNot Nothing Then
            bsVendor.DataMember = "Vendor"
            'bsVendor.DataSource = ds.Tables(0)
            bsVendor.DataSource = ds.Tables("Vendor")
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
 
        End If
    End Sub
 
End Class

Select allOpen in new window

If the Table name in the DB is called VendorPayments then try changing this line of code

ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"Vendor"})

To this:

ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"VendorPayments"})

' Use this in the code, this one will always work if there is one table in the DataSet
bsVendor.DataSource = ds.Tables(0)
' Then once you have that working replace the above line with this one. Once both ways are working use either
'bsVendor.DataSource = ds.Tables("VendorPayments")

excellent, thank you very much the code:
bsVendor.DataSource = ds.Tables("VendorPayments")
worked perfectly
well, now it finally loads again i will make a backup of the code; to go back to the original question now i will focus on the update/add/delete search. ill post up the code i have for the save in a moment but the search will be the main thing i have never done before if there is any suggestions you may have for that i would really appreciate it.
 
thank you!!

 

Hi psuscott;

I will be away for most of the day today.

Documentation on the DataGridView Control
http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview_members(VS.80).aspx
Properties to look into
AllowUserToAddRows
AllowUserToDeleteRows

DataGridView Control (Windows Forms)
http://msdn.microsoft.com/en-us/library/e0ywh3cz.aspx

The above documents should help.

Fernando

when trying to do a search, do i want the results to be filtered from an original data grid or should i display results from a stored procedure as the new datagrid? which way would work better...im trying to get the search function nailed down that i referred to in the original question. the rest of the program i will be ok with. thanks!

There is no need to repopulate the data grid that already has the data you need to search on.

ok, so using the code above what would be the best way to search on the datagrid? if i want to search on each field should i make a popup window one textbox for each field in the datagrid and if information is entered in a box it will run a search based on that criteria? sorry for all the questions i have never done a search function in visual.

if anyone can just tell me how to enable this add new button in the binding navigator i will award points since thats the only thing i have yet to figure out as simple as it probably is. or how do i code a new button to add a new record? i changed in the properties for it to be enabled but it is still greyed out?!?

addnew.jpg

Sorry psuscott; you last post fell through the crack on my end must of gotten delete in error. To your current post check to see if the following is the case?

From the documentation:
The BindingNavigator control will disable the Add New button if the BindingSource property is nullNothingnullptra null reference (Nothing in Visual Basic) or the AllowNew property is false.

http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingnavigator.addnewitem.aspx

Fernando

not a problem fernando you have been very helpful to me in the past i cant always expect you to drop everything and assist here. ive checked over the other options and allow new is also selected as true. im at a loss? should the button be coded, what code would i put in there?
 
i also tried to add the code below but that did not work either?

    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click
        Dim instance As BindingNavigator
        Dim value As ToolStripItem
 
        value = instance.AddNewItem
 
        instance.AddNewItem = value
 
    End Sub

Select allOpen in new window

addnew.jpg

Hi psuscott;

OK let me give you some steps to see if this is the way you have it set up.

1. Figure 1 below is the IDE with a Form, DataGridView, a BindingSource called ContactBindingSource, and a BindingNavigator called BindingNavigator1.

2. The ContactBindingSource is connected to the data source so the property DataSource of the ContactBindingSource should have the data source object that will be displayed in the data grid view.

3. The BindingNavigator1 should be connected to the binding source so that the BindingNavigator1 BindingSource property is assigned the object ContactBindingSource in this case. And the AllowNew property set to true.

4. The DataGridView DataSource property should be connected to the ContactBindingSource object.

5 At this point the Add New button in the BindingNavigator1 should be enabled and working without any needed code.

When you click on the Add New button the DataGridView will display as shown in figure 2 below. Note the last entry in the DataGridView, the left border on that line, has a right pointing arrow and the first column highlighted. Entering items in this row will assign then to that line. Now to make sure that the data that was just entered gets save to the data source  you need to do one of two thing 1, click on the |< or < button on the BindingNavigator1 control which moves to a different record which will cause the record to be saved or 2. in code execute the EndEdit of the BindingSource, for example in this case ContactBindingSource.EndEdit() which will cause the data to be saved

See if that helps.

Fernando

 
SP32-4.jpg
SP32-5.jpg

i think you may have found my problem (or one of them) i have no binding source. the screeshot shows the datagrid and the code is attached below. how do i connect a binding source to the binding navigator?
also, i do have .endedit in the code however it will only save the current row and if the last cell is no longer in edit mode. is there a way to save the entire row even if the last cell is being edited instead of hitting tab and jumping to the next row and saving nothing? sorry if i am spinning off other questions here i can start a new question if need be.

Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Drawing
Imports System.Drawing.Printing
 
Public Class frmMain
 
    Private bControlsDataBound As Boolean = False
    Private bsVendor As BindingSource
    Private dsVendor As DataSet
    Public ds As Object
 
    Public Sub New()
        InitializeComponent()
        bsVendor = New BindingSource()
        bsVendor.AllowNew = False
 
    End Sub
 
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
        Me.Cursor = System.Windows.Forms.Cursors.Default
        BindVendorDataGridView()
    End Sub
 
    Private Function RetrieveVendorDataSet() As DataSet
        Dim ds As New DataSet("VendorPayments")
        Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
            connection.Open()
            Dim command As New SqlCommand("GetVendors", connection)
            command.CommandType = CommandType.StoredProcedure
            Using drVendor As SqlDataReader = command.ExecuteReader()
                'ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"Vendor"})
                ds.Load(drVendor, LoadOption.OverwriteChanges, New String() {"VendorPayments"})
            End Using
            connection.Close()
        End Using
        Return ds
    End Function
 
 
    Private Sub BindVendorDataGridView()
        Dim ds As DataSet = RetrieveVendorDataSet()
        If ds IsNot Nothing Then
            bsVendor.DataMember = "VendorPayments"
            'bsVendor.DataSource = ds.Tables(0)
            bsVendor.DataSource = ds.Tables("VendorPayments")
            Me.BindingNavigator1.BindingSource = Me.bsVendor
            dgvVendor.DataSource = bsVendor
            dgvVendor.DataBindings.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
            If dgvVendor.RowCount > 0 Then
                dgvVendor.Rows.Item(0).Selected = True
            End If
            dgvVendor.Columns("vendor").HeaderText = "Vendor"
            dgvVendor.Columns("inv_num").HeaderText = "Invoice #"
            dgvVendor.Columns("inv_date").HeaderText = "Invoice Date"
            dgvVendor.Columns("po").HeaderText = "Purchase Order"
            dgvVendor.Columns("product").HeaderText = "Product"
            dgvVendor.Columns("inv_amt").HeaderText = "Invoice Amt"
            dgvVendor.Columns("dep_paid").HeaderText = "Deposit Paid"
            dgvVendor.Columns("amt_paid").HeaderText = "Amount Paid"
            dgvVendor.Columns("adjustment").HeaderText = "Adjustment"
            dgvVendor.Columns("rem_balance").HeaderText = "Remaining Bal"
            dgvVendor.Columns("ship_date").HeaderText = "Ship Date"
            dgvVendor.Columns("due_date").HeaderText = "Due Date"
            dgvVendor.Columns("insp_date").HeaderText = "Inspection Date"
            dgvVendor.Columns("intl_paid").HeaderText = "Intl Paid"
            dgvVendor.Columns("dep_paid_date").HeaderText = "Dep Paid Date"
            dgvVendor.Columns("paid_date").HeaderText = "Paid Date"
            dgvVendor.Columns("paid").HeaderText = "Paid"
 
        End If
    End Sub
 
    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        SaveItems(dgvVendor.SelectedRows(0).Cells(0).Value, dgvVendor.SelectedRows(0).Cells(1).Value, dgvVendor.SelectedRows(0).Cells(2).Value, dgvVendor.SelectedRows(0).Cells(3).Value, dgvVendor.SelectedRows(0).Cells(4).Value, dgvVendor.SelectedRows(0).Cells(5).Value, dgvVendor.SelectedRows(0).Cells(6).Value, dgvVendor.SelectedRows(0).Cells(7).Value, dgvVendor.SelectedRows(0).Cells(8).Value, dgvVendor.SelectedRows(0).Cells(9).Value, dgvVendor.SelectedRows(0).Cells(10).Value, dgvVendor.SelectedRows(0).Cells(11).Value, dgvVendor.SelectedRows(0).Cells(12).Value, dgvVendor.SelectedRows(0).Cells(13).Value, dgvVendor.SelectedRows(0).Cells(14).Value, dgvVendor.SelectedRows(0).Cells(15).Value, dgvVendor.SelectedRows(0).Cells(16).Value)
    End Sub
 
    Private Function SaveItems(ByVal vendor As Object, ByVal inv_num As Object, ByVal inv_date As Object, ByVal po As Object, ByVal product As Object, ByVal inv_amt As Object, ByVal dep_paid As Object, ByVal amt_paid As Object, ByVal adjustment As Object, ByVal rem_balance As Object, ByVal ship_date As Object, ByVal due_date As Object, ByVal insp_date As Object, ByVal intl_paid As Object, ByVal dep_paid_date As Object, ByVal paid_date As Object, ByVal paid As Object) As SqlTransaction
        Dim ds As DataSet
        Dim da As New SqlDataAdapter
        Try
            bsVendor.EndEdit()
            'If CType(bsVendor.DataSource, DataSet).HasChanges Then
            Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
                connection.Open()
                Dim command As New SqlCommand("SaveVendor", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.Add("@vendor", SqlDbType.Char)
                command.Parameters("@vendor").Value = vendor
                command.Parameters.Add("@inv_num", SqlDbType.Char)
                command.Parameters("@inv_num").Value = inv_num
                command.Parameters.Add("@inv_date", SqlDbType.DateTime)
                command.Parameters("@inv_date").Value = inv_date
                command.Parameters.Add("@po", SqlDbType.Char)
                command.Parameters("@po").Value = po
                command.Parameters.Add("@product", SqlDbType.Char)
                command.Parameters("@product").Value = product
                command.Parameters.Add("@inv_amt", SqlDbType.Money)
                command.Parameters("@inv_amt").Value = inv_amt
                command.Parameters.Add("@dep_paid", SqlDbType.Money)
                command.Parameters("@dep_paid").Value = dep_paid
                command.Parameters.Add("@amt_paid", SqlDbType.Money)
                command.Parameters("@amt_paid").Value = amt_paid
                command.Parameters.Add("@adjustment", SqlDbType.Money)
                command.Parameters("@adjustment").Value = adjustment
                command.Parameters.Add("@rem_balance", SqlDbType.Money)
                command.Parameters("@rem_balance").Value = rem_balance
                command.Parameters.Add("@ship_date", SqlDbType.DateTime)
                command.Parameters("@ship_date").Value = ship_date
                command.Parameters.Add("@due_date", SqlDbType.DateTime)
                command.Parameters("@due_date").Value = due_date
                command.Parameters.Add("@insp_date", SqlDbType.DateTime)
                command.Parameters("@insp_date").Value = insp_date
                command.Parameters.Add("@intl_paid", SqlDbType.Char)
                command.Parameters("@intl_paid").Value = intl_paid
                command.Parameters.Add("@dep_paid_date", SqlDbType.DateTime)
                command.Parameters("@dep_paid_date").Value = dep_paid_date
                command.Parameters.Add("@paid_date", SqlDbType.DateTime)
                command.Parameters("@paid_date").Value = paid_date
                command.Parameters.Add("@paid", SqlDbType.Char)
                command.Parameters("@paid").Value = paid
                command.ExecuteReader()
                connection.Close()
 
            End Using
            UpdateRemaining()
            BindVendorDataGridView()
            'End If
        Catch ex As Exception
 
        End Try
    End Function
    Private Sub UpdateRemaining()
        Dim ds As DataSet
        Dim da As New SqlDataAdapter
        Try
            Using connection As New SqlConnection(Global.WindowsApplication1.My.MySettings.Default.FinanceConnectionString)
                connection.Open()
                Dim command As New SqlCommand("UpdateVendor", connection)
                command.CommandType = CommandType.StoredProcedure
                command.ExecuteReader()
                connection.Close()
 
            End Using
        Catch ex As Exception
        End Try
 
    End Sub
 
 
    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click
        Dim instance As BindingNavigator
        Dim value As ToolStripItem
 
        value = instance.AddNewItem
        instance.AddNewItem = value
 
    End Sub
End Class

Select allOpen in new window

datagrid.jpg

Hi psuscott;

Do you have and can you upload to this site a copy of the database file that you are using to develop this app so that I may build a small test app to show how it is done?

Fernando

i attached the database below, there are only a few test records at the moment, i also posted the code snippets for the stored procedures called. and a image of the database structure.
***ALSO: i had to change the backup file from .BAK to .txt for the upload to be accepted

CREATE PROCEDURE [dbo].[SaveVendor]
	@vendor as char(30),
	@inv_num as char(22),
	@inv_date as datetime,
	@po as char(20),
	@product as char(200),
	@inv_amt as money,
	@dep_paid as money,
	@amt_paid as money,
	@adjustment as money,
	@rem_balance as money,
	@ship_date as datetime,
	@due_date as datetime,
	@insp_date as datetime,
	@intl_paid as char(1),
	@dep_paid_date as datetime,
	@paid_date as datetime,
	@paid as char(1)
	
 AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	if not exists(select * from VendorPayments where inv_num = @inv_num)
	BEGIN
		INSERT INTO VendorPayments
			(vendor,inv_num,inv_date,po,product,inv_amt,dep_paid,amt_paid,adjustment,rem_balance,
			ship_date,due_date,insp_date,intl_paid,dep_paid_date,paid_date,paid)
		VALUES (@vendor,@inv_num,@inv_date,@po,@product,@inv_amt,@dep_paid,@amt_paid,@adjustment,
		@rem_balance,@ship_date,@due_date,@insp_date,@intl_paid,@dep_paid_date,@paid_date,@paid)
	END
	else
	BEGIN
		UPDATE VendorPayments
		SET vendor = @vendor, inv_num = @inv_num, inv_date = @inv_date, po = @po, product = @product,
		inv_amt = @inv_amt, dep_paid = @dep_paid, amt_paid = @amt_paid, adjustment = @adjustment,
		rem_balance = @rem_balance, ship_date = @ship_date, due_date = @due_date, insp_date = @insp_date,
		intl_paid = @intl_paid, dep_paid_date = @dep_paid_date, paid_date = @paid_date, paid = @paid
		WHERE inv_num = @inv_num
	END
END
GO
 
 
CREATE PROCEDURE UpdateVendor  AS
 
update VendorPayments 
set rem_balance = inv_amt - dep_paid - amt_paid - adjustment
GO
 
 
CREATE PROCEDURE [dbo].[GetVendors] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	SELECT *
	FROM VendorPayments
	WHERE paid = 'N'
END
GO

Select allOpen in new window

database.jpg
Finance-db-200903171900.txt

thank you fernando that was very helpful. i added the binding source and made the changes to the code. it saves all data regardless of what is selected, however the strangest thing happens...the new data does not appear in the program? i can see it on the server however if i refresh the data by calling the retrieve process again or close it and reopen, none of the new data shows?

nevermind last comment, that was my error. thank you very much fernando that worked perfectly you are always a great help here.

very helpful as always :-)

Not a problem, glad I was able to help out.  ;=)