|
|
Hi All,
I am using vb.net 3.5 and Sql Server 2005
There is no problem with connection if Server Name and Database Name Exist.
But the Error occurs when Try to connect the server which is not available. then my application goes hang. It is continue Searching.
I'm using :
Public Sub ConnectConfig(ByVal DSource As String, Optional ByVal Uid As String = "", _
Optional ByVal Pwd As String = "", _
Optional ByVal InCat As String = "", _
Optional ByVal AuthType As String = "")
Dim ConnString As String = ""
If FrmConfigSetup.cbAuth.SelectedIndex = 0 Then
ConnString = "Data Source=" & DSource & ";Initial Catalog=" & InCat & ";Integrated Security=True;Timeout=60;" & ""
Else
ConnString = "Data Source=" & DSource & ";Initial Catalog=" & InCat & ";Uid=" & Uid & ";Pwd=" & Pwd & ";Timeout=60;"
End If
SQLConn = New SqlClient.SqlConnection(ConnString)
If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
SQLConn.Open()
Else
SQLConn.Open()
End If
End Sub
when it'sTrying to open the connection then goes hang.
So, What can I do , please help me.
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
The timeout in the connection string is used for responses to command queries. It does not apply to the connection itself. Your connection attempt can take a few minutes to timeout and throw an exception.
pdnet wrote: If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
SQLConn.Open()
Else
SQLConn.Open()
End If
What the heck is this?? Since you're creating a new connection object all the time, you just need to Open it. A new connection object's state is always going to be Closed.
|
|
|
|
|
Why don't you ping that server to make sure it is exist before connecting to it?
Like car accidents, most hardware problems are due to driver error.
Samir R. Ibrahim
|
|
|
|
|
Hello, I am in the process of trying to fix this bug that has cropped up in some code I am maintaining. The issue is this: The application runs through some data from an excel spreadsheet and when it is finished, it formats and saves the spreadsheet before closing it out. This works, but heres the thing, when a spreadsheet is run through the app the first time, the columns are formatted correctly as Currency, but when the same spreadsheet is run again, the columns formatted as Currency are now formatted as Accounting for some reason even though the lines of code doing the formatting clearly state Currency like this:
objExcelSheet.Columns(eSheetLocations.AMT).Style = "Currency"
This is proving to be one heck of a noodle scratcher, as I have no clue why it is doing this. I had originally considered a workaround by trying to get the current style by having a variable set to it, but the variable returns Nothing when I step through. The reason I need this to stay Currency is because zero-dollar amounts need to be seen as "$0.00" and not "-" as this confuses the end users. Any help and advice is welcome, thanks in advance.
|
|
|
|
|
That is strange, but then again you should always expect surprises from MickeySoft.
A simple work around would be to somehow flag the spreadsheet when you run it through the app, and checking for that flag first when you get to the style changing bit.
Not flagged = set style, and flag
Flagged = skip style setting, and leave the flag be.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thanks in advance
modified 28-Nov-12 8:06am.
|
|
|
|
|
Connect to both the files using OleDB connection and then compare using SQL queries.
जय हिंद
|
|
|
|
|
Hi ,
In vb.net, Merge function, merges the rows.
Is there any way to merge columns?
Thanks and Regards
Pankaj Garg
|
|
|
|
|
No, there is no built-in method to do so. You'll have to iterate through the rows and do it yourself.
|
|
|
|
|
Hi all,
it's been a lot of learning already, but here's a point I can't figure out even with the help all the sources I reviewed by now. Maybe one of you can help me find my error?
In my little application (written in VB 2008 Express) I use Backgroundworkers. One of them is supposed to read / filter Outlook-Mails and put extracted customer data into datatable rows. The other one gets the rows and checks a DB for previous inquiries of that customer. Between both of them I put a buffer in order to avoid race conditions. I hope I expressed the basic concept well enough to be understandable - and although it's working I'd appreciate comments and ideas to make the code easier.
What doesn't work is: The RunWorkerCompleted-event in the class "Consumer" is not fired, which I tried to work around in the ProgressChanged-event, but that can't be it. Here's the code:
First I give you the buffer class in which I guess the error must be - suspecting some SyncLock or Monitor activities (which I took from a sample without fully understanding them):
Public Class Puffer
Private mBuffer As New List(Of DataRow) With {.capacity = 1}
Property Puffer() As DataRow
Get
SyncLock (Me)
If mBuffer.Count = 0 Then
MsgBox("Alle Puffer sind leer. " & Thread.CurrentThread.Name & " wartet.")
Monitor.Wait(Me)
End If
Dim readValue As DataRow = mBuffer(0)
MsgBox(String.Format("{0} liest Element '{1}'.", Thread.CurrentThread.Name, readValue.Item("Name")))
OutCounter += 1
mBuffer.Remove(mBuffer(0))
Monitor.Pulse(Me)
Return readValue
End SyncLock
End Get
Set(ByVal WriteValue As DataRow)
SyncLock (Me)
If mBuffer.Count = mBuffer.Capacity Then
MsgBox("Alle Puffer sind voll. " & Thread.CurrentThread.Name & " wartet.")
Monitor.Wait(Me)
End If
MsgBox(String.Format("{0} schreibt Element '{1}'.", Thread.CurrentThread.Name, WriteValue.Item("Name")))
mBuffer.Add(WriteValue)
InCounter += 1
Monitor.Pulse(Me)
End SyncLock
End Set
End Property ' Buffer
End Class
Producer class (shortened):
Public Class Producer
Dim WithEvents ProducerThread As New BackgroundWorker
Private Puffer As Puffer
Public Sub New(ByVal sharedObject As Puffer)
With ProducerThread
.WorkerReportsProgress = True
.WorkerSupportsCancellation = True
.RunWorkerAsync()
End With
Puffer = sharedObject
End Sub
Public Sub Produce(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles ProducerThread.DoWork
Thread.CurrentThread.Name = "Produzent"
'Return a reference to the MAPI layer
Dim oApp As New Outlook.Application
Dim oNameSpace As Outlook.NameSpace = oApp.GetNamespace("MAPI")
....
Try
oNameSpace.Logon(Nothing, Nothing, False, False) ' TODO: Ggf ändern...
....
' Get Messages collection of Inbox and count them
Dim oItems As Outlook.Items = oInFolder.Items
Dim intTotal As Integer = oItems.Count
' Loop each unread message.
For i As Integer = 1 To oItems.Count
If ProducerThread.CancellationPending Then Exit Sub
If CurrentMedia <> "" Then
Dim CurrentRow As DataRow = MailsTable.NewRow()
CurrentRow("Absender") = Inquiry.ClientMail
....
' erst einmal in den Puffer
Puffer.Puffer = CurrentRow <----- write into the buffer
CurrentRow = Nothing
End If
oMsg = Nothing
Next
oItems = Nothing
Catch ex As System.Exception
MsgBox("Fehler in der Routine 'Produce'", MsgBoxStyle.Critical, String.Format("Klasse '{0}'", Me.GetType.Name))
End Try
End Sub
Private Sub ProducerThread_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles ProducerThread.RunWorkerCompleted
'Thread.CurrentThread.Name = "Produzent"
frmProcessOutlAtt.btnRead.Text = "Lesen"
End Sub
End Class
Consumer Class (shortened):
Public Class Consumer
Dim WithEvents ConsumerThread As New BackgroundWorker
Private Puffer As Puffer
Private rowCounterValid As Integer
Private rowCounterInvalid As Integer
Public Sub New(ByVal sharedObject As Puffer)
With ConsumerThread
.WorkerReportsProgress = True
.WorkerSupportsCancellation = True
.RunWorkerAsync()
End With
Puffer = sharedObject
End Sub
Public Sub Consume(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles ConsumerThread.DoWork
MailsTable.Clear()
Dim DA As m2prolapDataSetTableAdapters.AdressenTableAdapter = New m2prolapDataSetTableAdapters.AdressenTableAdapter
Try
If ConsumerThread.CancellationPending then Exit Sub
Dim myrow As DataRow = Puffer.Puffer <----- read from the buffer
While myrow IsNot Nothing
If DA.GetDataByNamesStreetsMail(myrow("Name"), myrow("Strasse"), myrow("Vorname"), myrow("Absender")).Count > 0 Then
rowCounterValid += 1
myrow("Antworten") = False
ConsumerThread.ReportProgress(1, myrow)
Else
rowCounterInvalid += 1
myrow("Antworten") = True
ConsumerThread.ReportProgress(0, myrow)
End If
myrow = Puffer.Puffer
End While
Catch ex As System.Exception
MsgBox("Fehler in der Routine 'Consume'", MsgBoxStyle.Critical, String.Format("Klasse '{0}'", Me.GetType.Name))
End Try
End Sub
Private Sub ConsumerThread_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles ConsumerThread.ProgressChanged
Dim myRow As DataRow = e.UserState
Dim Marker As Boolean = CBool(e.ProgressPercentage)
MailsTable.Rows.Add(myRow)
With frmProcessOutlAtt
.DataGrid.DataSource = MailsTable
.DataGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
.DataGrid.Refresh()
End With
'MessageBox.Show(String.Format("Datensatz '{0}' wurde ausgewertet.", myRow("Name")))
'OutCounter = MailsTable.Rows.Count
End Sub
'THIS ONE IS NOT FIRED AT ALL:
Private Sub ConsumerThread_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles ConsumerThread.RunWorkerCompleted
Thread.CurrentThread.Name = "Konsument"
MsgBox(String.Format("{0} Datensätze von {1} verarbeitet." & vbCrLf & "{1} ist beendet.", _
OutCounter, Thread.CurrentThread.Name), MsgBoxStyle.Information)
End Sub
End Class
I really hope that one of you can find the time and examine what happens behind the borders of my knowledge here... As written before, I think that something about "SyncLock" or "Monitor" prevents the Consumers "RunWorkerCompleted" from getting fired. But as we all know there are so many trees sometimes.... maybe you just find a typing error
Thank you in advance
Mick
|
|
|
|
|
You can remove the Monitor lines - you don't need them.
From what I've read in your code, it looks like you can also change the SyncLocks. You may also want to cahnge your design a bit. You can have one thread adding items to a Queue collection, wrapped by SyncLocks on the Queue synch object. Then you can have the other thread pulling items out of the Queue to process them, again wrapping the calls to Queue in SyncLocks.
|
|
|
|
|
Thank you Dave for the brief review. I'll try your suggestions as soon as possible. But you didn't mention the 'RunWorkerCompleted' problem - could you see anything in my code that might cause that problem?
Thanks again
Mick
|
|
|
|
|
Get rid of the Monitors...
|
|
|
|
|
Following your first suggestion - to take out the Monitor lines - brought up some news.
First: It solved my previous problem, i.e. the terminal event of the counsumer class fires now.
Second: Unfortunately the whole code doesn't work anymore It seems that the "consumer" is running quicker than the "producer" and tries to retrieve a data row from the buffer before it's filled, resulting in zero rows processed (in my sample it's supposed to be 4 of them). But at least the "RunWorkerCompleted" event is processed . Still I guess I would have needed the Monitor lines, no?
I'm going to read a bit about the Queue you mentioned.
|
|
|
|
|
You have a situation where you're running two threads, expecting the Completed to be fired by the Consumer thread that processes the rows. This thread shouldn't stop, so long as the applicaiton is running, IMHO. It can't stop because it cannot know, in your current design, if there are any more records coming from the Producer. It's the Producer that should be raising the Completed event. Only it knows if there is anything left to process on it's end.
|
|
|
|
|
Thanks again, Dave! This I guess is an important step for my understanding.
So I understand that in my code the 'consumer' thread keeps waiting until it's purposly ended, e.g. with a call from another thread or function. And in case there are further rows fed into the buffer, it will keep processing them, right?
This would still be ok as long as a waiting thread doesn't eat up too much memory and/or performance. But first I'll switch and try the queue concept you recommended.
Have a nice day,
Mick
|
|
|
|
|
All it has to do is wake up every so often to see if there are records in the queue. If so, process them. If not, go back to sleep for a little while. You may also want to check not only the queue, but a flag that tells the thread it should terminate nicely.
|
|
|
|
|
Daves hint about queues lead me to a real nice solution for the producer/consumer issue I've been fighting with. A first test with some sample code works perfectly between different threads.
For those interested, here's the sample (translated from C# to VB.NET):
Imports System.Threading
Public Class Test
Private Shared queue As ProducerConsumer
Shared Sub Main()
queue = New ProducerConsumer()
CType(New Thread(New ThreadStart(AddressOf ConsumerJob)), Thread).Start()
Dim rng As New Random(0)
For i As Integer = 0 To 9
Console.WriteLine("Producing {0} in thread {1}", i, Thread.CurrentThread.ManagedThreadId.ToString)
queue.Produce(i)
Thread.Sleep(rng.Next(1000))
Next i
End Sub
Private Shared Sub ConsumerJob()
' Make sure we get a different random seed from the first thread
Dim rng As New Random(1)
' We happen to know we've only got 10 items to receive
For i As Integer = 0 To 9
Dim o As Object = queue.Consume()
Console.WriteLine(Constants.vbTab + Constants.vbTab + Constants.vbTab + Constants.vbTab & "Consuming {0} in thread {1}", o, Thread.CurrentThread.ManagedThreadId.ToString)
Thread.Sleep(rng.Next(1000))
Next i
End Sub
End Class
Public Class ProducerConsumer
Private ReadOnly listLock As Object = New Object()
Private queue As New Queue()
Public Sub Produce(ByVal o As Object)
SyncLock listLock
queue.Enqueue(o)
' We always need to pulse, even if the queue wasn't empty before. Otherwise, if we add several items
' in quick succession, we may only pulse once, waking a single thread up, even if there are multiple
' threads() waiting for items.
Monitor.Pulse(listLock)
End SyncLock
End Sub
Public Function Consume() As Object
SyncLock listLock
' If the queue is empty, wait for an item to be added. Note that this is a while loop, as we may
' be(pulsed)but not wake up before another thread has come in and consumed the newly added object.
' In that case, we'll have to wait for another pulse.
Do While queue.Count = 0
' This releases listLock, only reacquiring it after being woken up by a call to Pulse
Monitor.Wait(listLock)
Loop
Return queue.Dequeue()
End SyncLock
End Function
End Class An extra warning / quote from the author: "Note that using these methods can easily lead to deadlock - if thread A holds locks X and Y, and waits on Y, but thread B needs to acquire lock X before acquiring and then pulsing Y, thread B won't be able to do anything. Only the lock which is waited on is released, not all the locks the waiting thread owns. Usually you should ensure that prior to waiting, a thread only owns the lock it's going to wait on. Sometimes this isn't possible, but in those cases you should think extra carefully about how everything is going to work."
But for my purposes I seem to have a proper basis
Kind regards and thank you, Dave!
Mick
|
|
|
|
|
Hi all,
modified 28-Nov-12 8:07am.
|
|
|
|
|
simple
1. replace "c:\DB\Output1\outputDB.txt" to "c:\DB\Output1\outputDB.xls"
2. replace Space(x) function with chrw(9).
in the your above code.
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Thanks for the reply.
In the above case I read the database and wrote as excel.
I would like to read a text file and write it into excel file.
|
|
|
|
|
Read the data from the text file using Streamreader.,
write the data in manner of you want , with tab delimiter.
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Just to make sure I understand you correctly...
Do you want to pick up each line in the TextFile, and have it's contents put into a series of cells in XL where the spaces in the line are disposed of?
ie Number of children in one cell, number of balloons in next etc?
So that the cell contents are just the value as VALUE, not as TEXT?
Cos if so there are two methods, one is to just load the text into one cell and then split it via VBA in XL, or to pick up the text form the text file, scan it, apply each set of non space characters to a variable, then fire the variable into a cell in xl, continue until end of line, next line etc.
Which is the one you would like?
------------------------------------
"Possessions make you poor, wealth is measurable only in experience."
Sun Tzu 621BC
|
|
|
|
|
I want each line in the text file has to be entered in the excel based on the fields.
If the excel file field is Parent, the first field from the text file has to be entered in that cell and so on.
I want the text to be entered and not the count/number.
|
|
|
|
|