Error when trying to run a stored procedure with data from Excel in a telerik coded step

Question

I am trying to run a SQL Server stored procedure which takes input parameters from a excel sheet and it is throwing me an error.

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CreatedDate DESC'

enter image description here

Code:

string SP = "dbEmail_Message_GetMessageListForFolder";
SqlCommand cmd = new SqlCommand(SP, conn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

string myPath = @"C:\Users\Monica\Desktop\ExcelTest.xlsx";
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(myPath);

// select the correct worksheet
Excel.Worksheet demoWorksheet = (Excel.Worksheet)excelApp.Worksheets["Sheet1"];

excelApp.Visible = false;

// select the right cell in the correct worksheet
// var value = Convert.ToString(demoWorksheet.get_Range("E2", "E2").Value2) ;
// file2.WriteLine("value=" + value);       
// file2.Close();

cmd.Parameters.Add("@strCurrentUser", SqlDbType.VarChar);
cmd.Parameters["@strCurrentUser"].Value = '"'+Convert.ToString(demoWorksheet.get_Range("A2", "A2").Value2)+'"';

cmd.Parameters.Add("@intCurrentSite", SqlDbType.Int);
cmd.Parameters["@intCurrentSite"].Value = demoWorksheet.get_Range("B2", "B2").Value2; 

cmd.Parameters.Add("@intStatus", SqlDbType.Int);
cmd.Parameters["@intStatus"].Value = demoWorksheet.get_Range("C2", "C2").Value2;

cmd.Parameters.Add("@blnSentMailOnly", SqlDbType.Int);
cmd.Parameters["@blnSentMailOnly"].Value = demoWorksheet.get_Range("D2", "D2").Value2;

cmd.Parameters.Add("@strSortBy", SqlDbType.VarChar);
cmd.Parameters["@strSortBy"].Value = demoWorksheet.get_Range("E2", "E2").Value2;

cmd.Parameters.Add("@intToSite", SqlDbType.Int);
cmd.Parameters["@intToSite"].Value = demoWorksheet.get_Range("F2", "F2").Value2;

cmd.Parameters.Add("@SubCategory", SqlDbType.VarChar);
cmd.Parameters["@SubCategory"].Value = demoWorksheet.get_Range("G2", "G2").Value2;

cmd.Parameters.Add("@intPageIndex", SqlDbType.Int);
cmd.Parameters["@intPageIndex"].Value = demoWorksheet.get_Range("H2", "H2").Value2;

cmd.Parameters.Add("@strToUserName", SqlDbType.VarChar);
cmd.Parameters["@strToUserName"].Value = '"' + Convert.ToString(demoWorksheet.get_Range("I2", "I2").Value2) + '"';

cmd.Parameters.Add("@intPageSize", SqlDbType.Int);
cmd.Parameters["@intPageSize"].Value = demoWorksheet.get_Range("J2", "J2").Value2;

cmd.Parameters.Add("@Category", SqlDbType.VarChar);
cmd.Parameters["@Category"].Value = '"' + Convert.ToString(demoWorksheet.get_Range("K2", "K2").Value2) + '"';

SqlParameter rowCount = cmd.Parameters.Add("@intTotalRecords", System.Data.SqlDbType.Int);
rowCount.Direction = System.Data.ParameterDirection.Output;

cmd.Connection = conn;
conn.Open();

SqlDataReader alertReader = cmd.ExecuteReader();
excelApp.ActiveWorkbook.Save();

excelApp.Workbooks.Close();
int count = 0;

System.IO.StreamWriter file = new System.IO.StreamWriter("C:\\Users\\Monica\\Documents\\source.txt");

if (alertReader.HasRows)
{
    while (alertReader.Read())
    {
        count = count + 1;
        file.WriteLine("DB Alert Count is:" + count);
    }

    alertReader.Close();
}

I have another test script which runs the same stored procedure with input values directly provided in the test script which works fine.

SqlConnection conn = new SqlConnection(connStr);

        string SP = "dbEmail_Message_GetMessageListForFolder";
        SqlCommand cmd = new SqlCommand(SP, conn);

        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.Add("@strCurrentUser", SqlDbType.VarChar);
        cmd.Parameters["@strCurrentUser"].Value = "derek";

        cmd.Parameters.Add("@intCurrentSite", SqlDbType.Int);
        cmd.Parameters["@intCurrentSite"].Value = 19;

        cmd.Parameters.Add("@intStatus", SqlDbType.Int);
        cmd.Parameters["@intStatus"].Value = 0;

        cmd.Parameters.Add("@blnSentMailOnly", SqlDbType.Int);
        cmd.Parameters["@blnSentMailOnly"].Value = 0;

        cmd.Parameters.Add("@strSortBy", SqlDbType.VarChar);
        cmd.Parameters["@strSortBy"].Value = "CreatedDate DESC";

        cmd.Parameters.Add("@intToSite", SqlDbType.Int);
        cmd.Parameters["@intToSite"].Value = 12;

        cmd.Parameters.Add("@SubCategory", SqlDbType.VarChar);
        cmd.Parameters["@SubCategory"].Value = "RouteToPCPRequest";

        cmd.Parameters.Add("@intPageIndex", SqlDbType.Int);
        cmd.Parameters["@intPageIndex"].Value = 1;

        cmd.Parameters.Add("@strToUserName", SqlDbType.VarChar);
        cmd.Parameters["@strToUserName"].Value = "hunterjane";

        cmd.Parameters.Add("@intPageSize", SqlDbType.Int);
        cmd.Parameters["@intPageSize"].Value = 100;

        cmd.Parameters.Add("@Category", SqlDbType.VarChar);
        cmd.Parameters["@Category"].Value = "OnlineNotification";

        SqlParameter rowCount = cmd.Parameters.Add("@intTotalRecords", System.Data.SqlDbType.Int);
        rowCount.Direction = System.Data.ParameterDirection.Output;

        cmd.Connection = conn;
        conn.Open();

        SqlDataReader alertReader = cmd.ExecuteReader();

The whole error log is pasted below:

Failure Information: ~~~~~~~~~~~~~~~ Exception thrown executing coded step: '[VerifyAlerts_CodedStep2] : Verify number of 'divs' equal to number of 'alerts''. InnerException: System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CreatedDate DESC'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Communicator.Alerts.Alerts_VerifyDivsCount_WithDatabase.Alerts_VerifyDivsCount_WithDatabase__Copy_CodedStep() in c:\tfs\HASINC\Development\QAAutomation\Communicator\Alerts\Alerts_VerifyDivsCount_WithDatabase - Copy.tstest.cs:line 123 ClientConnectionId:a4f49f47-65aa-4e0d-af2c-8791d3f2f610

Can Someone please let me where am I going wrong?

Jay Jay · Accepted Answer · 2014-08-22T17:51:56

CreatedDate DESC looks like it should be in the ORDER BY clause not in the list of column names but I don't see the SQL code which is where the exception is coming from..

Error when trying to run a stored procedure with data from Excel in a telerik coded step

1 Answers