Csharp MySQL how to insert variable into query

Question

This is what I am currently trying:

var value = Datepicker1.Value;

List<ChartData> data = new List<ChartData>();
string connetionString;
MySqlConnection cnn;
connetionString = “TAKEN OUT FOR SECURITY”;
cnn = new MySqlConnection(connetionString);
cnn.Open();

MySqlDataAdapter da = new MySqlDataAdapter("SELECT SUBSTRING(date_time,1,10), sum(Total), sum(Total_ly),  AVG ((Total + Total_ly)/2) FROM Transaction WHERE Substring(date_time,1,10) LIKE '%@param1%' Group By date_time Having sum(Total)<>0; ", cnn);   
da.SelectCommand.Parameters.AddWithValue("@param1", value);

DataTable table = new DataTable();
da.Fill(table);

So, all im trying to do is this: WHERE Substring(date_time,1,10) like '%2/24/2019%' Thats the hardcoded version i just need to have this stored in a variable and check the variable rather than the hardcoded string if that makes sense ?

But its not working ! And is giving no SQL exception !

Any help appreciated.

This is probably because you are supplying a DateTime variable which is not presenting itself in the query as you expect. What datatype is the date_time column and what is an example value if its not a DATETIME. — Alex K.
– Alex K., Commented Mar 20, 2019 at 13:13

Cody Gray · Accepted Answer · 2019-03-20 17:03:23Z

1

Incompatibilities between run time program and database was actually nothing to do with the SQL. Date formats need to be the same. For future reference, this is how to fix it:

String value = Datepicker1.Value.ToString();
String day = value.Substring(0,10);

It is a very simple fix.

edited Mar 20, 2019 at 17:03

Cody Gray♦

246k53 gold badges513 silver badges591 bronze badges

answered Mar 20, 2019 at 14:18

juniordev101

237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SMa · Accepted Answer · 2019-03-20 13:21:01Z

0

If I understand correctly, you want to have the Values for "date_time,1,10", "Total" and so on added to your SQL String, is this correct?

Try this, and put it as the first Argument in your MySqlDataAdapter

var sql = $"SELECT SUBSTRING({date_time},1,10), sum({Total}), sum({Total_ly}),  AVG ({(Total + Total_ly)/2}) FROM Transaction WHERE Substring({date_time},1,10)= @param1 Group By date_time Having sum({Total})<>0; ";

---EDIT----

Ok according to your edit, try the solution from here https://dev.mysql.com/doc/dev/connector-net/6.10/html/P_MySql_Data_MySqlClient_MySqlDataAdapter_SelectCommand.htm

edited Mar 20, 2019 at 13:21

answered Mar 20, 2019 at 12:50

SMa

1251 silver badge8 bronze badges

3 Comments

juniordev101 Over a year ago

Not exactly i want to take in a date from the user i.e.24/02/2019, which i have stored in the variable value, which is param1, so ill i want to do is check the DB for this value and display results, it works if i hard code it just doesnt when i have in a parameter @SMA

juniordev101 Over a year ago

Still no luck, if i set the parameter to be the date 24/02/2019 it works fine its just from the variable it wont accept

SMa Over a year ago

Good that you found the solution. Next time, in order to find an answer faster you should post the value and type of your variables - e.g. date_time in this case.

Collectives™ on Stack Overflow

Csharp MySQL how to insert variable into query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related