none
No value given for one or more required parameters in C# while updating MS-Access RRS feed

  • Question

  • I'm pretty new to C# development and I'm trying to update an MS-Access database with my application. When I run my code I get the "No value given for one or more required parameters" error message on my execute statement. Every example I find online tells me that my SQL statement is set up correctly, and I've confirmed that my symbolic parameters actually contain data, so I'm not trying to update my database with empty or null values. Can anyone suggest a course of action for me to correct this problem? Thanks in advance!

            private void AddJobsAcconutNumberToNotes()
            {
                //Create an SQL query string to find all customer names in the Notes table without an account number
                String inputString = "SELECT Jobs.Account_Number, Notes.Account_Number, Notes.DisplayID, Jobs.Job_ID " +
                    "FROM Notes, Jobs " +
                    "WHERE Jobs.Job_ID = Notes.DisplayId " +
                        "AND Notes.Account_Number is null " +
                        "AND Jobs.Account_Number Is Not null ";
    
                try
                {
                    //Convert the SQL query string into database compatible instructions using OleDbCommand,
                    OleDbCommand cmd = new OleDbCommand(inputString, conn);
                    OleDbDataReader reader = cmd.ExecuteReader();
    
                    //Start reading through the result set
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var jobsAccountNumber = reader.GetInt16(0);
                            var notesDisplayID = reader.GetString(2);
                            var jobsJobID = reader.GetString(3);
    
                            randomCtr++;
                            this.Text = "AzFloodSquad Maintenance On = " + randomCtr + " Rows";
    
                            // No matching contact was found
                            if (jobsJobID == null)
                            {
                                //Do nothing and loop around
                            }
                            else
                            {
                                var updateQuery = "UPDATE Notes " +
                                    "SET Notes.[Account_Number] = ? " +
                                    "WHERE Jobs.[Job_ID] = ? AND Notes.[DisplayID] = ? ";
    
                                var convertedToDbCommands = new OleDbCommand(updateQuery, conn);
    
                                convertedToDbCommands.Parameters.AddWithValue("Jobs", jobsAccountNumber);
                                convertedToDbCommands.Parameters.AddWithValue("Jobs", jobsJobID);
                                convertedToDbCommands.Parameters.AddWithValue("Notes", notesDisplayID);
    
                                //Execute the query 
                                convertedToDbCommands.ExecuteNonQuery();   ERROR OCCURS HERE
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    error_message = ex.Message;
                    MessageBox.Show(error_message);
                }
                disconnectToolStripMenuItem.PerformClick();
                connectToolStripMenuItem.PerformClick();
                MessageBox.Show("The second Notes table account number validation is completed!");
            }
    
    

    Wednesday, December 5, 2018 3:43 PM

All replies

  • "Jobs"... and... "Jobs"???

    convertedToDbCommands.Parameters.AddWithValue("Jobs", jobsAccountNumber); convertedToDbCommands.Parameters.AddWithValue("Jobs", jobsJobID

    Also - if you use a Name of the parameter to specify a parameter value - you must have the name of the parameter in the statement. 

    And... you need a list of the tables if you use relation between tables.

    P.S. Why you didn't create normal DAL for two table, test it and create BO based on two objects  and form to display data? 


    Sincerely, Highly skilled coding monkey.

    Wednesday, December 5, 2018 4:07 PM
  • Hello, Highly skilled coding monkey,

    I changed the Jobs, Jobs to Job1 and Job2, and it made no difference. Here's what it looks like now:

                                convertedToDbCommands.Parameters.AddWithValue("Job1", jobsAccountNumber);
                                convertedToDbCommands.Parameters.AddWithValue("Job2", jobsJobID

    I'm a real novice at C# and I'm sure your other suggestions make perfect sense, but I don't understand what you're driving at because I'm so new. Can you explain your suggestions in a more basic way? Meanwhile, I'll do some Internet research to try an figure it out as well. Thanks!



    • Edited by csharpMind Wednesday, December 5, 2018 5:39 PM
    Wednesday, December 5, 2018 5:37 PM
  • "Also - if you use a Name of the parameter to specify a parameter value"

    Not for OLEDB (which is being used here). When using OLEDB it uses positioning instead of names so the query has to use ? while the parameter names are completely irrelevant. OLEDB enumerates the parameters in the order provided and replaces each ? with the corresponding value.

    The likely issue with the error here is that there are 2 parameters being passed because ParameterCollection uses a dictionary to store values. Changing the second parameter to another name (doesn't matter what) will cause 3 parameters to be passed and the query to be satisfied.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, December 5, 2018 5:38 PM
    Moderator
  • Hi csharpMind,

    Thank you for posting here.

    For your question, I want to confirm some questions with you.

    >> var updateQuery = "UPDATE Notes " +"SET Notes.[Account_Number] = ? " +"WHERE Jobs.[Job_ID] = ? AND Notes.[DisplayID] = ? ";


    What effect do you want to use with this statement?

    Best regards,

    Jack J Jun.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 6, 2018 8:19 AM
    Moderator
  • You have your SQL-statement:

    UPDATE Notes 
       SET Notes.[Account_Number] = ? 
    WHERE Jobs.[Job_ID] = ? AND Notes.[DisplayID] = ? 

    You use a '?' mark to specify where the value will be placed. There are no names - "Jobs", "Job1" or "Job2" - and nobody can match value to the proper position.

    So,

    - you may change the the statement to have named parameters in it and use a names

    or

    - you can use unnamed - positional - parameters, with a way it should be used.

    It's up to you which method you use. Just don't mix them - they are not the same.

    Please also read a note from CoolDadTx about OleDb - I didn't use this option as it give lot of potential problem. 

    There also a problem in SQL syntax - the statement you wrote contain an error. The best option to fix a problem - use some kind of SQL-Studio (Access(?), with debugger) to see where and what kind of error are there. As option - you can form a complete SQL-statement manually by adding required parts of strings, execute it and get an error message.

    ----

    In your code you have SQL-statement, Connections, execution and management of form controls. All in one place. It's possible to handle, when your task include work with just two tables. As soon as number of tables and controls increase - you will spend more and more time on trying to fix problems in the code. To avoid complexity of the code usually code are separated by "what we do here". DAL (data access layer) contain all manipulation with database (CRUD) and basic verification of data.

    Point of this separation is that you can wrote and test relatively simple code and use this code in the rest of application.


    Sincerely, Highly skilled coding monkey.


    Thursday, December 6, 2018 9:34 AM
  • Hello,

    The first step to figuring this out is to run the query inside of ms-access which is recommended rather than write the query in your code first.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 6, 2018 10:15 AM
    Moderator