为什么这个sql语句不能正常工作？ [英] Why is this sql statement not working???

问题描述

我在Visual Studio中运行此错误时遇到此错误，在vb中："查询表达式'row_number（）中的语法错误（缺少运算符）（按代码顺序划分代码）'。"

这是我正在使用的sql语句，它在sql server中运行！：

 select * Into NewTabe from（select *，row_number（）over（按代码顺序按代码分区）as rn From database）t其中rn = 1 order by Code 

解决方案

你好chrisetiquette，

感谢您在MSDN论坛上发帖。

根据您的描述，我试图测试查询字符串。但是我没有在我的项目中发现这个问题，我可以在数据库中插入一个新表（NewTabe）。我在这种情况下使用了以下代码片段：

 Dim ConnStr As String =" Data Source =（LocalDB）\ MSSQLLocalDB; Initial Catalog = Database1; Integrated安全=真" 
 Dim Conn As SqlConnection = New SqlConnection（ConnStr）
 Dim query As String =" select * Into NewTabe from（select *，row_number（）over（partition by Code order by Code）as rn From database ）t其中rn = 1 by order by 
 Dim cmd As SqlCommand = New SqlCommand（query，Conn）
 Conn.Open（）
 cmd.ExecuteNonQuery（）
 Conn.Close（）

所以我建议您检查项目中的查询字符串，可能是一个小错误。

检查连接字符串以及相同的表格数据库。

希望有所帮助。

最好的问候，

Neda Zhang

I am getting this error when i run this in visual studio, in vb: "Syntax error (missing operator) in query expression 'row_number() over (partition by Code order by Code)'."

Here is the sql statement I am using, it works in sql server!:

select * Into NewTabe from( select *, row_number() over (partition by Code order by Code) As rn From database ) t where rn = 1 order by Code

解决方案

Hi chrisetiquette,

Thank you for posting in MSDN forum.

According to your description, I have tried to test the query string. But I didn't found this issue in my project, I could insert a new table(NewTabe) in database. And I used the following code snippet for this case:

Dim ConnStr As String = "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=Database1;Integrated Security=True"
Dim Conn As SqlConnection = New SqlConnection(ConnStr)
Dim query As String = "select * Into NewTabe from( select *, row_number()over(partition by Code order by Code) As rn From database) t where rn = 1 order by Code"
Dim cmd As SqlCommand = New SqlCommand(query, Conn)
Conn.Open()
cmd.ExecuteNonQuery()
Conn.Close()

So I suggest that you could check your query string in your project, maybe it was a small error.

Check the connection string and whether the tables within the same database.

Hope it helps.

Best Regards,

Neda Zhang

这篇关于为什么这个sql语句不能正常工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！