Click here to Skip to main content
15,881,172 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?

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[^]
 
Share this answer
 
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