从vb.net运行时列名无效

Question

当我试图运行我的vb.net代码时，我遇到了这个"无效列名"错误。这个vb.net将把一些参数传递给一个存储过程。但是，当我尝试从SQL server management studio执行存储过程时，它是有效的。

以下是我的代码：

LoadData("tbl_Sources", "sPlayerID,'',sPlayerName", "tbl_Dest", "dPlayerID,"",dPlayerName")
------------------------------------------
Public Function LoadData(ByVal frmTblname As String, ByVal frmClmnName As String, ByVal toTblName As String, ByVal toClmnName As String) As Integer
Dim con As New SqlClient.SqlConnection
Dim conManager As New Connection
Dim sqlParams() As SqlParameter = New SqlParameter() { _
New SqlParameter("@fromTable", SqlDbType.NVarChar, 50) With {.Value = frmTblname}, _
New SqlParameter("@fromColumn", SqlDbType.Structured, 4000) With {.Value = "'" + frmClmnName + "'"}, _
New SqlParameter("@toTable", SqlDbType.NVarChar, 50) With {.Value = toTblName}, _
New SqlParameter("@toColumn", SqlDbType.NVarChar, 4000) With {.Value = "'" + toClmnName + "'"} _
}
Dim err As Integer
con = conManager.GetConnection(sConn)      
err = ConnectionExec.RunSPReturnInteger(con, "insertUnknownDir", sqlParams)
Return err
End Function
---------------------------------------------------------
Public Function RunSPReturnInteger(ByVal con As SqlClient.SqlConnection, ByVal strSP As String, ByVal ParamArray commandParameters() As SqlClient.SqlParameter) As Integer
Dim retVal As Integer
LogManager.WriteLog("#################################### Start " + strSP + " ####################################")
Try
Dim cmd As New SqlCommand(strSP, con)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 0
Dim p As SqlClient.SqlParameter
For Each p In commandParameters
p = cmd.Parameters.Add(p)
p.Direction = ParameterDirection.Input
p.SqlDbType = SqlDbType.Structured              
Next
retVal = cmd.ExecuteNonQuery()
cmd.Dispose()
Return retVal
LogManager.WriteLog("Try RunSPReturnInteger 1 -> SP Name : " + strSP + " > " + retVal.ToString)
Catch ex As Exception
'MsgBox(ex.Message)
LogManager.WriteLog("Catch RunSPReturnInteger 1-> SP Name : " + strSP + ">> " + ex.Message)
Throw New Exception(ex.Message, ex)
Return -1
End Try        
End Function

这是我的存储过程：

ALTER procedure [dbo].[insertUnknownDir] @fromTable nvarchar(50),@fromColumn nvarchar(4000),@toTable nvarchar(50),@toColumn nvarchar(4000) 
as 
begin 
declare @Query nvarchar(4000)   print 'exec [insertUnknownDir] '+@fromTable+' ,'+@fromColumn+' ,'+@toTable+','+@toColumn+'' 
SET @Query ='INSERT INTO ['+@toTable+']('+@toColumn+') 
SELECT TOP 15 '+@fromColumn+' 
FROM '+@fromTable+'
WHERE profile.sPlayerID = '+@fromTable+'.sPlayerID'     
exec sp_executesql @Query
END

任何建议/帮助都将不胜感激！

Answer 1

由于使用动态SQL，可能会有一些参数组合失败。最好运行SQL事件探查器来捕获在特定数据库上执行的SQL语句，以查看导致错误的确切SQL查询