Update SQL Database help

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
45
Programming Experience
Beginner
2005 is showing in console but the DB update isnt working.
Its straightforward to you, but not to me i am learning. Here is a total example of my code
C#:
 try
            {

                 string Convert_FromOADate(string dateFormat)
                {
                    bool parsed = double.TryParse(dateFormat, out double parsed_double);
                    if (parsed)
                    {
                        DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
                        return ConvertToDate.ToShortDateString();
                    }
                    return string.Empty;
                }

                string Conversion = Convert_FromOADate("38456");
                if (Conversion != string.Empty)
                 Console.WriteLine(Conversion);


                FbConnectionStringBuilder rfs = new FbConnectionStringBuilder();
                rfs.DataSource = "localhost";
                rfs.Port = 3050;
                rfs.Database = @"192.168.7.57:C:\databases2\TEST2.IDB";
                rfs.UserID = "sysdba";
                rfs.Password = "masterkey";
                rfs.ServerType = FbServerType.Default;

                FbConnection db = new FbConnection(rfs.ToString());
                db.Open();

            


                String deletecommod = db.Query<String>(" UPDATE STOCKDET SET STOCKDET.QTY = 0 WHERE CAST (STOCKHDR.RECEIVED AS DATETIME)  >= -30, GETDATE()").FirstOrDefault();
                Console.WriteLine("Successful");

                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
                mail.From = new MailAddress("alerts@paterson-arran.com");
                mail.To.Add("tdignan@paterson-arran.com");
                mail.Subject = "RFS Schedule Clean successful";
                mail.Body = "RFS Stock Cleaned <br> Stock older than 30 days has successfully been written off.</br>";

                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential("alerts@paterson-arran.com", "");
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);


      
              

            }
            catch (Exception ex)
            {
                Console.WriteLine("Not successful");
                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");

                mail.From = new MailAddress("alerts@paterson-arran.com");
                mail.To.Add("tdignan@paterson-arran.com");
                mail.Subject = "RFS Stock Det Clean failed to update stock >30 days to 0";
                mail.Body = "Exception Details" + ex.ToString();

                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential("alerts@paterson-arran.com", "");
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);
            }

      


        }
    }
}
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
466
Location
UK
Programming Experience
10+
Dear oh dear.... Pick up a book on C# basics today.

This :
Should be replaced with a date you received from your database. And this does not belong inside a method. It is a function and is designed to be used as one. Paste this BELOW one of your other methods.
C#:
        private string Convert_FromOADate(string dateFormat)
        {
            bool parsed = double.TryParse(dateFormat, out double parsed_double);
            if (parsed)
            {
                DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
                return ConvertToDate.ToShortDateString();
            }
            return string.Empty;
        }
As par my instructions, this code would go inside a method which receives your date variables :
C#:
            string Conversion = Convert_FromOADate("38456");
            if (Conversion != string.Empty)
                Console.WriteLine(Conversion);
And to convert a double value from your database date table, you simply get the receiving double value variable and insert it here string Conversion = Convert_FromOADate("38456");. Conversion will then hold your date in normal date format "year/month/day".

To query against your database, you simply write the reverse of the code I gave you to convert a string formatted date to double. Something to the effect of this :
C#:
            DateTime dateTime = new DateTime(2019, 6, 4);
            double date = Convert.ToSingle(dateTime.ToOADate());
            Console.WriteLine(date.ToString());
 

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
45
Programming Experience
Beginner
I have picked up a book on C# basics.
Clearly i am lacking the knowledge. I will give this another try once i have finished the book.
Thanks for taking time to help me.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
461
Location
Virginia Beach, VA
Programming Experience
10+
This isn't a matter of learning C#. It's a matter of understanding logic and converting between units.

Think of it this way. Let's say you had a database filled with the daily average temperatures for various cities. The temperatures are all in Celsius. You are asked to find all the cities whose average temperature is between 70 and 75 Fahrenheit. How would you go finding those cities if you aren't allowed to modify the temperature column?

In the case of your specific problem, instead of temperature you are dealing with time. The database is using a unit of time measured in days since some reference date, while you are trying to perform a query using a unit of measure based on a number of years, months, and days since a different reference date.

I highly recommend solving this problem with pen and paper first using just a small number of rows of representative data. Once you understand what you need to do using pen and paper, then start looking at how to implement it the naive way: read all the data into memory, do the updates in memory, and then only write back the changed data to the database. Once you figure that out, then look at how to write a SQL UPDATE command so that all the updates are performed by the database engine on the database server.
 
Top Bottom