Guts and Core of the program
The criminal search form is the hardest, but most rewarding form i have ever created; it allows users to search the criminal database in superior flexibility, users have the ability to:
- Quick search the database; returning all criminal records and displaying them in the data grid
- Advanced Search the database; users have the ability to specify any component of the database, and querry the database against a set piece of data
-users have the ability to double click search results and view more details of the selected record on a seperate form, and have the ability to print the record from there.
It took some time to understand hwo to develop this advanced search sql coding:
from learning the basics of OLEDB, i had the ability to grab and display data, and vise versa back to the database, but no tutorial that i had come across outlined how to manipulate the sql to achieve the advanced search… after carefully looking at the code i have already established.. i realised i needed to create variables within variables to achieve this…. the concept evolved around this line of code
————— code ——————-
da = New OleDb.Ole.DbDataAdapter(sql, connection)
—————————————-
where sql was a variable. so i thought to my self, how can this variable which represents a string change and alter to fit the user’s specified advanced search data set.. the answer was within the definition of what a variable is, itself. vairbales represent something else.. so why not have a variable that represents and sql command, that also contains variables to define the sql’s string command! — a bit hard to explain in words, but its my home grown method that works.
within any SQL command you need:
-SELECT -FROM -WHERE
first step to achieve my advanced sql search was to have variables define what each of these sql Components will contain:
SELECT (the fields) — depending on whether the user selects to hide or show some fields we need variables to represent this :
———- code snippit —————
IF txtnationality.Text = “Any” Then
ElseIf txtnationality.Text = “Hide” Then
If txtlastname.Text = “” And txtfname.Text = “” Then
ElseIf txtlastname.Text = “” And txtfname.Text <> “” Then
ElseIf txtlastname.Text <> “” And txtfname.Text = “” Then
ElseIf txtlastname.Text <> “” And txtfname.Text <> “” Then
End If
——————————————————————————-
Though overall it may seem not to hard, and it might not involve a tonne of coding, the concept and technique is dificult to grasp, as to begin with to find the actual proper code to add a vairbale into sql is hard,and to implement it the proper way also takes time to realise.. not only that but you must think of every possible option the user may select, and test whether your system may crash, or provide an unexpected result… it is not as easy at it seems… plus, some consequences after the whole sql search, such as the data grid will not sort as expected.. because you find out that there are limits to sql suh as:
sql can not jump from refering to a variable that is integer defined to a variable that is string defined and put it all into one big variable that can only be defined in once instance, i.e: only a string, not integer and string at the same time!… so solve that problem i made all my integers into string, cause you cant go string to integer .. (daa) .. but then there are more consequences to that.. now when ever i try to sort my list after (like sorting in terms of age) the sort wont work properly… because its sorting strings.. different to sorting integers… so to solve that problem you must have the same number of characters within that field for every cell.. hence if your trying to sort this string :”5, 792, 181″ … it wont return 5, 181, 792″ but rather “5,792, 181″ ???? valid, but unexpected…. to solve that problem every component of that must have the maximum amount of characters in the set i.e: “005,792,181″ … then it will work!…
DBNull errors are also common, especially when i am trying to grab records after a user double clicks a certain row, especially if they click the blank row at the end… took some searching to find that answer DBNull.value which i covered in my previous posts.
Overall this part of the assignment was the hardest in terms of its concept, technique, and tediousness in every single part of the sql coding, and after effects…..
Can not Delete or Edit ANY records in my Database :(
unfortunatly, my database sql commands rely heavily on the database’s structure and primary key order… when someone or something (i.e: vb.net) edits or deletes a record from the database, it leaves an altered mark on the primary key, which makes the record edited or deleted change in order when the database adapter fills the data grid, although the database itself is still in order… many other sql functions that i have coded will not work either because the order has changed.. such as the form i created when users double click a search result record.. if the primary key field is altered in any way, the form will not be able to choose the correct record, as i add a constant to the record id selected…
hence, i now face a limit where i can not edit or delete criminal records, hence i must remove some objectives.
