插入多个查询以在不同的数据库表中进行记录时出错... [英] Error in inserting multiple queries to make records in different db tables...

问题描述

你好，

我在插入查询时遇到问题.如果我对插入查询进行注释，则第一个查询工作正常.
第一个查询正在添加余额，第二个查询正在交易表中记录存款金额，但是插入报表时出现错误.

任何人都可以帮助我为什么会出错吗?
这是一段代码:

Hello,

I am having problem in insert query. The first query works fine, if I comment the insert query.
First query is adding balance and second query is making record of the deposit amount in trasaction table but I am getting an error in insert into statement.

Can anyone please help on why I am getting an error?
Here is the piece of code:

MAcmd.CommandText = "Update registeraccount Set Balance =Balance-'" + c + "'where username='" + textBox3.Text + "'";
MAcmd.ExecuteNonQuery();
MAcmd.CommandText = "insert into transaction (username, withdrawamount, date) VALUES ("+textBox3.Text+", '"+textBox4.Text+"','"+textBox1.Text+"');
MAcmd.ExecuteNonQuery();

推荐答案

简单的DEBUGGER使用将告诉您最终的命令文本是什么.您只需要查看该文本，看看是否有任何问题.只是为了确认，将其复制并粘贴在SQL Server中形成的原始查询，然后查看它是否正确执行.这将告诉您查询的问题(如果有).

尝试(用户名值似乎缺少引号):

A simple use of DEBUGGER would had told you that what was the final command text formed. All you needed was to look at that text and see if there is any issue. Just to confirm, copy it and paste the raw query formed in SQL server and see if it executes without error. This would tell you the issue with the query, if any.

Try (Looks like quotes were missing for username value):

MAcmd.CommandText = "INSERT INTO transaction (username, withdrawamount, date) VALUES ('"+textBox3.Text+"', '"+textBox4.Text+"','"+textBox1.Text+"')";

附言:这是假设要插入的字段的数据类型为varchar类型.如果数据库字段是不同的数据类型，则在插入之前，您可能需要相应地格式化值.

现在，
1.始终使用参数化查询 [ ^ ] .这样可以避免由于引号引起的混乱和语法错误.此外，它消除了SQL注入问题.

2.始终使用控件的有意义的名称，以便您可以轻松理解控件/对象的值.

更新1:
在分号前也要加上引号.查询结尾不需要分号.

P.S.: This is assuming that the datatype for the fields being inserted is of varchar type. In case the database fields are different datatype, then you might need to format the values accordingly before inserting.

Now,
1. Always use parameterized query[^]. This would avoid confusion and syntax errors that can come into picture because of quotes. Further, it takes away SQL Injection issue.

2. Always use meaningful names of the control such that you can easily understand what value that control/object hold.

UPDATE 1:
Put a quote just before semicolon too. Query does not need a semicolon at the end.

这篇关于插入多个查询以在不同的数据库表中进行记录时出错...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！