Cannot get data from data adapter

[Steven Tennett]

I wrote a program with vs2008 which was scheduled to run in the background and scan a table held under SQL server 2017 for new invoices every few minutes. It read and updated flags on the table records once invoices had been extracted. This program ran well until Jan 2017 where it suddenly started to report the following error message:

Cannot get data from data adapter. Server={name of server running Pastel Evolution}; Database = {Database name};dsn = {Data source name defined by ODBC data source administrator}; Trusted_connection=yes;User ID=sa;

This failure is causing a huge problem on site. The program has intermittently worked and not worked since January this year. I think the failure is at the point where the program is trying to update the flag on the database table mentioned, and is somehow being denied access to update.

Can anyone suggest what might be the cause of this error and how to rectify this problem?
Thank you so much for your time.

Steve Tennett

[Shaggy Hiker]

The error message has a whole bunch of stuff about the connection, so why do you think it is on the update? Is there a previous connection that you can confirm HAS worked at the time when you get this error? For instance, perhaps you have some data displayed that could only have come from the same database some moments before the exception.

[Steven Tennett]

Thanks very much for your reply Shaggy Hiker. This same connection has worked before 2017. The program has not been changed since at least last year or longer - then suddenly this error started popping up in January this year. Since it runs as a background job on the despatch user it doesn't display data to the despatcher's screen. I wondered also if it had anything to do either with the access rights of the despatch user under SQL, or the SQL access rights on the table or database that it accesses? You are right, the error may not occur on the update, so this is just supposition! Its just that updating records is generally regarded more stringently by MS than reading records, and more likely to result in errors me thinks! None of the stuff concerning the connection has been changed to my knowledge.

[Shaggy Hiker]

I'm suspecting a network issue, or SQL issue as you noted. The reason I was asking those questions was to see whether that could be ruled out. If you knew that there were reads that had worked when this exception happens, then a network issue is nearly impossible, as a network problem would impact ALL access to the DB. If you know that reads are happening, but the update is failing, then it could be a permissions issue, except that those tend to be all or nothing. The very intermittent nature of this problem doesn't fit with any SQL permissions issue that I have ever seen. You either have them or you don't.

However, if you can't be certain that ANY DB access is working when this problem occurs, then a network issue is back on the table. It's also not really my thing.

[techgnome]

You've got some mixed messages in that connection string too... a DSN ... AND a Trusted Connection AND user info (SA??? Really? You don't have a DBA on staff do you? No self respecting DBA would ever let that go through.) So should it be connecting as the current user using Trusted Connection? Or connecting as SA? (ick!) Or connecting using the DSN info?

-tg

[Steven Tennett]

Thanks Shaggy Hiker. 'Reads' are definitely done as the invoice numbers of active invoices are extracted and sent to a comma delimited file for further processing by another programmer, and these are clearly evident when that file is copied and edited. These invoice numbers are however being repeated, sometimes 4 or 5 times or more per invoice. This would only happen if my program was unable to update the flag on my table to say that the invoice has already been extracted and printed, and should not be extracted again. (The complaint by the company is that invoices are continually repeated, not that they are missing). Hence I am suspecting that the updating of the flag on the extracted invoice record is not happening under certain conditions, and consequently the same invoice number is being extracted again and again with each new comma delimited file extraction. Since reads are being done it looks unlikely to be an network issue as you say. I think the other programmer renames his extraction file to process the invoices within his system, and my extraction (every few minutes) then creates a new file with new invoices. If the file is already present it adds invoices to the file already there.

[Steven Tennett]

I've had so much difficulty connecting to a Microsoft database over the past years. By far the hardest part of the whole programming job! Even now I'm not sure how to do it as there doesn't seem to be any STANDARD method! (very offensive to my mathematically exact brain!) I found it impossible to get it to work without these elements I have included, although they are probably conflicting as you say, I don't know. I've had no help and found nothing on the internet that once and for all CLARIFIES the situation for me. This site has no data administrator at all. I log in under sa therefore. The Trusted_Connection seemed necessary to log in. Maybe you can tell me what it means?

[Steven Tennett]

Thanks techgnome. The essential requirement is just to connect and get on with the job, and this has proved hugely difficult over the years. Why could MS not have come up with one single simple procedure to connect I wonder? Security issues - right!

[ChrisE]

Hello Steven,

I suspect a TCP/IP fault.
If the Connection works for a certain time you program will work, if it get's cut of
and reconnect's it will start the process again
that's why you perhaps have repeated Invoices

here a link you might want ro look at.
https://stackoverflow.com/questions/...-serverhttp://
regards
Chris

[Steven Tennett]

Thank you ChrisE. It is indeed a weird problem! Last time I went to the site to look at the problem the problem actually corrected itself before our eyes, and kept working OK for another month or so. I made no program change. Could the network cut off just before my program does its update, having already sent the invoices to the extraction file I wonder? This would indeed cause it to re-extract the same invoices, assuming in the meantime the network reconnects itself, and the timer of my program activates the program 2 minutes later to extract a new batch of invoices.

[ChrisE]

Hello Steve

this whole thing might be a Network issue and not your program


edit:
also check if there was an Update for SQL-Server In Jan.

Hardware: is a new Network Printer been Installed?
does the Printer go in Sleepmode when the problem's
start

regards
Chris

[Shaggy Hiker]

Hardware: is a new Network Printer been Installed?
does the Printer go in Sleepmode when the problem's
start

regards
Chris

THAT sounds like somebody talking from bitter experience. I've never encountered anything like that, but I can only imagine how hard it must have been to track it down.

[ChrisE]

THAT sounds like somebody talking from bitter experience. I've never encountered anything like that, but I can only imagine how hard it must have been to track it down.

yes it did, the Company was driving me Crazy, it took me a month to track that down.
what was it .. a Brand NEW rather cheap Network printer made in ..... !
oh.. the Company produces car's and make a Fortune with it
could only shake my Head

regards
Chris

[Steven Tennett]

Hello Chris

An idea has just occurred to me that may or may not have a bearing on my problem. I discovered that the other programmer has run the version of my program used in the scheduler from the exe version stored on the desktop of the despatch computer. Could the printer flag update problem I am experiencing be in any way caused by a conflict between multiple copies of the same program being run simultaneously? - one that constantly runs in the background under the scheduler (launched when the user logs into his computer), and another version launched by this programmer? Not a good combination! Do you think it in any way explains the problem though?

regards
Steve

[ChrisE]

Hello Chris
I discovered that the other programmer has run the version of my program used in the scheduler from the exe version stored on the desktop of the despatch computer. Could the printer flag update problem I am experiencing be in any way caused by a conflict between multiple copies of the same program being run simultaneously? - one that constantly runs in the background under the scheduler (launched when the user logs into his computer), and another version launched by this programmer? Not a good combination! Do you think it in any way explains the problem though?

regards
Steve

this might well be it....multiple copies of the same program being run simultaneously?
if you think about it, and your Program updates every few minutes
both will execute the Printout

one that constantly runs in the background under the scheduler (launched when the user logs into his computer)

can't you see here from which User/Computer the problems start ?


regards
Chris

[Steven Tennett]

There is only the one despatch computer concerned. The programmer assures me he has NOT run my program, though I have seen him do it at least once with my own eyes! The other thing is I want to increase the length between timing cycles in case the coding is unable to get through one cycle before another cycle is triggered. Maybe I can double or treble the time interval?

[Steven Tennett]

Hello Chris

How do I get to see from which User/Computer the problem starts at? From the scheduler? or Windows? I'm not familiar in this area.

Regards,
Steve

[ChrisE]

hello Steve,

well sombody creates(which User to which Invoice) the Invoice, who executes the Printout ?

can you run your program without the Timer event, just to make sure
that the Update routine for the table works fine?

you should perhaps have a good Talk with the other programmer also, I know
it's not a nice situation to do so, but if you have seen him use the program
you will have to confront him.. even with the CEO.

I am 54 years old and seen a few things, also I have had my share of problems
with other programmers on the Job.

you will have to break this down stage by stage.

regards
Chris

[Steven Tennett]

hello Steve,

well sombody creates(which User to which Invoice) the Invoice, who executes the Printout ?

can you run your program without the Timer event, just to make sure
that the Update routine for the table works fine?

you should perhaps have a good Talk with the other programmer also, I know
it's not a nice situation to do so, but if you have seen him use the program
you will have to confront him.. even with the CEO.

I am 54 years old and seen a few things, also I have had my share of problems
with other programmers on the Job.

you will have to break this down stage by stage.

regards
Chris

Hello Chris, thank you for your reply.

What I do is, based on a timer event (every couple of minutes), I send the new invoices to this other programmer's text file (either to new file or appended), and he picks them up from there for his system. He wanted it this way as he is not familiar with how to do the extractions himself from Pastel and SQL, so I did that bit.

To ensure uniqueness of invoices for him, I have quoted the company an addition to the program, where I store a record of each invoice number, extracted and sent to the other programmer, on a separate table on invoice number key. Before sending future invoices to his text file, I will check against this table for uniqueness of the extracted invoice, and thereby avoid sending him duplicates.

I have woken several times in the night thinking of this problem. Is it a case of my program "eating its own tail" so to speak? I have a chunk of code to pull out and mark invoices as printed every few minutes, which runs constantly in the background and is executed by a timer. What if it is unable to complete this chunk of code before the timer re-invokes the chunk of code?
And what if it reached a point in its cycle when the invoice had been sent to the other programmer's text fiie, but the table flag had not been updated to "printed" when the the chunk of code was re-invoked?
Maybe I need to dynamically stop the timer when entering this chunk of code, and restart it when leaving it? - so waiting for two minutes only begins when leaving the chunk of code, not when entering it?
Is it possible to do this dynamically under VB? and if so, what instructions do I have to perform to stop and start the timer again? (it will take me ages to find them without help!)

The alternative is to dynamically adjust the timer to a higher value when finding a duplicate invoice (e.g. add 10%), though how to do that dynamically in the middle of a code chunk in a timer cycle, I have no idea!

I do have a version of the program that does the same thing, but runs without the timer event. This can be used to test/investigate. Zimbabwe has no money and currently I don't even have money to get to the site to investigate! I have asked for a prepayment from the client! See what they say.


Chris, I greatly appreciate your experience. By the way, you are still a young man at 54! I am 66!

Regards
Steve

[ChrisE]

Hello Steve,

the first thing that comes in my mind is...
a) both of you have to work with one Table in the Database
that means create a new Table where you can transfer the Data immediately instead of a Textfile.

this would have to be joined to the "Invoice Table" (dont know how you call it)

b) this would make the Timer and Textfile obsolet !

c) from there you could write a routine ...
- transferd for printout (set Flag to True and with a Field Timestamp )
- Invoice printed (set Flag to True)
when the Invoice is printed and set as True nobody can execute another Print out

hope this might help to rethink the situation with Timer and Textfile

regards
Chris

[wes4dbt]

Is it possible to do this dynamically under VB? and if so, what instructions do I have to perform to stop and start the timer again? (it will take me ages to find them without help!)

Maybe I'm not understanding you because this is a very simple thing.

Me.timer1.Stop
'
'Export the invoices
'
Me.Timer1.Start

[wes4dbt]

Actually I think I'd want more control

Me.Timer1.Stop
If ExportInvoices Then
    Me.Timer1.Start
Else
    'code for error recovery
End If

    Private Function ExportInvoices() As Boolean
        Dim goodExport As Boolean = True
        Try
            'export invoice
        Catch ex As Exception
            'display error message
            goodExport = False
        End Try
        Return goodExport
    End Function

[Steven Tennett]

Hello Chris

Thanks, this is a very good suggestion for handing the timer. I so often search for properties, methods, events etc under MS VB code and can't find simple things that I feel should be available, and that seem logical to me. I am glad that in this case there is easy control over the timer properties/methods. The client is converting to another accounting system and is loathe to spend extra on this system's fixes/additions. Also, your solution would mean the other programmer would have to redesign his side and get the money while I got nothing. I have already done what you suggest, in my original solution to the problem, and extracted new invoices from the live accounts invoice table through an SQL trigger mechanism into my own table, containing the invoice info, date stamp, print flags etc. All the other programmer had to do was read directly from my table in the first place and update my print flags for those invoices he had extracted for printing. However he insisted on using the text file idea, which is really stupid in my view. He uses a really antiquated form of VB6 and doesn't seem to have the SQL skills to get the data directly from my SQL table. He is also too far removed from me physically, and working with him over this would be expensive and impractical as neither of us is on site that often. So I want to leave things as they are and place the work in my own hands, rather than in his, as I desperately need the income. As the other programmer is happy with this arrangement I put a quote forward to the client for my changes. I have agreed with the other programmer to increase the timer's interval property to 4 minutes which should help stop my program from "eating its own tail". However I'm still going to include the stop/start timer features you mentioned in my changes.
Thank you for your suggestions.

Regards
Steve

[ChrisE]

Hello Chris

However I'm still going to include the stop/start timer features you mentioned in my changes.
Thank you for your suggestions.

Regards
Steve

thank wes4dbt for the Timer Idea,

hope all goes well for you

regards
chris

[Steven Tennett]

Thanks for all your time and valuable suggestions Chris. I will let you know how it goes.

Best regards
Steve