DataContext.ExecuteQuery parameter issues

Question

I get the following error:

Procedure or function 'procTestReport' expects parameter '@StartDate', which was not supplied.

When I execute the following code:

String[] args = new String[2]{StartDate.ToShortDateString(), EndDate.ToShortDateString()};

lst = dbContext.ExecuteQuery<Summary>("procTestReport", args).ToList<Summary>();

Are the arguments I pass supposed to be presented in a different way? From the following link it seems like I'm using it correctly: https://msdn.microsoft.com/en-us/library/bb361109.aspx

you have to give the parameter name – Vivek Nuna Oct 18 '16 at 16:21 — Vivek Nuna, Oct 18 '16 at 16:21
@viveknuna great, can you show me an example how? – Angel Cloudwalker Oct 18 '16 at 16:29 — Angel Cloudwalker, Oct 18 '16 at 16:29

score 5 · Answer 1 · answered Oct 18 '16 at 16:45

5

You need to declare the parameters in your sql statement. For example:

lst = dbContext.ExecuteQuery<Summary>("EXEC dbo.procTestReport @StartDate={0}, @EndDate={1}", args).ToList<Summary>();

answered Oct 18 '16 at 16:45

Tom Regan

3,580
4
42
71

score 0 · Accepted Answer · edited Dec 18 '16 at 18:19

If you need to execute a stored procedure you can use instead:

SqlConnection con = new SqlConnection("your ConnectionString");

SqlCommand cmd = new SqlCommand("your SP_Name", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("param name", "param value"));
cmd.Parameters.Add(new SqlParameter("param2 name", "param2 value"));
.
.
.
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
con.Close();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);

return dt;

dt contains the return of the stored procedure.

If is necessary to use LinQ, try this

dataContext.ExecuteCommand("EXEC usp_SomeProcedure {0}, {1}, {2}", param1, param2, param3);

understood, but as per our shop, I have use datacontext to follow suit with previous code. — Angel Cloudwalker, Oct 18 '16 at 16:31

DataContext.ExecuteQuery parameter issues

2 Answers2