Procedure did not return an expected value

[Lin100]

After the procedure Obtain_Property_ID has ran, the Prop_ID suppose to be some value from 1 to 5, but instead it remains a 0.

string Prop_ID = "0";
Obtain_Property_ID(Prop_Name, Prop_ID);
R1.Reservation_Number.Text = "R-" + Prop_ID; <<--- Suppose to be a 3, but it has a value of 0.

       private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
           string Apartment_Status;
           DateTime Date_Reserved = DateTime.Today;
           MessageBox.Show("Date_Reserved =  " + Date_Reserved);

           object value = this.dataGridView2.CurrentRow.Cells[7].Value.ToString();
           Apartment_Status = value.ToString();
           MessageBox.Show("Apartment_Status =  " + Apartment_Status);

           switch (Apartment_Status)
             {
                case "Reserved":
                  Reservation2 R2 = new Reservation2();
                  R2.Show();
                break;

                case "Vacant":
                  Reservation1 R1 = new Reservation1();
                  R1.Property_Name.Text = this.dataGridView2.CurrentRow.Cells[0].Value.ToString();

                  string Prop_Name = R1.Property_Name.Text;
                  string Prop_ID = "0";
                  Obtain_Property_ID(Prop_Name, Prop_ID);

                  R1.Reservation_Number.Text = "R-" + Prop_ID;
                  R1.Unit_Number.Text = this.dataGridView2.CurrentRow.Cells[1].Value.ToString();
                  R1.Date_Reserved.Text = Date_Reserved.ToString();
                  R1.Lease_Months.Text = "6";
                  R1.Reservation_Cancelled.Text = "No";
                  R1.Show();
                break;

                default:
                break;
             }

            void Obtain_Property_ID(string Prop_Name, string Prop_ID)
            {
               OleDbConnection con = new OleDbConnection();
               con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
               con.Open();
               MessageBox.Show("Obtain_Property_ID procedure. Prop_Name =  " + Prop_Name);
               OleDbCommand cmd = new OleDbCommand();
           
               cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = '" + Prop_Name + "' ";

               cmd.Connection = con;
               OleDbDataAdapter da = new OleDbDataAdapter(cmd);
               DataSet ds = new DataSet();
               da.Fill(ds);

               Prop_ID = (string)ds.Tables[0].Rows[0].ItemArray[0];
               MessageBox.Show("Property_ID = " + Prop_ID);   //This showed a 3, and it is correct
               //The MessageBox did showed a correct number of 3, but
               //after it exit the procedure, the variable Prop_ID no longer has value of 3
               // but a 0.  
            }
        }

 

[Skydiver]

Parameters in C# are by-value by default. So the easiest way to think of this is that all parameters are local variables. So any changes you make within the method like changing what string is referenced by Prop_ID on line 53 will only have a local effect. If you want parameters to be passed by reference, use the ref modifier on them.

ref keyword - C# Reference - C#

ref keyword - C# Reference

learn.microsoft.com

 

[Skydiver]

As an aside, what are called "procedures" in Pascal and Visual Basic, and "functions" in C/C++, are called "methods" in C#. Same concept, different jargon.

 

[Lin100]

Thank you Skydiver for your help.

 

[cjard]

Your code should look more like this:

string ObtainPropertyID(string propName){
 
 
               using var con = new OleDbConnection();
               con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
               con.Open();
         
               using var cmd = new OleDbCommand();
      
               cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
               cmd.Parameters.AddWithValue("@pn", propName); //letting this slide for now, but don't use it with sql server

               cmd.Connection = con;

               return (string)cmd.ExecuteScalar();
}

Fixed:

* Non compliant naming conventions
* Lack of "using" statements to close/dispose command and connection
* SQL Injection hacking security flaw
* Unnecessary use of a data adapter and datatable to download a single value
* Incorrect method of returning values from a c# method

In C# you can only return one thing. If you reach a point where you're thinking "I need to return 3 bits of info; the person's name, their height and their birthday" then you encapsulate all those three things in a class that represents the person and you return one object of that type

Person GetPerson(int id){

    // get from db by id, select name, height, birthday from person where id = @id

    var p = new Person();
    p.Name = (string)dt.Rows[0]["name"];
    p.Height = (int)dt.Rows[0]["height"];
    p.DateOfBirth = (DateTime)dt.Rows[0]["birthday"];
    return p; //return one person, which is 3 bits of info
}

 

[cjard]

If you want parameters to be passed by reference, use the ref modifier on them.

ref keyword - C# Reference - C#

ref keyword - C# Reference

learn.microsoft.com

it might be preferable to avoid teaching people this way of returning values though; once shown a way that they get working people tend to find ways to build on it and

void GetPeople(string byLastName, ref int[] peopleIds, ref string[] firstNames, ref DateTime[] birthdays)

would be a travesty indeed..

 

[Skydiver]

Lol! But that exactly what a beginning C or JavaScript programmer would do in their respective languages.

But we expect better from fledgling C# programmers so we should teach them good habits early.

 

[Lin100]

Your code should look more like this:

string ObtainPropertyID(string propName){
 
 
               using var con = new OleDbConnection();
               con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
               con.Open();
        
               using var cmd = new OleDbCommand();
     
               cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
               cmd.Parameters.AddWithValue("@pn", propName); //letting this slide for now, but don't use it with sql server

               cmd.Connection = con;

               return (string)cmd.ExecuteScalar();
}

Fixed:

* Non compliant naming conventions
* Lack of "using" statements to close/dispose command and connection
* SQL Injection hacking security flaw
* Unnecessary use of a data adapter and datatable to download a single value
* Incorrect method of returning values from a c# method

In C# you can only return one thing. If you reach a point where you're thinking "I need to return 3 bits of info; the person's name, their height and their birthday" then you encapsulate all those three things in a class that represents the person and you return one object of that type

Person GetPerson(int id){

    // get from db by id, select name, height, birthday from person where id = @id

    var p = new Person();
    p.Name = (string)dt.Rows[0]["name"];
    p.Height = (int)dt.Rows[0]["height"];
    p.DateOfBirth = (DateTime)dt.Rows[0]["birthday"];
    return p; //return one person, which is 3 bits of info
}

Hi Cjard. I did as you have suggested and I have 3 errors.
The first two errors mentioned using C# 7.3

Based on the error description below (Error CS8370) is this correct
that the solution is to choose .NET Framework 4.8 as shown below ?
View Menu --> Solution Explorer --> Right-Click Project Name -->
Properties --> Application --> .NET Framework 4.8

As for Warning CS0618, I went to the suggested website but
the solution is not there.

//////////////////////////////////////////////////////////////////

Error CS8370 Feature 'using declarations' is not available in C# 7.3. Please use language version 8.0 or greater.

Error CS8370 Feature 'using declarations' is not available in C# 7.3. Please use language version 8.0 or greater.

Warning CS0618 'OleDbParameterCollection.Add(string, object)' is obsolete: 'Add(String parameterName, Object value) has been deprecated.
Use AddWithValue(String parameterName, Object value). What's obsolete in .NET Framework - .NET Framework'

//////////////////////////////////////////////////////////////////

  string Obtain_Property_ID(string Property_Name)
    {
       using var con = new OleDbConnection();      //ERROR CS8370
       con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
       con.Open();

       using var cmd = new OleDbCommand();       //ERROR CS8370

       cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
       cmd.Parameters.Add("@pn", Property_Name);  //Warning    CS0618

       cmd.Connection = con;
       return (string)cmd.ExecuteScalar();
    }

 

[cjard]

Sorry, I tend to write .net 6+ these days; constructs like using var x = ...; aren't available in older .net

Try it like:

string Obtain_Property_ID(string Property_Name)
    {
      using(var con = new OleDbConnection())
      using(var cmd = new OleDbCommand()){
       con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
       con.Open();

     

       cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
       cmd.Parameters.AddWithValue("@pn", Property_Name);

       cmd.Connection = con;
       return (string)cmd.ExecuteScalar();
      }
    }

Please_Dont_Use_Underscores_To_Separate_Every_Word_In_Method_Names

TheConventionIsToDoItLikeThis

 

[Skydiver]

Please_Dont_Use_Underscores_To_Separate_Every_Word_In_Method_Names

Yes. Snake_Case in not the same as PascalCase.

 

[cjard]

You can shortcut your life some by using Dapper The code for retrieving your string using Dapper would be something like:

  using(var c = new using OleDbConnection())
    return c.ExecuteScalar<string>("SELECT Property_ID FROM Property WHERE Property_Name = @pn", new { pn = ... });

 

[Lin100]

Sorry, I tend to write .net 6+ these days; constructs like using var x = ...; aren't available in older .net

Try it like:

string Obtain_Property_ID(string Property_Name)
    {
      using(var con = new OleDbConnection())
      using(var cmd = new OleDbCommand()){
       con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
       con.Open();

    

       cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
       cmd.Parameters.AddWithValue("@pn", Property_Name);

       cmd.Connection = con;
       return (string)cmd.ExecuteScalar();
      }
    }

Please_Dont_Use_Underscores_To_Separate_Every_Word_In_Method_Names

TheConventionIsToDoItLikeThis

Thank you Cjard and Skydiver for your help.
The code has to include ref string Property_ID in the method declaration otherwise Property_ID will always be 0.

{
      ....
      string Property_ID = "0";
      Obtain_Property_ID(ref Property_Name, ref Property_ID);
}

   string Obtain_Property_ID(ref string Property_Name, ref string Property_ID)
    {
       using (var con = new OleDbConnection())
       using (var cmd = new OleDbCommand())
        {
           con.ConnectionString = ConfigurationManager.ConnectionStrings["Apartment_Management.Properties.Settings.AMS_2007ConnectionString"].ToString();
           con.Open();

           cmd.CommandText = "SELECT Property_ID FROM Property WHERE Property_Name = @pn ";
           cmd.Parameters.AddWithValue("@pn", Property_Name);

           cmd.Connection = con;
           return Property_ID = (string)cmd.ExecuteScalar();
       }
   }

 

[cjard]

The code has to include ref string Property_ID in the method declaration otherwise Property_ID will always be 0.

No

The method returns the value and the calling code captures it

      string propertyID = ObtainPropertyID(propertyName);

As a beginner, don't use ref. If you've had to use ref to get something working you've probably done it wrong.

How can it be proven? Look at all Microsoft's code you're using to read from your database, or manipulate your Ui. Look at every method you've called (every name that appears to the left of a parentheses) that you didn't write; Microsoft have written nearly the completely entirety of .net itself without using ref

 

[Lin100]

No

The method returns the value and the calling code captures it

      string propertyID = ObtainPropertyID(propertyName);

As a beginner, don't use ref. If you've had to use ref to get something working you've probably done it wrong.

How can it be proven? Look at all Microsoft's code you're using to read from your database, or manipulate your Ui. Look at every method you've called (every name that appears to the left of a parentheses) that you didn't write; Microsoft have written nearly the completely entirety of .net itself without using ref

Thank you Cjard for your help. I got the code working just as you have prescribed. I will use what you have showed me from now on. The code
that you have showed me is much simpler and cleaner and of course
it is less coding.

Currently, when create a new project, Visual Studio used .NET 4.7.2
1) When do I used 4.7.2 and when do I used 4.8 ?
2) When I changed from .NET 4.7.2 to .NET 4.8 Visual Studio flagged
many errors in the code where previously there were no errors.

View Menu --> Solution Explorer --> Right-Click Project Name -->
Properties --> Application --> .NET Framework 4.8

 

[cjard]

Use 4.8 all the time

You'd need to tell us what some of the errors are