如何将数据插入SQL表 [英] How do I insert data into an SQL table

问题描述

我需要在sqlserver中保存数据



我尝试过：

I need to save data inside an sqlserver

What I have tried:

using (SqlConnection conn = new SqlConnection(@"Data source = ******* ; Database=********* ; User Id=sa ; Password=********"))
                    {

                        SqlCommand command = new SqlCommand("IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(NumFaturas char(250) NOT NULL, Cont1 char(250) NOT NULL, Cont2 char(250) NOT NULL)" +
                            "INSERT INTO UXFaturas (NumFaturas, Cont, Cont2)", conn);
                        
                        
                        command.Parameters.AddWithValue("@NumFaturas", numfatura);
                        command.Parameters.AddWithValue("@Cont1", cont);
                        command.Parameters.AddWithValue("@Cont2", cont2);
                    conn.Open();
                    command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    conn.Close();
                }

我试过这个但是没有插入。没有错误，但表仍然是空的。

I've tried this but is not inserting. There are no errors but the table stills empty.

推荐答案

仔细查看您的代码。在添加参数之前，您正在调用ExecuteNonQuery。您还需要更改sql语句以使用参数。

Look at your code carefully. You are calling ExecuteNonQuery before you even add your parameters. You also need to change your sql statement to use parameters.

SqlCommand insertCommand = new SqlCommand("INSERT INTO UXFaturas(NumFaturas, Cont1, Cont2) VALUES (@numfatura, @cont, @cont2)", conn);

在VALUES子句中添加@符号。

Add the @ symbol in the VALUES clause.

您正在调用 command.ExecuteNonQuery（） ; ;在命令对象中设置任何参数之前。您还指定在不需要存储过程时使用存储过程。

You are calling command.ExecuteNonQuery(); before you have set any of the parameters in your command object. You are also specifying to use a stored procedure when you do not need one.

我建​​议将表创建部分拆分为单独的命令。如果该表不存在，SQL可能很难准备查询以将数据插入其中。



您的 INSERT 命令当前缺少 VALUES 子句，因此您应该收到一个异常，告诉您语法不正确。

I'd suggest splitting out the table creation part to a separate command. If the table doesn't exist, SQL might struggle to prepare the query to insert data into it.

Your INSERT command is currently missing a VALUES clause, so you should be getting an exception telling you the syntax is not correct.

using (SqlConnection conn = new SqlConnection(@"Data source = 2c4138928627\Sage ; Database=ARMINDOData ; User Id=sa ; Password=sage2008+"))
{
    conn.Open();
    
    using (SqlCommand command = new SqlCommand("IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(NumFaturas char(250) NOT NULL, Cont1 char(250) NOT NULL, Cont2 char(250) NOT NULL)", conn))
    {
        command.CommandType = CommandType.Text;
        command.ExecuteNonQuery();
    }
    
    using (SqlCommand command = new SqlCommand("INSERT INTO UXFaturas (NumFaturas, Cont, Cont2) VALUES (@NumFaturas, @Cont, @Cont2)", conn))
    {
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("@NumFaturas", numfatura);
        command.Parameters.AddWithValue("@Cont1", cont);
        command.Parameters.AddWithValue("@Cont2", cont2);
        command.ExecuteNonQuery();
    }
}

INSERT（Transact-SQL）| Microsoft Docs [ ^ ]



注意：理想情况下，您应该避免连接为 sa 。该用户可以无限制地访问您的应用程序不需要的整个服务器。相反，您应该使用仅具有您的应用程序所需权限的帐户。

INSERT (Transact-SQL) | Microsoft Docs[^]

NB: Ideally, you should avoid connecting as sa. That user has unrestricted access to the entire server, which your application doesn't need. Instead, you should use an account which has only the permissions required by your application.

这篇关于如何将数据插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！