VFP - SQL server invoice

[kuma.lk]

Hi all,

I am new member & this is my first post. So first of all, I like to say “Hello” to all of forum members.

Than , my question. I am developing small vehicle parking system with VFP & sql server 2008.
I want to run my vfp code and generate the invoice automatically as soon as a record inset into one of table in sql server.

Can some one guide me to do this

thank you

 

[Olaf Doschke]

Hello Kuma,

Let me welcome you to this forum.

If your software inserts data into the SQL Server table, then you know and could create the invoice just as next few lines of code. You wouldn't be here asking, though. So I assume there is some third party software or even hardware involved adding data into the SQL Server. Is that so? The first option you have to react to that event is an INSERT TRIGGER within SQL Server, but there is no easy way out from a trigger to any external software like your VFP code. In any Client/Server architecture, the server always is reacting, not acting, especially not triggering a client-side action, that would turn around the roles.

Before I dive deeper into options, what's the overall situation here? Am I guessing right and the database maybe even is part of a system of hard- and software of the parking lot and you're trying to extend it with invoices beyond what the system itself offers as receipts? For long duration parking, perhaps? In case of third party software involved, I would also assume you have fewer options about the SQL Server, to extend the database with triggers, for example. So that would be important to know.

Bye, Olaf.

 

[Mike Lewis]

Welcome to the forum. You've found the right place to discuss VFP and get answers to your questions.

Olaf has given you some useful information. But he also points out that we need to know more about your overall situation, and in particular whether you are using third-party software.

For now, I will assume that you are developing all the software yourself, and that you have full control over your programming environment and also your database.

In that case, the short answer to your question (how to "generate the invoice automatically as soon as a record inset into one of table") is to set up an INSERT trigger on the table in question. This is done at the database level. In other words, you write code in the SQL Server language (T-SQL) and store it in the database. That code will have access to all the data in the record that you are inserting, as well as data from the other tables, and you will use this to generate the invoice. The invoice will be held as another record in a table somewhere in the database. Your VFP program will then access that data to physically print the invoice, generate a PDF, or whatever.

So, that's the short answer. But I wonder if that it is really what you want to do. The problem with a trigger is that it will always fire when the relevant action takes place (in this case, an INSERT), regardless of the reason for the action. So if you are inserting records for some other reason, your application won't be behaving as designed.

Another reason not to use triggers (in this case) is that it will mean spreading your code over two different platforms: VFP and SQL Server, with all that that implies for debugging and maintenance. And if you ever move the back-end database to a different platform, you might have to rewrite the trigger code, because of variations in SQL dialects.

For those reasons, a better approach would be to write the invoice-generating code in VFP. Make it into a self-contained function (or procedure or method). And call that function immediately after whatever code you are using to insert the record. That way you will have complete control over the timing of the invoice generation, and all your code will be under the same roof.

If you want to know how to write code to generate an invoice in VFP ... well, that's a completely different question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kuma.lk]

hello Olaf,

Thanks for your reply

this is i am trying to do.

there is two access control device use for identify IN & OUT

when customer come, a proximity card issue to customer & customer will touch the card to in access control enter in to car park (when the card punch card id and other data insert to ms sql db)

when he leaving, touch the card to exit access control, at this time i want to get the time gap and run calculation program and print receipt automatically without human involvement

thank you

kuma

 

[Olaf Doschke]

OK, but that still doesn't tell me whether all that's in your own software or happening external.
If these in/exit controls are in your own software, you'd have no problem, would you?
If you can let the punch card call your code, you can do whatever you want, including to read back the data first written when the car drove in, computing the parking duration, printing a receipt.
Also: Are you the technical owner admin of the SQL Server DB or can you be made that or is this a database reigned by already existing software you merely have read access?

Bye, Olaf.

 

[JRB-Bldr]

kuma - Welcome to the forum.
You have provided a very general description of what you intend to create
And, based on what you describe, it can be 'architected', designed and developed in a variety of ways. That is basically up to you to decide how to do it.

I'd suggest that after deciding on an application 'architecture' that you break the functionality down to a lot of small 'pieces' and begin developing them.
And, if you do it that way and run into specific questions on how to approach getting that specific something done, we will be here to offer advice and/or suggestions.

For example, unless you have already got the READ device development done and working, I'd suggest that you do NOT begin with

"generate the invoice automatically as soon as a record inset into one of table"

but instead begin with

when customer come, a proximity card issue to customer & customer will touch the card to in access control enter in to car park

1) Make certain that you can READ the data into your application.
2) Then, once you are reliably READing the data in, you can then work on storing it into your SQL Server Data Table.
3) Now that you can READ & WRITE the data consistently you can begin developing how to analyze the data and what to do with the analysis results.

Also don't forget that you might need a separate Administration portion of your application so that Administrators can enter/manage parking slot counts, prices (especially if Invoices are generated), etc.
I don't know about your specific application, but I have developed and worked on systems for Parking Garages, and Parking Lots, etc. and the Admin side can itself sometimes needs to be complex.

Good Luck,
JRB-Bldr