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
https://ibb.co/b3JPRXN

I use this code to load data from database

Basic
<pre> Private Sub fill_plan()
        EmployeeGridView.Rows.Clear()
        Try
            Dim i As Integer = 0
            con.Open()
            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.Add()
                    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
            myDR.Close()
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Σφάλμα")
        End Try
    End Sub


What I have tried:

Basic
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
        Try
            con.Open()
            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 =
                    cmd.ExecuteNonQuery()
                Next
            Next
            con.Close()
        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
Posted
Updated 5-Feb-21 7:41am
v4
Comments
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
   
Nothing
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[^]
   
v2
Comments
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