Updating two DataGridViews

First I'm going to try to explain my problem.

I have two tables in a SQLEXPRESS database, a company table (opdg) and a contacts table (opdg_cp) which is holding the contacts for a company by company Id (opdg_Id). BTW Id in the company table is the primary key and is Identity, increment 1.

To keep it simple:

Company DataBase

Id Name

1 HillWood

2 Fergusson

3 van Dyke


Contacts Database

Id Lastname opdg_Id

1 Dewalt 1

2 Johnson 1

3 Henderson 3

4 Anderson 2

5 Eastwood 2

So 2 contacts belong to Fergusson, 2 to Hillwood and 1 to van Dyke

I created two datagridviews on one form. On holding the Companies and on holding the contacts. Both datagridviews are able to update, add, and insert records. What I'm trying to do is:

If I walk through the company database in the first datagridview, the second datagridview should show me the contacts for that company because of the opdg_Id cell in the contacts table. Well, I did that, it works well. I will include all code below.

But, I get a problem when I'm scrolling down in the company datagridview and I hit the CurrentRow.IsNewRow event to add a new record. Because at that point there is just at that particular moment no known value in the Id cell of the company table, it's still NULL. At that point I'm using the



datagridview_CurrentCellChanged event to search for matching contacts by C_Id, but the Id cell is NULL so I get an error.

Below the Code. BTW for opening, closing the database I use a separate Class, which I also include below.

This is the Class I'm using:

Imports System.Data.SqlClient

Public Class SQLControl
Dim HostIP = My.Settings.HostIP
Dim DataBase = My.Settings.Database
Dim User = My.Settings.User
Dim Password = My.Settings.Password

' CONNECTION
Public SQLCon As New SqlConnection With {.ConnectionString = "server=" & HostIP & ";User=" & User & ";Pwd=" & Password & ";Database=" & DataBase}
Public SQLCmd As New SqlCommand

Public Function HasConnection() As Boolean
Try
SQLCon.Open()

SQLCon.Close()
Return True
Catch ex As Exception
MsgBox(ex.Message)
Return False
End Try

End Function

' SQL DATA
Public SQLDA As SqlDataAdapter
Public SQLDS As DataSet

' QUERY PARAMETERS
Public Params As New List(Of SqlParameter)

' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String


Public Sub ExecQuery(Query As String)
Try
SQLCon.Open()

' CREATE SQL COMMAND
SQLCmd = New SqlCommand(Query, SQLCon)

' LOAD PARAMETERS INTO SQL COMMAND
Params.ForEach(Sub(x) SQLCmd.Parameters.Add(x))

' CLEAR PARAMETER LIST
Params.Clear()

' CREATE NEW DATASET AND DATAADAPTER
SQLDS = New DataSet
SQLDA = New SqlDataAdapter(SQLCmd)
RecordCount = SQLDA.Fill(SQLDS)

SQLCon.Close()

Catch ex As Exception
Exception = ex.Message
End Try

If SQLCon.State = ConnectionState.Open Then SQLCon.Close()
End Sub

End Class

Below the code for the Windows form, I deleted as much as code to keep it clear, and the BOLD part is my problem. BTW I'm from Holland, so I'm trying to explain my problem in English as good as I can.


Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Opdrachtgevers
' TWO NEW INSTANCES FROM SQLCONTROL CLASS
Private SQL As New SQLControl
Private SQL1 As New SQLControl
Public search As String
' WHEN FORM IS LOADED
Private Sub Opdrachtgevers_Load(sender As Object, e As EventArgs) Handles MyBase.Load

' EXECUTE QUERY AND POPULATE GRID, (FIRST DATAGRIDVIEW, COMPANY)
SQL.ExecQuery("select * from opdg")

' CALL LOADGRID_OPDG SUBROUTINE (First DataGridView, COMPANY)
LoadGrid_opdg()

' DISABLE SAVE BUTTON
btn_Opslaan_Opdg.Enabled = False

End Sub

' LOADGRID_OPDG SUBROUTINE
Private Sub LoadGrid_opdg()

' IF DATA IS RETURNED, POPULATE GRID & BUILD UPDATE COMMAND
If SQL.RecordCount > 0 Then
Opdg_Grid_View.DataSource = SQL.SQLDS.Tables(0)
Opdg_Grid_View.Rows(0).Selected = True
Opdg_Grid_View.Columns("id").Visible = False
SQL.SQLDA.UpdateCommand = New SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand
End If
End Sub

Private Sub opdg_grid_view_CurrentCellChanged(sender As Object, e As EventArgs) Handles Opdg_Grid_View.CurrentCellChanged

If Opdg_Grid_View.CurrentCellAddress.X < 0 Or Opdg_Grid_View.CurrentCellAddress.Y < 0 Then Exit Sub
If Opdg_Grid_View.CurrentRow.IsNewRow Then

' THIS IS WHERE I HAVE A PROBLEM. IF A NEW ROW IS DETECTED AND THERE

' IS A NULL VALUE IN THE ID CELL, I CAN'T SEARCH THE CONTACTS TABLE,

' BECAUSE, THIS IS A NEW COMPANY RECORD, AND NOT SAVED

' BUT INSTEAD OF THAT, AFTER FILLING IN THE COMPANY'S RECORD,

' IT SHOULD BE POSSIBLE TO GO TO THE SECOND DATAGRIDVIEW TO FILL

' IN THE FIRST NEW CONTACT FOR THAT COMPANY.
Else
' GET THE COMPANY'S ID
search = Opdg_Grid_View.CurrentRow.Cells("id").Value

'SEARCH FOR THE COMPANY'S CONTACT
SQL1.ExecQuery("select * from opdg_cp where opdg_id =" & search)

' LOAD THE CONTACTS DATAGRIDVIEW
LoadGrid_opdg_cp()
End If
Exit Sub
End Sub
Private Sub LoadGrid_opdg_cp()


' IF DATA IS RETURNED, POPULATE GRID & BUILD UPDATE COMMAND
If SQL1.RecordCount > 0 Then

Opdg_Cp_Grid_View.DataSource = SQL1.SQLDS.Tables(0)
Opdg_Cp_Grid_View.Rows(0).Selected = True
Opdg_Cp_Grid_View.Columns("id").Visible = False
Opdg_Cp_Grid_View.Columns("opdg_id").Visible = False

SQL1.SQLDA.UpdateCommand = New SqlCommandBuilder(SQL1.SQLDA).GetUpdateCommand
Else
Call CType(Opdg_Cp_Grid_View.DataSource, DataTable).Rows.Clear()
End If
End Sub

Private Sub opdg_cp_grid_view_CurrentCellChanged(sender As Object, e As EventArgs) Handles Opdg_Cp_Grid_View.CurrentCellChanged

If Opdg_Cp_Grid_View.CurrentCellAddress.X < 0 Or Opdg_Grid_View.CurrentCellAddress.Y < 0 Then Exit Sub
If Opdg_Cp_Grid_View.CurrentRow.IsNewRow Then

Else
Dim search = Opdg_Cp_Grid_View.CurrentRow.Cells("id").Value
SQL1.ExecQuery("select * from opdg_cp where opdg_id =" & search)
LoadGrid_opdg_cp()
End If
Exit Sub
End Sub


Private Sub btn_Opslaan_Opdg_Click(sender As Object, e As EventArgs) Handles btn_Opslaan_Opdg.Click
' SAVE UPDATES TO THE DATABASE
SQL.SQLDA.Update(SQL.SQLDS) ' TO DO : ERROR CHECKING, DATA VALIDATION

' REFRESH GRID DATA
LoadGrid_opdg()

' DISABLE SAVE BUTTON
btn_Opslaan_Opdg.Enabled = False
MsgBox("Gegevens zijn opgeslagen")

End Sub

Private Sub Opdg_Grid_View1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles Opdg_Grid_View.CellValueChanged
btn_Opslaan_Opdg.Enabled = True
End Sub

Private Sub Opdg_Grid_View1_RowsRemoved(sender As Object, e As DataGridViewRowsRemovedEventArgs) Handles Opdg_Grid_View.RowsRemoved
btn_Opslaan_Opdg.Enabled = True
End Sub

Private Sub Opdrachtgevers_Closing(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
If Me.WindowState = FormWindowState.Normal Then
My.Settings.Opdg_Location = Me.Location
My.Settings.Opdg_Size = Me.Size
End If
My.Settings.Opdg_Ws = Me.WindowState
My.Settings.Save()
End Sub

Private Sub btn_Zoek_Opdg_Click(sender As Object, e As EventArgs) Handles btn_Zoek_Opdg.Click
SQL.ExecQuery("select * from opdg where Bedrijfsnaam like '" & txt_Zoek_Opdg.Text & "%'")

LoadGrid_opdg()

End Sub

Private Sub Btn_Opslaan_Opdg_Cp_Click(sender As Object, e As EventArgs) Handles Btn_Opslaan_Opdg_Cp.Click


Opdg_Cp_Grid_View.CurrentRow.Cells(0).Value = search


SQL1.SQLDA.Update(SQL1.SQLDS)
MsgBox("Gegevens zijn opgeslagen")
End Sub
End Class



So, this is it. In short:

If a new company is added, don't search for the contact, but it should be able to add that contact directly after the company's record is filled in in the first datagridview.

Many thanks for everybody who takes some time to look at this problem.

regards,

Mark Hofland