Finding a record by its primary key
-
When I joined my current company about three years ago, I was given the task of maintaining the company's business critical order processing system. This is written as an Access front end to a SQL Server back end, and was written by a contractor who had left at the end of his contract with a large amount of the company's money in his bank account! Some investigation after my first week or two lead me to discover the following horrors: There was not a single error trapping routine in the whole front end - if something fell over, the standard error message was "an error occurred in the click event of btn....." Each routine that inserted a record was written as a Sub, rather than a function that returned a success/failure value. Every user on the database was assigned DBO permissions! There was absolutely no referential integrity defined in the database. There was no transaction processing in the system - if someting fell over part way through (as it often did), updates/inserts/deletes had to be rolled back manually using Enterprise manager. An example of his coding style is shown here - it shows how he retrieved a single record using its primary key:
Set rstMain = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "spSELocationForSageImport" Set prm = cmd.CreateParameter("@LocationCode", adVarChar, adParamInput, 20, "Sage") cmd.Parameters.Append prm cmd.CommandType = adCmdStoredProc rstMain.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc With rstMain Do While Not .EOF strLocation = !\[Location\] .MoveNext Loop End With Set rstMain = NothingMaybe I'm missing something, but do you need to loop though a dataset that can only contain one record? His date handling was also a bit esoteric:
txtDateCreated = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())I recently had occasion to rewrite some of his code that applied changes from an external system to the database. His code took two or more hours to run and slowed the system so much it was unusable (meaning I usually had to do unpaid overtime to apply the data out of hours). One of the lovely bits of code he wrote looped through a data set of 7000+ records, taking a value from each record and using this as a parameter in a stored procedure t
Chris Quinn wrote:
Maybe I'm missing something, but do you need to loop though a dataset that can only contain one record?
Because it returns a list with 0 or more values. Or maybe he only knew that single pattern ;P
xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008) -
I have the feeling that the guy in question once read a chapter on ADO and has abused the same example ever since ....
I'm largely language agnostic
After a while they all bug me :doh:
-
When I joined my current company about three years ago, I was given the task of maintaining the company's business critical order processing system. This is written as an Access front end to a SQL Server back end, and was written by a contractor who had left at the end of his contract with a large amount of the company's money in his bank account! Some investigation after my first week or two lead me to discover the following horrors: There was not a single error trapping routine in the whole front end - if something fell over, the standard error message was "an error occurred in the click event of btn....." Each routine that inserted a record was written as a Sub, rather than a function that returned a success/failure value. Every user on the database was assigned DBO permissions! There was absolutely no referential integrity defined in the database. There was no transaction processing in the system - if someting fell over part way through (as it often did), updates/inserts/deletes had to be rolled back manually using Enterprise manager. An example of his coding style is shown here - it shows how he retrieved a single record using its primary key:
Set rstMain = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "spSELocationForSageImport" Set prm = cmd.CreateParameter("@LocationCode", adVarChar, adParamInput, 20, "Sage") cmd.Parameters.Append prm cmd.CommandType = adCmdStoredProc rstMain.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc With rstMain Do While Not .EOF strLocation = !\[Location\] .MoveNext Loop End With Set rstMain = NothingMaybe I'm missing something, but do you need to loop though a dataset that can only contain one record? His date handling was also a bit esoteric:
txtDateCreated = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())I recently had occasion to rewrite some of his code that applied changes from an external system to the database. His code took two or more hours to run and slowed the system so much it was unusable (meaning I usually had to do unpaid overtime to apply the data out of hours). One of the lovely bits of code he wrote looped through a data set of 7000+ records, taking a value from each record and using this as a parameter in a stored procedure t
As the others have said, it does look like he found some example and kept using it over and over. Pretty much any VBA coding in Access is a strong candidate for being a coding horror.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
As the others have said, it does look like he found some example and kept using it over and over. Pretty much any VBA coding in Access is a strong candidate for being a coding horror.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
I don't agree - it's not the tool you use - it's the tool that uses it!
==================================== Transvestites - Roberts in Disguise! ====================================
-
I don't agree - it's not the tool you use - it's the tool that uses it!
==================================== Transvestites - Roberts in Disguise! ====================================
True. Access can be alright when used correctly.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
True. Access can be alright when used correctly.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
By default, Access should be removed from the PC of anybody who is tempted to open it up and knock together a crappy app with zero understanding of basic database procedures. Hello. It's called normalization people, and it's not an optional extra. If you don't understand normalisation, then you should be in a more touchy-feely career and not one that demands reaching higher than a 2 on the clue-o-meter.
Deja View - the feeling that you've seen this post before.
-
By default, Access should be removed from the PC of anybody who is tempted to open it up and knock together a crappy app with zero understanding of basic database procedures. Hello. It's called normalization people, and it's not an optional extra. If you don't understand normalisation, then you should be in a more touchy-feely career and not one that demands reaching higher than a 2 on the clue-o-meter.
Deja View - the feeling that you've seen this post before.
Yeah. Anyone wanting to use access should in addition to being required to pass the 'not excel' test for data design, also understand why changes are saved immediately instead of when you click the save icon. [my younger sister}, who creates single flat table dbs and never manages to grasp why I explain it isn't bundled in the cheap version of office, I mean you. :mad:
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
By default, Access should be removed from the PC of anybody who is tempted to open it up and knock together a crappy app with zero understanding of basic database procedures. Hello. It's called normalization people, and it's not an optional extra. If you don't understand normalisation, then you should be in a more touchy-feely career and not one that demands reaching higher than a 2 on the clue-o-meter.
Deja View - the feeling that you've seen this post before.
Pete O'Hanlon wrote:
Access should be removed from the PC of anybody who is tempted to open it up and knock together a crappy app with zero understanding of basic database procedures. Hello. It's called normalization people, and it's not an optional extra. If you don't understand normalisation, then you should be in a more touchy-feely career and not one that demands reaching higher than a 2 on the clue-o-meter.
Amen to that :)
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
Yeah. Anyone wanting to use access should in addition to being required to pass the 'not excel' test for data design, also understand why changes are saved immediately instead of when you click the save icon. [my younger sister}, who creates single flat table dbs and never manages to grasp why I explain it isn't bundled in the cheap version of office, I mean you. :mad:
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
dan neely wrote:
it isn't bundled in the cheap version of office
Thanks to God for that :) We have Access 2007 installed in the labs at my college where I teach and I took one look at it, and never looked at it again :laugh:
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
As the others have said, it does look like he found some example and kept using it over and over. Pretty much any VBA coding in Access is a strong candidate for being a coding horror.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
Sad: I have to maintain
-
When I joined my current company about three years ago, I was given the task of maintaining the company's business critical order processing system. This is written as an Access front end to a SQL Server back end, and was written by a contractor who had left at the end of his contract with a large amount of the company's money in his bank account! Some investigation after my first week or two lead me to discover the following horrors: There was not a single error trapping routine in the whole front end - if something fell over, the standard error message was "an error occurred in the click event of btn....." Each routine that inserted a record was written as a Sub, rather than a function that returned a success/failure value. Every user on the database was assigned DBO permissions! There was absolutely no referential integrity defined in the database. There was no transaction processing in the system - if someting fell over part way through (as it often did), updates/inserts/deletes had to be rolled back manually using Enterprise manager. An example of his coding style is shown here - it shows how he retrieved a single record using its primary key:
Set rstMain = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "spSELocationForSageImport" Set prm = cmd.CreateParameter("@LocationCode", adVarChar, adParamInput, 20, "Sage") cmd.Parameters.Append prm cmd.CommandType = adCmdStoredProc rstMain.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc With rstMain Do While Not .EOF strLocation = !\[Location\] .MoveNext Loop End With Set rstMain = NothingMaybe I'm missing something, but do you need to loop though a dataset that can only contain one record? His date handling was also a bit esoteric:
txtDateCreated = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())I recently had occasion to rewrite some of his code that applied changes from an external system to the database. His code took two or more hours to run and slowed the system so much it was unusable (meaning I usually had to do unpaid overtime to apply the data out of hours). One of the lovely bits of code he wrote looped through a data set of 7000+ records, taking a value from each record and using this as a parameter in a stored procedure t
Sad: I have to maintain code like this.
-
Yeah. Anyone wanting to use access should in addition to being required to pass the 'not excel' test for data design, also understand why changes are saved immediately instead of when you click the save icon. [my younger sister}, who creates single flat table dbs and never manages to grasp why I explain it isn't bundled in the cheap version of office, I mean you. :mad:
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
At the risk of sounding like a fool, could I ask you to please explain the bit about changes being saved immediately instead of when the save icon is clicked.
-
At the risk of sounding like a fool, could I ask you to please explain the bit about changes being saved immediately instead of when the save icon is clicked.
Databases have implicit save behaviour, e.g. triggered by an action such as moving to a new row. An application like Excel does not know when you want to save the data, so it only saves when you trigger an explicit save.
Deja View - the feeling that you've seen this post before.
-
Databases have implicit save behaviour, e.g. triggered by an action such as moving to a new row. An application like Excel does not know when you want to save the data, so it only saves when you trigger an explicit save.
Deja View - the feeling that you've seen this post before.
Okey doke -- but this is only the case when there is data binding between the database and the client right? I'm sure there must be plenty of apps out there, other than Excel, that required an explicit save. I'm not sure if I get the point that was originally being made. Was this being suggested as a good thing, a bad thing, or just an example of where the "developer" mis-understood what was going on?
-
Okey doke -- but this is only the case when there is data binding between the database and the client right? I'm sure there must be plenty of apps out there, other than Excel, that required an explicit save. I'm not sure if I get the point that was originally being made. Was this being suggested as a good thing, a bad thing, or just an example of where the "developer" mis-understood what was going on?
This was a case of a luser insisting on using the wrong tool (access instead of excel), and then continually whining about how it didn't do what she wanted.
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
When I joined my current company about three years ago, I was given the task of maintaining the company's business critical order processing system. This is written as an Access front end to a SQL Server back end, and was written by a contractor who had left at the end of his contract with a large amount of the company's money in his bank account! Some investigation after my first week or two lead me to discover the following horrors: There was not a single error trapping routine in the whole front end - if something fell over, the standard error message was "an error occurred in the click event of btn....." Each routine that inserted a record was written as a Sub, rather than a function that returned a success/failure value. Every user on the database was assigned DBO permissions! There was absolutely no referential integrity defined in the database. There was no transaction processing in the system - if someting fell over part way through (as it often did), updates/inserts/deletes had to be rolled back manually using Enterprise manager. An example of his coding style is shown here - it shows how he retrieved a single record using its primary key:
Set rstMain = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "spSELocationForSageImport" Set prm = cmd.CreateParameter("@LocationCode", adVarChar, adParamInput, 20, "Sage") cmd.Parameters.Append prm cmd.CommandType = adCmdStoredProc rstMain.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc With rstMain Do While Not .EOF strLocation = !\[Location\] .MoveNext Loop End With Set rstMain = NothingMaybe I'm missing something, but do you need to loop though a dataset that can only contain one record? His date handling was also a bit esoteric:
txtDateCreated = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())I recently had occasion to rewrite some of his code that applied changes from an external system to the database. His code took two or more hours to run and slowed the system so much it was unusable (meaning I usually had to do unpaid overtime to apply the data out of hours). One of the lovely bits of code he wrote looped through a data set of 7000+ records, taking a value from each record and using this as a parameter in a stored procedure t
Transactions are a blessing. Writing code to undo all changes after an error is no fun at all. And how do you proceed when even more errors pop up while undoing things? Unfortunately you don't always have transactions. I'm working on a larger project that uses a MySql database for both low cost and speed. Using another table format would make transactions available, but also slow everything down in an unacceptable manner. So I guess, the only choice is dealing with some tight spots in the buisiness logic.
A while ago he asked me what he should have printed on my business cards. I said 'Wizard'. I read books which nobody else understand. Then I do something which nobody understands. After that the computer does something which nobody understands. When asked, I say things about the results which nobody understand. But everybody expects miracles from me on a regular basis. Looks to me like the classical definition of a wizard.