Mysql query not inserting values

Question

I am trying to use the query below to insert a concatenated converted set of integers to string for use on a datetime field in my table.

TABLE

Field        Type
empID        int(11)
time_stamp   datetime
in_out       char(3)
am_pm        char(2)

QUERY

Dim query As String = "INSERT INTO attendance VALUES(" & empID.Text & _
 "STR_TO_DATE(CONCAT("& empYear.Text & ",'-'," & empMonth.Text & ",'-'," & _
  empDay.Text & ",' '," & empHour.Text & ",':'," & empMin.Text & ",':'," & _
  empSec.Text & ",'%Y-%m-%d %H:%i:%s'),'out','pm')"

There is no problem with the connection and the values. I have tried to insert the values into a test column of string type and the output is this:

133201712311827

I am pretty sure it's with how I use these characters: '' "" "," - :. I just can't figure out how.

Do yourself a favor - parameterize your SQL and you will not need this sadomazo of a date — T.S.
– T.S., Commented Jan 23, 2017 at 4:10
do you have a clear/step by step/good read step by step on how to parameterize sql in vb.net? — oozmac
– oozmac, Commented Jan 23, 2017 at 5:29
There are plenty examples for query parameterization here on SO - see my answer — T.S.
– T.S., Commented Jan 23, 2017 at 5:32
this 133201712311827 doesn't look right. According to your code should be 201712311827133 — T.S.
– T.S., Commented Jan 23, 2017 at 15:40
133201712311827 133 = empID.Text 2017 = empYear.Text 1 = empMonth.Text 23 = empDay.Text 11 = empHour.Text 8 = empMin.Text 27 = empSec.Text — oozmac
– oozmac, Commented Jan 24, 2017 at 11:11

T.S. · Accepted Answer · 2017-01-24 15:32:41Z

1

First problem I see, here

& empID.Text & "STR_TO_DATE(. . .  .

you're missing comma after first value

& empID.Text & "***,*** STR_TO_DATE(. . . .

Second issue, I identified when I've replaced your text values with hard coded values - You are missing closing parenthesis for str_to_date. Here ,'%Y-%m-%d... should be ), '%Y-%m-%d...

STR_TO_DATE(CONCAT(1999,'-',01,'-',01,' ',10,':',25,':',30***)***,'%Y-%m-%d %H:%i:%s')

As you see- my replacement shows that you have no issues with concatenation, single quote and :. Theo only other variable here is quality of data in text boxes.

Update

This answer (above) is correct. Using sql fiddle I created schema and when replaced text box values with hard-coded ones - all worked. My suggestions to add comma and parenthesis hold true. Your claim about problems with single quotes are false.

create table xxx (empID int(11), time_stamp datetime, in_out char(3), am_pm  char(2));
INSERT INTO xxx VALUES(123,
    STR_TO_DATE(CONCAT('2017','-','1','-','23',' ','10',':','35',':','40'),'%Y-%m-%d %H:%i:%s'),
    'out','pm');

commit;
Select * from xxx

empID | time_stamp | in_out | am_pm
123 | January, 23 2017 10:35:40 | out | pm

End Update

On top of that, you could do it much better by parameterizing, which will look like something like this

command.CommandText = "insert into ... values (@1, @2, @3, @4)"
command.Parameters.AddWithValue("@1", Convert.ToInt32(empID.Text))
dim date as new DateTime(Convert.ToInt32(empYear.Text), Convert.ToInt32(empMonth.Text), . . . . )
command.Parameters.AddWithValue("@2", date)
. . .  . . .
command.ExecuteNonQuery()

Parameterizing will make it easy to work with dates and strings

edited Jan 24, 2017 at 15:32

answered Jan 23, 2017 at 4:12

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

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

9 Comments

oozmac Over a year ago

i tried using your corrections but it did not insert any data :(

T.S. Over a year ago

@oozmac My corrections are correct. if you parameterize you should have no issues. Unfortunately I am not big on MySql, so you need to check correct usage of mySql functions. For example, I don't know if this '%Y-%m-%d %H:%i:%s' is correct. And it also would be helpful to know what error you're getting

oozmac Over a year ago

that is correct.. as i have said the query works fine as is without ' ' " " : these things

T.S. Over a year ago

@oozmac Well, until you post error it will not be possible to judge - too many variables because data comes from text boxes. The things I told you - missing comma, and missing parenthesis are correct. Once you get that right, I see no issues related to ' " : because those are just strings

oozmac Over a year ago

i am doing what you have advised me and so far empID.Text got inserted to a temporary table just to try if it works, however, i'm not sure how to str_to_date(concat()) the separate values of 'year, month, day, ' ' , hour, min, sec' to insert to my datetime datatype time_stamp column

|

Collectives™ on Stack Overflow

Mysql query not inserting values

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related