CODEWITHSUNDEEP
mysqlvb.net
oozmac
oozmac

Reputation: 161

Mysql query not inserting values

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.

Upvotes: 0

Views: 155

Answers (1)

T.S.
T.S.

Reputation: 19330

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

Upvotes: 1

Related Questions