insert date in wrong way

debugcn Published at Dev

DR.P

im trying to insert into table date in this type dd/mm/yyyy. in the mysql i set the data type to DATE. when i click add and insert the date this error is shown. "mysql.data.types.mysqlconversionexpection {"Unable to convert MySQL date/time value to System.DateTime"}"

i cant find the mistake. thanks alot!!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;


namespace WebApplication1
{
    public partial class usageDisp : System.Web.UI.Page
    {
        string connectionstring = @"Data Source=localhost; Database=globaldotdb; user ID=root; Password=peleg1708";

        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                //check
                BindData();

            }
        }
        private void BindData()
        {         
            using (MySqlConnection cn = new MySqlConnection(connectionstring))
            {
                MySqlDataAdapter adp = new MySqlDataAdapter(("SELECT  tblusage.codeUsage,tblcustom.Customer, tblvendor.Vendor, tblusage.dateStart, tblusage.dateEnd, tblregion.Region, tblservice.Service, tblservice.unit, tblusage.isSecure,tblusage.Usages FROM     ((((tblvendor INNER JOIN tblusage ON tblvendor.codeVendor = tblusage.codeVendor) INNER JOIN  tblservice ON tblusage.codeService = tblservice.codeService) INNER JOIN  tblregion ON tblusage.codeRegion = tblregion.codeRegion) INNER JOIN  tblcustom ON tblusage.codeCust = tblcustom.codeCust)"), cn);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    gv.DataSource = dt;
                    gv.DataBind();



                }
            }
        }

        protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int codeusage = int.Parse(gv.DataKeys[e.RowIndex].Value.ToString());

            deleteusage(codeusage);
            BindData();
        }

        private void deleteusage(int codeusage)
        {
            using (MySqlConnection cn = new MySqlConnection(connectionstring))
            {
                string query = "DELETE FROM tblusage WHERE codeUsage=" + codeusage + " ";
                MySqlCommand cmd = new MySqlCommand(query, cn);
                cn.Open();
                cmd.ExecuteNonQuery();

            }
        }

        protected void gv_DataBound(object sender, EventArgs e)
        {
            DropDownList DDLCu = gv.FooterRow.FindControl("DDLCu") as DropDownList;
            DropDownList DDLVe = gv.FooterRow.FindControl("DDLVe") as DropDownList;
            DropDownList DDLSe = gv.FooterRow.FindControl("DDLSe") as DropDownList;
            DropDownList DDLRe = gv.FooterRow.FindControl("DDLRe") as DropDownList;


            using (MySqlConnection cn = new MySqlConnection(connectionstring))
            {
                MySqlDataAdapter Cadp = new MySqlDataAdapter(("SELECT * from tblcustom"), cn);
                DataTable Cdt = new DataTable();
                Cadp.Fill(Cdt);
                if (Cdt.Rows.Count > 0)
                {
                    DDLCu.DataSource = Cdt;
                    DDLCu.DataTextField = "Customer";
                    DDLCu.DataValueField = "codeCust";
                    DDLCu.DataBind();

                }


                MySqlDataAdapter Vadp = new MySqlDataAdapter(("SELECT * from tblvendor"), cn);
                DataTable Vdt = new DataTable();
                Vadp.Fill(Vdt);
                if (Vdt.Rows.Count > 0)
                {
                    DDLVe.DataSource = Vdt;
                    DDLVe.DataTextField = "Vendor";
                    DDLVe.DataValueField = "codeVendor";
                    DDLVe.DataBind();

                }


                MySqlDataAdapter Sadp = new MySqlDataAdapter(("SELECT * from tblservice"), cn);
                DataTable Sdt = new DataTable();
                Sadp.Fill(Sdt);
                if (Sdt.Rows.Count > 0)
                {
                    DDLSe.DataSource = Sdt;
                    DDLSe.DataTextField = "Service";
                    DDLSe.DataValueField = "codeService";
                    DDLSe.DataBind();

                }


                MySqlDataAdapter Radp = new MySqlDataAdapter(("SELECT * from tblregion"), cn);
                DataTable Rdt = new DataTable();
                Radp.Fill(Rdt);
                if (Rdt.Rows.Count > 0)
                {
                    DDLRe.DataSource = Rdt;
                    DDLRe.DataTextField = "Region";
                    DDLRe.DataValueField = "codeRegion";
                    DDLRe.DataBind();

                }
            }

        }

        protected void lnkAdd_Click(object sender, EventArgs e)
        {
            DropDownList DDLCu = gv.FooterRow.FindControl("DDLCu") as DropDownList;
            DropDownList DDLVe = gv.FooterRow.FindControl("DDLVe") as DropDownList;
            DropDownList DDLSe = gv.FooterRow.FindControl("DDLSe") as DropDownList;
            DropDownList DDLRe = gv.FooterRow.FindControl("DDLRe") as DropDownList;
            DropDownList DDLIS = gv.FooterRow.FindControl("DDLIS") as DropDownList;
            TextBox txtds = (TextBox)gv.FooterRow.FindControl("TBDS");
            TextBox txtde = (TextBox)gv.FooterRow.FindControl("TBDE");
           TextBox txtus = (TextBox)gv.FooterRow.FindControl("TextBoxUnit");
            int usage = int.Parse(txtus.Text);
            int cc = int.Parse(DDLCu.SelectedValue);
            int cv = int.Parse(DDLVe.SelectedValue);
            int cs = int.Parse(DDLSe.SelectedValue);
            int cr = int.Parse(DDLRe.SelectedValue);
            int iss = int.Parse(DDLIS.SelectedValue);
            string DS = txtds.Text;
            string DE = txtde.Text;


            add(cc, cv, cs, cr, usage, iss, DE, DS);
            BindData();
            Response.Redirect("http://localhost:56717/usageDisp.aspx");

        }

        private void add(int cc, int cv, int cs, int cr,int usag,int isecure, string ds, string de)

        {

            using (MySqlConnection cn = new MySqlConnection(connectionstring))
            {

                string query = "insert into tblusage(codeCust,codeVendor,codeService,codeRegion,Usages,isSecure,dateStart,dateEnd) values (" + cc + "," + cv + "," + cs + "," + cr + "," + usag + "," + isecure + "," + ds + "," + de + ") ";
                MySqlCommand cmd = new MySqlCommand(query, cn);
                cn.Open();
                cmd.ExecuteNonQuery();

            }
        }

        protected void CalendarStart_SelectionChanged(object sender, EventArgs e)
        {
            TextBox txtds = (TextBox)gv.FooterRow.FindControl("TBDS");
            Calendar cas = gv.FooterRow.FindControl("CalendarStart") as Calendar;
            txtds.Text = cas.SelectedDate.ToString("d");

        }

        protected void CalendarEnd_SelectionChanged(object sender, EventArgs e)
        {
            TextBox txtde = (TextBox)gv.FooterRow.FindControl("TBDE");
            Calendar cas = gv.FooterRow.FindControl("CalendarEnd") as Calendar;
            txtde.Text = cas.SelectedDate.ToString("d");
        }




    }
}



<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="usageDisp.aspx.cs" Inherits="WebApplication1.usageDisp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="usageDisp" runat="server">
<asp:GridView ID="gv" runat="server" 
    DataKeyNames="codeUsage" 
        onrowdeleting="gv_RowDeleting" 
         AutoGenerateColumns="False" ondatabound="gv_DataBound" ShowFooter="True">
    <Columns>
        <asp:TemplateField HeaderText="codeusage" Visible="False">
            <EditItemTemplate>
                <asp:TextBox ID="txtcode" runat="server" Text='<%# Eval("codeUsage") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("codeUsage") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Customer">
            <EditItemTemplate>
                <asp:TextBox ID="TXTCust" runat="server" Text='<%# Eval("Customer") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DDLCu" runat="server" AutoPostBack="True">
                </asp:DropDownList>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" Text='<%# Eval("Customer") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Vendor">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("Vendor") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DDLVe" runat="server" AutoPostBack="True">
                </asp:DropDownList>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" Text='<%# Eval("Vendor") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="dateStart">
            <EditItemTemplate>
                <asp:TextBox ID="TXTDS" runat="server" Text='<%# Eval("dateStart") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:Calendar ID="CalendarStart" runat="server" BackColor="#FFFFCC" 
                    BorderColor="#FFCC66" BorderWidth="1px" DayNameFormat="Shortest" 
                    Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399" Height="108px" 
                    ShowGridLines="True" Width="132px" 
                    onselectionchanged="CalendarStart_SelectionChanged">
                    <DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
                    <NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
                    <OtherMonthDayStyle ForeColor="#CC9966" />
                    <SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
                    <SelectorStyle BackColor="#FFCC66" />
                    <TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt" 
                        ForeColor="#FFFFCC" />
                    <TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
                </asp:Calendar>
                <asp:TextBox ID="TBDS" runat="server"></asp:TextBox>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label4" runat="server" Text='<%# Eval("dateStart") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="dateEnd">
            <EditItemTemplate>
                <asp:TextBox ID="TXTDE" runat="server" Text='<%# Eval("dateEnd") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:Calendar ID="CalendarEnd" runat="server" BackColor="White" 
                    BorderColor="#3366CC" BorderWidth="1px" CellPadding="1" 
                    DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt" 
                    ForeColor="#003399" Height="108px" Width="132px" 
                    onselectionchanged="CalendarEnd_SelectionChanged">
                    <DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
                    <NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
                    <OtherMonthDayStyle ForeColor="#999999" />
                    <SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
                    <SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
                    <TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px" 
                        Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
                    <TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
                    <WeekendDayStyle BackColor="#CCCCFF" />
                </asp:Calendar>
                <asp:TextBox ID="TBDE" runat="server"></asp:TextBox>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label5" runat="server" Text='<%# Eval("dateEnd") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="service">
            <EditItemTemplate>
                <asp:TextBox ID="TXTSe" runat="server" Text='<%# Eval("Service") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DDLSe" runat="server" AutoPostBack="True">
                </asp:DropDownList>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label6" runat="server" Text='<%# Eval("Service") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="region">
            <EditItemTemplate>
                <asp:TextBox ID="TXTRe" runat="server" Text='<%# Eval("Region") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DDLRe" runat="server" AutoPostBack="True">
                </asp:DropDownList>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label7" runat="server" Text='<%# Eval("Region") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="isSecure">
            <EditItemTemplate>
                <asp:TextBox ID="TXTIS" runat="server" Text='<%# Eval("isSecure") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DDLIS" runat="server">
                    <asp:ListItem Value="1">true</asp:ListItem>
                    <asp:ListItem Value="0">false</asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label8" runat="server" Text='<%# Eval("isSecure") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="unit">
            <EditItemTemplate>
                <asp:TextBox ID="TXTunit" runat="server" Text='<%# Eval("unit") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label9" runat="server" Text='<%# Eval("unit") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="usage">
            <EditItemTemplate>
                <asp:TextBox ID="TXTusage" runat="server" Text='<%# Eval("Usages") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                &nbsp;<asp:TextBox ID="TextBoxUnit" runat="server"></asp:TextBox>
                <asp:LinkButton ID="lnkAdd" runat="server" onclick="lnkAdd_Click">add</asp:LinkButton>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="Label10" runat="server" Text='<%# Eval("Usages") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:CommandField HeaderText="Operation" ShowDeleteButton="True" />
    </Columns>
</asp:GridView>
    <div>

    </div>
    </form>
</body>
</html>

A_Sk

you just need to add Convert.ToDateTime() , then your query'll definately work.

but, your doing it in a wrong way. use Parameterized Query,

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks

In your case, you need to change the insert query:

string query = "insert into tblusage(codeCust,codeVendor,codeService,codeRegion,Usages,isSecure,dateStart,dateEnd) values (" + cc + "," + cv + "," + cs + "," + cr + "," + usag + "," + isecure + "," + Convert.ToDateTime(ds) + "," + Convert.ToDateTime(de) + ") ";

Or, You can change the ds and dt variable datatype

CultureInfo provider = CultureInfo.InvariantCulture;
string format="dd/mm/yyyy";//define your datetime format here

DateTime ds=DateTime.ParseExact(textbox1.Text, format, provider);
DateTime de=DateTime.ParseExact(textbox2.Text, format, provider);

and then pass it to the query,like

 string query = "insert into tblusage(codeCust,codeVendor,codeService,codeRegion,Usages,isSecure,dateStart,dateEnd) values (" + cc + "," + cv + "," + cs + "," + cr + "," + usag + "," + isecure + "," +ds + "," + de + ") ";

Edit 2:

 private void add(int cc, int cv, int cs, int cr,int usag,int isecure, string ds, string de)

        {

            using (MySqlConnection cn = new MySqlConnection(connectionstring))
            {

                string query = "insert into tblusage(codeCust,codeVendor,codeService,codeRegion,Usages,isSecure,dateStart,dateEnd) values (@cc,@cv,@cs,@cr,@usag,@isecure,@ds,@de) ";
                MySqlCommand cmd = new MySqlCommand(query, cn);

                cmd .Parameters.Add("@cc",MySqlDbType.Int).Value=cc;
                cmd .Parameters.Add("@cv",MySqlDbType.Int).Value=cv;
                cmd .Parameters.Add("@cs",MySqlDbType.Int).Value=cs;
                cmd .Parameters.Add("@cr",MySqlDbType.Int).Value=cr;
                cmd .Parameters.Add("@usag",MySqlDbType.Int).Value=usag;
              cmd .Parameters.Add("@isecure",MySqlDbType.Int).Value=isecure;
                cmd .Parameters.Add("@ds",MySqlDbType.Date).Value=ds;
                cmd .Parameters.Add("@de",MySqlDbType.Date).Value=de;

                cn.Open();
                cmd.ExecuteNonQuery();

            }
        }

Collected from the Internet

Please contact [email protected] to delete if infringement.