如何在MySQL服务器和Asp中使用datetime [英] How to work with datetime in MySQL server and Asp

问题描述

我有一个场景，我必须从用户那里收到消息和当前的消息发布时间。

然后在MySQL中使用此消息和日期时间，然后将有查询显示最近日期的10条消息。



I已阅读一些教程，但他们只告诉如何将日期时间转换为字符串，然后存储在数据库中。但是如何从MySQL过滤这些消息并再次显示在页面上。





我写了以下代码

I have a scenario where I have to get a message from user and the current time of message posting.
Then use this message and date-time in MySQL, then there will be query to display the 10 messages by Recent dates.

I have read some tutorial but they only tell how to convert date time to string and then store in the database. But how to filter these messages from MySQL and again display on the page.


I have write the following Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Odbc;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


public partial class GetNotification : System.Web.UI.Page
{
    DBClass db1 = new DBClass();

    String strmsg;
    protected void Page_Load(object sender, EventArgs e)
    {
        

    }
    protected void ButPost_Click(object sender, EventArgs e)
    {
        if (TextBox1.Text == null || TextBox1.Text == " ")
        {
            Error1.Visible = true;
        }
        else 
        {
            strmsg = TxtNotification.Text;
         
            try
            {
                db1.sqlcon.Open();
string sqlquery = "INSERT INTO TempTable (Tdate,Msg) VALUES (NOW()," + "'strmsg'" + ")";
                db1.sqlcmd = new SqlCommand(sqlquery, db1.sqlcon);
                if (db1.sqlcon.State == ConnectionState.Closed)
                { db1.sqlcon.Open(); }
                db1.sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ee)
            {
                Response.Write(ee);
            }
        }
    }
}

and have the followng error

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '('. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at GetNotification.ButPost_Click(Object sender, EventArgs e) in e:\ECCII\GetNotification.aspx.cs:line 41 ClientConnectionId:ee18928b-cd18-48e5-9852-5f7d208c09c9 

推荐答案

您一直在阅读错误的教程。为什么要将datetime转换为字符串？只需将它们存储为mysql作为datetime。请参阅： Working_with_Dates_and_Times_in_MySQL [ ^ ]

至于按最近日期获取10条消息，获取此消息的方法是按顺序排序消息表消息日期按特定用户的降序排列，然后检索前10条记录：

You have been reading the wrong tutorials. Why do you want to convert datetime to string? just store them into mysql as datetime. Refer: Working_with_Dates_and_Times_in_MySQL[^]
As for getting the 10 messages by recent dates, the way to get this is to order your message table by the message date in descending order for the particular user, then retrieve the top 10 records:

SELECT message_field FROM table_name WHERE user_id = "useridvalue" ORDER BY message_date_field DESC LIMIT 10 

按订单排序 [ ^ ]

限制 [ ^ ]

尝试此查询来排序您的日期字段，



SELECT max（your_datefieldname）as date from yourtableName GROUP BY your_uniquefieldname

Try this query to sort your date fields,

SELECT max(your_datefieldname) as date FROM yourtableName GROUP BY your_uniquefieldname

这篇关于如何在MySQL服务器和Asp中使用datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！