|
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
|
|
|
|
|
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
|
|
|
|
|
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
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?
|
|
|
|
|
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...
|
|
|
|
|
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.
|
|
|
|
|
|
You're welcome.
|
|
|
|
|
Hi! All
I want to save doc,pdf,jpg file to sqlserver database. please help me to store file to database. And also retrive file from the database. I am using vb.net Please try is as soon as possible.
Thanks & Regards,
Harun
harun
|
|
|
|
|
You See there is one thing you should do before you post in a Forum, not only CodeProject, in any Forum or before you ask for help. at least try to make your own research first and Google
http://www.google.co.za/search?hl=en&q=Saving+a+File+to+SQL&meta=[^]
Hope this helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Yes i do agree with you!!! this is for everyone around the forum!!!
ZAK
|
|
|
|
|
hi all,
i filter a datagrid with input into a texbox. It filters fine. But how do i bring back the original data into the grid when the textbox is empty?
code below:
'This is in the textchanged of txtQS
If Not txtQS.Text.Trim = "" Then<br />
Dim dt As TMSDataSet.LASTIDNOREFFDataTable = New TMSDataSet.LASTIDNOREFFDataTable()<br />
LASTIDNOREFFTableAdapter.Fill(dt)<br />
Dim dv As DataView = New DataView(dt)<br />
dv.RowFilter = "lastmonth = " + txtQS.Text<br />
LASTIDNOREFFDataGridView.DataSource = dv<br />
LASTIDNOREFFDataGridView.Refresh()<br />
Else<br />
Me.LASTIDNOREFFTableAdapter.Fill(Me.TMSDataSet.LASTIDNOREFF)<br />
End If
Thanx.
He who goes for revenge must first dig two graves.
|
|
|
|