C# .NET - Converting system datetime to Mysql datetime

Asked By shilpa g
10-Feb-12 05:07 AM
hi...

can any body help me out.

//here is my question

userdobexpdate = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;

this will give format 2012-2-10

and my insert qry to mysql is

INSERT INTO userxreminder(id_user, reminder_desc, reminder_expiry_date,id_reminder_type,updated_on) VALUES("

 

 

+ fndid + ", '" + "Birthday Reminder" + "', " + userdobexpdate + ", " + idremindertype + ", NOW())

so how to convert system datetime to mysql format

i:e mm dd yyyy to yyyy mm dd.

thnx in adv


 

  Reena Jain replied to shilpa g
10-Feb-12 05:24 AM
Hi,

"MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format." This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it

I believe you will need to format your data appropriately for MySQL DateTime data Type. Try this:

DateTime dt = DateTime.Parse(txt1.Text + " " + txt2.Text);
cmd1.Parameters.Add("@parDateTime1", MySqlDbType.DateTime).Value = String.Format("{0:yyyy-MM-dd HH:mm}", dt);

This will solve your problem for sure :)
  dipa ahuja replied to shilpa g
10-Feb-12 05:50 AM
You need to enter the date in which the database support, 

use the query

This is simple example of insert update delete

Insert Query
protected void Button1_Click(object sender, EventArgs e)
{
  string conn = "ConnectionString";
  SqlConnection sqlcon = new SqlConnection(conn);
  sqlcon.Open();
  SqlCommand comm = new SqlCommand("Insert into table1 (field1,bday) values (@f1,@bday)", sqlcon);
  comm.Parameters.AddWithValue("f1", int.Parse(txtid.Text.ToString()));
  comm.Parameters.AddWithValue("bday", DateTime.Now);
  comm.ExecuteNonQuery();
}

  Somesh Yadav replied to shilpa g
10-Feb-12 07:44 AM

Hi here we can do it in two ways,

   1)   Keep in mind that you can hard-code ISO format

string formatForMySql = dateValue.ToString("yyyy-MM-dd hh:mm");

or use next:

// just to shorten the code
var isoDateTimeFormat = CultureInfo.InvariantCulture.DataTimeFormat;

// "1976-04-12T22:10:00"
dateValue.ToString(isoDateTimeFormat.SortableDateTimePattern); 

// "1976-04-12 22:10:00Z"    
dateValue.ToString(isoDateTimeFormat.UniversalSortableDateTimePattern)

and so on


 2) If your string format for the DateTime is fixed you can convert to the System.DateTime using:

string myDate = "12-Apr-1976 22:10";
DateTime dateValue = DateTime.Parse(myDate);

Now, when you need it in your specific format, you can then reverse the process, i.e.:

string formatForMySql = dateValue.ToString("yyyy-MM-dd HH:mm");
Create New Account
help
please give me correct code ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. SqlConnection conn = new SqlConnection ( "server = 192.168.2.102;database = saaudit;uid = siaudit;pwd = siaudit_1" ); SqlCommand comm = new SqlCommand textBox1.Text); comm.Parameters.AddWithValue( "empName" , textBox2.Text); comm.Parameters.AddWithValue( "addre" , textBox3.Text); comm.ExecuteNonQuery(); / / Use for INSERT UPDATE AND DELETE to reflect changes to db MessageBox .Show( "Inserted " ); comm.ExecuteNonQuery(); c# and Mysql You are missing OPEN statement of connection. just add it like SqlConnection conn = new SqlConnection ( "server = 192.168.2.102;database = saaudit;uid = siaudit;pwd = siaudit_1" ); SqlCommand comm = new SqlCommand
mysql connection must be valid and open to commit the transaction? This s my code. . . . . . using (SqlConnection sqlConn = new SqlConnection(connectionString)) try { SqlCommand cmd = new SqlCommand("Usp_InvoicelistAdd", sqlConn); / / MySqlCommand cmd = new MySqlCommand("Usp_InvoicelistAdd", sqlConn); cmd StoredProcedure; sqlConn.Open(); cmd.Parameters.Add("@Company", SqlDbType.VarChar, 200).Value = invoice.Companyname.Trim(); cmd.ExecuteNonQuery().ToString(); sqlConn.Close(); } catch (Exception ex) { msg = ex.Message; } return msg; insert datas into mysql. . but it through the error. . . But I can able to read the data from MYSQL. . . Please give me the sol. . . Just try to right cmd ExecuteNonQuery() Instead of cmd.ExecuteNonQuery().ToString(); check that and let me know and also check connection string is correct hi
connect with optimized pooling to Microsoft Sql Server. ''' < / summary> Public Class MySqlConnection Public Connection As SqlConnection Public DataReader As SqlDataReader Public Command As SqlCommand Private mySqlConnectionString As String = "Data Source = . \ SQLEXPRESS Then Connection.Close() End If Connection.Dispose() End If End Sub Public Function CreateConn() As SqlConnection If Connection Is Nothing Then Connection = New SqlConnection() End If If Connection.ConnectionString = String.Empty OrElse Connection.ConnectionString Is Nothing Then Try Connection Sub cmdsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdsave.Click Dim mySql As New MySqlConnection() mySql.CreateConn() mySql.Command = mySql.Connection.CreateCommand() mySql.Command.CommandText = "INSERT INTO UnimaidPortal.Student (txtjamnum, txtmatricID, txtsurname, txtfname, txtdob, selmarital, selgender, txtemail, txtphonenum
How I can retrieve data from MySql Database and store it in sqlite database progarmatically subject itself is the question. Hi, If you are familiar with using SQL in .NET via the SqlConnection and SqlCommand objects, MySQL also provides a DLL that has these: MySQL .NET ADO.NET Provider: http: / / dev mysql.com / downloads / connector / net / There is also one for Sqlite: http: / / sqlite.phxsoftware.com / You project. You then get the relevant connection strings for your databases: http: / / www.connectionstrings.com / mysql http: / / www.connectionstrings.com / sqlite The code will look something like the following: DataTable dt Note this is pseudo-code, it won't directly copy and paste. HTH, Adam First, mysql connector / net, download it from here: http: / / mirror.mirimar.net / mysql / Downloads / Connector-Net / mysql-connector-net-6.1.3.zip And for Sqlite connector, download
want to connect with database . Follow this code- protected void GetDetails_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(" con string "); SqlDataAdapter da; string mySQL = "SELECT * FROM emptable where empid = '" + TxtEmpId.Text + "' "; da = new SqlDataAdapter(mySQL, con); con.Open(); DataSet ds = new DataSet(); da.Fill(ds); / / Now Filling Details- TxtEmpId.Text Tables[0].Rows[0][1].ToString(); / / for name } Try and let me know. Untitled document SqlConnection con = new SqlConnection(" Data Source = myServerAddress; Initial Catalog = myDataBase; User Id = myUsername; Password = myPassword; " ); con.Open(); SqlCommand comm which will help you. . protected void Button1_Click( object sender, EventArgs e) { string struname = string .Empty; SqlConnection con1 = new SqlConnection( "Data Source = . \ SQLEXPRESS;AttachDbFilename = 'C: \ Users \ temp1 \ Desktop \ JB Gold Palace \ App_Data \ JB Database.mdf