如何使用带有c＃的asp.net将数据存储在带有datetime的mysql数据库字段中 [英] how to store the data in mysql database field with datetime using asp.net with c#

问题描述

我使用asp.net 4.0和c＃一起使用mysql



iam为日期列传递此值 2013年1月23日8:46： 08 AM datetime列给出错误



语法错误不正确。

表名是包





CUSTOMER_PHONE VARCHAR（20）

IN_DATE datetime

STATE TINYINT

PACK_CODE VARCHAR（50）

FK_SIZE TINYINT

PAY_CODE VARCHAR（50）

OPEN_CODE VARCHAR（50）

SEND_CODE VARCHAR（50）





MySqlConnection con = new MySqlConnection（constring）;

hi , iam using asp.net 4.0 with c# with mysql

iam passing this value for date column 1/23/2013 8:46:08 AM datetime column is giving error

incorrect syntax error.
table name is pack


CUSTOMER_PHONE VARCHAR(20)
IN_DATE datetime
STATE TINYINT
PACK_CODE VARCHAR(50)
FK_SIZE TINYINT
PAY_CODE VARCHAR(50)
OPEN_CODE VARCHAR(50)
SEND_CODE VARCHAR(50)


MySqlConnection con = new MySqlConnection(constring);

MySqlCommand cmd = new MySqlCommand("insert into pack(CUSTOMER_PHONE ,IN_DATE,STATE ,PACK_CODE ,FK_SIZE ,PAY_CODE ,OPEN_CODE,SEND_CODE)values('" + Convert.ToString(txtPhoneNo.Text) + "'," + Convert.ToDateTime(txtInDate.Text) + "," + Convert.ToInt16(0) + ",'" + Convert.ToString(txtPackCode.Text) + "'," + Convert.ToInt16(ddlSize.SelectedItem.Vlue) + ",'" + Convert.ToString(0) + "','" + Convert.ToString(txtopencode.Text) + "'")", con);

        if (cmd.ExecuteNonQuery() != 0)
        {
            con.Close();

            Label1.Text = "Stored Successfully..";
        }
        else { Label1.Text = "Error";
        }

请给我想解决这个问题。

please give me idea to solve this issue.

推荐答案

不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



它们也有助于解决您的问题：将DateTime值作为参数传递，MySql将直接接受它 - 您正在做的是转换一个DateTime的文本字符串，然后将其转换回一个字符串，然后将其传递给MySql - 它不太喜欢它，因为它包含一个空格。

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

They will also help to solve your problem: Pass the DateTime value as a parameter and MySql will accept it directly - how you are doing is converting a text string to a DateTime, then converting that back to a string, then passing that to MySql - which doesn't like it much, because it contains a space.

MySqlCommand cmd = new MySqlCommand("insert into pack(CUSTOMER_PHONE ,IN_DATE,STATE ,PACK_CODE ,FK_SIZE ,PAY_CODE ,OPEN_CODE,SEND_CODE)values(@CPN, @IND, @STA, @PKC, @FKS, @PAY, @OPC, @SNC)", con);
cmd.Parameters.AddWithValue("@CPN", txtPhoneNo.Text);
cmd.Parameters.AddWithValue("@IND", Convert.ToDateTime(txtInDate.Text));
cmd.Parameters.AddWithValue("@STA", 0);
cmd.Parameters.AddWithValue("@PKC", txtPackCode.Text);
cmd.Parameters.AddWithValue("@FKS", Convert.ToInt16(ddlSize.SelectedItem.Vlue));
cmd.Parameters.AddWithValue("@PAY", 0);
cmd.Parameters.AddWithValue("@OPC", txtopencode.Text);

我还怀疑Vlue应该是Value但是这取决于你...

I also suspect that "Vlue" should be "Value" but that's up to you...

MySQL：

在MySQL中你需要以24小时格式传递DateTime。

例如：如果你想更新/插入1/23/2013 8:46:08 AM。

你需要传递像2013-01-23 08:46:08。

MySQL:
In MySQL you need to pass DateTime in 24 hr format.
eg: if you want to update/insert "1/23/2013 8:46:08 AM".
you need to pass like "2013-01-23 08:46:08".

这篇关于如何使用带有c＃的asp.net将数据存储在带有datetime的mysql数据库字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！