Resolved conversion failed when converting date string from database

Omer Butt

Active member
Joined
Sep 6, 2021
Messages
29
Programming Experience
Beginner
I have a Chart on Dashboard where I want when the dashboard load the Chart displays the data according to database.
Below is a Table I created named Medic
Databese Table:
Create table Medic(
    Med_ID int Primary Key Identity (1,1),
    Med_Name varchar(250) not null,
    M_Date varchar(250) not null,
    E_Date varchar(250) not null,
);
In this Already have saved one record successfully through Add Medic Form
Where the Manufacture Date and Expiry Date are BunifuDatePicker

BunifuDatePicker Name is Dash_ValidExpire_Chart

Now want that record to be displayed on Dashboard in a chart to show Valid and expiry Medicine.

Below is a code that I did on dashboard load event

Code for Chart to Display Data on Dashboard Load:
DbConnect Conn = new DbConnect();
Int64 Count;
        
        private void P_DashboardUC_Load(object sender, EventArgs e)
        {
            LoadChart();
        }
        public void LoadChart()
        {
            try
            {
                string ChartQuery = "select count(Med_Name) from Pharmacist_Medicine where E_Date >= getdate()";
                SqlDataAdapter da = new SqlDataAdapter(ChartQuery, Conn.Connect);
                DataSet ds = new DataSet();
                da.Fill(ds);
                Count = Int64.Parse(ds.Tables[0].Rows[0][0].ToString());
                this.Dash_ValidExpire_Chart.Series["Valid Medicines"].Points.AddXY("Medicine Validity Chart", Count);
                Dash_ValidExpire_Chart.DataSource = ds;
            }
            catch(Exception msg)
            {
                MessageBox.Show(msg.Message);
            }
        }



Below is the Image of a Chart in Which I want the data to be displayed shown in the image is a default chart image

chart.jpg
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,250
Location
Sydney, Australia
Programming Experience
10+
Why would you have a table that stores dates as text in the first place? Do you store numbers as text? Of course not, because that would be silly. You use numeric data types to store numbers like any sane person. Why would you not do the same thing for dates?
 

Omer Butt

Active member
Joined
Sep 6, 2021
Messages
29
Programming Experience
Beginner
Why would you have a table that stores dates as text in the first place? Do you store numbers as text? Of course not, because that would be silly. You use numeric data types to store numbers like any sane person. Why would you not do the same thing for dates?
Dates are not just numbers Dates includes Char and Strings as well Like Comma Day name and Month Name, If I will not use varchar and use Date as a variable then how to parse date while passing a parameter of store procedure here:
AddMedicine.Parameters.AddWithValue("Expry_Date", Txt_AddMedicine_ExpiryDate.Text);
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,250
Location
Sydney, Australia
Programming Experience
10+
Dates are not just numbers Dates includes Char and Strings as well Like Comma Day name and Month Name, If I will not use varchar and use Date as a variable then how to parse date while passing a parameter of store procedure here:
AddMedicine.Parameters.AddWithValue("Expry_Date", Txt_AddMedicine_ExpiryDate.Text);
Actually, dates are just numbers under the hood. That's the point. Databases have dedicated data types for dates and/or times for a reason. Do you think that SQL Server has all these data types for no good reason? Use the proper data type(s) in your database and then use the .NET DateTime type in your code to work with the data. If you are using Windows Forms then you probably ought to be using a DateTimePicker in the first place, rather than a TextBox, because the Value property is already type DateTime. If you must get the user input as text, the DateTime type has methods for converting a string to DateTime, which you can then pass on to the database.
 

Omer Butt

Active member
Joined
Sep 6, 2021
Messages
29
Programming Experience
Beginner
Actually, dates are just numbers under the hood. That's the point. Databases have dedicated data types for dates and/or times for a reason. Do you think that SQL Server has all these data types for no good reason? Use the proper data type(s) in your database and then use the .NET DateTime type in your code to work with the data. If you are using Windows Forms then you probably ought to be using a DateTimePicker in the first place, rather than a TextBox, because the Value property is already type DateTime. If you must get the user input as text, the DateTime type has methods for converting a string to DateTime, which you can then pass on to the database.
Yes, I am using DateTimePicker not a textbox. And I updated my database and store procedure with DateTime Data type for Expiry and Manufacturing Date and tried added a medicine this time which is not adding a medicine and giving an exception error that is: Error Converting Datatype nvarchar to datetime.

is there anything to do with this line of code to be parsed or converted if so please guide how to ?
AddMedicine.Parameters.AddWithValue("Expry_Date", Txt_AddMedicine_ExpiryDate.Text);
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,250
Location
Sydney, Australia
Programming Experience
10+
If you are using a DateTimePicker control then why are you using a "Txt" prefix on the name? The only thing worse than using Hungarian Notation is using it incorrectly. The whole point of the prefix is to tell you what type the object/value is and "Txt" or the like is supposed to indicate a TextBox control. You're using a pointless system that indicates a type in order to indicate the wrong type.

Apart from that, I already told you what you should be doing. You quoted my post but apparently didn't read it. Go back and read it properly and you will have your answer.
 

Omer Butt

Active member
Joined
Sep 6, 2021
Messages
29
Programming Experience
Beginner
ok Thank you Sir, I simply used Convert.DateTime to convert AddMedicine.Parameters.AddWithValue("Expry_Date", Convert.ToDateTime(Txt_AddMedicine_ExpiryDate.Text)); and its now working thanks for helping
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,250
Location
Sydney, Australia
Programming Experience
10+
ok Thank you Sir, I simply used Convert.DateTime to convert AddMedicine.Parameters.AddWithValue("Expry_Date", Convert.ToDateTime(Txt_AddMedicine_ExpiryDate.Text)); and its now working thanks for helping
No, that's not it. I told you what to do and I directed you go back and read it again. Can you not see what's right in front of you?
If you are using Windows Forms then you probably ought to be using a DateTimePicker in the first place, rather than a TextBox, because the Value property is already type DateTime.
If I could make it flash then I would have done that too. I understand that people don't know everything about programming and beginners know even less but when someone provides you with the information you need to take notice of it.
 
Top Bottom