如何将变量传递到SqlCommand语句中并插入到数据库表中

发布于 2024-09-30 01:21:12 字数 855 浏览 8 评论 0原文

我正在用 C# 编写一个小程序，它使用 SQL 在运行时根据用户的输入将值存储到数据库中。

唯一的问题是我无法找出将变量传递到数据库的正确 Sql 语法。

private void button1_Click(object sender, EventArgs e)
    {
        int num = 2;

        using (SqlCeConnection c = new SqlCeConnection(
            Properties.Settings.Default.rentalDataConnectionString))
        {
            c.Open();
            string insertString = @"insert into Buildings(name, street, city, state, zip, numUnits) values('name', 'street', 'city', 'state', @num, 332323)";
            SqlCeCommand cmd = new SqlCeCommand(insertString, c);
            cmd.ExecuteNonQuery();
            c.Close();
        }

        this.DialogResult = DialogResult.OK;
    }

在此代码片段中，我使用除尝试传递到数据库的 num 变量之外的所有静态值。

在运行时我收到此错误：

A parameter is missing. [ Parameter ordinal = 1 ]

谢谢

原文

I'm writing a small program in C# that uses SQL to store values into a database at runtime based on input by the user.

The only problem is I can't figure out the correct Sql syntax to pass variables into my database.

private void button1_Click(object sender, EventArgs e)
    {
        int num = 2;

        using (SqlCeConnection c = new SqlCeConnection(
            Properties.Settings.Default.rentalDataConnectionString))
        {
            c.Open();
            string insertString = @"insert into Buildings(name, street, city, state, zip, numUnits) values('name', 'street', 'city', 'state', @num, 332323)";
            SqlCeCommand cmd = new SqlCeCommand(insertString, c);
            cmd.ExecuteNonQuery();
            c.Close();
        }

        this.DialogResult = DialogResult.OK;
    }

In this code snippet I'm using all static values except for the num variable which I'm trying to pass to the database.

At runtime I get this error:

A parameter is missing. [ Parameter ordinal = 1 ]

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

短叹 2024-10-07 01:21:12

在执行命令之前添加一个参数：

cmd.Parameters.Add("@num", SqlDbType.Int).Value = num;

Add a parameter to the command before executing it:

cmd.Parameters.Add("@num", SqlDbType.Int).Value = num;

回复收藏 0 原文

甚是思念 2024-10-07 01:21:12

您没有为 SQL 语句中的 @ 参数提供值。 @ 符号表示一种占位符，您将通过它传递值。

使用 SqlParameter 对象，如 < a href="http://asp-net-example.blogspot.com/2008/10/sqlparameter-example-how-to-use.html" rel="nofollow">此示例来传递值到该占位符/参数。

构建参数对象的方法有很多种（不同的重载）。如果您遵循相同类型的示例，一种方法是将以下代码粘贴到声明命令对象的位置之后：

        // Define a parameter object and its attributes.
        var numParam = new SqlParameter();
        numParam.ParameterName = " @num";
        numParam.SqlDbType = SqlDbType.Int;
        numParam.Value = num; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 

        // Provide the parameter object to your command to use:
        cmd.Parameters.Add( numParam );

You didn't provide a value for the @ parameter in the SQL statement. The @ symbol indicates a kind of placeholder where you will pass a value through.

Use an SqlParameter object like is seen in this example to pass a value to that placeholder/parameter.

There are many ways to build a parameter object (different overloads). One way, if you follow the same kind of example, is to paste the following code after where your command object is declared:

        // Define a parameter object and its attributes.
        var numParam = new SqlParameter();
        numParam.ParameterName = " @num";
        numParam.SqlDbType = SqlDbType.Int;
        numParam.Value = num; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 

        // Provide the parameter object to your command to use:
        cmd.Parameters.Add( numParam );

回复收藏 0 原文

~没有更多了~