|
Good afternoon,
Please, I m trying to generate a customized automatic numbers using entity framework.
The number is a combination of multiple columns from a table:
table1 (next_id int, prefix_text varch(5), table_name varchar(100))
in the table2( id int, invoice_number varchar(20))
The custom automatic number is invoice_number=(prefix_text)+' '+ (next_id+1) + ' _ '+ Current_year.
I tried to google but nothing. I m trying to use the triggers and also user defined function but I m feeling it not the best way.
Thank you in advance
F.O
|
|
|
|
|
Creating "meaningful" primary keys is usually considered a "bad" design decision for any number of reasons. At "report time" (or for searching), you can do anything you want.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Thank you for your answer, the invoice number is not the primary key. it is just to display on the report. is there another way to implement.
regards,
FO
|
|
|
|
|
(prefix_text)+' '+ (next_id+1) + ' _ '+ Current_year.
Why can't you do that at report time?
Store the individual fields in the db record as needed; not their concatenation.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Thank you so much for your time. Let me explore that option.
best regards
FO
|
|
|
|
|
You could use a datetime field's number of seconds as the "automatic number". Even in a moderately busy system, that would pretty much always be unique. When coupled with a row ID in a clustered primary key, duplicates will not be allowed, so Bob's your uncle.
EDIT ================================
If you're going to down-vote my answer, tell me why.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 29-Dec-21 6:01am.
|
|
|
|
|
I am not really in agreement with the other comments since they somehow seem to be drilling down on a 'primary' key even though I see nothing in your request that suggests that.
Your posting should focus on creating an 'invoice number'
It will need to have the following
1. Be unique
2. Be human readable
3. Be small enough that humans will not be annoyed by it.
Presumably the 'prefix_text' is unique by itself. It does not rely on the 'table_name'. If that is not the case then the uniqueness constraint for the invoice number will not be met by the form that you posted.
I would suggest that you do not use underscores. A separator is ok but use a dash since it tends to be more obvious. However you can also consider the dash as being a display character only. So for example credit card numbers often use a dash but the numbers are actually the only part of the actual id. So your UI could take it with dashes but remove it for searches. And in the database you store it without the dashes.
Without the separator the format must be fixed however. So it is a design decision.
Volume of the business and generation of the code, now and for some time into the future, impacts how you might want to do this. If you are producing say 100 of them a day then it is easier than if you are producing 100 million in a day.
There might be a security concern if you generate numbers that are easily guessed. So for example if you use the current date along with an incrementing number then it is easy to guess valid invoice numbers. This also is a matter for how the business works. It isn't much of a concern if there are other authentication factors used to limit access to the invoices.
I don't see anything conceptually wrong with your basic idea although I would format the id (pad zeros to the left.)
Fidele Okito wrote: using entity framework.
I am guessing that this in fact is your real problem.
What you want to do is just use native SQL. Myself I would probably use a stored proc but you can do it with SQL in the application.
You need the following in a transaction. This is pseudo code but the idea is basically sound.
declare @lastId int;
update table1 set next_id = next_id + 1 where prefix_text='xxx' and table_name='fff';
select @lastId = next_id from table1 where prefix_text='xxx' and table_name='fff';
insert into table2 (invoice_number) values('xxx' + '-' + @lastId + '-' + ...);
select @lastId;
The last part of that returns the newest id to your application. Not needed if that is not something that is needed at that point.
The transaction is necessary to insure uniqueness.
|
|
|
|
|
"Drilling"? "Primary Key" was mentioned in passing because that is typically a "novice" mistake; on the other hand, it's amazing how something so simple can be made so complicated; particularly since every party to the transaction has to now quote and enter and reference it when it means absolutely nothing to them.
Every client and vendor has their own "numbering system"; why it never caught on in the "stationary" dept when it came to "invoice pads" will always be a mystery. Maybe because it was so easy to tell when one was missing in the sequence.
Actually, use a blockchain.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
modified 29-Dec-21 21:33pm.
|
|
|
|
|
"Invoice pads" ... reminds me of my first job many many moons ago. I committed the cardinal sin of using the last requisition in the book for something other than another requisition book. Chaos ensued for the department.  
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
You broke the chain!
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Gerry Schmitz wrote: "Drilling"? "Primary Key" was mentioned in passing because that is typically a "novice" mistake; on the other hand, it's amazing how something so simple can be made so complicated; particularly since every party to the transaction has to now quote and enter and reference it when it means absolutely nothing to them.
Not sure what that means.
Invoices mean a lot to many people that actually look at the invoice. The invoice number is the name of the invoice. Just that. And certainly much better than attempting to describe the invoice by time, date, contents. And even those might be ambiguous in some situations. For example if a duplicate is created. In those case the invoice number is the only thing that allows one to determine that there are two different ones.
|
|
|
|
|
It means what it means because you used the word "drilling" and "primary key".
I also don't see anything to indicate that you have actually worked with AP, AR and ERP systems and integrated them.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Gerry Schmitz wrote: I also don't see anything to indicate that you have actually worked with AP, AR and ERP systems and integrated them.
First of course nothing in the question suggests this has anything to do with integrating systems. It very much looks like creating a value that people, not systems, would consume.
Second your comment is nothing but an attempt to denigrate and doesn't address anything I said or what was said in the original thread.
Third I didn't see you present any credentials that suggested you are an expert on that specific subject.
Fourth as far as credentials go I have been working with databases for 40 years. That includes as DBA, DBA Team lead and designing from scratch for several companies. And it does include large migrations and mitigations on large systems using disparate database systems.
Gerry Schmitz wrote: what it means because you used the word "drilling" and "primary key".
No idea what that means. You are the one that specifically first mentioned "primary key" in your first post in response to the initial question.
So yes it was appropriate for me to address that comment in terms of differentiating the need for a displayable invoice number and a "primary key" since there was no indication that they intended to use is as a "primary key".
|
|
|
|
|
Dear jschell,
Thank you for your comment which is helpful alot.
I was thinking to create a user defined function(tsql) :
create function NextInvoiceValue()
return nvarchar(30)
Begin
declare @lastId int;
declare @valueinvoice nvarchar(30)
--update table1 set next_id = next_id + 1 where prefix_text='xxx' and table_name='fff';
select @lastId = next_id from table1 where prefix_text='xxx' and table_name='fff';
--insert into table2 (invoice_number) values('xxx' + '-' + @lastId + '-' + ...);
set @valueinvoice='xxx' + '-' + (@lastId +1) + '-' + ...;
return @valueinvoice;
end
This function will be used on create table table2 (..., invoicenum nvarchar(30) not null default dbo.NextInvoiceValue()...)
and the last step is to create a trigger(after insert) on table2, this trigger will execute the update statement :
update table1 set next_id = next_id + 1 where prefix_text='xxx' and table_name='fff';
I m wondering if this will make sense for daily traffic of 100 000 insertions
Thank you again
FO
|
|
|
|
|
Fidele Okito wrote: I m wondering if this will make sense for daily traffic of 100 000 insertions
You should validate the growth rate. Nothing to bad about that for the idiom though.
Although the algorithm works I don't expect that the format will. The numbers are just going to be too big almost immediately for people to like looking at them.
There are various ways to do this but for example start the id at 100,000,000. Then format the invoice to be like the following. (You can start the id at zero and then zero fill it.)
... + (id/1000) + '-' + (id%100000)
There are other ways to do this for example use a 4 char field which starts at AAAA and increments to ZZZZ every 1,000,000 and which resets the id. (You can in fact compute the text value with care rather than explicitly tracking and resetting.)
|
|
|
|
|
Dear jschell,
This is well received and well noted. This is what I was looking for.
I will try it.
Thank you so much
|
|
|
|
|
hello guys, i have this on my PC where i code,
but i want to put correct path,
here is what i tried
ReportDocument report = new ReportDocument();
string ap = Environment.CurrentDirectory + "\\ARGES.rpt";
report.Load(ap);
but i get this massage from client pc failed to load report
but what is the correct path on server????
BUILD ACTION : content
|
|
|
|
|
Crystal Reports runs on the server, as does all your C# code: it has no direct access to the client filesystem at all - for the obvious security reasons - and cannot in any way access client folders.
If you are trying to access report files which are located on the server, then they must be in a folder that is available to the process running your code - which for a web-based app is not your user, it's a special user that runs IIS for example.
Additionally, the EXE file current folder is probably located (in production at least) in the Program Files folder, which is write protected by default, again for security - so it's very unlikely that a report file will be of any use unless it is store in a "sensible" location.
Start by working out what generates the report, and where exactly it stores it relative to the code that tries to read it. Then you can start working on where it should be stored so that it is accessible to client and server.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
the file shared is here on server where are my .rpt or my report
\\MIN\\rom\\COMM\\COMMER\\bin\\Debug\\ARGES.rpt"
i want to know how to load the correct path please
here
report.Load("\\MIN\rom\\COMM\\COMMER\\bin\\Debug\\ARGES.rpt"");
or is there any way to do it
|
|
|
|
|
Presumably "client" in this case means a specific box under your control and one that remains under your control. It will not ever be one that a customer uses. Nor does it represent a test box as a stand in for multiple customers.
If so...
You log into the client machine.
Then see if you can access that path.
If not then determine the exact permissions needed, both on the client box and the server box to access that path.
Then you figure out what user the application is running on the client box and you set up the permissions for that user.
If this is intended to run on customer machines (boxes outside of your control of any sort) then you need a different design. You need to 'deliver' the report as a file to any standard idiom just like you would for any internet web content. I believe Crystal reports supports such a mechanism but it does require setting up the server that way. Then the client app would need to retrieve the report (just like getting any other web file) and display it (just like displaying any other web file.)
Alternatively you use a different web server and deliver the file into that web server. And then the same process occurs (get the web file and display it.)
|
|
|
|
|
Hi,
I have a small WPF application which gets some data (~300 rows) from an API and gets displayed in a DataGrid. Up to this point everything works well. Some Header-columns of the DataGrid have nested textboxes, which should serve as search-boxes. I bound their text to a property in my ViewModel and wrote a filter for the CollectionView. If I enter a filter-text everything is okay and I get the result I expect. The problem appears when I try to remove my filter-text again. When I want to remove the last character from the text-box the UI freezes for a few seconds. I think this comes from loading all the rows into the DataGrid again. Has anyone faced this problem as well?
Thanks for your help in advance!
|
|
|
|
|
Based on no code and a vague problem description:
300 rows is trivial once they're in memory. How are you "loading again"? Going back to the "API"?
Since your filter seems to be tied to a keyboard handler, I would look at that. Maybe you need a clearer "Apply / Clear (filter)" function.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
As a suggestion
Identify the columns that actually need filtering
Move the filter entry from the headers to textboxes above the datagrid
Apply/clear the filter via a command button(s)
This divorces the filtering process from the datagrid, allows you to apply the filter to the underlying data in the VM rather than relying on the binding to the header textboxes. You will also apply the filter once (when the user clicks the command button) rather than every time the header textboxes change content.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hello
I am making WMI queries to remote computers that are in our production hall. The first connection with ManegementScope takes realy long. If the first connection is done the next connection is then much faster. How can i speed up this connection time. That's the code i am using:
public void WMI_Connect()
{
System.Management.ConnectionOptions options = new System.Management.ConnectionOptions();
options.Username = "user";
options.Password = "password";
WMI_path = "\\\\" + MAE_Selection.MAE_IP_PCU + "\\root\\cimv2";
ManagementScope scope = new ManagementScope(WMI_path, options);
try
{
// Start WMIconnect
scope.Connect();
// WMI Connection done (8-10 seconds)
// The method in that i am doing the WMI queries
Read_General_Data_WMI();
}
catch (Exception ex)
{
using (StreamWriter sw = File.AppendText((Start.error_path)))
{
sw.WriteLine(ex);
WMI_Con_State = "Connection_failed";
}
}
}
|
|
|
|
|
Pre-load it 10 seconds before you need it.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
