0

I've facing this "invalid column name" error when I try to run my vb.net code. This vb.net will pass some parameters to a stored procedure. But when I try to execute the stored procedure from SQL server management studio, it works.

Below is my code: 

    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

And here is my store procedure:

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

Any advice/help is appreciated!

Tony
  • 119
  • 1
  • 2
  • 13
  • I don't see how that code could even compile. Look at the declaration for `RunSPReturnInteger` and look at how you're calling it. Not even close. You create a `SqlParameter` array and never use it. – jmcilhinney Jul 23 '18 at 07:15
  • @jmcilhinney, sorry, that's an old code, there I've edit it already. thanks for that – Tony Jul 23 '18 at 07:19
  • Why are you changing the SqlDbType of every parameter to Structured? You're setting that property via the constructor to the proper value and then you change it for no apparent reason inside `RunSPReturnInteger`. – jmcilhinney Jul 23 '18 at 07:51
  • @jmcilhinney well, I'm still a beginner, I just follow https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.value(v=vs.110).aspx and also from here https://stackoverflow.com/questions/16070932/sqlparameters-array-in-vb-net – Tony Jul 23 '18 at 07:59
  • I don't see where either of those tell you to set the `SqlDbType` to `Structured`, so neither is the reason. Set the `SqlDbType` of each parameter to the appropriate data type for the data. If you don't know what those values mean, do some research. Anyway, if that error message is being generated when the final SQL gets executed then you ought to determine exactly what that SQL code is, don't you think? There's no point just looking at the code to build it. Look at the result and se whether it is what you expect. – jmcilhinney Jul 23 '18 at 08:23
  • I don't get what you mean, I've been fixing this since last Tuesday, still don't know why this error appear, besides when I try to print the execution statement and execute it by my self in SQL Management Studio, no error show up..anyway thanks for your time. – Tony Jul 23 '18 at 08:38
  • So what is the actual SQL code being executed? Please provide **ALL** the relevant information. – jmcilhinney Jul 23 '18 at 08:43
  • I doubt that your SQL Code actually does make sense. You're wrapping your lists of column names in single-quotes, which denotes text in SQL and your second column list seems to contain a lone double-quote. I don't think that you actually have tested the actual SQL code that gets generated by executing that. – jmcilhinney Jul 23 '18 at 08:47
  • exec insertUnknownDir tbl_Sources, 'sPlayerID,'''',sPlayerName', tbl_Dest, 'dPlayerID,'',dPlayerName'. this is the exact query,and if I run this,on SQL Management Studio, its working. – Tony Jul 23 '18 at 08:51
  • As a side note, even though you are using a stored procedure and parameters, since you are concatenating strings inside the stored procedure, it is still vulnerable to sql injection attacks. – Chris Dunaway Jul 23 '18 at 14:40
  • You are calling LoadData with 6 parameters but the Function only has 4. – Mary Jul 23 '18 at 21:34
  • There is a great answer by Dan Guzman at https://stackoverflow.com/questions/51372427/using-like-and-in-a-stored-procedure-with-parameter-in-from-clause/51372665#comment89717836_51372665 Look at the stored procedure it is similar to yours. The SqlDataType for table names and column names is NVarChar, 128 – Mary Jul 23 '18 at 21:56

1 Answers1

0

Since you use dynamic SQL there may be a combination of parameters that fail. It is better to run SQL Profiler to catch the SQL statements executed on a specific database to see the exact SQL query that caused error

Eralper
  • 6,461
  • 2
  • 21
  • 27