Incorrect syntax near ', '.'

Question

1.00/5 (1 vote)

See more:

C#

txtTotal.Text = getTotal("Select (sum(total_netpay),0) from emp_payroll where week(PDate) =week(now())").ToString("#,##0.00");

The problem is Incorrect syntax near ','.'

What I have tried:

C#

txtTotal.Text = getTotal("Select ifnull(sum(total_netpay),0) from emp_payroll where week(PDate) =week(now())").ToString("#,##0.00");

Posted 11-Jun-23 20:15pm

Hackweiser27

Updated 12-Jun-23 2:54am

PIEBALDconsult

v2

Add a Solution

Comments

mtoha 12-Jun-23 2:22am

what is get total function?

Hackweiser27 12-Jun-23 2:34am

public double getTotal(string sql)
{
double _total = 0;

// try
{

P_Connection NewConnection = new P_Connection();
NewConnection.Connection_P();

SqlCommand comm = new SqlCommand(sql,P_Connection.conn);
// comm.Connection = P_Connection.conn;

_total = double.Parse(comm.ExecuteScalar().ToString());

}
// catch
{
MessageBox.Show("Error", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

}
return _total;

}

mtoha 12-Jun-23 2:36am

when you debug string sql on get total, what is the value of sql string?

Hackweiser27 12-Jun-23 2:48am

0

Hackweiser27 12-Jun-23 2:58am

I tried to change ifnull to ISNULL my error is An expression of non-boolean type specified in a context where a condition is expected, near 'DATENAME'.'

0x01AA 12-Jun-23 3:02am

Use COALESCE instead of ISNULL.
See here: SQL Server COALESCE() Function[^]

mtoha 12-Jun-23 2:57am

Select ifnull(sum(total_netpay),0) from emp_payroll where week(PDate) =week(now()). if that sql above is fine, maybe the error occured at another function not there,

Hackweiser27 12-Jun-23 3:01am

txtTotal.Text = getTotal("Select ISNULL(SUM(total_netpay),0) from emp_payroll where DATENAME(WEEKDAY,PDate) DATENAME(WEEKDAY,Now))").ToString("#,##0.00"); I tried to change. when I try again the error is An expression of non-boolean type specified in a context where a condition is expected, near 'DATENAME'.'

2 solutions

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)

mtoha · Accepted Answer · 2023-06-11T21:11:00

Solution 2

Try to change your code like this :

txtTotal.Text = getTotal("Select ISNULL(sum(total_netpay),0) from emp_payroll where PDate is not null AND  DATENAME(week, PDate) = DATENAME(week, getdate()) AND year(getdate()) = year(PDate)").ToString("#,##0.00");

Posted 11-Jun-23 21:11pm

mtoha

Comments

Hackweiser27 12-Jun-23 3:18am

Thank you @mtoha. i got this error: Operand data type varchar(max) is invalid for sum operator.'

CHill60 12-Jun-23 4:10am

Your database design has total_netpay stored in varchar(max) - don't do that. Always use the correct data type for the data you are storing - in this case one of the numeric types would be appropriate.
See What Are Data Types and Why Are They Important?[^]

mtoha 12-Jun-23 5:35am

this style is called cowboy coding.. new style of coding anounced at 1900.

mtoha 12-Jun-23 3:22am

change to this:
txtTotal.Text = getTotal("Select ISNULL(sum(convert(decimal, isnull(total_netpay, 0)),0) from emp_payroll where PDate is not null AND DATENAME(week, PDate) = DATENAME(week, getdate()) AND year(getdate()) = year(PDate)").ToString("#,##0.00");

Hackweiser27 12-Jun-23 6:01am

OK THank you.

OriginalGriff · Accepted Answer · 2023-06-11T20:59:00

Solution 1

IFNULL is not an SQL Server function: it's only applicable to MySql and SqLite - for which you would need a different set of DB classes: MySqlConnection, SqliteConnection, ... and so on.

The SQL Server equivalent is ISNULL

Posted 11-Jun-23 20:59pm

OriginalGriff

Comments

Hackweiser27 12-Jun-23 3:11am

yes, I found it my problem is when I try again my error is An expression of non-boolean type specified in a context where a condition is expected, near 'DATENAME'.'