locked
Retrieve data from database MYsql and problems with Format date RRS feed

  • Question

  • User-909867351 posted

    HI

    Please look at my code:

    //verifica se o seguro existe
            SqlDataSource2.SelectParameters.Clear();
            SqlDataSource2.SelectCommand = "select * from seguros where fracao=? and edificio=?";
            SqlDataSource2.SelectParameters.Add("@a", DropDownList1.Text);
            SqlDataSource2.SelectParameters.Add("@b", Session["edificio"].ToString());        
            DataView dvsqllist = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
            DataTable tablelist = dvsqllist.Table;
            SqlDataSource2.DataBind();
            System.Data.DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
    
    
            
            foreach (DataRow row in tablelist.Rows)
            {           
                txtApolice.Text = row["apolice"].ToString();
                txtCapitalSeguro.Text = row["capital_seguro"].ToString();
                txtDataValidade.Text = ((DateTime)row["data_validade"]).ToShortDateString();
                txtNotas.Text = row["notas"].ToString();
                txtPremio.Text = row["premio"].ToString();
                txtSeguradora.Text = row["seguradora"].ToString();
                txtTipoSeguro.Text = row["tipo_seguro"].ToString();
            }

    Here is my problem. I have one textBox with TextMode as Date and the format of my date is dd/mm/yyyy

     I retrieve the row["data_validade"] from Mysql database in the format yyyy-mm-dd therefore the txtDataValidade.Text dos not show the date. How can I solve this issue?

    Thank you

    Tuesday, December 3, 2019 5:31 PM

All replies

  • User475983607 posted

    Your code and description make little sense. 

    The result of the loop below is the last row in the tablelist which seems like a bug.

            foreach (DataRow row in tablelist.Rows)
            {           
                txtApolice.Text = row["apolice"].ToString();
                txtCapitalSeguro.Text = row["capital_seguro"].ToString();
                txtDataValidade.Text = ((DateTime)row["data_validade"]).ToShortDateString();
                txtNotas.Text = row["notas"].ToString();
                txtPremio.Text = row["premio"].ToString();
                txtSeguradora.Text = row["seguradora"].ToString();
                txtTipoSeguro.Text = row["tipo_seguro"].ToString();
            

    If you are having problems inserting a DateTime make sure to use proper parameter types. 

    Lastly, please use the Visual Studio debugger to double check your work.

    Tuesday, December 3, 2019 7:07 PM
  • User-719153870 posted

    Hi mariolopes,

    Here is my problem. I have one textBox with TextMode as Date and the format of my date is dd/mm/yyyy

     I retrieve the row["data_validade"] from Mysql database in the format yyyy-mm-dd therefore the txtDataValidade.Text dos not show the date. How can I solve this issue?

    Please refer to DateTime.ToString Method for information about how you can show your datetime in specific format.

    You can also check below demo:

    aspx:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:SqlDataSource ID="SqlDataSource2" ConnectionString="Data Source=.;Initial Catalog=DatabaseTestPool;Integrated Security=True" runat="server"></asp:SqlDataSource>
                ToShortDateString():<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
                ToString("dd/MM/yyyy"):<asp:TextBox ID="txtDataValidade" runat="server"></asp:TextBox>
            </div>
        </form>
    </body>
    </html>

    cs:

    public partial class DateTimeFormatDemo : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource2.SelectParameters.Clear();
                SqlDataSource2.SelectCommand = "select * from Users";
                DataView dvsqllist = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
                DataTable tablelist = dvsqllist.Table;
                SqlDataSource2.DataBind();
                foreach (DataRow row in tablelist.Rows)
                {
                    TextBox1.Text = ((DateTime)row["UDate"]).ToShortDateString();
                    txtDataValidade.Text = ((DateTime)row["UDate"]).ToString("dd/MM/yyyy");
                }
            }
        }

    User table in my demo whose date is in "YYYY-MM-DD" format:

    And below is the result of the demo:

    Best Regard,

    Yang Shen

    Wednesday, December 4, 2019 2:05 AM
  • User-909867351 posted

    Thank you all for your help. Unfortunately the problem is not solved

    First:

    I only have one record. Ok, the foreach is not good option but I don't know other way of doing this.

    Second

    If my textBox is TextMode=Date I can't show the date but if I remove the TextMode option it works. The textMode=Date is important because I allow the user to change the date and if it is configured as TextMode="Date" he doesn't make mistakes.

    Any idea? I use google Chrome

    Friday, December 6, 2019 5:52 PM
  • User-719153870 posted

    Hi mariolopes,

    First:

    I only have one record. Ok, the foreach is not good option but I don't know other way of doing this.

    If your field has only one record then your foreach code should be just fine.

    Second

    If my textBox is TextMode=Date I can't show the date but if I remove the TextMode option it works. The textMode=Date is important because I allow the user to change the date and if it is configured as TextMode="Date" he doesn't make mistakes.

    Sorry i missed the information that your textbox is in the textmode="date" format.

    First of all, you said it's in "dd/mm/yyyy" format but in fact it's in "mm/dd//yyyy", right?

    Seems the textmode="date" textbox only accept the value which is in "yyyy-MM-dd" format, you can see it here.

    Also, press the F12 of your chrome to open the devops and you will see the warning message:

    Thus, the solution for your page to show the date from your database to a textbox with the textmode="date" should be like below which is converting the date to the "yyyy-MM-dd" format:

    aspx:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:SqlDataSource ID="SqlDataSource2" ConnectionString="Data Source=.;Initial Catalog=DatabaseTestPool;Integrated Security=True" runat="server"></asp:SqlDataSource>
                <asp:TextBox ID="txtDataValidade" TextMode="Date" runat="server"></asp:TextBox>
            </div>
        </form>
    </body>
    </html>

    cs:

    protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource2.SelectParameters.Clear();
                SqlDataSource2.SelectCommand = "select * from Users";
                DataView dvsqllist = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
                DataTable tablelist = dvsqllist.Table;
                SqlDataSource2.DataBind();
                foreach (DataRow row in tablelist.Rows)
                {
                    txtDataValidade.Text = ((DateTime)row["UDate"]).ToString("yyyy-MM-dd");
                }
            }

    Best Regard,

    Yang Shen

    Monday, December 9, 2019 1:53 AM