[RESOLVED] I have my GUI concept but am unsure how to structure/implement my database

[The_Hobbyist]

Hello all! I'm the new guy and full disclaimer, I don't have a lot of experience with VB.NET. Fifteen or twenty years ago I used VB 6 frequently to develop some stand alone programs but I'm afraid I have lost any knowledge that I once had. At the time though, I wrote some relatively neat stuff. My biggest accomplishment was a program that worked in conjunction with a card reader (hardware) via RS232. It would read & write to the EEPROM on a satellite dish access card (back in the old DirecTV days when they used the "H" and then "HU" cards). I'm Canadian and I couldn't buy a subscription but I loved the service, so I simply opened the card up. There was a lot of reading involved in that project both here on the VB forums and in the satellite dish forums. I managed though, after a year, and I'm sure that with some hand holding and guidance I'll manage this project as well.

Anyway, I'm attempting to develop a simple little program to help me catalogue my rock collection. I've gone ahead and put together a GUI concept to get the look and feel that I'm after. My goal is to be able to write to a database based using a user's input through the provided text boxes, combo boxes, radio buttons and check boxes, ect. I would also like to be able to remove an "entry" and of course to search the database. I don't want the search to use all of the data fields when searching but rather have it limited to specific data points (highlighted in green on my screen shot attached below). The other data fields are just extra information in the format of long text (full sentences and paragraphs) which are likely too difficult to search. It really isn't an ambition of mine to search based on those fields anyway. The key search fields are highlighted as I mentioned.

As you can see, I have included a screen shot of the GUI. I would like to make the search function able to search by single criteria (green highlights on screenshot) or any number of green highlighted entries. I currently do not have a database nor do I know how to begin. My intention was to use a single Access (.mdb) database file however, SQL was suggested to me as well. Honestly, I don't know enough to know what the difference is.

I'm committed to seeing this project through to completion but as I've mentioned above, I'm quite green with this. I appreciate your patience with me.

So.......After viewing the screenshot and getting an idea of what I am trying to accomplish, how would you go about setting up and implementing the database? Where should I begin?

Any help is greatly appreciated. Thank you!

[Zvoni]

1) SQL is not a Database-System. It's the "Query"-Language. Or did you mean "Microsoft SQL-Server"?
2) Ask yourself the following questions: Who is going to use that program at any time? Just yourself? Multiple Users (at the same time)?
If only yourself, then MS SQL-Server is IMO way Overkill. Though instead of MS Access i'd rather go for SQLite (which VB.NET should have everything to support it).
In VB6 and SQLite your best bet is RichClient6, otherwise MS Access is still the "goto" Database
(The reason i mentioned SQLite: SQLite is way closer to "Standard/ANSI"-SQL, so if you have trouble with the Queries you might get answers first compared to Access)

3) You should try to "structure" the Data.
You have your "stones".
Those stones have "Attributes".
Are they a fixed number? Variable Number?
You have comments (Freetext).
Only one comment per Stone? Multiple Comments?

Depending on such Questions/Answers it influences your decision, which tables and relations to create

Give a shout if you've come to some answers.

[The_Hobbyist]

Excellent! This gives me some direction and is greatly appreciated. I am going to do my best to answer your questions:

1. I am sorry for the confusion. I was referring to Microsoft SQL-Server. In a previous forum thread Microsoft SQL-Server was brought up however, I didn't fully understand its meaning. I'm still not crystal clear on it but it seem to me that Microsoft SQL-Server is the database management system and not a database engine. That is to say, Microsoft SQL-Server is the "query" language which connects the VB6 platform to the database engine and it should not be confused with SQL-Lite which is the database engine (which builds/maintains the database)?

2. My program is intended to be a stand alone, single user interface. I have no intention to make it network capable. (I think you've helped me to better understand why Microsoft-SQL-Server is not the database management system that I'm going to need here - it is overkill!)

While I am familiar with Access to a certain degree and feel most comfortable utilizing it as my database engine, I am quite open to moving away from it! I'm on a mission to learn here, so I am quite agreeable when it comes to being swayed to learn a new database engine. In another forum thread, SQL-Lite had been brought up as well and for that reason I'm going to side with you folks and chose the path of SQL-Lite. You obviously know what you're doing. Done! SQL-Lite it shall be.

I'm unsure where RichClient6 comes into the picture? My assumption is that it is the "query" language or is the go to between the VB6 platform and the SQL-Lite database engine? In any event, I'm happy to go down this road as well. This all seems like a fantastic place to start - thank you for that!

3. As far as structuring the data goes, I think I have a plan for that. In my head it goes like this (please refer to my screenshot for clarity):

The data fields - "Subject", "Type", "Class", "Crystal System" and "Rarity" will each be a variable that is defined by one of the selections provided in the combo box drop down list. Typically a single word or a few words separated by commas. The search results should be made on an exact match to the entirety of the field though and not just a single word.

The data fields - "Name", "Chemical Formula", "Composition" & "Vendor Name" will each be a variable that is defined by the user's input into either a combo box or a textbox. These entries can be either a single word or a string of words resembling a short sentence. The intent is for the search function to return results based on the entirety of the field and not just a portion of it (an exact match).

The data fields - "Density g/cm3", "Hardness (Mohs)-A", "Hardness (Mohs)-B" & "Inventory Number" will each be a variable defined by the user's input into a text box. These will only be numeric values and up to two decimal points.

The data fields - "Magnetic YES" and "Magnetic NO" are to be variables defined by radio buttons.

The data fields - "Chakra" check boxes (all 7 of them) and the "Astrological" check boxes (all 12 of them) can be variables defined by a TRUE/FALSE notation, I think?

The data field - "Quantity in Inventory" I want to be an integer generated by the search results. Essentially this will be a counter. This field should represent the number of records which yield a positive search result based on search criteria entered however, since I want this database to include items that I both currently own AND do not own and this counter should only return results for items in the personal inventory list and not just the entire database. I think there will have to be a cross reference during the query of which I do not understand yet. Perhaps a cross reference to the "Purchase Price" value where anything beyond a NUL return generates a positive count?

All of the remaining text boxes in the form will be free text (?) and not intended to be part of the searchable data points. With that said, they are intended to be tied to the record set and written and removed from the database upon command.

With all of that laid out, do you have a better understanding of what I want to achieve? If so, where do you feel is the first place that I need to look to get going in the right direction? Can you offer that direction? I fully understand that this is not going to be an overnight success and accept that my progress will most likely be frustrating slow at first.

I'm going to assume that my first step would be to obtain SQL-Light and RichClient6? You mentioned that VB.NET should have everything to support SQL-Light. Does that mean that SQL-Lite is not a piece of software to download but a built in function of VB.NET?

Thank you for your help! I can appreciate that my reply is rather lengthy. My biggest hurdle at the moment (from what I can tell) is that I don't know the terminology yet and trying to ask clear questions can be difficult. I'll apologize in advance if my posts are word heavy! I'll get there...

[techgnome]

1 - yeeee... no. That's not right.
SQL = Structured Query Language - this is the query language that is used to pull/edit/delete (also known as Create, Read, Update, Delete or CRUD) data. Looks like this: Select * from yourTable Where someField = 'a value'
SQL Server - this is a Microsoft product. It's a server/service that allows you to connect to databases and perform SQL queries against it. Further more, there's different flavors of SQL Server - SQL Server Express (designed to be lightish and run on developer's systems for development purposes. SQL Server Enterprise - designed to run on full-servers, allows for data farms, user management, etc.
SQLite - this is a set of DLLs that allows for a light weight db engine that only requires you to deploy those dlls - as opposed to SQL Server which requires a completely different install and isn't re-distributable (if I remember right).
Access - this is the database applicaton that comes with MS Office. It uses either hte Jet (*.mdb) or the newer ACCDB engine (*.accdb).
Now here is where it gets messy. For the most part SQL is interchangeable between the different dbms (database management systems) ... to a point ... at which their individual accents/dialects come into play. The "S" in SQL is for "Structure" not "Standard" ... which can trip people up. Since SQL Server Express is a subest of SQL Server, any SQL written in one will run in the other no issues. Moving it to Access sometimes requires a little bit of tweaking. Moving to SQLite requires more tweaking. Moving to MySQL (yet another db engine/dbms product requires more tweaking again, and moving to Oracle requires to you question your life's choices.

2 - yeah, for a one-user, standalone system with no sharing... your best bets are going to be either Access, or SQLite...
RichClient6 coems into this if you're working with VB6 as it has SQLite capabilities built into it that allow for easy setup and use.

-tg

[Zvoni]

Tg answered the points for 1) and 2)

For 3): you have to differentiate between how you display your Data, and how your Data is structured (tables, columns, primary keys, foreign keys etc.)
e.g.
i saw you mentioning a „vendor“: to me that‘s a strong indicator to have „vendors“ their own table (a vendor can sell multiple stones).
OTOH, i saw a „chemical formula“: that‘s a strong indicator for being a column/field of the table „stones“, since no two stones have the same formula.
that‘s what i meant with to „structure“ your data.

i want you to think about, which „attribute“ (or property) belongs to which „entity“ (vendor, stone are examples for entities)
because out of my left sleeve, i already know that the relationship „vendor“ to „stone“ is a „m:m“ relation („many to many“),
because a vendor can sell multiple different stones, but one stone can be bought from multiple different vendors.

those are the basics of database design, called „normalization“ (look it up)

HOW you display that data has nothing to do with how you store the data

[The_Hobbyist]

Thank you folks! That helps, I think. I'm going to look up 'database normalization' and return here again.

Stay tuned...

Cheers!

[Zvoni]

To get you started:
“Subject“, „Class“ and „Crystal System“ have their own (simple) table.
“Name“ (obviously) goes into „stone“ as well as any numeric value („Hardness“ both) and the „magnetic“ thing are direct columns/fields/attributes of „stone“

„Rarity“ again its own table

[The_Hobbyist]

To get you started:
“Subject“, „Class“ and „Crystal System“ have their own (simple) table.
“Name“ (obviously) goes into „stone“ as well as any numeric value („Hardness“ both) and the „magnetic“ thing are direct columns/fields/attributes of „stone“

„Rarity“ again its own table

I can see that some intense thought and attention to detail come into play here! I also feel that this is more tricky than it appears at first glance, because it would stand to reason that a poorly structured database could be the downfall of any application designed to work with said poorly designed database. With all of that said, while looking online for a more simple explanation to the question "How to design a database", I came across an interesting read (microsoft website) and I believe it has set me onto the same path that you are attempting to put me on. Correct me if I'm wrong, please. In any event, the following is a copy/paste from that website. It makes sense in my head and helps me to understand the terminology a little better (tables, columns, fields, primary keys) and their function.

I'm going to ponder this for a some time and attempt to structure a series of tables which will contain columns (fields) that will store the specific data points, creating rows. Then a primary key will be assigned to one of the columns in each table that will allow the individual rows from that column to be called upon when need be. Is that correct so far? What still has me a little foggy is how to create the relationships between the tables. I'll keep reading...


The design process consists of the following steps:

Determine the purpose of your database
This helps prepare you for the remaining steps.

Find and organize the information required
Gather all of the types of information you might want to record in the database, such as product name and order number.

Divide the information into tables
Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.

Turn information items into columns
Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.

Specify primary keys
Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.

Set up the table relationships
Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.

Refine your design
Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.

Apply the normalization rules
Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

[jg.sa]

G'Day TH

I'm going to ponder this for a some time and attempt to structure a series of tables

If you create a look up table so that any string can be equated to a number eg. 1-99 = Rock Types , 100-199 = Scientific Data Type so then you would have a row " 101 , Quartz " in the look up table and just 101 as the value ( not the text ) in the row of the 'Rock Assets' table.

In the future if you wanted to change the 'spelling' of Quartz to Quartes !!! you only need to change the lookup tables 1 row # 101.

This is a 'poor mans' normalisation and was 1st done to deal with the internationalisation of apps.

Theoretically normalisation could be endless !!!

[Zvoni]

What still has me a little foggy is how to create the relationships between the tables. I'll keep reading...

Imagine an entity called "Person"
This "Person" has Attributes like a Primary Key called "ID", then First Name, Last Name, Date of Birth, Weight, Height, .......
You'll find it very often, that "ID" refers to an anonymous Numeric Integer-Value

Now imagine an entity called "Device" (think tablet, Smartphone etc.)
This "Device" again has a Primary Key called "ID" (You can "reuse" columnnames in different tables), and attributes like Type (Phone, Tablet), Phone-Number, Operating System etc.
.... and lastly it has something called a "Foreign Key", let's name it "PersonID"

Now, without a separate Table, how would you store data for one and the same person, if this person has an iPhone as well as an iPad?
Without separate tables you'd have to create a row for each device, despite it being the same person.
That's where Primary Keys and Foreign Keys come into play

In above entity "Device" the Foreign Key is basically a Pointer to the Primary Key of the Person to which it belongs.
That way you wouldn't have to enter the Persons Details into the device-table.

Relationsships are NOT "created" per se in a Database, they are "there", and it's your job to implement it, and to adhere to its rules.
though pretty much all Database-Systems provide you some basic tools/mechanisms to keep so called "referential integrity"

[Zvoni]

Here is an example based on your data (I've taken just the rudimentary stuff)

For looking at SQLite-Databases i use DB Browser for SQLite: https://sqlitebrowser.org/dl/
Just take the standard installer.
Beware: If you're gonna use VB6 it must be the 32-Bit Version

[The_Hobbyist]

Imagine an entity called "Person"
This "Person" has Attributes like a Primary Key called "ID", then First Name, Last Name, Date of Birth, Weight, Height, .......
You'll find it very often, that "ID" refers to an anonymous Numeric Integer-Value

Now imagine an entity called "Device" (think tablet, Smartphone etc.)
This "Device" again has a Primary Key called "ID" (You can "reuse" columnnames in different tables), and attributes like Type (Phone, Tablet), Phone-Number, Operating System etc.
.... and lastly it has something called a "Foreign Key", let's name it "PersonID"

Now, without a separate Table, how would you store data for one and the same person, if this person has an iPhone as well as an iPad?
Without separate tables you'd have to create a row for each device, despite it being the same person.
That's where Primary Keys and Foreign Keys come into play

In above entity "Device" the Foreign Key is basically a Pointer to the Primary Key of the Person to which it belongs.
That way you wouldn't have to enter the Persons Details into the device-table.

Relationsships are NOT "created" per se in a Database, they are "there", and it's your job to implement it, and to adhere to its rules.
though pretty much all Database-Systems provide you some basic tools/mechanisms to keep so called "referential integrity"

This helps, thank you. I have downloaded your sample database and with the DB Browser for SQlite, I am viewing it now. At first glance, I think I can see what you're doing here. I'm going to spend some more time with it and then get back to you. Thank you for this by the way. Your time is appreciated!

[The_Hobbyist]

I don't want you fine folks to think that I have given up - I haven't! In fact, with your help I've managed to make some pretty good progress. I have started to structure my database in a way that I believe is to standard. I've read about and have implemented the 3NF rules. See the attached photo of my hand sketched structure (I quickly took that photo before leaving for work - I was working on the actual database and the application right up until I had to leave for my day job). Sorry it shows as being sideways. It isn't in that orientation on my phone? Anyway...You get the point.

I have opted to start with a database that only has a portion of the data fields in it. Its less daunting for me to start with a smaller number of attributes and its helping. As I progress I'll add and expand the database.

All that said, I have managed to populate several of the text boxes in my form from the database (on form load). I have some tweaking to do for sure! I only got it to start working just as I had to leave the house today. I'm pretty happy with the progress and grateful for the help. You have all certai ly put me on the right path and with a little research, I am managing to make progress. Thanks for that.

In time I'm going to have so many more questions! Like, do I have to have a datagrid on the form? How do I create a search function? Adding or editing a field or row of data will be a concern down the road. So many questions.... For now, I'm happy to just load the database into a couple of corresponding text boxes....

Stay tuned. I'll be back - i just wanted to say thank you and share my progress report.

[Zvoni]

Are those Column-Names or Sample-Entries for the 3 tables left hand side (What you've written within each "square" after/below "ID")
Because i think you haven't understood how relationsships work

[techgnome]

Yeaahhh.... at first I thought hte OP had gotten it to a first normal form, which is fine, but rarely optimal ... but after tilting my head more and looking at it .... yeah... that's not quite right.

Maybe something like this would be better:

rockCollection - table
ID
StructureType -- Foreign Key (FKey) to StructureType table
FormationType -- Foreign Key (FKey) to FormationType table
Quantity
Name

----------
StructureType -- table
ID
Name

----------
FormationType -- table
ID
Name

----------
RockAttributes -- table
ID
RockCollectionID
HardnessA
HardnessB
DensityA
DensityB
Rariity

Although it could be argued that the RockAttributes could be moved in the to RockCollection table.

The other two type tables ... all they need is an ID and a Name .... The Name field then consists of Mineral, Crystal, and Rock. The other would be Sedimentary, Ingenious, Metamorphic.
From those tables, what you store in the RockCollection table are the IDs that correspond to which row it is. So the ID for Crystal & Metamorphic.


-tg

[Zvoni]

Yeaahhh.... at first I thought hte OP had gotten it to a first normal form, which is fine, but rarely optimal ... but after tilting my head more and looking at it .... yeah... that's not quite right.

Maybe something like this would be better:

rockCollection - table
ID
StructureType -- Foreign Key (FKey) to StructureType table
FormationType -- Foreign Key (FKey) to FormationType table
Quantity
Name

----------
StructureType -- table
ID
Name

----------
FormationType -- table
ID
Name

----------
RockAttributes -- table
ID
RockCollectionID
HardnessA
HardnessB
DensityA
DensityB
Rariity

Although it could be argued that the RockAttributes could be moved in the to RockCollection table.

The other two type tables ... all they need is an ID and a Name .... The Name field then consists of Mineral, Crystal, and Rock. The other would be Sedimentary, Ingenious, Metamorphic.
From those tables, what you store in the RockCollection table are the IDs that correspond to which row it is. So the ID for Crystal & Metamorphic.


-tg

That's pretty much, how my sample-DB looks like, except "Rarity" which has its own table ("table occurence")

[The_Hobbyist]

Are those Column-Names or Sample-Entries for the 3 tables left hand side (What you've written within each "square" after/below "ID")
Because i think you haven't understood how relationsships work

OK.....So I have spent most of my morning going over my database's structure. I think I understand what is going on here but since I work best with visual aids (sometimes reading/comprehension is difficult for me), I have drawn up my current structure. I do believe (if I understand correctly) that his now adheres to the 3NF standard. I'll share my photo below. Sorry if my penmanship was fading off towards the end - I'm embarrassed to admit how many attempts this took me today! In any event, the image is of 3 separate images, one beneath the next. The top two images should have been a single image however, I recognized an error in my 'Scientific_Data_Table' and therefore I drew that one up again... Anyway, I hope the visual helps to explain my work so far.

The 'Rock_Collection_Table' is where I pull all the tables together to create a complete dataset (if that terminology is correct, I don't know). It is from this table in which I populate my form's text boxes and combo boxes anyway.... More on that later I guess. I'm mostly focused on just getting this darn database structure correct and within the 3NF guidelines.

Thank you again for your patience everyone. It is greatly appreciated!

For now, I'm out the door and off to my day job! Cheers!

[Zvoni]

Now this look promising.
you‘re on the right path

[The_Hobbyist]

OK....So it would appear as though I have my database structure sorted out. It seems to be up to the 3NF standard I think... My next question obviously is how to go about the CRUD side of things? I reckon this is going to be some heavy stuff.

Common sense tells me that I will need to open a connection to the database (database.db). How to go about that and then what to do next is an enigma to me. I would have to assume that depending on which aspect of the CRUD I want to focus on, that will determine my direction. I suppose my first question in regards to the CRUD side is this: is there a function that should be written before the others? The READ function for example? Furthermore, should the database be preloaded on the form load event or is opening the database something that would only happen on a button click for example?

Where do I start?

Perhaps I should start a new thread for this?

[techgnome]

There was a time when the predominate way of doing thingswas to open the connection when the app starts, and then close it on shutdown. That was Vb6.

With .NET and connection pooling, now the recommended way is to open the connection, do your database oerations, clost the connection. What that DB operation is... varies. Could be one seelect to get data. Could be trhee or four selects to get data. Could be an update, or a delete, or a combination of selects, update, deletes, and inserts.... Open the connection, do what you need to do at that point, and then close it. It's like getting something out of thefridge. Open it, grab the cheese, the mayo, and ham ... close it... make your sandwitch, then open the fridge and put back the leftover ham and mayo, close.

As for what's next. Typically what I do is get ti to load the data and display it. Then insert new data, then edit it, and finally delete.

-tg

[The_Hobbyist]

OK. I've been watching You-Tube tutorials, reading and tinkering with code but I've mostly been just banging my head against the table for a few days now. Regarding the creation of a database "Search" function, I feel like I'm close but I'm not there just yet...

On my Form Load event, my datagrid populates with the data from the database, as intended. I can then click on the various lines within the datagrid and the text within the textboxes and combo boxes within my form change to correspond with the data from the grid. My issue seems to stem from the "Search" function that I am attempting to create. Using the text from a textbox, when I click on the button to search I get this error:

Data Binding Error: Cannot bind to the property or Column Name on the datasource: Parameter name: dataMember

I feel like it has something to do with the way in which I am calling upon two different tables ("ScientificData" and "Chemistry"). Please see my notes below, in the code.

My database structure:
1. Database name = Database.db
2. Tables ("The_Collection", "ScientificData" & "Chemistry")
3. Fields for "The_Collection" ("ID", "ScientificDataID" & "ChemistryID")
4. Fields for "ScientificData" ("ID", "Name", "DensityA", "DensityB", "HardnessA", "HardnessB", "Rarity" & "Magnetic")
5. Fields for "Chemistry" ("ID" & "ChemicalFormula")

(Note: The "ID" field in each table is the Primary Key & only the "ID" type is set to INTEGER while all the others are set to TEXT)

    Private Sub UpdateDataBiding(Optional cmd As SQLiteCommand = Nothing)

        Try
            If cmd Is Nothing Then
                command.CommandText = "SELECT * FROM The_Collection
                                        INNER JOIN ScientificData
                                        ON The_Collection.ScientificDataID = ScientificData.ID
                                        INNER JOIN Chemistry                           '<-------- I FEEL THE PROBLEM STEMS FROM THIS??
                                        ON The_Collection.ChemistryID = Chemistry.ID;" '<-------- I FEEL THE PROBLEM STEMS FROM THIS??

            Else
                command = cmd
            End If

            Dim adapter As New SQLiteDataAdapter(command)
            Dim dataSt As New DataSet()
            adapter.Fill(dataSt, "TheList")

            bindingSrc = New BindingSource()
            bindingSrc.DataSource = dataSt.Tables("TheList")

            Dim tb As TextBox
            Dim cb As ComboBox

            For Each ctr As Control In grpbxScientificData.Controls 

                If TypeOf ctr Is TextBox Then
                    tb = CType(ctr, TextBox)
                    tb.DataBindings.Clear()
                    tb.Text = ""
                End If

                If TypeOf ctr Is ComboBox Then
                    cb = CType(ctr, ComboBox)
                    cb.DataBindings.Clear()
                    cb.Text = ""
                End If
            Next

            cmbName.DataBindings.Add("Text", bindingSrc, "Name")
            cmbChemicalFormula.DataBindings.Add("Text", bindingSrc, "ChemicalFormula")
            txtHardnessA.DataBindings.Add("Text", bindingSrc, "HardnessA")
            txtHardnessB.DataBindings.Add("Text", bindingSrc, "HardnessB")
            txtDensityA.DataBindings.Add("Text", bindingSrc, "DensityA")
            txtDensityB.DataBindings.Add("Text", bindingSrc, "DensityB")
            cmbMagnetic.DataBindings.Add("Text", bindingSrc, "Magnetic")
            cmbRarity.DataBindings.Add("Text", bindingSrc, "Rarity")

            DataGridView1.Enabled = True
            DataGridView1.DataSource = bindingSrc
            DataGridView1.AutoResizeColumns(CType(DataGridViewAutoSizeColumnsMode.AllCells, DataGridViewAutoSizeColumnsMode))
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.Columns(0).Width = 60

        Catch ex As Exception
            MessageBox.Show("Data Binding Error: " & ex.Message.ToString())
        End Try

    End Sub



    Private Sub btnSearchDatabase_Click(sender As Object, e As EventArgs) Handles btnSearchDatabase.Click

        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If

        Try
            If String.IsNullOrEmpty(txtKeyword.Text.Trim()) Then
                UpdateDataBiding()
                Exit Sub
            End If

            sql = "Select * FROM ScientificData "
            sql &= "WHERE Name LIKE @keyword2 "
            sql &= "OR DensityA LIKE @keyword2 "
            sql &= "OR Magnetic = @keyword1 "
            sql &= "OR DensityB LIKE @keyword2 "
            sql &= "OR Rarity LIKE @keyword2 "
            sql = "Select * FROM Chemistry "                 '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "WHERE ChemicalFormula LIKE @keyword2 "   '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "ORDER by ID ASC"

            command.CommandType = CommandType.Text
            command.CommandText = sql

            command.Parameters.Clear()

            Dim KeywordString As String = String.Format("%{0}%", txtKeyword.Text)

            command.Parameters.AddWithValue("@Keyword1", txtKeyword.Text)
            command.Parameters.AddWithValue("@Keyword2", KeywordString)

            UpdateDataBiding(command)

        Catch ex As Exception
            MessageBox.Show("Search error: " & ex.Message.ToString(),
                                    "Error Message : TEST code",
        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            connection.Close()
            txtKeyword.Focus()
        End Try

    End Sub

Am I on the right path?

[Zvoni]

I‘m trying to understand how the SQL in your search-function is supposed to work?
You do realize that you’re overwriting the scientificdata SQL with the chemistry SQL?
and seeing those OR with LIKE reminds me of a tutorial i wrote for SQLite

[wes4dbt]

You need to provide the exact line of code that throws the error. Either step through the code with the debugger to find it or comment out the the Try/Catch and the program should stop with that line highlighted.

Also, don't know where "connection" and "command" come from, they must be global or form level variables. So there is no need to pass command as a parameter.

It's not clear what the relation between The_Collection and ScientificData tables is. Is it a one to one relation? If so, what's the need for The_Collection table?

[The_Hobbyist]

I‘m trying to understand how the SQL in your search-function is supposed to work?
You do realize that you’re overwriting the scientificdata SQL with the chemistry SQL?
and seeing those OR with LIKE reminds me of a tutorial i wrote for SQLite

Nope. I did not realize that I was writing over the ScientificData SQL with the Chemistry SQL. That would certainly be a problem! I don't know enough about this stuff to really know what SQL is or what it does. Lastly, I was essentially just following along with the video tutorial that I watched (https://www.youtube.com/watch?v=vzrE94oGXBs) and trying to adapt the code to my environment - obviously doing so without truly understanding it.

I'll spend some more time researching and reading about this. Regarding my previous database structure inquiry, I was directed to look into "Database Normalization". That keyword or phrase allowed me to better pin point my focus by providing me with the proper terminology relating to my plyte of my code. Might there be a specific term or phrase I can use to help me focus on this issue and to better understand what SQL is and what it is doing in here in my code?

You need to provide the exact line of code that throws the error. Either step through the code with the debugger to find it or comment out the the Try/Catch and the program should stop with that line highlighted.

Also, don't know where "connection" and "command" come from, they must be global or form level variables. So there is no need to pass command as a parameter.

It's not clear what the relation between The_Collection and ScientificData tables is. Is it a one to one relation? If so, what's the need for The_Collection table?

Back when I used VB6, I was able to run the code, line by line and pin point exactly where the errors were. While using Visual Studio 2019 and VB.NET, I have yet to figure out how to do so. I haven't spent a lot of time trying to figure it out to be fair. Furthermore, when I run the code provided, I get the error as indicated but unlike other errors, it doesn't direct me to any specific line number. I'm at a loss here.

The relationship between "The_Collection" & "ScientificData" is that they are both tables within the database. I'm not sure whether or not I can explain this quite right as I've only just sorted out the database structure and normalization (I think) but I'll give it a go. ScientificData is one of my several tables. It has fields in it which with headers like "Name", "Hardness", "Density" & "Magnetic" to name some. "The_Collection" is the table which calls upon all of the other tables in an effort to align all the data. For example is calls tables such as "ScientificData" & "Chemistry". Does that make sense?

Connection & Command are in the Public Class:

Imports System.Data.SQLite

Public Class frmMain
    Private dbCommand As String = ""
    Private bindingSrc As BindingSource

    Private dbName As String = "Database.db;"
    Private dbPath As String = Application.StartupPath & "\" & dbName
    Private conString As String = "Data Source=" & dbPath & "Version=3;New=False;Compress=True;"

    Private connection As New SQLiteConnection(conString)
    Private command As New SQLiteCommand("", connection)

    Private sql As String = ""
.
.
.

I'll bang my head a little more and then try to bring a more educated question to the table. Thank you for your patience!

[wes4dbt]

Nope. I did not realize that I was writing over the ScientificData SQL with the Chemistry SQL. That would certainly be a problem! I don't know enough about this stuff to really know what SQL is or what it does.

It really doesn't have anything to do with SQL, it's all about working with strings.

Here you are building a string called "sql"

            sql = "Select * FROM ScientificData "
            sql &= "WHERE Name LIKE @keyword2 "
            sql &= "OR DensityA LIKE @keyword2 "
            sql &= "OR Magnetic = @keyword1 "
            sql &= "OR DensityB LIKE @keyword2 "
            sql &= "OR Rarity LIKE @keyword2 "

Then you replace the contents of "sql" with

            sql = "Select * FROM Chemistry "                 '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "WHERE ChemicalFormula LIKE @keyword2 "   '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "ORDER by ID ASC"

.Net will also stop on the line throwing the error unless your using something to catch the error, which is what Try/Catch does. That's why I said you could comment out the Try/Catch for testing. But the better solution is to learn to use the Debugger which is located on the main menu under Debug.

As for you db design, I don't really know enough about what your trying to achieve. The main question is what type of relationship does each table have to the other. One to one, one to many, many to many. For example, if the SAME ChemistryData can be used for multiple ScientificData records, that a one to many relationship. But if each ChemistyData record will only have one matching ScienticData then that a one to one relationship.

[The_Hobbyist]

It really doesn't have anything to do with SQL, it's all about working with strings.

Here you are building a string called "sql"

            sql = "Select * FROM ScientificData "
            sql &= "WHERE Name LIKE @keyword2 "
            sql &= "OR DensityA LIKE @keyword2 "
            sql &= "OR Magnetic = @keyword1 "
            sql &= "OR DensityB LIKE @keyword2 "
            sql &= "OR Rarity LIKE @keyword2 "

Then you replace the contents of "sql" with

            sql = "Select * FROM Chemistry "                 '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "WHERE ChemicalFormula LIKE @keyword2 "   '<-------- OR I FEEL THE PROBLEM STEMS FROM THIS??
            sql &= "ORDER by ID ASC"

.Net will also stop on the line throwing the error unless your using something to catch the error, which is what Try/Catch does. That's why I said you could comment out the Try/Catch for testing. But the better solution is to learn to use the Debugger which is located on the main menu under Debug.

As for you db design, I don't really know enough about what your trying to achieve. The main question is what type of relationship does each table have to the other. One to one, one to many, many to many. For example, if the SAME ChemistryData can be used for multiple ScientificData records, that a one to many relationship. But if each ChemistyData record will only have one matching ScienticData then that a one to one relationship.

Very good. I'll work on this. Thank you!