亲爱的，我正在使用visual studio 2010，我有一个插入数据到SQL Server 2014数据库的问题。这是我的代码： [英] Dear all, I am using visual studio 2010, I have a problem with insert data to database of SQL server 2014. Here is my code:
问题描述
String ConnectionString =Data Source = Lenovo-PC\\SQLSERVER; Integrated Security = True; +
初始目录= Google;
SqlConnection Conn =新SqlConnection（ConnectionString）;
字符串查询=INSERT INTO EMPLOYEE 1（姓名，父亲，CNIC，手机，城市，州）价值观（+
'+ textName.Text +'，+
'+ textFather .Text +'，+
'+ textCNIC.Text +'，+
'+ textMobile.Text +'，+
'+ textCity.Text +'，+
+ textState.Text +，+）;
SqlDataAdapter da = new SqlDataAdapter（Query，Conn）;
DataTable dt = new DataTable（）;
da.Fill（dt）;
错误是：
'1'附近的语法不正确。
但是，对于最佳实践，请在Web.config或app.config中声明连接字符串，如下所示。 />
<？ xml version = < span class =code-keyword> 1.0 encoding = utf-8 ？ >
< configuration >
< connectionStrings >
< add name = CONSTRING connectionString < span class =code-keyword> = 数据源= Lenovo-PC\\SQLSERVER;初始目录= Google;集成安全性=真 / >
< / connectionStrings >
< / configuration >
然后声明一个变量并添加tha连接字符串
public string conn = ConfigurationManager.ConnectionStrings [ CONSTRING]。ConnectionString;
}
然后编写如下代码
SqlConnection Conn = new SqlConnection（conn）;
字符串 Query = INSERT INTO EMPLOYEE 1 （姓名，父亲，CNIC，移动，城市，州）VALUES（ +
' + textName.Text + '， +
' + textFather.Text + < span class =code-string>'， +
' + textCNIC.Text + '， +
' + textMobile.Text + '， +
' + textCity .Text + '， +
+ textState.Text + ， + ）;
SqlDataAdapter da = new SqlDataAdapter（Query，Conn）;
DataTable dt = new DataTable（）;
da.Fill（dt）;
NOOOO !!!
你对sql插入是开放的。这是一件非常非常糟糕的事情！
错误在INSERT INTO EMPLOYEE 1
1不应该在那里，或者如果表被称为EMPLOYEE 1然后添加方括号到sql知道它是单个术语：
使用（SqlConnection conn = new SqlConnection（））
{//添加使用因此配置conn称为
字符串查询=
INSERT INTO [EMPLOYEE 1]（姓名，父亲，CNIC，移动，城市，州）VALUES（@ name，@ father，@ cnic，@ mobile，@ city，@ state）;;
using（SqlCommand command = conn.CreateCommand（））
{
command.CommandType = CommandType.Text;
command.CommandText = Query;
command.Parameters.AddRange（new []
{
new SqlParameter（@ name，SqlDbType.NVarChar）{Value = textName.Text}，
new SqlParameter（@ father ，SqlDbType.NVarChar）{Value = textFather.Text}，
new Sq lParameter（@ cnic，SqlDbType.NVarChar）{Value = textCNIC.Text}，
new SqlParameter（@ mobile，SqlDbType.NVarChar）{Value = textMobile.Text}，
new SqlParameter（ @city，SqlDbType.NVarChar）{Value = textCity.Text}，
new SqlParameter（@ state，SqlDbType.NVarChar）{Value = textState.Text}，
}）;
conn.Open（）;
command.ExecuteNonQuery（）;
conn.Close（）;
}
}
您不需要数据适配器。插入不返回值。返回一个int告诉你有多少行受到影响。
查看SQL注入。使用参数应该可以很好地保护您。任何参数都被编码以便禁止注入。
关于SQL注入的主题：
http://en.wikipedia.org/wiki/SQL_injection [ ^ ]
我的父亲是'; drop table *;'。他的伙伴称他为邋。
请使用此解决方案作为第一个补充。 Debasish Mishra关于连接字符串最佳实践是正确的。
编辑：忘记打开连接＃^ _ ^＃
String ConnectionString = "Data Source=Lenovo-PC\\SQLSERVER;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = " INSERT INTO EMPLOYEE 1(Name,Father,CNIC,Mobile,City,State) VALUES (" +
" '" + textName.Text + "', " +
" '" + textFather.Text + "', " +
" '" + textCNIC.Text + "', " +
" '" + textMobile.Text + "', " +
" '" + textCity.Text + "', " +
" " + textState.Text + ", " + ")";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
the error is:
Incorrect syntax near '1'.
It means your first line was having a incorrect connection string.
However for best practices declare connection string in Web.config or app.config like below.
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="CONSTRING" connectionString="Data Source=Lenovo-PC\\SQLSERVER;Initial Catalog=Google;Integrated Security=True"/> </connectionStrings> </configuration>
then declare a variable and add that connection string
public string conn = ConfigurationManager.ConnectionStrings["CONSTRING"].ConnectionString; }
then write your code as below
SqlConnection Conn = new SqlConnection(conn); String Query = " INSERT INTO EMPLOYEE 1(Name,Father,CNIC,Mobile,City,State) VALUES (" + " '" + textName.Text + "', " + " '" + textFather.Text + "', " + " '" + textCNIC.Text + "', " + " '" + textMobile.Text + "', " + " '" + textCity.Text + "', " + " " + textState.Text + ", " + ")"; SqlDataAdapter da = new SqlDataAdapter(Query, Conn); DataTable dt = new DataTable(); da.Fill(dt);
NOOOO!!!
You are wide open to sql insertions. That's a very, very bad thing!
An the error is in" INSERT INTO EMPLOYEE 1
wither the 1 shouldn't be there, or if the table is called EMPLOYEE 1 then add square braces to sql knows it's a single term:
using (SqlConnection conn = new SqlConnection()) { // added using so the dispose of conn is called String Query = " INSERT INTO [EMPLOYEE 1](Name,Father,CNIC,Mobile,City,State) VALUES (@name,@father,@cnic,@mobile,@city,@state);"; using (SqlCommand command = conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = Query; command.Parameters.AddRange(new[] { new SqlParameter("@name", SqlDbType.NVarChar) {Value = textName.Text}, new SqlParameter("@father", SqlDbType.NVarChar) {Value = textFather.Text}, new SqlParameter("@cnic", SqlDbType.NVarChar) {Value = textCNIC.Text}, new SqlParameter("@mobile", SqlDbType.NVarChar) {Value = textMobile.Text}, new SqlParameter("@city", SqlDbType.NVarChar) {Value = textCity.Text}, new SqlParameter("@state", SqlDbType.NVarChar) {Value = textState.Text}, }); conn.Open(); command.ExecuteNonQuery(); conn.Close(); } }
You don't need the data adapter. Inserts do not return values. The return a single int telling you how many rows were affected.
Look into SQL Injection. Using parameters should protect you to a good degree. Any parameters are encoded so as to disallow injection.
On the subject of SQL Injection:
http://en.wikipedia.org/wiki/SQL_injection[^]
My father is '; drop table * ;'. His mates called him 'droppy' for short.
Please use this solution as an addition to the first. Debasish Mishra is correct about the connection string best practice.
EDIT: forgot to open the connection #^_^#
这篇关于亲爱的，我正在使用visual studio 2010，我有一个插入数据到SQL Server 2014数据库的问题。这是我的代码：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
