oracle 使用c#将数据插入Oracle数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39337413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting data into Oracle database using c#
提问by Gerrit de Beer
I get this error "ora-00928 missing select keyword" when using a button to submit the query. I have other queries on other buttons and the select statements work but for some reason the insert statement doesnt work. I've seen other posts on this error but nothing seems to help mine
使用按钮提交查询时，我收到此错误“ora-00928 缺少选择关键字”。我对其他按钮还有其他查询，并且 select 语句有效，但由于某种原因，插入语句不起作用。我看过其他关于此错误的帖子，但似乎没有任何帮助
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace Oracle
{
public partial class Register : Form
{
string name;
int pass;
int repass;
string email;
public Register()
{
InitializeComponent();
}
OleDbConnection con = new OleDbConnection("Provider=MSDAORA;Data Source=DESKTOP-HQCK6F1:1521/CTECH;Persist Security Info=True;User ID=system;Password=G4ming404;Unicode=True");
OleDbCommand cmd = new OleDbCommand();
private void button1_Click(object sender, EventArgs e)
{
name = txtname.Text;
pass = Convert.ToInt32(txtpass.Text);
repass = Convert.ToInt32(txtrepass.Text);
email = txtemail.Text;
cmd.Connection = con;
cmd.CommandText = "INSERT INTO SYSTEM.CUSTOMER('CUSTOMER_ID', 'CUSTOMER_NAME', 'CUSTOMER_EMAIL', 'CUSTOMER_PASSWORD')" + "VALUES('%"+ null + "%','%'" + txtname.Text + "%','%'" + txtemail.Text + "%','%'" + txtpass.Text + "%')";
con.Open();
if (pass == repass)
{
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
MessageBox.Show("Record not inserted");
}
else {
MessageBox.Show("Success!");
}
MessageBox.Show("User has been created");
this.Close();
Form1 login = new Form1();
login.Show();
}
else {
MessageBox.Show("Password mismatch");
}
con.Dispose();
}
回答by Steve
There are some problems in your query.
First you don't need single quotes around the column names, You need double quotes only if any of your columns has the same name as a reserved keyword.
您的查询存在一些问题。
首先，列名不需要单引号，仅当任何列与保留关键字同名时才需要双引号。
Second problem is the string concatenation of the input boxes text to the query command. This should be avoided at all cost because it is the source of parsing problems and sql injection hacks. Use parameters instead.
第二个问题是输入框文本到查询命令的字符串连接。这应该不惜一切代价避免，因为它是解析问题和 sql 注入黑客的根源。改用参数。
Finally your OleDbConnection should be local to your method and inside a using statement to ensure correct disposing of the unmanaged resources also in case of exceptions
最后，您的 OleDbConnection 应该位于您的方法的本地并在 using 语句中，以确保在发生异常时也正确处理非托管资源
private void button1_Click(object sender, EventArgs e)
{
name = txtname.Text;
pass = Convert.ToInt32(txtpass.Text);
repass = Convert.ToInt32(txtrepass.Text);
email = txtemail.Text;
if (pass != repass)
{
MessageBox.Show("Password mismatch");
return;
}
string cmdText = @"INSERT INTO SYSTEM.CUSTOMER
(CUSTOMER_NAME, CUSTOMER_EMAIL, CUSTOMER_PASSWORD)
VALUES(?,?,?)";
using(OleDbConnection con = new OleDbConnection(.......))
using(OleDbCommand cmd = new OleDbCommand(cmdText, con))
{
con.Open();
cmd.Parameters.Add("p1", OleDbType.VarChar).Value = txtname.Text;
cmd.Parameters.Add("p2", OleDbType.VarChar).Value = txtemail.Text;
cmd.Parameters.Add("p3", OleDbType.VarChar).Value = txtpass.Text ;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
MessageBox.Show("Record not inserted");
}
else
{
MessageBox.Show("Success!");
MessageBox.Show("User has been created");
}
}
Form1 login = new Form1();
login.Show();
}
I have also removed the passing of a parameter for the CUSTOMER_ID field. This seems to be a field that is calculated automatically by Oracle (a Sequence?) and thus you don't need to provide a value for it.
我还删除了 CUSTOMER_ID 字段的参数传递。这似乎是一个由 Oracle 自动计算的字段（序列？），因此您不需要为其提供值。
Finally an advice. Do not store password in plain text in the database. This is a security risk very seriours. You should read Best way to store passwords in a database
最后给个建议。不要将密码以纯文本形式存储在数据库中。这是一个非常严重的安全风险。您应该阅读将密码存储在数据库中的最佳方法
回答by Orkhan Alikhanov
Your CommandTextseems wrong. Why do you wrap all values with '%'? And you should pass nullas string. Concatination with nulldoes not changes any string value.
你的CommandText好像错了。为什么用'%' 包裹所有的值？你应该null作为字符串传递。连接 withnull不会改变任何字符串值。
I think it should be:
我觉得应该是：
cmd.CommandText = $@"INSERT INTO SYSTEM.CUSTOMER
('CUSTOMER_ID', 'CUSTOMER_NAME', 'CUSTOMER_EMAIL', 'CUSTOMER_PASSWORD')
VALUES(NULL, {txtname.Text}, {txtemail.Text}, {txtpass.Text})";
