|
I tried to follow your code and modified it with a data reader.
Public Function ExecuteQuery(ByVal query As String) As Boolean
Dim con As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbCommand
con = New System.Data.OleDb.OleDbConnection(DSN)
cmd = New System.Data.OleDb.OleDbCommand(query, con)
cmd.CommandType = CommandType.Text
Try
con.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
con.Close()
End Try
Return True
End Function
Private Function frmUploader_upload() As Boolean
Dim strConnection As String
Dim strCommand As String
Dim query As String
Dim excelConnection As System.Data.OleDb.OleDbConnection
Dim excelCommand As System.Data.OleDb.OleDbCommand
Dim dr As System.Data.OleDb.OleDbDataReader
Dim dt As New DataTable
Dim i As Integer = 0
strConnection = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=Excel 8.0;Persist Security Info=False", Me.tbFile.Text)
excelConnection = New System.Data.OleDb.OleDbConnection(strConnection)
strCommand = String.Format("SELECT * FROM [{0}$]", Me.cbSheet.Text)
excelCommand = New System.Data.OleDb.OleDbCommand(strCommand, excelConnection)
Try
excelConnection.Open()
dr = excelCommand.ExecuteReader
While dr.Read
query = String.Format("INSERT INTO Sl_Logproof_200311 VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19},{20},{21},'{22}',{23},{24},'{25}','{26}','{27}',{28})", _
ReplaceQuote(dr(0).ToString), ReplaceQuote(dr(1).ToString), ReplaceQuote(dr(2).ToString), ReplaceQuote(dr(3).ToString), ReplaceQuote(dr(4).ToString), ReplaceQuote(dr(5).ToString), ReplaceQuote(dr(6).ToString), ReplaceQuote(dr(7).ToString), ReplaceQuote(dr(8).ToString), ReplaceQuote(dr(9).ToString), ReplaceQuote(dr(10).ToString), _
ReplaceQuote(dr(11).ToString), ReplaceQuote(dr(12).ToString), ReplaceQuote(dr(13).ToString), ReplaceQuote(dr(14).ToString), ReplaceQuote(dr(15).ToString), ReplaceQuote(dr(16).ToString), ReplaceQuote(dr(17).ToString), IsEmptyText(dr(18).ToString, "0"), IsEmptyText(dr(19).ToString, "0"), IsEmptyText(dr(20).ToString, "0"), _
IsEmptyText(dr(21).ToString, "0"), ReplaceQuote(dr(22).ToString), IsEmptyText(dr(23).ToString, "0"), IsEmptyText(dr(24).ToString, "0"), ReplaceQuote(dr(25).ToString), ReplaceQuote(dr(26).ToString), ReplaceQuote(dr(28).ToString), ReplaceQuote(dr(27).ToString))
ExecuteQuery(query)
If i Mod 1000 = 0 Then
Me.tsStatus.Text = String.Format("{0} Rows have been transformed or copied.", i)
End If
i += 1
End While
Catch ex As Exception
MsgBox(ex.Message)
Return False
Finally
excelConnection.Close()
End Try
Return True
End Function
It can copy now, but is there any better way? With this code, it still takes too much time to import and it needs 2 connections. Using SQL Server DTS is a lot faster.
Best Regards,
Indra Permana Rusli
|
|
|
|
|
Well, yes DTS is a lot quicker, thats what it was designed for! You may be able to tweak your code, but I doubt if it will ever outperform dts.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hohohoho, thanks, finally I can import it well using DTS. I am using Microsoft DTS Custom Task Object Library reference in my source code and create DTS Package with a global variable named gvDataSource in the SQL Server. Then here is my new code.
Private Function frmUploader_upload() As Boolean
Dim pkg As DTS.Package2
pkg = New DTS.Package
pkg.LoadFromSQLServer(mDBSERVER, mDBUSER, mDBPASSWORD, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "APLIKASI3:E_START.Sl_Logproof_200311")
For Each gv As DTS.GlobalVariable In pkg.GlobalVariables
pkg.GlobalVariables.Remove(gv.Name)
Next
pkg.GlobalVariables.AddGlobalVariable("gvDataSource", Me.tbFile.Text)
Me.Cursor = Cursors.WaitCursor
Try
pkg.Execute()
Catch ex As Exception
MsgBox(ex.Message)
Return False
Finally
pkg.UnInitialize()
pkg = Nothing
End Try
Me.Cursor = Cursors.Default
Return True
End Function
Best Regard,
Indra Permana Rusli
|
|
|
|
|
pleased to be of some use.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Check the extended properties bit of your connection string - it can differ for different versions of excel.
|
|
|
|
|
Hi Friends,
I am working in vb2005.
I have been struck up in my work while retrieving the computer information in a active directory .
This is the code I am using,
<br />
dim np as object<br />
np = GetObject("LDAP:")<br />
dim user as object <br />
user = np.OpenDSObject( LDAPPath , vbNullString, vbNullString, ActiveDs.__MIDL___MIDL_itf_ads_0000_0018.ADS_SECURE_AUTHENTICATION)<br />
This code is working well while retrieving person data ,but when it comes to computer
it is throwing the following message .
The specified directory service attribute or value does not exist. (Exception from HRESULT: 0x8007200A)
The LDAPPath that had passed is correct one.But still getting the error.
Please help me out to solve this problem.Thanks in advance .
Regards,
Chaitanya
|
|
|
|
|
I added Database file in VS2005 project. and this is the connection string i am using
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes"
It works fine since database file and application is on local system. But when i try to connect to remote database by providing the IP address i get this error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
How can i make connection with Remote instance of database file located in the data directory, on remote SQL Server Express instance
|
|
|
|
|
The most common cause for this error message is found in the error message:
"When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."
Verify that the database that you are connecting to is set up to accept remote connections.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
I have enabled the remote connection also. but it is still giving me the error.
this is the connection string i am using. is this correct?
Server=IP\SQLEXPRESS;Database=Database;Trusted_Connection=yes;
|
|
|
|
|
A trusted connection only works if you have a domain account, so that it's the same account on both computers. Otherwise you have to use an SQL Server login.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
|
Hi
I solved this problem by enabling the TCP/IP protocols in SQL Server Configuration Manager...
hope this helps!
Anoop
|
|
|
|
|
This can be done only if we have SQL express edition instaled. But if i am using mdf in the project like in VS 2005 then i need to connect to another mdf file on network
|
|
|
|
|
Hii
I want to change the back color of selected dated in month calender.
Like i have appointments on following dates 1-10-2008,5-10-2008,10-10-2008,15-10-2008
so i want to show these date back color is green.
How can i do this.
Please suggest.
Thanx
Mitesh Khatri
~Khatri Mitesh
khatrimitesh@hotmail.com
Bikaner (Rajasthan)
INDIA
|
|
|
|
|
Hi Mitesh,
You can only show a range between to dates wiith different backcolor in the month calender control but you can not show for that different different dates(not in range).In range all the dates witll have same color.
Thnx
|
|
|
|
|
hi,
my problem is that.....
Recently i get a complex xml.Which have huge no of data.When i read xml by dataset i get 6 realated tables.Each have 10 to 20 no of coloumn.so i want to directly move this data to sqlserver database.
please help me
modified on Wednesday, October 1, 2008 5:12 AM
|
|
|
|
|
Hello,
So you basically wants to move the XML data into the SQL server. If you are trying to do
this through code, then you can have a look at the given web link which should give you
some fair idea on how to achieve this.
http://www.dbazine.com/sql/sql-articles/cook2[^]
Hope this will help.
Regards,
Allen Smith
ComponentOne LLC
www.componentone.com
|
|
|
|
|
hi Allen,
Thanks for your suggestion.I think it will work if i create related table first. But i do not want to create table by myself.i want something dynamically to store it from dataset to database.
Hope you help me again.
|
|
|
|
|
You just need to issue a create table statement first, surely...
|
|
|
|
|
|
Hi, is that a way using VB6 to check printer status whether it connected or not connect to the printer device before print any job?
Please comment.
Thanks in advance.
Regards,
Jy
|
|
|
|
|
Hi all,
I have a server application awaiting incoming connections through a series of sockets set in "listen" mode. I have a number of client applications in remote locations that communicate via tcp/ip, opening the corresponding socket connection to the server and transmitting data.
The problem I have is that the client-to-server socket connections seem to die out after a while, specially in locations where internet is intermittent. When this happens, the connection does not terminate, rather it stays in "connected" state, but data transfer is lost in the web. Even though the winsock.state = sckConnected, the data is not received.
In order to solve this, I implemented a refresh timer, that closes and reopens all sockets in the server once every n seconds. It works, but it's terribly unelegant. I have found no other way of doing this, keeping the connections alive.
Anybody could please give me a suggestion on how this can be done properly? How can I determine if a connection is alive or dead, regardless of what .state says?
Thanks,
Regards.
|
|
|
|
|
are you using win sock . I had the similar problem before using win sock where i lost data even the client was connected with the server.
But later i used dart sock and it helped me you can get the trial version of these from www.dart.com and try it out, hope this would help you.
|
|
|
|
|
Yes, I use winsock. Thanks for the tip!!, but I think I might have solved temporally in a rather clumsy way.
I set up each client to "ping" the server every second. The server "pongs" to each "ping". I there is no "pong", the connection is down and a reconnect is issued. This check is performed at both sides.
Unelegant, but it works, and it´s free...
Has anybody implemented a different type of "keep alive" mecanism?
Regards,
|
|
|
|
|
I am getting ORA-01036 (Illegal Variable Name/Number) when I call the oracledataadaptor.update method.
I am able to fill the dataset & bind to the datagridview. When I try to update Oracle with the dataadaptor,
I get the Illegal Variable name exception.
Any ideas?
Thanks in advance!
' '' ''' '''' Call the update method
Try
daCustomers.Update(ds.Tables("Customers"))
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
' '' ''' '''' Set up Oracle Update Command
Private Sub SetCommands()
Dim uCMD As New OracleCommand
uCMD.Connection = cnOra
uCMD.CommandType = CommandType.Text
uCMD.Parameters.Add("@inFNAME", OracleType.Char, 30, "FNAME")
uCMD.Parameters.Add("@inID", OracleType.Int32, 12, "ID")
uCMD.CommandText = "Update NYOUG_CUSTOMERS SET FNAME = @inFNAME WHERE ID = @inID"
daCustomers.UpdateCommand = uCMD
End Sub
|
|
|
|
|