以dd-MM-yyyy格式插入日期 [英] Insert date in dd-MM-yyyy format

问题描述

我正在尝试以c＃格式插入 dd-MM-yyyy 格式的日期。查询插入的是

I'm trying to insert date in dd-MM-yyyy format in c#. Query for inserting is

SqlCommand cmd_cust = new SqlCommand(@"insert into custdetail values ('" + txtInvoiceNo.Text + "','" + txtCustomerName.Text + "','" + txt_contact.Text + "', '" + txtAddress.Text + "', '" + txt_total_amt.Text + "', '" + dt_date.Value.ToString("dd-MM-yyyy") + "')", con_create);
            con_create.Open();
            cmd_cust.ExecuteNonQuery();
            con_create.Close();

我创建的表的列名称为date，数据类型为 date 。插入记录后，日期列字段中的值采用 yyyy-dd-MM 格式。我想要此格式 dd-MM-yyyy 。

I have created table with column name date has datatype date. After inserting record the value in date column field is in yyyy-dd-MM format. I want this in dd-MM-yyyy format.

推荐答案

不要尝试连接字符串以构建正确的sql命令。

这只会导致解析问题和。

改用参数化查询

Do not try to concatenate a string to build a correct sql command.
This leads only to parsing problems and Sql Injection Attacks.
Use instead a parameterized query

int isok = 0;
try 
{
    // Now your query is more readable and there are no more formatting problems here
    SqlCommand cmd_cust = new SqlCommand(@"insert into custdetail values
                         (@invNo,@custName,@contact,@address,@amount,@dt)", 
                         con_create);
    con_create.Open();
    cmd_cust.Parameters.AddWithValue("@invNo",txtInvoiceNo.Text );
    cmd_cust.Parameters.AddWithValue("@custName",txtCustomerName.Text );
    cmd_cust.Parameters.AddWithValue("@contact",txt_contact.Text);
    cmd_cust.Parameters.AddWithValue("@address",txtAddress.Text.Text);
    // The following parameter could require a conversion if the db field is not of text type
    // cmd_cust.Parameters.AddWithValue("@amount", Convert.ToDecimal(txt_total_amt.Text)); 
    cmd_cust.Parameters.AddWithValue("@amount", txt_total_amt.Text); 
    cmd_cust.Parameters.AddWithValue("@dt",dt_date.Value );
    isok= cmd_cust.ExecuteNonQuery();
    con_create.Close();
}

使用参数无需担心如何格式化DateTime值字符串，您直接传递数据库字段所期望的DateTime值。正确地将此值传递到基础数据库表是框架工作。

Using a parameter you don't need to worry how to format a DateTime value to a string, you pass directly the DateTime value as expected by the database field. It is the framework job to correctly pass this value to the underlying database table.

对于其他字段（例如字符串字段）也是如此。如果用户在其中一个文本框中键入单引号，则会出现字符串连接语法错误。用户键入的引号错误地关闭了值，使文本的其余部分保留为无效的SQL文本

（例如， textCustomerName.Text = O'Brian 变为 ....，'O'Brian'，.... ）

This is true also for the other fields like the string ones. If your user types a single quote inside one of your textboxes you get a syntax error with the string concatenation. The quote typed by your user mistakenly closes the value leaving the remainder of the text as invalid sql text
(e.g. textCustomerName.Text = O'Brian becomes ....,'O'Brian' ,....)

这篇关于以dd-MM-yyyy格式插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！