0

I have a C# program that uses SQLite in a UWP application. I have 2 classes, my main class and my sql container class.

My sql container class (SQLManage) has the following code:

private void totalWorthBtn_Click(object sender, RoutedEventArgs e)
        {
            // SELECT sum(Artwork.worth) as totalGalleryWorth, Gallery.galleryName from Gallery INNER JOIN Artwork on Gallery.GalleryID = Artwork.galleryiD where Gallery.galleryName = 'CatLand';"
            dataManager.ArtworkAvgQuery("SELECT round(avg(Artwork.worth),2) as avgArtworkWorth, Gallery.galleryName from Gallery INNER JOIN Artwork on Gallery.GalleryID = " +
                "Artwork.galleryiD where Gallery.galleryName = '\"" + $"{Input.Text}" + "\"'; ");

            while (dataManager.queryResult.Read())
            {
                
                if (firstPass == true) { counter++; }

                Debug.WriteLine(dataManager.queryResult.GetDouble(0));
                Debug.WriteLine(dataManager.queryResult.GetString(1));
            }
            firstPass = false;
            dataManager.database.Clos

This is the method where I try and use Input.Text (a textbox) to be used for a where clause.

My schema has the following inserts into 3 tables

        public void CreateArtists()
        {
            dataManager.InsertIntoArtist(1, "Jimmy", "2003-07-11", "Alive");
            dataManager.InsertIntoArtist(2, "Johnny", "1998-03-11", "Alive");
            dataManager.InsertIntoArtist(3, "Bonny", "2007-05-12", "Alive");
            dataManager.InsertIntoArtist(4, "Jesus", "0000-12-25", "InCatHeaven");
        }

        public void CreateGalleries()
        {
            dataManager.InsertIntoGallery(1, "FunLand", "37 DateLand St");
            dataManager.InsertIntoGallery(2, "MiseryLand", "38 DateLand St");
            dataManager.InsertIntoGallery(3, "DisneyLand", "39 DateLand St");
            dataManager.InsertIntoGallery(4, "CatLand", "40 DateLand St");
        }

        public void CreateArtwork()
        {
            dataManager.InsertIntoArtwork(1, 4, 4, "Statue", 30, 30, 5, 15000);
            dataManager.InsertIntoArtwork(2, 4, 4, "Painting", 5, 15, 2, 75500);
            dataManager.InsertIntoArtwork(3, 2, 2, "Painting", 15, 15, 2, 175670);
        }

I am trying to have the database with userInput for the where of some of these queries, I was trying to have the user be able to enter into a textbox lets say 'catland' and the SQL would find that table and result in it, instead of manually being done with literal values.

I am just not sure if this can be done, or if this is a good way of going around it. I'm quite new to SQL and am trying to get a better understanding of how this works. I would appreciate if you guys could help me go in the right direction.

In terms of the error I am getting when I click the total worth button when Input.Text is there I get the following Error:

System.InvalidOperationException: 'The data is NULL at ordinal 0. This method can't be called on NULL values. Check using IsDBNull before calling.'

I am not too sure how to fix this, can you please help me <3

Captured Night
  • 27
  • 7
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jun 14 '22 at 15:47
  • You should study how to use parameters and all the problems with that complex code to write the Input.Text will disappear together with a more serious problem called Sql Injection – Steve Jun 14 '22 at 15:50
  • See https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements. Also you shouldn't cache things like connections and readers. Instead dispose them with `using` – Charlieface Jun 14 '22 at 15:55
  • When I enter in the where for example 'CatLand' it works fine though – Captured Night Jun 14 '22 at 15:55
  • Yes of course it does, it's a literal value. But you shouldn't inject user values. Instead use parameters, see link above – Charlieface Jun 14 '22 at 15:56
  • I originally had the query in a query command in my seperate class that did the query but wasn't sure how I'd access input.text from that separate file – Captured Night Jun 14 '22 at 15:58
  • I have my sql code wrapped in a DAL in another class and then call that in the main – Captured Night Jun 14 '22 at 16:01
  • 1
    Don't use `input.Text`. I don't know what `input` is but I assume it's some form control. Think of getting input from the form and calling SQL as two unrelated things and write them as two entirely separate parts of your code. When writing the SQL part, pretend that the form doesn't exist. You don't know where the values are coming from. If you tie those things together the code becomes more complicated and harder to test. What if you want to execute the same SQL but the values don't come from a UI form? – Scott Hannen Jun 14 '22 at 16:02
  • I have sql queries inserted in that all work with other sets of data, I just want to try get it to work with user input – Captured Night Jun 14 '22 at 16:04
  • 1
    *"I just want..."* - but it's never that simple, and people are trying to open your eyes to that. What you're doing is tightly bound and extremely unsafe. It's in your best interest to learn how to eliminate these flaws. On some level, this isn't just a Q&A site... professional developers are trying to elevate the craft by educating others so that less bad code ends up out in the world and software can be more trustworthy. – madreflection Jun 14 '22 at 16:14
  • Yeah I do understand that I just wasn't sure if I was being misunderstood and I wasn't being clear enough or if it was as simple as I had assumed – Captured Night Jun 14 '22 at 16:20
  • Not so much that you're being misunderstood, more that you haven't included enough information for anyone to provide meaningful help. We know nothing about your schema or what `dataManager` is., and you haven't provided the *exact* error message you're getting, only a paraphrasing of it (and possibly an incorrect and misleading interpretation). We can only see what you've posted, which contains at least one problem that needs to be nipped in the bud. – madreflection Jun 14 '22 at 16:26
  • Im sorry, I added more information and I hope this helps, I included the two code files and a better description along side the actual error I get <3 – Captured Night Jun 14 '22 at 16:41
  • You added a lot more code, but now it's too much. A sizeable amount isn't related to the problem. What you post needs to be targeted to the problem, a [mre]. – madreflection Jun 14 '22 at 16:58
  • 1
    I should note that the `SQLManage` class represents an anti-pattern. You're using the `Sqlite*` classes all wrong. The class should store the connection string, not a `SqliteConnection` object. Then it should create a new `SqliteConnection` within each method, and return the results (not `void`). Don't store or expose the reader, either. Use the reader in the method and return the results. – madreflection Jun 14 '22 at 17:57

0 Answers0