0

I have this MS SQL query inside the String.Format:

    string sqlCommandx = String.Format("SELECT CASE WHEN sum(d1) > 0 THEN sum(n1)/sum(d1) ELSE null END as metricScore1 FROM DataBridgeStage Where programID={0} and DATEPART(m,reportingDate) = {1} and DATEPART(yyyy,reportingDate) = {2}",
            ProgramID, month, year);

I'm getting an error that says: SQL logic error or missing database\r\nno such column: m. Is there a a problem with my syntax?

Bot Sundi
  • 109
  • 8
  • `DATEPART(m,reportingDate)` Do you have the `m` column in the table? – Valentin Mar 15 '16 at 15:00
  • 4
    You have a comma `,` after `metricScore1` and immediately before the `from` which is a syntax error in SQL... but my guess is why you're getting the error you are is because you don't have the `+` at the end of the 2nd line of text. You need to fix both – freefaller Mar 15 '16 at 15:00
  • 1
    http://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it –  Mar 15 '16 at 15:02
  • I don't have issues with the commas and pluses( +). This one is working when I ran it in MS SQL : SELECT DATEPART(m,GETDATE()), DATEPART(yyyy,GETDATE()) – Bot Sundi Mar 15 '16 at 15:04
  • Good luck @Bot - you've just lost my interest in your problem, as you appear to have thrown my suggestion away without realising what I'm telling you – freefaller Mar 15 '16 at 15:05
  • I didn't mean that. I'm sorry @freefaller – Bot Sundi Mar 15 '16 at 15:07
  • 4
    Don't build SQL using string concatenation (either directly using `+` or using string.Format). Use *parameters*. It's what they're designed for, and it's a safe approach which means you don't have to worry about SQL Injection. – Damien_The_Unbeliever Mar 15 '16 at 15:09
  • What are the values of the ProgramID, month, year arguments? – Ɖiamond ǤeezeƦ Mar 15 '16 at 15:22
  • ProgramID = 1, month = 1, year = 2016 – Bot Sundi Mar 15 '16 at 15:23
  • have you tried replacing this part DatePart(m,reportingDate) with Datepart(month,reportingDate)? – thepanch Mar 15 '16 at 15:31
  • Yes, here's the error: "SQL logic error or missing database\r\nno such column: month" – Bot Sundi Mar 15 '16 at 15:35
  • Please tag what database system you're using. Most people seeing C# and just "sql" may assume SQL Server but "SQL logic error or missing database\r\nno such column: month" isn't a SQL Server error message and datetime functions vary between database systems. – Damien_The_Unbeliever Mar 15 '16 at 15:42
  • I'm using Microsoft SQL Server. – Bot Sundi Mar 15 '16 at 15:43
  • Are you **sure**? Because when I google the phrase "SQL logic error or missing database", all of the hits I'm finding are for SQLite. – Damien_The_Unbeliever Mar 15 '16 at 15:45
  • Yes my source data is from MS SQL and I have a service that migrate the data to SQL Lite. – Bot Sundi Mar 15 '16 at 15:46
  • 1
    Well, the database you're sending that query to is the SQLite one, not the SQL Server one, and *my* mention of SQLite was the first one that's turned up here. – Damien_The_Unbeliever Mar 15 '16 at 15:47
  • Thanks. That would help @Damien_The_Unbeliever – Bot Sundi Mar 15 '16 at 15:48
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106378/discussion-between-bot-sundi-and-damien-the-unbeliever). – Bot Sundi Mar 15 '16 at 15:53
  • How to get the month number of a date using SQLite? – Bot Sundi Mar 15 '16 at 15:53

2 Answers2

0

I finally got the fixed. But I would like to thank @Damien_The_Unbeliever. I didn't think that it's a SQLite issue. In this case, I used the SQLite syntax. Here's the syntax:

string sqlCommandx = String.Format("SELECT CASE WHEN sum(d1) > 0 THEN sum(n1)/sum(d1) ELSE null END as metricScore1 FROM DataBridgeStage Where programID={0} and strftime('%m',reportingdate) = '{1}' and strftime('%Y',reportingdate)  = '{2}'",
        ProgramID, month, year);
Bot Sundi
  • 109
  • 8
-2

the correct form to use String.Format is:

String.Format("{0} {1}",one,two);
Alejandro Callis
  • 1