C#-运行SQL查询时出现问题(在数据库中创建VIEW) [英] C# - Problem running SQL query (Create VIEW in DataBase)

问题描述

大家好，我正在使用Visual Studio 2010 c#.假定该功能可以连接到数据库并创建视图以供进一步使用.不幸的是，当我运行程序时，它说"View附近的语法不正确".但是语法是正确的！我尝试在SQL查询中运行字符串comand，它可以工作.但是表格Visual c#发送错误消息.我已经仔细检查了数据库中是否没有其他视图.如果我从comand字符串中删除"CREATE VIEW"部分，剩下的就剩下，程序就可以工作了！太奇怪了……请帮助.提前感谢.
问候

Hi guys, i am using visual studio 2010 c#. This function is suposed to connect to a database and create a view for further use. Unfortunately when i run the program it says "Incorrect syntax near ''View'' ". But the syntax is correct! I try running the string comand in SQL query and it works. But form Visual c# sends the error message. I have doublechecked that there is NOT another view in the database. And if i take off the "CREATE VIEW" part from the comand string and leave the rest, the program works! Thats weird... Plz HELP. Thanks in avdvance.
Regards

 private void button1_Click(object sender, RoutedEventArgs e)
 {


     int i = 0;
     Boolean Error = false;
String comand=null;
int j = i - 1;

String connect = @"Data Source=STAGE1-SW\SQLEXPRESS;Initial Catalog=License_Data;Integrated Security=True";
     SqlConnection con = new SqlConnection(connect);
     SqlDataReader rdr = null;
     String comand2 = null;
     if(i==0){
         comand = @"CREATE VIEW [Vista0] AS (SELECT * FROM [Customers] WHERE (Ragione_Sociale LIKE (@Nome + ''%'')))"; /*This is the part that is run ignore the other command for now*/
     i++;
     }
     else
     {
         comand = @"Create View Vista" + i.ToString() + " as SELECT  * FROM Vista" + j.ToString() + " WHERE (Ragione_Sociale LIKE @Nome + ''%'')";
     }


     con.Open();

     SqlCommand cmd = new SqlCommand(comand, con);
     cmd.Parameters.Add("@Nome", System.Data.SqlDbType.VarChar).Value ="ma";

     cmd.ExecuteNonQuery();
     cmd.Dispose();


     con.Close();


 }

推荐答案

在SQL中，您不能使用参数http://www.sqlservercentral.com/articles/Miscellaneous/paramterizingviews/1178/ [ ^ ]

我建议您创建一个存储过程，然后再传递参数值.

乔
Giovanni

in SQL you cannot have views with parameters http://www.sqlservercentral.com/articles/Miscellaneous/paramterizingviews/1178/[^]

I suggest you creating a Stored Procedure and than you can pass the parameter value.

Ciao
Giovanni

SqlConnection conn = null;
conn = new SqlConnection("yourConnectionString");
conn.Open();
string strSQLCommand = "CREATE VIEW vw_YourView AS SELECT YOurColumn FROM YourTable";
SqlCommand command = new SqlCommand(strSQLCommand, conn); 
string returnvalue = (string)command.ExecuteScalar(); 
conn.Close();

这篇关于C#-运行SQL查询时出现问题(在数据库中创建VIEW)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！