0

So I'm starting to learn some vb.net, and I have the following mysql statement to get a distinct count for a column in one of my tables. It's been giving me fits with syntax errors, but pretty much everything I've read suggests what I have should be correct. Also, I took it into MySQL Workbench and it works fine, for whatever reason it just refuses to work in my code.

Dim sRetrieve As String
Dim numvals As OdbcDataReader
sRetrieve = "SELECT COUNT (DISTINCT defect_code) FROM daily_data WHERE MONTH(date)=" & select_month & " AND YEAR(date)=" & select_year
Dim query_exe As New Odbc.OdbcCommand(sRetrieve, cn)
numvals = query_exe.ExecuteReader()

I've been banging my head against this for awhile now, so any help telling me what's wrong with that mysql statement would be greatly appreciated.

Figured it out, the space between COUNT and the first ( was doing it. Such a hilariously small thing for it to have held me up for so long, but I guess that's how it goes. Thanks for the help, everybody.

user2498668
  • 125
  • 1
  • 12
  • You might need to add some quotes around the parameters `select_month` and `select_year`. Also, you should probably be using [parameterized queries](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) and not string concatenation. – jbabey Jun 21 '13 at 17:37
  • The query looks fine as far as it goes. What are the values (and data types) of `select_month` and `select_year`? And how does it "refuse to work"? Is there a MySQL error? A VB error? – Ed Gibbs Jun 21 '13 at 17:37
  • Sticking quotes around it didn't seem to do the trick. select_month and select_year are just integers that are set based on values selected by the user from a dropdown list on the form. For example, select_month could be 6, select_year might be 2013. It's a MySQL error: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.6.11]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM (SELECT DISTINCT defect_code FROM daily_data) WHERE MONTH(date)=6 AND YE' at line 1 – user2498668 Jun 21 '13 at 17:52
  • You should be using parameterized queries as mentioned above. I think you need to tell us more about the variables. – Mike Cheel Jun 21 '13 at 18:11
  • Not that I think it helps your issue above (but it might), you could declare numVals as an integer and then do ExecuteScalar on the command. I am thinking there is something going on with the odbcCommand since your SQL looks good. – APrough Jun 21 '13 at 18:17
  • I'll be sure to look into parameterized queries. I'm something of a beginner and don't know much about them outside of they help protect against SQL injection attacks. Do you suspect switching to that might resolve my issue, or is it just something I should be doing anyway? As for the variables, the error statement has demonstrated that it recognizes the appropriate values from them, so I don't think they're the issue. I've also tried just hard coding values for the Where statement, as well as just deleting it altogether and I'm still getting the same syntax error as above. – user2498668 Jun 21 '13 at 18:21
  • I was thinking the same thing, APrough, but no dice with changing it to an integer and running with ExecuteScalar. – user2498668 Jun 21 '13 at 18:25
  • did your problem solved? – Praveen Prasannan Aug 06 '13 at 04:16

1 Answers1

0

MONTH(date) in mysql returns string. So you need to enclose with ' '.

sRetrieve = "SELECT COUNT (DISTINCT defect_code) FROM daily_data WHERE MONTH(date)='" & select_month & "' AND YEAR(date)=" & select_year;
Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
  • I thought for sure this would fix it, but no such luck. Still, good catch. I'm going to try converting it to a parameterized query and see if that does anything for me. If nothing else, it sounds like it's something I should've done in the first place, if only for security reasons. – user2498668 Jun 21 '13 at 18:48