Parameter direction output problem in sqlce

Question

0.00/5 (No votes)

See more:

I'm getting error in parameter direction output in sqlce query for paging.

What I have tried:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY [invoiceId] desc) AS RowNumber,invoiceId,invoice.customerName,invoice.custId,customerPhone,customerAddress,packageName,packageId,amountIncTax,fromDate,toDate,needTrainer,assignedTrainerName,assignedTrainerId,trainerFromDate,trainerToDate,trainerFeesPerMonth,trainerTotPaid,finalFeeAmountIncTax,discountPerc,discountAmount,finalFeeIncTaxAfterDisc,taxPerc,taxAmount,paidOn,paidAmnt,invoicePrinted into #Results from invoice inner join master_customer on master_customer.custId=invoice.custId WHERE lower('Inv'+(cast(invoiceId as varchar(max)))+' '+invoice.customerName+' '+REPLACE(CONVERT(VARCHAR,fromDate, 106), ' ', '-')+' '+REPLACE(CONVERT(VARCHAR,toDate, 106), ' ', '-')+' ') LIKE +'%' + @SearchTerm + '%';
 SELECT @RecordCount = COUNT(*) FROM #Results;
SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1; DROP TABLE #Results;

and then my parameters are

C#

cmd.Parameters.AddWithValue("@PageSize", pageSize);
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@SearchTerm", searchQuery.ToLower().Trim());
                    cmd.Parameters.Add("@RecordCount", SqlDbType.Int);
                    cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

I'm getting error in parameter direction output. but for paging purpose i need it because all example in internet using this type of query for paging.

Posted 16-May-16 18:46pm

souvikcode

Updated 16-May-16 19:06pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Animesh Datta · Accepted Answer · 2016-05-16T19:06:00

Solution 1

Hello ,
Output Parameter is not supported in SqlCe . Instead of that , you may use ExecuteScalar method .

int recordcount= (int)cmd.ExecuteScalar();

Thanks

Posted 16-May-16 19:06pm

Animesh Datta

Updated 16-May-16 19:07pm

v2

Comments

souvikcode 17-May-16 3:38am

can you please give example where to write that? I have posted full query so that users can give me answer with where to change. With my query above where should I write cmd.ExecuteScalar()?

Animesh Datta 17-May-16 7:31am

You simple call it at bottom after adding parameters
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@SearchTerm", searchQuery.ToLower().Trim());
//call here
int recordcount= (int)cmd.ExecuteScalar();

souvikcode 17-May-16 7:36am

okk,got it.thanks.

Animesh Datta 17-May-16 8:20am

if your problem solved then accept the answer so that it will not appear in unanswered list