Insert today's date on the record displayed on the datagrid in vb.net

This will work for you.

Just add it in your codefile.

DateTime.Now.ToString()

Hi Sammy,
Thanks for your response, I'm having a tough time understanding where in code I will add
DateTime.Now.ToString(), will appreciate if you can expalin further.
thanks

you want to show to the user after they fill in the columns or do you just want to save the date into database?
can you show us the code?

Hi Mike,
I want to do both, I want to show the user after he fills in the columns and when press save  today date will appear on the date field and the entire data on the current datagrid should get saved for that date on the metal tbl.

The metal tbl  table will be empty to start with, so eveyday I want to keep track of the above log of metal name and metal used amount.
I have a calendar object name A to select a date so  the datagrid doesn't show all the previous records they entered.Also in the future we can use it to check by date and make any change if needed.

so i made a oledaadapter using wizard and named it OleDbDataAdapter11 usi the query on that is like this

SELECT [Metal name], [metal Used],  [Date info] FROM [metal tbl]
WHERE     ([Date info] = ?)
the dataset associated with this adapter is  steshone

I have two buttons

button 1 click of event I display the fields
Metal name  Metal used  date info  for the selected date on the calendar. Intially the table is empty so passing the date my only intention is not to bring any previous days records.

 Private Sub MacOnebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MacOnebtn.Click
        Try

           

              OleDbDataAdapter11.SelectCommand.Parameters("Date_info").Value = A.SelectionStart
            FnMCO()

        Catch ex As Exception
            ' MsgBox(" Changes have been Saved")
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub
---------

' Function info


Private Sub FnMCO()
        SteShone1.Clear()
        DateTime.Now.ToString()
        OleDbDataAdapter11.Fill(SteShone1, "metal tbl")
        Me.DataGrid1.DataSource = Me.SteShone1.Tables("metal tbl").DefaultView
    End Sub
-------------

Button to save the data entered on the datagrid to the table metal tbl. button 2

Private Sub SaveMaconebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveMaconebtn.Click
        Try

            Dim Dsschedule = SteShone1.GetChanges
            Dim nno As DBNull
            If (Dsschedule Is nno) Then
                MsgBox("You Have Not Made Any Change To The Schedule.Hit Ok to Exit this message")

            Else

                OleDbDataAdapter11.Update(Dsschedule)
                SteShone1.AcceptChanges()
                FnMCO()

            End If
        Catch ex As Exception
            'MsgBox(" The Changes That You Made To Missed Order Has Been Saved")
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

-----------


So assuming the user selects 4/5/2011 on the calendar and presses the button 1

the datagrid will appear
\
Metal name  Metal used amt    Date info


then the user will enter
Metal name   Metal used amt  Date info
Copper           20

then he should press save button I want 4/5/10 appear on all the rows in the datgrid and the data should save back in metal tbl so in the example able when he press save

Metal name  metal used     Date info
copper           20                 4/5/2011

and on the table it will get saved

example

metal name    metal used      date info

iron                    10                 4/2/2011
steel                   11                 4/3/2011
copper                20                 4/5/2011

hope this makes sense, later, I will

 

i see,
 try this in the button click event you have

      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim name As String
        Dim used As Integer
        Dim dateIn As Date = Now.Date
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(2).Value = dateIn
            name = DataGridView1.Rows(i).Cells(0).Value
            used = DataGridView1.Rows(i).Cells(1).Value
            Me.UpdateMetalTable(name, used, dateIn)
        Next
    End Sub

    Private Sub UpdateMetalTable(ByVal name As String, ByVal used As Integer, ByVal dateIn As Date)
        Dim connection As OleDb.OleDbConnection = "your connection string here"
        Dim updateStatement = "Update metal_tbl Set metalname = @metalname, metalused = @metalused, date = @date"
        Dim updateCommand As New OleDb.OleDbCommand(updateStatement, connection)
        updateCommand.Parameters.AddWithValue("@metalname", name)
        updateCommand.Parameters.AddWithValue("@metalused", used)
        updateCommand.Parameters.AddWithValue("@date", dateIn)
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            'Error
        Finally
            connection.Close()
        End Try
    End Sub

Select allOpen in new window

sorry, didn't know you have the update code already, so just use the buttonclick code you should be seen the date gets displayed and saved.
one thing i'm too sure about the example you gave is do you want all rows being updated with same date as my code does? or do you actually want to update/display on specific rows like the last example you showed?

You can use the RowUpdating event of the dataadapter and set the date parameter to the value you want

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.rowupdating.aspx

Here is a more relevant example

http://dodgethecode.blogspot.com/2009/04/change-gridview-row-values-using.html

Hi Mike,
Thanks for the post, on the code

For i As Integer = 0 To DataGridView1.Rows.Count - 1

it doesn't recogonize Datagridview1 do I have to declare it, if so how?

thanks

it's your datagrid name sivasan, let me know if the outcome is what you wanted.

Hi Mike,
The updatecommand says not declared should I declare that?
thanks

which updating method you using?
try put this at the top of your form
Imports System.Data.Oledb
then in updating declarations
Dim updateCommand As NEW Oledbcommand

Hi Mike,
I get error Public member Rows on type Dataview not found any suggestions?
thanks for all your help?

yes i guess that's because you're using visual studio 2003, datagrid isn't the same as datagridview.
try this to replace the for loop:

For i as integer = 0 to yourdatagrid.visablerowcount

Next

Select allOpen in new window

see if this works

Hi Mike,
Yes I use Visual studio 2003 so there is so confusion. I'm using the system datagrid and the default name is datagrid1

row doesn't seem to be a member of it so how do I do the for loop?

 Dim i As Integer
                Dim dateIn As Date = Now.Date
                For i = 0 To DataGrid1.?         ( what would be after . ?)


                 DataGrid1.Row(0).Cells(2).Value = dateIn  ( get error )
Next

try this:
For i = 0 to SteShone1.Tables("metal tbl").Rows.Count -1
then change DataGrid1.Row(0).Cells(2).Value = dateIn  to DataGrid1.Item(0).Cells(2).Value = dateIn

Get the following error

Value of type 'Integer' cannot be converted to 'System.Windows.Forms.DataGridCell'.


when i try either one of the following
 DataGrid1.Item(0).Cells(2).Value = dateIn

or
 DataGrid1.Item(i).Cells(2).Value = dateIn
thanks

I think

DataGrid1.Item(0).Cells(2).Value = dateIn

should be
DataGrid1.Item(0,2) = dateIn

I'm testing this now, I see it getting populated but ot saving.

yes you'r probably right item(0,2) might be it.
did your dataset recognise the change?

Hi Mike,
I  now see the datagrid does get populated with the date but it doesn't save it back to the table.
Another thing is that it only populates the first row and not the following rows example

I put on the datagrid

metal name   metal used   Loc    date ino

copper           100              CA    
Iron                 10000          Ak
Steel                20               NY

then I hit the button that has the for next loop the datagrid shows as



metal name   metal used   Loc    date ino

copper           100              CA       5/6/2011
Iron                 10000          Ak
Steel                20               NY

but doesn't save back any data to the table also it only populates for the 1st row not the ones below it.
Please advise

To give you a better understanding, I have attached the code and the result I like to see.

This is the button to pull record for the selected date- Assume this to be Button1


Private Sub MacOnebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MacOnebtn.Click
        Try


            OleDbDataAdapter11.SelectCommand.Parameters("Date_info").Value = A.SelectionStart
            FnMCO()



        Catch ex As Exception
            ' MsgBox(" Changes have been Saved")
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub



----------

The query in Oledbdataadapter11

SELECT [Metal name], [Metal Used], Loc, [Date info] FROM [metaltbl] WHERE ([Date info] = ?)

the dataset for this is Steshone

-----------------------




'Definition for Function FnMCO()

------------

Private Sub FnMCO()
        SteShone1.Clear()

        OleDbDataAdapter11.Fill(SteShone1, "metaltbl")
        Me.DataGrid1.DataSource = Me.SteShone1.Tables("metaltbl").DefaultView
    End Sub


-------------------

'Button2 to save after I enter Metal name, metal used with the location hard coded and date added back to the data base- Assume this to be Button2
------

 Private Sub SaveMaconebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveMaconebtn.Click


        Try

            Dim Dsschedule = SteShone1.GetChanges
            Dim nno As DBNull
            If (Dsschedule Is nno) Then
                MsgBox(" No Change made.Hit Ok to Exit this message")

            Else

                Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Project\metal.mdb"
                Dim myConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
                myConnection.ConnectionString = connString

                myConnection.Open()

                Dim i As Integer
                Dim dateIn As Date = Now.Date
                Dim MetalName As String
                Dim MetalUsed As Integer
               
                Dim Loca As String
                Loca = "NY"

                For i = 0 To SteShone1.Tables("metaltbl").Rows.Count - 1
                                       

                   MetalName = DataGrid1.Item(i, 0)
                    MetalUsed = DataGrid1.Item(i, 1)

                    DataGrid1.Item(i, 2) = Loca
                    DataGrid1.Item(i, 3) = dateIn




                    Dim stSQL As String = "UPDATE [metaltbl] SET  [Metal name] = MetalName, [Metal Used] = MetalUsed, Loc=Loca, [Date info]=# " & dateIn & " # "

                    Dim cmd As New System.Data.OleDb.OleDbCommand(stSQL, myConnection)

                    cmd.ExecuteNonQuery()

                    myConnection.Close()





                    OleDbDataAdapter11.Update(Dsschedule)
                    SteShone1.AcceptChanges()
                Next

                ' CALL FUNCTION TO FILL
                '      FnMCO()

            End If

        Catch ex As Exception

            '     MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub



So when I press buton 1, since the table is empty as such for the selected date from the calender


Metal Name    Metal used     Loc        Date info



then I enter


Metal Name    Metal used     Loc        Date info
Copper          1000
Iron            200
Steel            4000

After I enter the above information I will press button 2 the result I desire to see is as seen below
Metal Name    Metal used     Loc        Date info
Copper          1000          Ny         5/6/2011
iron            200           Ny          5/6/2011
Steel            4000         Ny          5/6/2011


this should get displayed on the grid and also saved back on metaltbl


But this is what gets displayed

Metal Name    Metal used     Loc        Date info
Copper          1000          Ny         5/6/2011
iron            200            
Steel            4000        


and no data is saved back to the metaltbl the table is still empty

so the first row gets the desired date and location added and displayed the following rows don't and none of the data is save back to the table.
Please let me know what I'm doing wrong .

thanks

            

as for only one row was filled, change :item(0,2) to item(i,2)
item(rownumber, column number)

can you tell if your dataset gets updated?

I already had it as  item(i,2)
so not sure that is the issue.

I had the Next in the wrong location, so now, display all but doesn't save the data back to the table

in your updating lines, when you debugging it in steps, does it throw error at all?

   Dim stSQL As String = "UPDATE [metaltbl] SET  [Metal name] = MetalName, [Metal Used] = MetalUsed, Loc=Loca, [Date info]=# " & dateIn & " # "

Select allOpen in new window

this doesn't look right to me,
change it to

   Dim stSQL As String = "UPDATE [metaltbl] SET  [Metal name] = " & MetalName & ", [Metal Used] = " & MetalUsed & ", Loc=" & Loca & ", [Date info]=# " & dateIn & " # "

Select allOpen in new window

You were right, I enabled the error code it threw error message, I changed the syntex, now all the values format looks fine but I get error
No value given for one or more required parameter

that error tells you  one of the parameter you parsing into the update query is missing, do a step by step check see which parameter's value you'r missing

Hi Mike,
Thank you so much for anwsering all my question, I'm getting close to making it work.
Now it displays and saves but it doubles it the second row, so I'm wondering if the placement of  for next loop.

so for example

if i have
metal name metal used   loc      date
copper           2               Ny
Steel               1              Ny


and hit save it display as follows

metal name metal used   loc      date
copper           2               Ny        5/10/2011
Steel               1              Ny         5/10/2011

then when I check for 5/10/2011

then when I check

metal name metal used   loc      date
copper           2               Ny        5/10/2011
Steel               1              Ny         5/10/2011
Steel                1             Ny         5/10/2011

and when I check the table the 2nd row seem to be saved 3 time twice with the all the info and once with the just metal name and metal used.

metal name metal used   loc      date
copper           2               Ny        5/10/2011
Steel               1              Ny         5/10/2011
steel                1             Ny         5/10/2011
Steel                 1


So my placement of for and next loop is doing this I guess, any suggestions?
thanks

can i have a look at your modified code of for loop and your updating code?

Hi Mike,
Please see below the code for the save button which when press adds the date and location displays and should save

some notes-
------
A.SelectionStart is date from Calendar object
OleDbDataAdapter1 - definition below
SELECT [metal name], [metal Used], Loc,  Id, [Date info] FROM [metaltbl]

Sh1metalsave1  is dataset for OleDbDataAdapter1
'----- end of Notes-----

' This is the loop and update button '

 Private Sub SaveMaconebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveMaconebtn.Click

        Try


            Dim i As Integer
            Dim dateIn As Date = A.SelectionStart
            Dim metalname As String
            Dim metalUsed As Integer
           
            Dim Loca As String
            Loca = "TX"
            Dim idnos As Integer

            For i = 0 To SteShone1.Tables("metaltbl").Rows.Count - 1
               


                DataGrid1.Item(i, 5) = dateIn



                Metalname = DataGrid1.Item(i, 0)
                metalUsed = DataGrid1.Item(i, 1)
                DataGrid1.Item(i, 2) = Loca
               
       



            Dim Dsschedule = SteShone1.GetChanges
            Dim nno As DBNull
            If (Dsschedule Is nno) Then
                MsgBox("You Have Not Made Any Change To The Program.Hit Ok to Exit this message")

            Else

                Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Project\metal.mdb"
                Dim myConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
                myConnection.ConnectionString = connString

                myConnection.Open()


                Dim stSQL As String = "UPDATE [metaltbl] SET [metal name] = " & metalname & ", [metal Used] = " & Used & ", Loc = 'TX', [Date info] = # " & dateIn & " # where isnull([Date info])=true   "
                Dim cmd As New System.Data.OleDb.OleDbCommand(stSQL, myConnection)



                cmd.ExecuteNonQuery()

                myConnection.Close()

                    OleDbDataAdapter1.Update(Dsschedule)
                    Sh1metalsave1.AcceptChanges()


             

            End If
            Next

        Catch ex As Exception

            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

It worked great. I had to chane addwithvalue to Add.
Thank you so much for all your reply and the great help. Thanks a millionnn