|
VB.Net/MS Access. I need to insert records into a particular table (say X)after getting the data from two different tables (say A & B) and doing some permutations and combinations on it. I am getting entire data from tables A & B into two different Datasets. A loop has been written to parse the records from A (dataset), check in B(dataset) and insert the record in table C. However, this entire process is taking a long time. (i.e: in my test run, to insert around 8 lakh records, it took approx 4 hrs).
As per my perception, the time delay is being caused since I am firing an insert statement directly on the database during every loop cycle. Is there a way to avoid this and speed up the process. (i.e. Can the records be inserted into a dataset and then all the records be directly inserted into the table from the dataset)
Please help me its very urgent !!! Sorry for the long description
With Best Regards,
Mayur
-- modified at 0:10 Wednesday 8th February, 2006
|
|
|
|
|
Without seeing any of the relevant code, it's impossible to tell you what's going on.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Sorry once again I am providing a huge chunk of code below. Please review it and provide me with some ideas to speed up the process.
Code :
----------------------------------------------------------------------------
str = "select id, code, name, period from EMP2000"
ipcon.Open()
DA = New OleDbDataAdapter(str, ipcon)
DS = New DataSet
DA.Fill(DS, "irec")
str2 = "select id, code, addr, period from EMPADDR"
DA2 = New OleDbDataAdapter(str2, ipcon)
DS2 = New DataSet
DA2.Fill(DS2, "imsctrl")
DV = New DataView(DS2.Tables("imsctrl"))
str3 = "select Id, code, name, addr from EMPDATA where period = '2000'"
DA3 = New OleDbDataAdapter(str3, ipcon)
DS3 = New DataSet
DA3.Fill(DS3, "emp")
Dim cb As New OleDbCommandBuilder(DA3)
Dim cnt As Integer
cnt = 0
'''
Dim DataRow As DataRow
For Each myrow In DS.Tables("irec").Rows
If (myrow(1).Equals(System.DBNull.Value)) Then
code1 = ""
Else
code1 = myrow(1)
End If
If (myrow(3).Equals(System.DBNull.Value)) Then
period1 = ""
Else
period1 = myrow(3)
End If
If (myrow(2).Equals(System.DBNull.Value)) Then
name1 = ""
Else
name1 = myrow(2)
End If
For Each DataRow In DV.Table.Rows
If Trim(DataRow(1)) = Trim(code1) And Trim(DataRow(3)) = Trim(period1) Then
addr1 = Trim(DataRow(2))
Exit For
End If
Next
dsNewRow = DS3.Tables("emp").NewRow
dsNewRow.Item("code") = Trim(code1)
dsNewRow.Item("name") = trim(name1)
dsNewRow.Item("addr") = Trim(addr1)
DS3.Tables("emp").Rows.Add(dsNewRow)
DA3.Update(DS3, "emp")
Next
----------------------------------------------------------------------------
Dave, Is there a way to insert all the records in a dataset and finally update the dataset to the database? If there is such a way then is it really fast. Thnx for your help.
With Best Regards,
Mayur
|
|
|
|
|
Convert these to parameterized stored procedures.
Only grab the data you need at the time.
It looks like you're retrieving every employee in the table EMP2000, then going to another table and grabbing all of their addresses, then your grabbing more name and addresses according to some WHERE statement. WHY????
Do you really need all this information at any one point in your code??? I doubt it! You could retrieve all the RELEVANT stuff in one SELECT statement! But, only get what you need at the time. If you updating someones name, you don't need to retrieve the entire database!
The only thing slowing your code down is your own logic.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Thnx for your reply. I guess you did not understand my requirement. I need to get the address of each employee and insert the info into another table. EMP2000 contains only the emp code,name and period. EMPADDR contains emp code,addr and period. I need to link these and insert each employee record in EMPDATA.
With Best Regards,
Mayur
|
|
|
|
|
General Question: Why would a program exit after a dll call without any error given? Is there any way to find it an error occurs or what the error is within a dll?
Very Specific one:
The following shows some dll declaration conversions for DVP1412.dll (see link at bottom).
The first 2 methods are called and return values that would be correct, however the third call will close program without giving an error while trying to initialise the board.
VB.NET code with VCpp declarations commented out:
<br />
<StructLayout(LayouKind.sequential)>Public Class DVPCaptureCard<br />
'int DVP1412_CreateSDKInstence(void **pp);<br />
Declare Function DVP1412_CreateSDKInstence Lib "DVP1412.dll" (<MarshalAs(UnmanagedType.LPStruct)>ByRef pp As DVPCaptureCard) As Int32<br />
'virtual int DVP1412_GetNoOfDevices() = 0;<br />
Declare Function DVP1412_GetNoOfDevices Lib "DVP1412.dll" () As Int32<br />
'virtual int DVP1412_InitSDK(int NoOfDevs, int* IDList) = 0;<br />
Declare Function DVP1412_InitSDK Lib "DVP1412.dll" (ByVal NoOfDevs As Int32, ByRef IDList As Int32()) As Int32<br />
....<br />
.... rest of methods<br />
end class<br />
<br />
'then on the main form<br />
Private Sub Form1_Load( ....)<br />
Dim cap as DVPCaptureCard = new DVPCaptureCard()<br />
result = cap.DVP1412_CreateSDKInstance(cap) ' result = 1 (success)<br />
result = cap.DVP1412_GetNoOfDevices() ' result = 1 (1 card is connected)<br />
<br />
Dim pn(16) as int32<br />
result = cap.DVP1412_InitSDK(1, pn) 'program exits here and doesnt reach next line, no error shown<br />
<br />
....<br />
....<br />
'program closes early: The program has exited with code 0<br />
The method declaration seems ok because changes to it will return an incorrect parameter value from the method (and wont crash) but with what seems the correct use it will crash.
anyone been able to use DVP1412_InitSDK with the DVP1412 board?
original Visual Cpp code is from http://www.advantech.com/support/detail_list.asp?model_id=DVP-1412[^]
|
|
|
|
|
I have just read out the article
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q302896
Which is using to add a button on the command bar to Outlook.
I have try to run the provide code,
The button is added.
But I don't know how to remove(delete) this Add-in completely, not just hide it.
Thank you!!
|
|
|
|
|
I am new to VB.NET. I really try a few ways but seems like this is not run. I'm migrating a VB Engine to VB.NET. The following are the code I need to change to VB.NET. Really wish someone can give me a help here. Thank you all so much~!
Private Sub Spam_Now()
Dim conStr1 As String
Dim con1 As ADODB.Connection
Dim rs1, rs2, rs3 As ADODB.Recordset
Dim sql1, sql2, sql3 As String
conStr1 = "provider=SQLOLEDB;data source=" & DellServerIP _
& ";database=pre_sms;uid=sa;pwd=" & DellServerPass
Set con1 = New ADODB.Connection
con1.Open conStr1
Set rs1 = New ADODB.Recordset
Set rs1.ActiveConnection = con1
Set rs2 = New ADODB.Recordset
Set rs2.ActiveConnection = con1
sql1 = "SELECT TOP 20 * FROM TT_Member WHERE Reminder = '0' AND Status = '1'"
rs1.Open (sql1)
If rs1.EOF And rs1.BOF Then
Label1.Caption = "Finished - " & Now
Else
Do Until rs1.EOF
Label1.Caption = "Spamming - " & Now
OriginatingAddress = rs1.Fields("Mobile_No").Value
If Mid(OriginatingAddress, 1, 3) = "601" Then
Telco = 2
ElseIf Mid(OriginatingAddress, 1, 3) = "019" Then
Telco = 9
End If
Reminder_Msg = "Free Msg.Subscription Reminder.Fee:Auto renewal datedate, unless cancelled. To cancel, send STOP TT to 32132."
MT_Msg = Reminder_Msg
MT_Msg = Replace(MT_Msg, "datedate", Renewal_Date3)
Call Sent_MT
Call Close_Record
rs1.MoveNext
Loop
End If
End Sub
|
|
|
|
|
It's not complete solution. But you can change as you like.
It use SQL Server database.
Private Sub Spam_Now()
Dim conStr1 As String
Dim con1 As SqlClient.SqlConnection 'ADODB.Connection
Dim cmd As SqlClient.SqlCommand
Dim rd1, rd2, rd3 As SqlClient.SqlDataReader 'ADODB.Recordset
Dim sql1, sql2, sql3 As String
Dim strUserName As String = "sa" 'set your user name
Dim strPwd As String = "sa" 'set your password
Dim strDatabase As String = "pubs" 'set your database name
Try
conStr1 = "packet size=4096;user id=" & strUserName & ";password=" & strPwd & ";data source=TEST-PC;persist security info=False;initial catalog=" + strDatabase
'conStr1 = "provider=SQLOLEDB;data source=" & DellServerIP _
'& ";database=pre_sms;uid=sa;pwd=" & DellServerPass
con1 = New SqlClient.SqlConnection(conStr1) 'ADODB.Connection
cmd = New SqlClient.SqlCommand(Nothing, con1)
If con1.State = ConnectionState.Closed Then con1.Open()
'rs1 = New ADODB.Recordset
'rs1.ActiveConnection = con1
'rs2 = New ADODB.Recordset
'rs2.ActiveConnection = con1
sql1 = "SELECT TOP 20 * FROM TT_Member WHERE Reminder = '0' AND Status = '1'"
cmd.CommandText = sql1
rd1 = cmd.ExecuteReader()
If rd1.HasRows Then
While rd1.Read
' rd1("ColName")
'
End While
End If
'**********************************************
If Not rd1.IsClosed Then rd1.Close()
Catch ex As Exception
'Error handling code here
Finally
If con1.State = ConnectionState.Open Then con1.Close()
con1.Dispose()
cmd.Dispose()
End Try
'If rs1.EOF And rs1.BOF Then
' Label1.Caption = "Finished - " & Now
'Else
' Do Until rs1.EOF
' Label1.Caption = "Spamming - " & Now
' OriginatingAddress = rs1.Fields("Mobile_No").Value
' If Mid(OriginatingAddress, 1, 3) = "601" Then
' Telco = 2
' ElseIf Mid(OriginatingAddress, 1, 3) = "019" Then
' Telco = 9
' End If
' Reminder_Msg = "Free Msg.Subscription Reminder.Fee:Auto renewal datedate, unless cancelled. To cancel, send STOP TT to 32132."
' MT_Msg = Reminder_Msg
' MT_Msg = Replace(MT_Msg, "datedate", Renewal_Date3)
' Call Sent_MT()
' Call Close_Record()
' rs1.MoveNext()
' Loop
'End If
End Sub
!alien!
|
|
|
|
|
Hello,can you give me a more detail solution? I'm really new to this VB.NET. How should I change,It still doesn't really works for me here. Can you please help me more?
Thank you so much~!
|
|
|
|
|
Private Sub Spam_Now()
Dim conStr1 As String
Dim con1 As SqlConnection
Dim reader As IDataReader
Dim sql1 As String
Dim command As SQlCommand
conStr1 = "provider=SQLOLEDB;data source=" & DellServerIP _
& ";database=pre_sms;uid=sa;pwd=" & DellServerPass
con1 = New SqlConnection(conStr1)
sql1 = "SELECT TOP 20 Mobile_No FROM TT_Member WHERE Reminder = '0' AND Status = '1'"
command = New SqlCommand(sql1, con1)
reader = command.ExecuteReader()
If not reader.Read() Then
Label1.Caption = "Finished - " & Now
Else
Do
Label1.Caption = "Spamming - " & Now
OriginatingAddress = reader.GetString("Mobile_No")
If OriginatingAddress.Substring(0, 3) = "601" Then
Telco = 2
ElseIf OriginatingAddress.Substring(0, 3) = "019" Then
Telco = 9
End If
Reminder_Msg = "Free Msg.Subscription Reminder.Fee:Auto renewal datedate, unless cancelled. To cancel, send STOP TT to 32132."
MT_Msg = Reminder_Msg
MT_Msg = Replace(MT_Msg, "datedate", Renewal_Date3)
Call Sent_MT()
Call Close_Record()
Loop Until not reader.Read()
End If
End Sub
One difference between a Recordset and a DataReader that can be good to know, is that you use the Read() method to both advance the row pointer and check for more rows, just like you use MoveNext() and EOF with a recordset. Also you have to call Read() before you can read any data from the DataReader, it doesn't automatically read the first record as a Recordset does.
---
b { font-weight: normal; }
|
|
|
|
|
Hello Mr. Guffa,Ur solution is really a new thing for me. But I found that was not working properly for me when I test it. The following lines is not running during I execute it. But it doesn't promp me any error in my Visual Studio 2003. This is the line that it doesn't run as expected:
OriginatingAddress = reader.GetString("Mobile_No")
I put a messagebox to check the value inside Originating Address,but it show nothing inside. Do you have any idea with this? My database for this is fill will a number.
Hope to get some solution from you all here.
Thanks~!
|
|
|
|
|
I have an access db table with a column of type ole object. When I right click in the column a context menu displays that allows me to add an image.
Once the image is added the column displays 'bitmap image'. If I add an image via ado.net the column displays 'long binary format'.
How can I convert these existing 'bitmap image' items to 'long binary format'? The bitmap ones are causing problems with object and control references. For example I can declere a bitmap object and load from a stream as it throws an exception.
Thanks
Glenn
|
|
|
|
|
|
Let me rephrase my question.
I'm using vb.net. I'm doing the following and this exeption was thrown. ex.message = invalid parameter used.
The msaccess db table column is set to type ole object. Try this, go to the db table and right click on the column and click inser object and insert a bmp. The column value will now display bitmap image. Try your code against that and you'll experience the problem. Why?
Dim cmd As OleDbCommand = New OleDbCommand(sql, cnn)
cnn.Open()
Dim b() As Byte
b = cmd.ExecuteScalar()
cnn.Close()
If (b.Length > 0) Then
Dim stream As New MemoryStream(b, True)
stream.Write(b, 0, b.Length)
'Display image
Dim PictureBox1 As New System.Windows.Forms.PictureBox
'exception is thrown at this statement
PictureBox1.Image = New Bitmap(stream)
'ex.message = invalid parameter used.
stream.Close()
End If
'conn.Close()
Why?
Thanks,
Glenn
|
|
|
|
|
The links Roath gave you have nothing to do with converting an OLEObject bitmap image to a usable Bitmap in VB.NET.
I think you're looking for something more like this:
Imports System.Io
.
.
.
Dim ms As New MemoryStream
Dim oleData() As Byte = dataRow.Item("ColumnName")
ms.Write(oleData, 78, oleData.Length - 78)
Dim bm As New Bitmap(ms)
PictureBox1.Image = bm
You got the right idea, just the wrong offset when you wrote your image data to the MemoryStream.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
You hit the nail right on the head. The offset was all I needed. I searched high and low for this info and could not find anything. How did you know this? Alow is their any way to test if the column value is a ole data bitmap or an image in long binary format. I may incounter both types when running the conversion.
Thanks
Glenn Romaniuk
.net virtuoso
|
|
|
|
|
I've seen the code before, but I can't remember where. I thought the offset was kind of odd myself.
As for determining which type the data is, you might want to take a look at the first bytes of each type to find a pattern between the two. I think the OLE objects will have a consistent header, while the pictures might have a different header.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave Kreskowiak wrote: The links Roath gave you have nothing to do with converting an OLEObject bitmap image to a usable Bitmap in VB.NET.
My name is Kanel, don't call me Roath. Roath is my family name.
|
|
|
|
|
Well as the title indicates I have a search that populates a datagrid with results. I am wondering if i click the first cell in the datagrid if I can then copy that informaton into another form. Since there can be duplicate people by last name, I need to be sure that the correct one is A clicked then the form is populated withthe users information. Is this possible with datagrids in VB6. i have seen tons of references to asp.net but notta with VB6
After playing a bit
<br />
Private Sub DataGrid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)<br />
MsgBox "clicked"<br />
End Sub<br />
works when a record is clicked So the next step would be to copy the data or at least 1 field say a unique ID i could i guess perform another search and populate the form with that search. But would rather copy the data from the datagrid
-- modified at 17:15 Tuesday 7th February, 2006
|
|
|
|
|
Well this doesn't quite work
<br />
With Form1<br />
For I = 1 To DataGrid1.Col + 1<br />
FrmSearch.DataGrid1.Col = I<br />
Form1.Text1(I).Text = FrmSearch.DataGrid1.Text<br />
Next I<br />
End With<br />
Load Form1<br />
Form1.Visible = True
Any ideas? 
This results in 1 field from the datagrid placed into 1 textbox on the other form
-- modified at 17:57 Tuesday 7th February, 2006
|
|
|
|
|
Well I have revamped my process
here is the steps involved
1.search for a customer by last name Done
2.click Search Done
3. see results in datagrid Done
4. select customerID cell which could be in any row
for the correct customer with mouse click Not Done
> Capture mouse click?
> Capture cell value ?
5. return the clicked cell's value to a textbox. Not Done
|
|
|
|
|
During looping throug the directories the program tries to access the system directory
"C:\System Volume Information". The access is denied by the system and I receive an error message.
See subject.
So far I know there are two pssibilities
1. To avoid the error with ATTRIBUTE
2. To catch the error with TRY.
If I use ATTRIBUTE I don't know how the code looks and where I have to implemente it.
If I use TRY the program does not continue the loop.
Mein Code :
Private Function FindFiles(ByRef FoundFiles As ArrayList, ByVal dirname As String, ByVal filespec As String, ByVal recurseDirs As Boolean) As ArrayList
Dim strFilter As String = filespec
Dim m_arExt() As String = strFilter.Split(";")
Dim dic As New IO.DirectoryInfo(dirname)
For Each Filter As String In m_arExt
For Each fname As String In System.IO.Directory.GetFiles(dirname, Filter)
FoundFiles.Add(fname)
Next fname
' recurse on subdirectory if so requested
If recurseDirs Then
For Each dname As String In System.IO.Directory.GetDirectories(dirname)
FindFiles(FoundFiles, dname, filespec, recurseDirs)
Next dname
End If
Next Filter
Return FoundFiles
End Function
Can anybody assist me to sove the problem ?
Best regards Theo
|
|
|
|
|
Avoid exception handling in the normal flow of the code. Creating exception objects is rather costly.
Look at the attributes. Here is the documentation of the codes:
MSDN: FileAttributes Enumeration[^]
---
b { font-weight: normal; }
|
|
|
|
|
The articlke about ATTRIBUTE I found already. My problem is, I cant find a solution with this description. (Newbie).
Theo
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
