• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Search/Add/Edit/Delete SQL Records in Datagrid

hello experts, i am trying to retrieve all of the records in a table and put them into a datagrid. i have been successful in retrieving the records but i have been unable to figure out the search/add/update/delete commands. can anyone please help? i would like to be able to search on any record (if possible) and have full control to write back to sql server any changes. if the search is easier done on a pop up form i think that would be ok as well. thank you very much. attached is a screenshot of the database properties.
vendorpayments.jpg
0
psuscott
Asked:
psuscott
  • 19
  • 14
  • 2
  • +1
1 Solution
 
dqmqCommented:
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.
0
 
psuscottAuthor Commented:
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.
0
 
dqmqCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JackOfPHCommented:
Can you post the code for retriving?
0
 
psuscottAuthor Commented:
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.
0
 
Fernando SotoCommented:
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
0
 
psuscottAuthor Commented:
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

Open in new window

0
 
psuscottAuthor Commented:
the primary key is going to  be a composite key made up of the vendor and inv_num fields
0
 
Fernando SotoCommented:
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

Open in new window

0
 
Fernando SotoCommented:
To your question, "the primary key is going to  be a composite key made up of the vendor and inv_num fields",  Yes.
0
 
psuscottAuthor Commented:
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

Open in new window

0
 
Fernando SotoCommented:
' 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
0
 
psuscottAuthor Commented:
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
0
 
Fernando SotoCommented:
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)
0
 
psuscottAuthor Commented:
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

Open in new window

0
 
Fernando SotoCommented:
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

Open in new window

0
 
psuscottAuthor Commented:
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.
0
 
psuscottAuthor Commented:
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

Open in new window

0
 
Fernando SotoCommented:
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")
0
 
psuscottAuthor Commented:
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!!

 
0
 
Fernando SotoCommented:
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
0
 
psuscottAuthor Commented:
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!
0
 
Fernando SotoCommented:
There is no need to repopulate the data grid that already has the data you need to search on.
0
 
psuscottAuthor Commented:
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.
0
 
psuscottAuthor Commented:
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
0
 
Fernando SotoCommented:
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
0
 
psuscottAuthor Commented:

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

Open in new window

addnew.jpg
0
 
Fernando SotoCommented:
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
0
 
psuscottAuthor Commented:
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

Open in new window

datagrid.jpg
0
 
Fernando SotoCommented:
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
0
 
psuscottAuthor Commented:
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

Open in new window

database.jpg
Finance-db-200903171900.txt
0
 
Fernando SotoCommented:
Hi psuscott;

Here is a small project that will add a new row to the data grid when you click on the + in the BindingNavigator toolstrip and save one or more records to the database once the Save button is clicked. I have not implemented very much error checking. I basically took your code and changed it around. You will need to change the connection string in the projects property page settings.

To download the sample project click on the following link:
https://filedb.experts-exchange.com/incoming/ee-stuff/7566-WindowsApplication6.zip
If it ask for a log in ID and password it is the same as you use here at the main EE site  

Fernando
0
 
psuscottAuthor Commented:
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?
0
 
psuscottAuthor Commented:
nevermind last comment, that was my error. thank you very much fernando that worked perfectly you are always a great help here.
0
 
psuscottAuthor Commented:
very helpful as always :-)
0
 
Fernando SotoCommented:
Not a problem, glad I was able to help out.  ;=)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 14
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now