|
many thanks for the suggestions.
Can you please give me an example code to fill into value?
|
|
|
|
|
Well the question is what kind of inserting you want to do.
Simply put you could do something like this:
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) values(1,2,3,4,5,6,7)", con)
or
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) values(1," & SomeVariable & "," & SomeOtherVariable & "," & YetAnotherVariable & ",5,6,7)", con)
or
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) SELECT field1, field2, field3, field4, field5, field6, field7 FROM AnotherTable WHERE some condition is true ", con)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Since I am looping through each row of a datagridview, I would like to insert each row of the datagridview. What should I do in this case?
Many Thanks
|
|
|
|
|
Use the second option from my previous answer, and set the value of each variable to the value you get from each field in each row in the datagridview
Something like:
Variable1 = row.Item("Field1")
Variable2 = row.Item("Field2")
etc.
My advice is free, and you may get what you paid for.
|
|
|
|
|
I tried:
"INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ", " & dgr1.Cells(7) & " )"
but it says syntax error
|
|
|
|
|
Something like that, yes.
Also adapt your code like this, so you will know if something goes wrong:
Try
'Your code here
Catch ex As Exception
MsgBox(ex.ToString)
End Try
and remember to check the length of the content of each cell against the maximum length of each field.
My advice is free, and you may get what you paid for.
|
|
|
|
|
When I did like this:
Try
Dim dgr1 As DataGridViewRow
For Each dgr1 In Me.DataGridView1.Rows
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\db1.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ")", con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
It said:
Error 1 Operator '&' is not defined for types 'String' and 'System.Windows.Forms.DataGridViewCell'.
I don't know what values I should enter. This must be a simple stuff but I am just struck.
|
|
|
|
|
Yep, that's why the best first step in learning this stuff is the Try Catch statement.
okay, so don't panic, this problem is something that you are going to bump into a lot when coding, and the solution is very simple:
for example dgr1.Cells(0) is in fact the cell as an object, not the cell's content. So all you need to do is get to the object's (your datagridview cell) content.
try dgr1.Cells(0).Value , and see what it says next.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thank you very very much. Following code works:
Try
Dim dgr1 As DataGridViewRow
For Each dgr1 In Me.DataGridView1.Rows
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\db1.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values('" & dgr1.Cells(0).Value & "', '" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "')", con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
But I just wonder why I am getting one extra blank row in my database table.
|
|
|
|
|
That is because the datagridview offers an empty line at the bottom for the user to enter new rows.
You can prevent this row from being entered into your database by checking if all cells are empty or not.
So instead of putting dgr1.Cells(0).Value directly into the insert statement, you could do something like this:
If Not dgr1.Cells(0).Value & dgr1.Cells(1).Value & dgr1.Cells(2).Value & etc. = "" Then
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values('" & dgr1.Cells(0).Value & "', '" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "')", con)
End If
My advice is free, and you may get what you paid for.
|
|
|
|
|
But I am getting two blank rows. One is the obvious one but I am getting one another row on the top of the default blank row.
|
|
|
|
|
got it got it!
Thank you so much.
I am all done!
|
|
|
|
|
You can also test if the row is the empty one at the bottom by using:
If Not dgr1.IsNewRow Then
' insert
End If
|
|
|
|
|
Your's is actually the better alternative for this case (I hadn't even thought of that one).
The only advantage to the method I suggested is that if the user deletes the contents of all the cells of a row, but does not delete the row itself, the row will also not be inserted.
My advice is free, and you may get what you paid for.
|
|
|
|
|
How to copy some files from one folder to other with multithreading in VB.NET?
thx
|
|
|
|
|
You are very stupid, aren't you ?
You asked three times in the C# forum, did you want C# or VB code ? Why do you even want to do this ?
Christian Graus
Driven to the arms of OSX by Vista.
"I am new to programming world. I have been learning c# for about past four weeks. I am quite acquainted with the fundamentals of c#. Now I have to work on a project which converts given flat files to XML using the XML serialization method" - SK64 ( but the forums have stuff like this posted every day )
|
|
|
|
|
Welcome in the CP's Memorable Quotes page. ([^] the link is indirect, since the PermaLink on the message is broken, sorry...).
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
Multithreading, no idea. I do know how to copy files from one folder to another, just take a look at System.IO . I won't explain exactly how to, that will be a nice (though a simple) challenge
Greets,
Zaegra
Motivation is the key to software development.
|
|
|
|
|
ok here is my code for loading the file
Imports System.Xml.XPath
Imports System.Xml.Linq
Imports System
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xmldoc As XDocument = XDocument.Load("OpenFileDialog1.FileName")
Dim
OpenFileDialog1.Filter = "Xml (*.xml)|*.xml"
OpenFileDialog1.ShowDialog()
System.Xml.Linq.XDocument.Load(OpenFileDialog1.FileName)
End Sub
End Class
How do i load node text into text box from here
a simple xml file for testing here person.xml
<xml version="1.0" encoding="utf=8">
<person>
<fname>Keith</fname>
<lname>Ryan</lname>
</person>
thanks
|
|
|
|
|
here is the xml
<xml version="1.0" encoding="utf=8">
<person>
<fname>Keith
<lname>Ryan
|
|
|
|
|
<xml version="1.0" encoding= "utf=8"?>
<Person>
<FName>Keith</FName>
<LName>Ryan</LName>
</Person>
|
|
|
|
|
Try the following
Dim doc as New xml.xmldocument
doc.Load(Filename)
Dim MainNode as xml.xmlnode = doc.ChildNodes(1)
For Each NameNode As Xml.Xmlnode In MainNode.ChildNodes
Dim Name as String = NameNode.InnerText
TextBox1.Text = Textbox1.Text & vbCrLf & Name
Next
Just replace the names of the controls, that's pretty obvious.
Note: You might have to delete the first enter (vbCrLf) in your textbox. (Why? Take a good look at the code :P)
Hope this helped you out,
Zaegra
Motivation is the key to software development.
|
|
|
|
|
here is what i have. childnodes not seen by system.xml.linq.xdocument is there another import i need to do for this or refrence i am refrenceing microsoft xml 6.0. What do you mean replace name of controls i am really new to programming
OpenFileDialog1.Filter = "Xml (*.xml)|*.xml"
OpenFileDialog1.ShowDialog()
Dim filename = OpenFileDialog1.FileName
Dim xmldoc As XmlDocument
xmldoc.Load(filename)
Dim MainNode As Xml.XmlNode = xmldoc.ChildNodes(1)
For Each NameNode As Xml.XmlNode In MainNode.ChildNodes
Dim Name As String = NameNode.InnerText
TextBox1.Text = TextBox1.Text & vbCrLf & Name
|
|
|
|
|
Well, why do you need an XDocument in the chain ? Why not just use File.ReadAllText ?
Christian Graus
Driven to the arms of OSX by Vista.
"I am new to programming world. I have been learning c# for about past four weeks. I am quite acquainted with the fundamentals of c#. Now I have to work on a project which converts given flat files to XML using the XML serialization method" - SK64 ( but the forums have stuff like this posted every day )
|
|
|
|
|
i think i need xdocument not sure i am new to programming because the file i am loading into memory is an xml file i need the information from each node to fill different text boxes. File.readalltext is for text files only correct?
|
|
|
|
|