Insert date into an SQL database

Question

So I have another problem doing my school project. FYI, we don't use sql-parameters and didn't learn how to use them as of yet.

I am trying to insert a birthday into the sql database but I tried everything but there is always a data type mismatch.

Can you guys help me out (without changing the structure of the code)? You can look it up by searching "birthday" as everything else has German names.

I would really appreciate your help as I'm really desperate. EDIT: There is a textbox, where user are supposed to type in the birthday. That's where I get the data.

EDIT: I removed all other unnecessary strings etc.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

public class webUser
{

private DateTime _birthday;

public webUser()
{
    //
    // TODO: Add constructor logic here
    //

public DateTime birthday
{
    get { return _birhday; }
    set { _birthday= value; }
}

public bool checkUser(string eMail)
{
    string sql = "SELECT eMail, kennwort FROM Benutzerdatenbank WHERE eMail ='" + eMail + "'";
    string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Benutzerdatenbank.accdb");

    OleDbConnection con = new OleDbConnection(conStr);
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();

    if (ds.Tables[0].Rows.Count == 1)
        return true;
    else
        return false;
}

public bool addUser(string eMail, string kennwort, string vorname, string zuname, string telefonnummer, string strasse, string plz, string ort, string firma, string titel, DateTime birthday)
{
    if (this.checkUser(eMail) == true)

    {
        return false; 

    }

    else
    {

        string zeichen =   "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghjiklmnopqrstuvwxyz0123456789";
        string aktivierungscode = "";
        Random rnd = new Random();
        for (int i = 1; i < 62; i++)
        {
            aktivierungscode = aktivierungscode + zeichen.Substring(rnd.Next(0, zeichen.Length - 1), 1);
        }

        string sql = "INSERT INTO Benutzerdatenbank (eMail, kennwort, Titel, Vorname, Zuname, Firma, birthday, Telefonnummer, Strasse, PLZ, Ort, aktivierungscode) VALUES ('" +
        eMail + "','" + kennwort + "','" + titel + "','" + vorname + "','" + zuname + "','" + firma +  "','" + birthday+ "','" + telefonnummer + "','" + strasse + "','" + plz + "','" + ort + "','" + aktivierungscode + "');";


        string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Benutzerdatenbank.accdb");
        OleDbConnection con = new OleDbConnection(conStr);
        OleDbCommand cmd = new OleDbCommand(sql, con);

        con.Open();
        cmd.ExecuteNonQuery(); 
        con.Close();
        return true;
    }
}

public void ReadUser(string eMail, string kennwort)
{
    string sql = "SELECT * FROM Benutzerdatenbank WHERE eMail='" + eMail + "' AND kennwort ='" + kennwort + "'";
    string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Benutzerdatenbank.accdb");

    OleDbConnection con = new OleDbConnection(conStr);
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();

    if (ds.Tables[0].Rows.Count == 1)
    {
        this.eMail = (string)ds.Tables[0].Rows[0]["eMail"];
        this.vorname = (string)ds.Tables[0].Rows[0]["Vorname"];
        this.zuname = (string)ds.Tables[0].Rows[0]["Zuname"];
        this.telefonnummer = (string)ds.Tables[0].Rows[0]["Telefonnummer"];
        this.strasse = (string)ds.Tables[0].Rows[0]["strasse"];
        this.plz = (string)ds.Tables[0].Rows[0]["PLZ"];
        this.ort = (string)ds.Tables[0].Rows[0]["ORT"];
        this.titel = (string)ds.Tables[0].Rows[0]["Titel"];
        this.firma = (string)ds.Tables[0].Rows[0]["Firma"];
        this.birthday= Convert.ToDateTime(ds.Tables[0].Rows[0]["birthday"];

    }
    else
    {
        this.eMail = "";
        this.vorname = "";
        this.zuname = "";
    }
}

}

Answer 1

FYI, we don't use sql-parameters and didn't learn how to use them as of yet.

Then learn how to use them. There is really no point in learning to do it the wrong way. Plus, using dates without parameters is actually more complicated than doing it with parameters.

Parameters are really simple. The following question contains everything you need to get started:

• Why do we always prefer using parameters in SQL statements?

The only difference you need to be aware of is that OleDbCommand uses ? instead of @parameterName as the parameter placeholder in the SQL statement. The parameter name is ignored, parameters are added in the order in which the ? placeholders appear.

---

In your case, the relevant code would look like this:

string sql = "INSERT INTO Benutzerdatenbank (eMail, kennwort, Titel, Vorname, Zuname, Firma, birthday, Telefonnummer, Strasse, PLZ, Ort, aktivierungscode) " +
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

OleDbCommand cmd = new OleDbCommand(sql, con);

// The parameter names (first argument) are ignored, the order is important
cmd.Parameters.AddWithValue("@eMail", eMail);
...
cmd.Parameters.AddWithValue("@birthday", birthday);
...

cmd.ExecuteNonQuery(); 

Answer 2

Format your date parameter as "year date month" with "yyyyMMdd", like: birthday.ToString("yyyyMMdd").
Otherwise, SQL Server tries to convert it from m/d/yyyy format.

string sql = "INSERT INTO Benutzerdatenbank (eMail, kennwort, Titel, Vorname, Zuname, Firma, birthday, Telefonnummer, Strasse, PLZ, Ort, aktivierungscode) VALUES ('" +
eMail + "','" + kennwort + "','" + titel + "','" + vorname + "','" + zuname + "','" + firma +  "','" + birthday.ToString("yyyyMMdd") + "','" + telefonnummer + "','" + strasse + "','" + plz + "','" + ort + "','" + aktivierungscode + "');";

Answer 3

It could be that the format of your DateTime object does not match what the SQL database wishes. datetime format to SQL format using C#

Answer 4

This is an example of a insertion in SQL Server with SQL language:

The table is PERSON and this is you struct:

ID, int, primary key, autoincrement

NAME, nvarchar(50), not null

BIRTHDATE, datetime

now, for insert is:

INSERT INTO PERSON (NAME, BIRTHDATE) VALUES('Jose Luis', DATETIMEFROMPARTS(1988, 7, 27, 0,0,0,0));

and for select is:

SELECT * FROM PERSON WHERE BIRTHDATE = DATETIMEFROMPARTS(1988, 7, 27, 0,0,0,0);

this code is testing in SQL Server.

The idea is that you change the value of the variable 'sql' of you code, for example push:

var bithdateformat = string.Format("DATETIMEFROMPARTS({0}, {1}, {2}, {3}, {4}, {5}, {6})", birthday.Year, birthday.Month, birthday.Day, 0, 0, 0, 0);

string sql = "INSERT INTO Benutzerdatenbank (eMail, kennwort, Titel, Vorname, Zuname, Firma, birthday, Telefonnummer, Strasse, PLZ, Ort, aktivierungscode) VALUES ('" + eMail + "','" + kennwort + "','" + titel + "','" + vorname + "','" + zuname + "','" + firma + "','" + bithdateformat + "','" + telefonnummer + "','" + strasse + "','" + plz + "','" + ort + "','" + aktivierungscode + "');";