使用sqlcommand进行数据添加 [英] Data Adding using sqlcommand

问题描述

大家好，

我在数据库中使用了三个数据，例如产品，价格，折扣..... i我给默认值折价... 0 ...我错过了Default

字段以输入输入.当我运行程序时.它显示错误，例如("附近的语法不正确)....请为此帮助我

sql是

Hi All,

I Used Three Data In DataBase like product,price,discount........i give default value off discount ...0.......I miss Default

field to enter input.. When I Run The Program . It Shows Error like(Incorrect Syntax Near '')''....Please Help Me For This

sql is

create table products(product nvarchar(max),
                  price numeric(18,2),
                  discount numeric(18,2) default 0)

使用的命令是

used command is

Com=New sqlcommand("insert into pro values('" & textbox1.text &"'," & textbox2.text &"," & textbox3.text &")",con)

谢谢
普里扬(S.Priyan)

[edit]已添加代码块-OriginalGriff [/edit]

Thanks
S.Priyan

[edit]Code blocks added - OriginalGriff[/edit]

推荐答案

首先，请不要那样做.您的代码使您容易受到蓄意或意外的SQL注入攻击，这很容易破坏整个数据库.请改用参数化查询.
其次，请对控件使用合适的名称:您可能已经知道"textbox2"中现在有什么数据，但是我不知道，几个月后您也不会！将其命名为"tbAddress"或"tbProductDescription"，您的代码将变得更具可读性.
第三，您的SQL INSERT语法错误.您需要在表格中指定列.

将它们放在一起，请尝试以下操作:

First off, don''t do it like that. Your code leave you open to a deliberate or accidental SQL Injection attack, which could easily destroy your whole database. Use Parametrized queries instead.
Secondly, please use sensible names for controls: You may have a good idea what data is in "textbox2" now, but I don''t - and neither will you in a months time! Call it "tbAddress" or "tbProductDescription" and you code becomes much more readable.
Thirdly, your syntax for the SQL INSERT is wrong. You need to specify the columns in your table.

Putting these together, try this:

Com=New sqlcommand("INSERT INTO pro (Column1, Column2, Column3) VALUES(@C1, @C2, @C3)", con);
Com.Parameters.AddWithValue("@C1", textbox1.text);
Com.Parameters.AddWithValue("@C2", textbox2.text);
Com.Parameters.AddWithValue("@C3", textbox3.text);

其中"Column1"，"Column2"等是"pro"表中的字段名称.您也可以更改"@ C1"，"@ C2"和"@ C3"以使用明智的名称-我不能，因为我不知道您的字段包含什么...

Where "Column1", "Column2" etc. are the field names in your "pro" table. You may also with to change "@C1", "@C2" and "@C3" to use sensible names as well - I can''t because I don''t know what you fields contain...

通过源代码进行调试.
在命令行上放置一个调试点，然后尝试看一下字符串.

同时查看所有文本框值，并确保所有值都不为空.

尝试在后端运行查询，以查看是否首先创建了表(在运行插入之前).

Debug through your source code.
Put a debug point on the command line and try to have a look at the string.

Also look at all the text box values and make sure none of them are null.

Try running the query in the back end to see if the table has been created first (before running the insert).

首先，永远不要将值连接到SQL语句.使用 SqlParameter [

First of all, never ever concatenate values to the SQL statement. Use SqlParameter[^].

After that your code could look something like:

SqlCommand command = new SqlCommand();
command.CommandText = "INSERT INTO Products (Product, Price, Discount) VALUES (@p1, @p2, @p3)";
command.Connection = con;

SqlParameter parameter;
parameter = new SqlParameter("p1", System.Data.SqlDbType.VarChar, 2000);
parameter.Value = textbox1.Text;
command.Parameters.Add(parameter);

parameter = new SqlParameter("p2", System.Data.SqlDbType.Decimal);
parameter.Value = textbox2.Text;
command.Parameters.Add(parameter);

parameter = new SqlParameter("p2", System.Data.SqlDbType.Decimal);
parameter.Value = textbox3.Text;
command.Parameters.Add(parameter);

try {
   rowsAffected = command.ExecuteNonQuery()
} catch ...

现在，您将不会出现单引号，没有SQL注入等问题.

Now you won''t have problems with apostrophes, no SQL injections etc.

这篇关于使用sqlcommand进行数据添加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！