cannot insert datetime in visual basic

Question

I have one question and was wondering if someone could help.. I have created a program in vb net. When I press a button then it should insert in my sql database in the column "date" the current date and time. I use for this purpose the following code :

query1 = "insert into visit(visit,textfile,p_id) VALUES('" & Date.Today & "','" & s & "',(select patient.p_id from patient where (patient.name=('" & ComboBox1.Text & " '))))"

Well it does its job but when i look in my database in the column 'visit' it displays only zeros.

Any ideas?

You should use SQL Parameters instead of string concatenation to build up your query. — mason, Jul 16 '14 at 20:24

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

Use a parameterized query (this example is for Sql Server). In this way you don't need to worry how to quote a date, a string or what is the correct decimal separator required by the database.
Moreover you avoid any problem with Sql Injection attacks

 query1 = "insert into visit(visit,textfile,p_id) VALUES(@p1, @p2, " & _ 
          "(select patient.p_id from patient where patient.name=@p3)"

 Dim cmd As MySqlCommand = new MySqlCommand(query1, connection)
 cmd.Parameters.AddWithValue("@p1", Date.Today)
 cmd.Parameters.AddWithValue("@p2", s)
 cmd.Parameters.AddWithValue("@p3", ComboBox1.Text)
 cmd.ExecuteNonQuery()

edited May 23 '17 at 12:29

Community

1
1

answered Jul 16 '14 at 20:30

Steve

213,761
22
232
286

i have tried this but i gives e error in sql command...do i have to import anything? – Dimitris Dimitri Jul 16 '14 at 20:52
it says: operator = is not defined for types'mysqldata.mysqlclient.mysqlcommand and mysql.data.mysqlclient.mysqlcommand – Dimitris Dimitri Jul 16 '14 at 21:01
:) ok no problem,but unfortunately it says that there is an error in mysql near 1. – Dimitris Dimitri Jul 16 '14 at 21:10
my fault i mistyped it...but for some reason it adds duplicate rows and although it is displayed properly it is saved with today date and 12:00 no matter how man times i click it. – Dimitris Dimitri Jul 16 '14 at 21:21
Do not worry how the tool that show the database info presents a date value. It is common to show for datetime fields also the value 12.00 in the output. – Steve Jul 16 '14 at 21:22
First of all thank you so much for your help...well yes but shouldn't is say instead of 12:00 the current time?..Anyway problem solved .AGain THANKS!! – Dimitris Dimitri Jul 16 '14 at 21:26
If you want to store also the current time you need to pass DateTime.Now instead of DateTime.Today.... – Steve Jul 16 '14 at 21:31
Thank you so much for you help..you were really clear and helpfull. – Dimitris Dimitri Jul 16 '14 at 21:38

cannot insert datetime in visual basic

1 Answers1