|
How can i make a primary key like if column is ID how can i make it as my primary key and control it in my insert statment?
|
|
|
|
|
Hi
In this I am loading the data into the dataset when reading the values from Excel file. Datatable in dataset supports the primary key contraints enforncement on the dataset which restrict the duplicate value to be insert in dataset. This will helps you to achive the functionality. But in this you need to check whether the data loaded in dataset should be unique otherwise you will get an exception while puting the primary key constraints.
Or you can also predefine the datatable in dataset and put the primary key contraint on respective column and then load the data from the excel but you must ensure that the excel has not been tempered by the user manually. In case user has manually instert the duplicate row in excel then you will get an exception while reading the value from excel.
You can refer to the below link to create primary key constraints in datatable.
http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx[^]
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
hi, i want to make Korean to Khmer dictionary.
1. how can i load font in vb.net?
2. how can i load the word list and definition from Access 2007?
3. how can i publish this application in smaller size?
4. Please give me some more advice for making a dictionary.
|
|
|
|
|
The problem I have is I do my development on my work PC at home. The SQL Server (SQL 2000) is located in our Home Office on a server farm. Running a query that looks at the C: drive will not work when run on the SQL server as my excel file is not located there (it's on my PC). The only solution I've been able to come up with is NewRow and Rows.Add to do this, but OMG is it slow. I have a spreadsheet on 2 tabs that has a little over 100k rows and it takes about 3-4 hours to upload this. And it's 2 fields that I'm uploading, a number and a bit. Any suggestions on what I can do otherwise? Due to NT security with the company I work with we can't place files on the SQL server, and we also cannot create linked servers.
Thank you.
Wendell
|
|
|
|
|
Hi there, Great Job !!!the code works perfect !!!
I have one question, how can i update an excell file that is shared among other users ???
The code you wrote DOES update the file when it's in shared mode, BUT I have to close the file and open it again to see the changes. For some reason it won't refresh automatically. Can you help me with this ??
Thanks in advance.
Facu20yo.
modified on Sunday, April 19, 2009 10:04 AM
|
|
|
|
|
i trying to read and write data from/to excel file row by row.how i can identified row and column.your code given only to read the entire data.please help me...
|
|
|
|
|
Hi
Actually I am not very clear with your requirement. I have two points coming in my mind. Please confrim are you looking the solution corresponding to that points or any other requirement you have.
1. Are you want to constantly conecting to Excel sheet and read the row one by one.
2. Or you want to read the complete data from Excel at one shot and use the data one by one with the help of Binding Manager. For e.g. in you application you want to display the data in some form and move between different records by pressing the Next, Previous button on the form.
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
actually i'm trying to read data from cell and use it for calculation in my program.i have a lot of data in excel file.
i want to read data from row 1 and multiple column.for example : i read data from row 1 and column A to P.i need to read the A1 and do the calculation.read data B1 and do the calculation and so on...
i think i should put some looping.but i don't know how??please help me...
|
|
|
|
|
by the way bro, im Paul Jacquez, 2nd year student SSCT Surigao City, Philippines. i want studying vb.net bro because i want a programmer someday bro, plz help me bro. i want codes edit,delete and search with use of excel as database bro. plz help me bro.?
Thank you bro. God Bless U.
|
|
|
|
|
Hi Paul
Please check the code given below which helps you to edit and update the record in Excel. Rest of the code same as I was posted on website.
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Update [Sheet1$] set " & _
"FirstName=@FName, LastName=@LName, Age=@Age, Phone=@Phone where " & _
"FirstName=@Name"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
pram = olecomm1.Parameters.Add("@Name", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram.SourceVersion = DataRowVersion.Original
If IsNothing(ds) = False Then
dr = ds.Tables(0).Rows(0)
dr.BeginEdit()
dr("FirstName") = "Bunty"
dr("LastName") = "Aggarwal"
dr("Age") = 26
dr("Phone") = 98989898
dr.EndEdit()
oleadpt.UpdateCommand = olecomm1
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
The above code shows that how you can update the first row from dataset, you can customize your code to delete the specific row from the dataset.
As far as delete is concerned, it is currently not allowed in Excel using code but you can achive the same by delete the old excel file and writing the new one.
In case of any query please feel free to contact me.
Thanks
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
base in your program about "TestExcel2" bro, i need your help bro on how to edit the existing record bro, using vb.net bro. plz help me bro?
|
|
|
|
|
Hi bro,i have a question how to delete data in excel 2007 using vb.net?..pls reply with this codes thank you....and god bless...
|
|
|
|
|
Hi bro, wow very nice bro like it. Thank you so much bro, i really appreciate your code project "Reading and writing excel file using VB.NET."
|
|
|
|
|
Please remove yourself from the computer programming field, jesus.
|
|
|
|
|
Thanks for the suggention. I will definatly think about this, but could you please let me know why you want me to quite from my job. If you are having some issue with the code then let me know. I will try my best to reslove your issue.
Regards
Raman Tayal
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Because the code you posted doesn't not run "out of the box". What's the point of posting code that doesn't work? When I say doesn't work, I should be able to download a code example and it should run without an issue.
|
|
|
|
|
Hi
I really appologies for the inconvenience caused. I forget to include the sample excel file in the source code. This code is running perfectly and thousands for users are using this code. As of now to run this code please create an excel file on c drive name as test.xls. Add the four column in the excel as shown in the image given in the article.
e.g.: -
1. FirstName
2. LastName
3. Age
4. Phone.
Now try to run the code, this should work as expected. If you are still facing any issue please feel free to contact me.
Thanks
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks bro, don't listen to lazy people that instead of critizicing should thank you for your time uploading this solution.
Regards,
|
|
|
|
|
Thanks for the appriciation
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks alot, I love it when people post code that simple doesn't run out of the box. Please, remove yourself from the field.
|
|
|
|
|
Please reply me ASAP. Thanks
Hi, I used your code for select, insert data in excel.
I implemented it and runs successfully.
If number of rows in dataset is less, i.e. aroud 100 then it works fast. But I have more than 2000 rows in my dataset and the mathod oleDataAdepter.InsertCommand = cmdInsertCommand is taking too much time to write thode data in excel. it takes around 10 minutes.
whereas Time taken when I manually loop throw my dataset and wrte data using wrksheet.Range("A" & rowNum).Value = dsPlan.Tables(0).Rows(i)("mphId") is arond 3-4 minutes.
I thought your code will work fast as it does not involves looping. and use Inser, Update methods of oleDBComman. But I dont know why it takes much time.
Here is my method which accepts dataset as input parameter which contains data selected from database tables of SQL Server. I want to write those data in excel sheet.
Private Sub WriteVegUnitData(ByVal ds As DataSet)
'Parameter dataset "ds" which contains data selected from SQL Server
Dim olecon As OleDbConnection
Dim oleSelectCmd As OleDbCommand
Dim oleInsertCmd As OleDbCommand
Dim oleadpt As OleDbDataAdapter
Dim dsExcel As DataSet
Dim pram As OleDbParameter
Dim drExcel As DataRow
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
oleSelectCmd = New OleDbCommand
oleSelectCmd.CommandText = "Select ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL from [VEGUNIT$]"
oleSelectCmd.Connection = olecon
oleInsertCmd = New OleDbCommand
oleInsertCmd.CommandText = "Insert into [VEGUNIT$] " & _
"(ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL) values (@ID, @VEGWORKPLANID, @ACTION, @VEGSPECIESNAME, @VEGSIZENAME, @ACTIONDETAILNAME, @ACTIONDETAILDISTANCE, @VEGSPECIESGROWTHTYPEID, @MAPPING_ID, @NEW_REC_ID, @DEL)"
oleInsertCmd.Connection = olecon
pram = oleInsertCmd.Parameters.Add("@ID", OleDbType.VarChar)
pram.SourceColumn = "ID"
pram = oleInsertCmd.Parameters.Add("@VEGWORKPLANID", OleDbType.VarChar)
pram.SourceColumn = "VEGWORKPLANID"
pram = oleInsertCmd.Parameters.Add("@ACTION", OleDbType.VarChar)
pram.SourceColumn = "ACTION"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESNAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESNAME"
pram = oleInsertCmd.Parameters.Add("@VEGSIZENAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSIZENAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILNAME", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILNAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILDISTANCE", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILDISTANCE"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESGROWTHTYPEID", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESGROWTHTYPEID"
pram = oleInsertCmd.Parameters.Add("@MAPPING_ID", OleDbType.VarChar)
pram.SourceColumn = "MAPPING_ID"
pram = oleInsertCmd.Parameters.Add("@NEW_REC_ID", OleDbType.VarChar)
pram.SourceColumn = "NEW_REC_ID"
pram = oleInsertCmd.Parameters.Add("@DEL", OleDbType.VarChar)
pram.SourceColumn = "DEL"
oleadpt = New OleDbDataAdapter(oleSelectCmd)
dsExcel = New DataSet
olecon.Open()
oleadpt.Fill(dsExcel)
If IsNothing(dsExcel) = False Then
For Each dr As DataRow In ds.Tables(0).Rows
drExcel = dsExcel.Tables(0).NewRow
drExcel("ID") = dr("strID")
drExcel("VEGWORKPLANID") = dr("wpdWphID")
drExcel("ACTION") = dr("wpdPruneRemove")
drExcel("VEGSPECIESNAME") = dr("vspSpeciesDesc")
drExcel("VEGSIZENAME") = dr("vszSizeDesc")
drExcel("ACTIONDETAILNAME") = dr("vacActionDesc")
drExcel("ACTIONDETAILDISTANCE") = dr("vdsDistanceDesc")
drExcel("VEGSPECIESGROWTHTYPEID") = dr("wpdGrowthTypeId")
drExcel("MAPPING_ID") = dr("wpdID")
drExcel("NEW_REC_ID") = "A"
drExcel("DEL") = dr("dlt")
dsExcel.Tables(0).Rows.Add(drExcel)
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = oleInsertCmd
Dim i As Integer = oleadpt.Update(dsExcel)
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
oleSelectCmd = Nothing
oleInsertCmd = Nothing
oleadpt = Nothing
dsExcel = Nothing
drExcel = Nothing
pram = Nothing
End Try
End Sub
|
|
|
|
|
Hi
Sorry for my delayed response to your query. I have gone through your code and my understanding is you are trying to fetch the data from one excel or any database and put the same into some other excel file or any database. I have found some performance issues in your code. I have try to simulate the same case at my end and which is working perfectly and take approximate some couple of seconds to do all the processing.
Following are the details for the same
I have taken 2 buttons on my windows application one for fetching the data and one for inserting the data. In this code I am reading more than 2000 records from Test.xls file and inserting the same in Test1.xls file. One thing I am assuming here is xls file are already present on your system.
Dim olecon As OleDbConnection
Dim oleadpt As OleDbDataAdapter
Dim ds As DataSet
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Const connstring1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Sub btnFetch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim olecomm As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
olecomm = New OleDbCommand
olecomm.CommandText = "Select FirstName, LastName, Age, Phone from [Sheet1$]"
olecomm.Connection = olecon
oleadpt = New OleDbDataAdapter(olecomm)
ds = New DataSet
olecon.Open()
oleadpt.Fill(ds, "Sheet1")
If (IsNothing(ds) = False) Then
DataGridView1.DataSource = ds.Tables(0)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
End Try
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim pram As OleDbParameter
Dim dr As DataRow
Dim olecomm1 As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring1
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Insert into [Sheet1$] " & _
"(FirstName, LastName, Age, Phone) values (@FName, @LName, @Age, @Phone)"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
If (IsNothing(ds) = False) Then
For Each dr In ds.Tables(0).Rows
dr.SetAdded()
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = olecomm1
olecon.Open()
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If (IsNothing(olecon) = False) Then
olecon.Close()
End If
olecon = Nothing
olecomm1 = Nothing
dr = Nothing
pram = Nothing
End Try
End Sub
I hope this will answer your query.
Please feel free to write me in case of any query
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Dataset ds contains data from SQL Server.
I have more than 32 colums in dataset which I need to transform to excel sheet.
Earlier I was assigning values to each cell by looping through dataset as below.
dim i as integer
dim rowNum as integer
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A" & rowNum).Value = ds.Tables(0).Rows(i)("wphId")
wrksheet.Range("B" & rowNum).Value = ds.Tables(0).Rows(i)("wphMpdId")
wrksheet.Range("C" & rowNum).Value = ds.Tables(0).Rows(i)("wphCustId")
wrksheet.Range("D" & rowNum).Value = ds.Tables(0).Rows(i)("wphComnts")
'
'
'
'
so on up to total 32 colums
'
'
i += 1
rowNum += 1Loop
Loop
But I found that, instead of going trough all cells one by one we can directly assign values to all cells by using following code
which gives me result within couple if seconds
compare to 32 lines above now i have to write onlyn one line.
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A1:AF1").Value = ds.Tables(0).Rows(i).ItemArray
i += 1
Loop
of course your code will give even faster result than above one, but as I was in hurry and need to complete task within my time limit, I found above one as a faster alternative.
today I tried your code, you used something like, "dr.SetAdded()", I am using VB.NET2003 and this gives me error like datarow have no method named SetAdded()
Any way, thanks a lot for answering my question. It was my first question in this website and had good experience.
Thanks
|
|
|
|
|
Dear,,
after I read the excel file and converted it into a dataset
how can I insert these dataset into my dataase(SQLserver database)?
Thanks in advance
gho;.lkijh
|
|
|
|
|
|