Re: Saving DataGidView data to Excel - Visual Basic Discussion Boards

PL SUGGEST ME HOW CAN IN INSERT DATETIMEPICKER IN A DATAGRIDVIEW IN VB.NET

THANKS

rmshah
Developer

you can see a sample here[^]

Il principe Wink | ;)

ya i tried the one you shown it's working fine to me!! thanks mate Wink | ;)

ZAK

For Each row As DataGridViewRow In DataGridView1.Rows
            row.Cells("Date").Value = Today
     Next

Please anybody there...know how to detect if a removable storage device is write protected?? i having difficulty to figure this out.. Please help?...

i do really appreciate to anybody share their idea... please.. thanks in advance...

You could aways try to write a temporary file to it and catch the exception if the device is write-protected.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

I have a project where a Teacher can choose their class from a ComboBox and this is turn selects all the relevant student from an Access Database and displays this along with some extra columns in a DataGridView.

The Teacher can then add 'Penalty Points' and 'Demerits' to the student before saving them to a spreadsheet (reason being the info needs a lot of formulas to get it to work out several thing the Heads of Year want to see!). I can Insert into Excel using code such as:

Imports System.Data
Public Class Form1
    Private Sub cmdSave_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles cmdSave.Click
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String

            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
            "'S:\Penalty Points\07-08\Test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")

            MyConnection.Open()
            myCommand.Connection = MyConnection
            sql = "Insert into [Sheet1$] (StudentId,Name) values('5','e')"
            myCommand.CommandText = sql
            myCommand.ExecuteNonQuery()
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        MsgBox("Row Added Successfully")
    End Sub
End Class

But I need to loop through the DatagridView, Inserting all rows that have been ammended. Can anyone help me out please?

Thanks in Advance Wink | ;)

Here's my latest code:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

        Try
            Dim MyExcelConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim daExcel As New System.Data.OleDb.OleDbDataAdapter

            MyExcelConnection = New System.Data.OleDb.OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\SanctionsDb\2007-08\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")

            For Each row As DataGridViewRow In DataGridView1.Rows
                With myCommand
                    .CommandText = "Insert into [Sheet1$] (StudentId,StuName,ClassGrp,TheDate,Merits,Demerits,Lesson," + _
                        "TeachingGrp,Description,Action,MeritType) values(?,?,?,?,?,?,?,?,?,?,?)"
                    .CommandType = CommandType.Text
                    .Connection = MyConnection
                    ' Configure the insert parameters
                    .Parameters.Add(New OleDb.OleDbParameter("StudId", OleDb.OleDbType.Double, 0, "StudId"))
                    .Parameters.Add(New OleDb.OleDbParameter("Name", OleDb.OleDbType.Double, 0, "Name"))
                    .Parameters.Add(New OleDb.OleDbParameter("Class Grp", OleDb.OleDbType.Double, 0, "ClassGrp"))
                    .Parameters.Add(New OleDb.OleDbParameter("Date", OleDb.OleDbType.Double, 0, "Date"))
                    .Parameters.Add(New OleDb.OleDbParameter("Merits to Add", OleDb.OleDbType.Double, 0, "Merits"))
                    .Parameters.Add(New OleDb.OleDbParameter("Demerits to Add", OleDb.OleDbType.Double, 0, "Demerits"))
                    .Parameters.Add(New OleDb.OleDbParameter("Lesson", OleDb.OleDbType.Double, 0, "Lesson"))
                    .Parameters.Add(New OleDb.OleDbParameter("TeachingGrp", OleDb.OleDbType.Double, 0, "TeachingGrp"))
                    .Parameters.Add(New OleDb.OleDbParameter("Description", OleDb.OleDbType.Double, 0, "Description"))
                    .Parameters.Add(New OleDb.OleDbParameter("Action", OleDb.OleDbType.Double, 0, "Action"))
                    .Parameters.Add(New OleDb.OleDbParameter("Type", OleDb.OleDbType.Double, 0, "Type"))
                End With
            Next
            daExcel.InsertCommand = myCommand

            MyExcelConnection.Open()
            daExcel.Update(ds, "Groups")
            ' Cleanup
            MyExcelConnection.Close()
            daExcel.Dispose()
            daExcel = Nothing
            MyExcelConnection.Dispose()
            MyExcelConnection = Nothing

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        MsgBox("Row Added ")
    End Sub

Surely it should work shouldn't it?

I am filling combo box as per below code,and i want to insert the respectibe selected item's code from the database into other table.
how to get selected value?

My code for filling combo box is..

dbconn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=C:\guest\dmb.mdb")
dbconn.Open()
sql = "Select distinct DESIGNATION from DESIG"
dbcomm = New OleDb.OleDbCommand(sql, dbconn)
dbread = dbcomm.Executereader()
cmbdesig.Items.Clear()
While dbread.Read
cmbdesig.Items.Add(dbread("DESIGNATION"))
End While
dbread.close()
dbconn.Close()

Designation table have both desig_code and designation.
I want to get the selected value (desig_code).Which property should i set to get it??
Answer please

kissy

Hi Kissy

Something like:

daGroups.Fill(ds, "Groups")
        cboTeachingGrp.DataSource = ds.Tables("Groups")
        cboTeachingGrp.DisplayMember = "groupcode"

Where Groups is the column containing the info from the database.

Hope this helps Wink | ;)

Thanks for ur answer.
But ur code is not working.
Here i want it like in asp.net dropdownlist's
Datatextfield(TEXT) and datavaluefield(code).
So when iwant to insert code ,i can use only the selectedvalue.
but in ur code it,is displaying code in the combobox.
i want to display text and insert the respective code.
Give me some other suggestion in frawework 2.0.
Windows application using vb in framework 2.0

kissy

Kissy

Sorry I assumed you were using OleDb to connect to your Database. Can you paste what you have so far so we can see?

Please check my first post,i have already pasted my code there.

kissy

you want the property's
combobox.displaymember
combobox.valuemember

but you'r sql string only contains 1 column ???

simplest way to do what you want is to get a datatable from you'r connection
and bind it to the combobox and then set the 2 property's

Dim dt As New DataTable
dim DbConnection as New OleDbConnection([ConnectionString])
dbconnection.open()
Dim da As New OleDbDataAdapter(Sql, DbConnection)
da.Fill(dt)

combobox.datasource = dt
combobox.displaymember = "column to show"
combobox.valuemember = "column that contains unique key"

hope this helps

Yes as mentioned above use combobox.valuemember for capturing the Primary Key Column which is commonly used for Edit and Deleting mode!!!

Example:-
**********

combobox.datasource = dt<br />
combobox.displaymember = "EmployeeName"<br />
combobox.valuemember = "EmployeeID"

Hope this helps you in understanding the diffrence between Displaymember and valuemember

similar example in TextBox control as well

TextBox1.Text = "EmployeeName"<br />
TextBox1.Tag = "EmployeeID"

Have a Nice day ahead!!!

Regards,

ZAK

i have a MDI form ina project
and it has a child forms

my problem is ....
when i minimise a child form then automatically
the parent i.e Mdi form also minimised

how can i do it....

thanks in advance
vijay

Hello Vijay,

Define a variable in child form of type MDIForm.

Dim _mrm As MainForm

Now define a function in Child form which takes an argument of type MDIForm. For example:

Public Sub parentmin(ByVal mainform As MainForm)

_mrm = mainform

Me.Show()

End Sub

Set the following code in the LostFocus() event of the childform

Private Sub Form1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LostFocus

_mrm.Focus()

_mrm.WindowState = FormWindowState.Minimized

End Sub

Now From MDIForm, you should call the Child form in the following manner(Lets say on menu button click):

Dim frm As New Form1
frm.parentmin(Me)

I believe this should help.

Regards,

Allen

Allen Smith

ComponentOne LLC
www.componentone.com

yeah Allen it works perfect Wink | ;)

ZAK

hello frnds,
i have a file of XML i want to convert it to word document .
is it possible ???
either by manually or by using programming vb.Net

regrds
koolprasad2003

If the message is useful for U then please Rate This message...

Be a good listener...Because Opprtunity knoughts softly...N-Joy

Not sure of exactly what you are looking for here.

You can always just open an XML file in word.

What is it that you are trying to do?

You want the data formatted when you open it?

FFRF[^]

View Ray Cassick's LinkedIn profile[^]

hi, i'm using VS2008 and working on windows form application,
here my problem..
i can receive hex data that can convert it to ascii char,
but i have problem when receive hex 0x80 till 0xff where it show value 63, everything ok when receive hex 0x7f and it will display 127 as result in decimal, here my sample code:

'variable 'data' is the hex data that i receive
dim newData as string
newData = Asc(data)
TextBox1.Text = newData

any help please why this happen...

hello there...

how can i detect or catch the event when mouse click/open any folder in windows explorer??

and how can i get that open folder path??

or even just collects all folder path in all open folder in any windows explorer that is open??

please help anybody there, who have a good idea about this?... please...
thanks in advance... Smile | :)

I asked about the cam device control and cam picture control in the wrong place. Stupid of me, I know. I've looked in alot of places. Most times they tell how to access the web cam without using the cam control devices. I want to know how to use them. Can anyone tell me how? Thanx, StephenStefl

I found some code to save a persons signature to a file. It also will load a saved Signature and display it in the panel. I want to create a report using Crystal reports. I don't know much about it. But, I want to be able to display the signature on the report. Is there a way to do this in Crystal reports? If not, do you know the best way to do this? I would like to find some code that can convert a Vector file into a image file and then put the file on the Crystal report. Any suggestions?

Thank you.

Hi,

anything you can draw to a Panel, you can turn into an image file, using
Control.DrawToBitmap() and Image.Save(). So the signature would become just another image.
I trust CryRep knows how to handle images.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Voting for dummies? No thanks. Dead | X|