如何在VB.NET中的内连接表中修复模糊列名'id' [英] How to fix ambiguous column name 'id' when inner join tables in VB.NET

问题描述

SqlStr =选择ROW_NUMBER（）OVER（ORDER BY id）AS [Sno]，Employees.EmpID，Employees.contactno，Empname as [Employee Name]，WorkType，ClientName，SiteName，Siteschedule.TotalHours AS [总工作时间] ]来自Siteschedule内部联接客户端客户端.clientid = Siteschedule.clientid内部联接站点site.SiteID = Siteschedule.SiteID内部联接Employees雇员.EmpID = Siteschedule.empid其中（StartDateValue介于& TxtStartDate.Value.Date.ToOADate之间） &和& TxtEndDate.Value.Date.ToOADate&）& substring



当innerjoining错误是ambigous columnname id



我尝试了什么：



i尝试了一个解决方案，但没有改变同样的错误

解决方案

根据这里发布的内容。此查询引用了id列

 选择 ROW_NUMBER（） OVER （ ORDER   BY  id）

id列可能存在于多个表中



Siteschedule

clients

网站

员工



要解决此错误，您可以更新代码以使用表别名，然后更新此片段ORDER BY id类似于ORDER BY [Table Alias] .id



SQL别名 [ ^ ]



顺便说一句，我还建议使用参数化查询

https://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

< blockquote>该错误表示多个表中有一列 ID ;并且查询不知道您想要哪一个。要修复它，使用表名限定。

也就是说，修复你的WHERE子句，因为SQL注入已经成熟;永远不要使用字符串方法构建Sql命令，你应该使用的是参数

  //  仅为简洁语句的WHERE条件 
 SqlStr =   WHERE（StartDateValue BETWEEN @StartDate AND @EndDate） 
 cmd.Parameters.AddWithValue（  @ StartDate，TxtStartDate.Value.Date.ToOADate）; 
 cmd.Parameters.AddWithValue（  @ EndDate，TxtEndDate.Value.Date.ToOADate ）; 

SqlStr = "select ROW_NUMBER() OVER (ORDER BY id) AS [Sno],Employees.EmpID,Employees.contactno,Empname as [Employee Name],WorkType,ClientName,SiteName,Siteschedule.TotalHours AS [Total Working Hours] from Siteschedule inner join clients on clients.clientid=Siteschedule.clientid inner join sites on sites.SiteID=Siteschedule.SiteID inner join Employees on Employees.EmpID=Siteschedule.empid where (StartDateValue between " & TxtStartDate.Value.Date.ToOADate & " and " & TxtEndDate.Value.Date.ToOADate & ") " & substring

when innerjoining error is ambigous columnname id

What I have tried:

i have tried one solution but not change same error

解决方案

Based on what posted here. This query is referencing the id column

select ROW_NUMBER() OVER (ORDER BY id)

The id column could exists in more than one tables

Siteschedule
clients
sites
Employees

To solve this error, you can update the code to use table alias and then update this piece "ORDER BY id" to something like ORDER BY [Table Alias].id

SQL Aliases[^]

By the way, I would also suggest to use Parameterized query
https://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

That error means there is a column ID in multiple tables; and the query does not know which one you want. To fix it, qualify it with the table name.
That said, fix your WHERE clause as it is ripe for SQL Injection; NEVER EVER build an Sql Command using string methods, what you should use are Parameters

// just the WHERE condition of the statement for brevity
SqlStr ="WHERE (StartDateValue BETWEEN @StartDate AND @EndDate)" 
cmd.Parameters.AddWithValue("@StartDate", TxtStartDate.Value.Date.ToOADate);
cmd.Parameters.AddWithValue("@EndDate", TxtEndDate.Value.Date.ToOADate);

这篇关于如何在VB.NET中的内连接表中修复模糊列名'id'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！