Dependant tables
-
Hello CP, Really unsure whether or not to post this here or with SQL. I'm currently working with C# and Sql Server 2005 Express. I've got two tables:
_Cases_ CaseID, (PK, Identity (+1 increment)) ClientID (FK) Description, DateRegistered, etc _Tasks_ TaskID (PK, Identity (+1 increment)) CaseID (FK) CompanyCost ClientCostAs you can see, tasks depend on a case. My form contains a lot of info for the Case and a small datagridview for the tasks. I save my case and wish to add tasks to it. Problem is that I don't know what CaseID the case has gotten. I can query for Max(CaseID) possibly with a DateTime in the 'where' clause. Although my method works, I was wondering if someone has a better idea, preferably in C# code. -
Hello CP, Really unsure whether or not to post this here or with SQL. I'm currently working with C# and Sql Server 2005 Express. I've got two tables:
_Cases_ CaseID, (PK, Identity (+1 increment)) ClientID (FK) Description, DateRegistered, etc _Tasks_ TaskID (PK, Identity (+1 increment)) CaseID (FK) CompanyCost ClientCostAs you can see, tasks depend on a case. My form contains a lot of info for the Case and a small datagridview for the tasks. I save my case and wish to add tasks to it. Problem is that I don't know what CaseID the case has gotten. I can query for Max(CaseID) possibly with a DateTime in the 'where' clause. Although my method works, I was wondering if someone has a better idea, preferably in C# code.Your problem is to get last inserted caseID or selected caseID from gridview?
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
Hello CP, Really unsure whether or not to post this here or with SQL. I'm currently working with C# and Sql Server 2005 Express. I've got two tables:
_Cases_ CaseID, (PK, Identity (+1 increment)) ClientID (FK) Description, DateRegistered, etc _Tasks_ TaskID (PK, Identity (+1 increment)) CaseID (FK) CompanyCost ClientCostAs you can see, tasks depend on a case. My form contains a lot of info for the Case and a small datagridview for the tasks. I save my case and wish to add tasks to it. Problem is that I don't know what CaseID the case has gotten. I can query for Max(CaseID) possibly with a DateTime in the 'where' clause. Although my method works, I was wondering if someone has a better idea, preferably in C# code.You can't rely on the Max, or (necesarily) the datetime. If you do get two close-to-simultaneous updates, then there is a very good chance that the dates will be the same anyway. Instead, do not rely on the database to ensure unique ID's - change the Id field type to Guid, and assign them yourself, before you insert the new record to the table. Advantages: you never have to search for the record you just inserted to find out the ID because you knew it before you inserted it. If you put a try-catch block round the insert, you can handle the (extremely unlikely1) event that you do get a duplicate and assign a new Guid for a retry. I stopped using int IDs except for log entries a few years ago, and use Guid nearly exclusively. If you must use DB assigned ints, then you can get round the problem using a stored proceedure to insert the record and return the ID, using IDENT_CURRENT: MSDN[^] 1 You are more likely to win the lottery every draw for the rest of your life than to get two matching Guids, in theory!
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
-
You can't rely on the Max, or (necesarily) the datetime. If you do get two close-to-simultaneous updates, then there is a very good chance that the dates will be the same anyway. Instead, do not rely on the database to ensure unique ID's - change the Id field type to Guid, and assign them yourself, before you insert the new record to the table. Advantages: you never have to search for the record you just inserted to find out the ID because you knew it before you inserted it. If you put a try-catch block round the insert, you can handle the (extremely unlikely1) event that you do get a duplicate and assign a new Guid for a retry. I stopped using int IDs except for log entries a few years ago, and use Guid nearly exclusively. If you must use DB assigned ints, then you can get round the problem using a stored proceedure to insert the record and return the ID, using IDENT_CURRENT: MSDN[^] 1 You are more likely to win the lottery every draw for the rest of your life than to get two matching Guids, in theory!
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
OriginalGriff wrote:
If you must use DB assigned ints, then you can get round the problem using a stored proceedure ...
Does one really need an SP, last week I successfully used a compound command on MySQL, basically like this:
string cmd="INSERT INTO myTable (field1, field2) VALUES(@value1, @value2); SELECT last_insert_id()";
using (MySqlCommand dbCmd=new MySqlCommand(cmd, dbCon)) {
dbCmd.Parameters.Add("@value1", MySqlDbType.String).Value=value1;
dbCmd.Parameters.Add("@value2", MySqlDbType.String).Value=value2;
PK=(long)dbCmd.ExecuteScalar(); // last_insert_id() insists on type long!
...
}on a table with an integer auto-incrementing primary key. Are there arguments against that? :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
OriginalGriff wrote:
If you must use DB assigned ints, then you can get round the problem using a stored proceedure ...
Does one really need an SP, last week I successfully used a compound command on MySQL, basically like this:
string cmd="INSERT INTO myTable (field1, field2) VALUES(@value1, @value2); SELECT last_insert_id()";
using (MySqlCommand dbCmd=new MySqlCommand(cmd, dbCon)) {
dbCmd.Parameters.Add("@value1", MySqlDbType.String).Value=value1;
dbCmd.Parameters.Add("@value2", MySqlDbType.String).Value=value2;
PK=(long)dbCmd.ExecuteScalar(); // last_insert_id() insists on type long!
...
}on a table with an integer auto-incrementing primary key. Are there arguments against that? :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
You don't actually need an SP to do it, but it makes it a bit more maintenance safe if you do. A new bod looking at it could easily "bleep over" the end of the insert and miss it. Plus I am not sure how SQL handles compound commands internally: could it process another command in between the two? I'd rather go with Guids and be safe every time. :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
-
You can't rely on the Max, or (necesarily) the datetime. If you do get two close-to-simultaneous updates, then there is a very good chance that the dates will be the same anyway. Instead, do not rely on the database to ensure unique ID's - change the Id field type to Guid, and assign them yourself, before you insert the new record to the table. Advantages: you never have to search for the record you just inserted to find out the ID because you knew it before you inserted it. If you put a try-catch block round the insert, you can handle the (extremely unlikely1) event that you do get a duplicate and assign a new Guid for a retry. I stopped using int IDs except for log entries a few years ago, and use Guid nearly exclusively. If you must use DB assigned ints, then you can get round the problem using a stored proceedure to insert the record and return the ID, using IDENT_CURRENT: MSDN[^] 1 You are more likely to win the lottery every draw for the rest of your life than to get two matching Guids, in theory!
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
OriginalGriff wrote:
You are more likely to win the lottery every draw for the rest of your life than to get two matching Guids
Both the lottery and Microsoft telling you so, I'm still not sure it would be correct as it would strongly depend on the number of inserts you're performing, their frequency could be a lot higher than that of you buying a lottery ticket. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
OriginalGriff wrote:
You are more likely to win the lottery every draw for the rest of your life than to get two matching Guids
Both the lottery and Microsoft telling you so, I'm still not sure it would be correct as it would strongly depend on the number of inserts you're performing, their frequency could be a lot higher than that of you buying a lottery ticket. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
This is true, but with a space of 3.4E38 versus 1.4E7 it's a big difference! :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
-
This is true, but with a space of 3.4E38 versus 1.4E7 it's a big difference! :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
Sounds alright. So you actually do hope to win the lottery. Good luck then. :-D
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
You don't actually need an SP to do it, but it makes it a bit more maintenance safe if you do. A new bod looking at it could easily "bleep over" the end of the insert and miss it. Plus I am not sure how SQL handles compound commands internally: could it process another command in between the two? I'd rather go with Guids and be safe every time. :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
I hear what you say, FWTIW as I just read this[^]. :-D
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Sounds alright. So you actually do hope to win the lottery. Good luck then. :-D
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
"It is the hope of reward that sweetens labour." Otherwise known as "The Stupid Tax". And, no, I'm not a Budgie Waver. :laugh:
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Manfred R. Bihy: "Looks as if OP is learning resistant."
