Remove From My Forums

Retrieve data from database MYsql and problems with Format date

Question

User-909867351 posted

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

0

Sign in to vote

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
0

Sign in to vote
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

Asked by:

Retrieve data from database MYsql and problems with Format date

Question

All replies