Click here to Skip to main content
14,828,337 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi guys
I have a DataGridView with Header Colunms days of month.
Here is an image

I use this code to load data from database

<pre> Private Sub fill_plan()
            Dim i As Integer = 0
            Dim query As String = "SELECT UnitID,Unit,Udate,State FROM Units where Month(Udate) =" & cmbMonth.SelectedIndex + 1 & ""
            cmd = New SqlCommand(query, con)
            myDR = cmd.ExecuteReader
            If myDR.HasRows Then
                While myDR.Read
                    EmployeeGridView.Rows(i).Cells(0).Value = myDR.GetInt32(myDR.GetOrdinal("UnitID"))
                    EmployeeGridView.Rows(i).Cells(1).Value = myDR.GetString(myDR.GetOrdinal("Unit"))
                    Dim myday As Integer = DatePart(DateInterval.Day, myDR.GetValue(myDR.GetOrdinal("Udate")))
                    EmployeeGridView.Rows(i).Cells(myday + 1).Value = myDR.GetString(myDR.GetOrdinal("State"))
                    i = i + 1
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Σφάλμα")
        End Try
    End Sub

What I have tried:

Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
            cmd.CommandText = "INSERT INTO Units(Unit,Udate,State) VALUES(@unit,@udate,@state)"
            cmd.Parameters.Add("@unit", SqlDbType.NVarChar, 255)
            cmd.Parameters.Add("@udate", SqlDbType.DateTime)
            cmd.Parameters.Add("@state", SqlDbType.NVarChar, 10)
            For Each row As DataGridViewRow In EmployeeGridView.Rows
                For i = 1 To 31
                    'Dim myday As Integer = DatePart(DateInterval.Day, CDate("Udate"))
                    cmd.Parameters("@unit").Value = EmployeeGridView(1, EmployeeGridView.CurrentCell.RowIndex).Value.ToString()
                    cmd.Parameters("@udate").Value = 
                    cmd.Parameters("@state").Value =
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Σφάλμα")
        End Try

    End Sub

The parameters are empty because i don't know what to write

Thanks in advance
Updated 5-Feb-21 7:41am
Richard MacCutchan 5-Feb-21 10:18am
You use the fields that are related to the database columns.
jomachi 5-Feb-21 10:56am
Hi my friend
If i add in @unit << row.Cells(1).Value >>
it says parameter @unit not supplied
Richard MacCutchan 5-Feb-21 11:00am
Please show the exact code and the exact error message.
jomachi 5-Feb-21 11:09am
I edit the code.

The error is:
The parameterized query '(@Unit Nvarchar(255), @Udate Datetime, @State nvarchar(10)) INSERT' expects the parameter @state which was not supplied
Richard MacCutchan 5-Feb-21 11:15am
What is returned from row.Cells(2).Value when this error occurs?
jomachi 5-Feb-21 11:17am
Richard MacCutchan 5-Feb-21 11:19am
Then you cannot use it as a parameter value. You need to check the values as you extract them and either ignore rows with missing values, or add some default.
jomachi 5-Feb-21 11:24am
Thank for the help
Gerry Schmitz 5-Feb-21 11:15am
That's why someone probably invented MVVM ... keeps you from mucking in the "view". Use a "data source".

1 solution

I would recommend using a BindingSource, see: Bind data to DataGridView Control - Windows Forms .NET Framework | Microsoft Docs[^]

Also see this CodeProject article: Databinding for Beginners[^]
jomachi 5-Feb-21 13:44pm
Hi Rick

I don't want to bind data to datagridview.
I want to save data from datagridview to database.
I have to put @state parameter in days columns in datagridview.
RickZeeland 5-Feb-21 13:54pm
Data binding might seem strange and frightening to you at first, but it is well worth learning. Sadly I'm not a VB.NET programmer so I'm not going to invest a lot of time trying to fix your code, sorry!
jomachi 5-Feb-21 14:53pm
Ok thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900