how to convert the format of datetime picker and store date in mysql data base

Question

    sqlcon = New MySqlConnection
    sqlcon.ConnectionString = "server=localhost;userid=root;password=soumya;database=bams;"
    Dim da, da1 As MySqlDataAdapter
    Dim ds, ds1 As New DataSet
    Dim attendedclass, noofclass As Integer
    Dim query, query1 As String
    Dim yr, mon, day, yr1, mon1, day1 As Integer
    yr = DateTimePicker1.Value.Year
    yr1 = DateTimePicker2.Value.Year
    day = DateTimePicker1.Value.Day
    day1 = DateTimePicker2.Value.Day
    mon = DateTimePicker1.Value.Month
    mon1 = DateTimePicker2.Value.Month

    sqlcon.Open()
    query1 = "select * from attendence_master where ( REGISTER_NO='" & TextBox1.Text & "' ) and ( SUBJECT='" & ComboBox1.SelectedItem.ToString() & "' ) and ( LECTURER_NAME='" & TextBox4.Text & "' ) and ( CLASS ='" & ComboBox2.SelectedItem.ToString() & "' ) and ( DAY  between " & day & " and " & day1 & ")  and ( MONTH between " & mon & " and " & mon1 & " ) and (YEAR between " & yr & " and " & yr1 & ")"
    sqlcom = New MySqlCommand(query1, sqlcon)
    da1 = New MySqlDataAdapter(sqlcom)
    da1.Fill(ds1)
    noofclass = ds1.Tables(0).Rows.Count()
    query = "select * from attendence_master where ( REGISTER_NO='" & TextBox1.Text & "')  and ( SUBJECT='" & ComboBox1.SelectedItem.text & "' )  and ( LECTURER_NAME='" & TextBox4.Text & "' ) and ( ATTENDENCE='P' ) and ( CLASS ='" & ComboBox2.SelectedItem.ToString() & "' ) and  ( DAY  between " & day & " and " & day1 & ")  and ( MONTH between " & mon & " and " & mon1 & " ) and (YEAR between " & yr & " and " & yr1 & ")"
    sqlcom = New MySqlCommand(query, sqlcon)
    da = New MySqlDataAdapter(sqlcom)
    da.Fill(ds)
    attendedclass = ds.Tables(0).Rows.Count()
    DataGridView1.DataSource = ds
    sqlcon.Close()

i cannot store date in mysql data base , cz i cld nt convert the format of datetimepicker n vb , . now i hve split the values of dtpicker and stored it as integer n data base ,.. need help to execute the query .. help plz .. ty

query = "select * from attendence_master where ( REGISTER_NO='" & TextBox1.Text & "') and ( SUBJECT='" & ComboBox1.SelectedItem.text & "' ) and ( LECTURER_NAME='" & TextBox4.Text & "' ) and ( ATTENDENCE='P' ) and ( CLASS ='" & ComboBox2.SelectedItem.ToString() & "' ) and ( DAY between " & day & " and " & day1 & ") and ( MONTH between " & mon & " and " & mon1 & " ) and (YEAR between " & yr & " and " & yr1 & ")" sqlcom = New MySqlCommand(query, sqlcon) da = New MySqlDataAdapter(sqlcom) da.Fill(ds) i have error in ths section — soumya k, Mar 27 '17 at 06:07
On a separate note you should use SQL parameters. You are open to **SQL injection**. It would also help to reduce syntax issues. — Bugs, Mar 27 '17 at 08:21

PRABA · Answer 1 · 2017-03-27T09:26:25.933

0

replace this query and check. In your query you used fields day,month and year are keywords, so add brackets with that table fields in the where clause and check

  sqlcon = New MySqlConnection
sqlcon.ConnectionString = "server=localhost;userid=root;password=soumya;database=bams;"
Dim da, da1 As MySqlDataAdapter
Dim ds, ds1 As New DataSet
Dim attendedclass, noofclass As Integer
Dim query, query1 As String
Dim yr, mon, day, yr1, mon1, day1 As Integer
yr = DateTimePicker1.Value.Year
yr1 = DateTimePicker2.Value.Year
day = DateTimePicker1.Value.Day
day1 = DateTimePicker2.Value.Day
mon = DateTimePicker1.Value.Month
mon1 = DateTimePicker2.Value.Month

sqlcon.Open()
query1 = "select * from attendence_master where ( REGISTER_NO='" & TextBox1.Text & "' ) and ( SUBJECT='" & ComboBox1.SelectedText.ToString() & "' ) and ( LECTURER_NAME='" & TextBox4.Text & "' ) and ( CLASS ='" & ComboBox2.SelectedText.ToString() & "' ) and ( `DAY`  between " & day & " and " & day1 & ")  and ( `MONTH` between " & mon & " and " & mon1 & " ) and (`YEAR` between " & yr & " and " & yr1 & ")"
sqlcom = New MySqlCommand(query1, sqlcon)
da1 = New MySqlDataAdapter(sqlcom)
da1.Fill(ds1)
noofclass = ds1.Tables(0).Rows.Count()
query = "select * from attendence_master where ( REGISTER_NO='" & TextBox1.Text & "')  and ( SUBJECT='" & ComboBox1.SelectedText.text & "' )  and ( LECTURER_NAME='" & TextBox4.Text & "' ) and ( ATTENDENCE='P' ) and ( CLASS ='" & ComboBox2.SelectedText.ToString() & "' ) and  ( `DAY`  between " & day & " and " & day1 & ")  and ( `MONTH` between " & mon & " and " & mon1 & " ) and (`YEAR` between " & yr & " and " & yr1 & ")"
sqlcom = New MySqlCommand(query, sqlcon)
da = New MySqlDataAdapter(sqlcom)
da.Fill(ds)
attendedclass = ds.Tables(0).Rows.Count()
DataGridView1.DataSource = ds
sqlcon.Close()

edited Mar 27 '17 at 09:26

answered Mar 27 '17 at 06:21

PRABA

460
1
6
19

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 '[DAY] between 26 and 28) and ( [MONTH] between 3 and 3 ) and ([YEAR] between 2' at line 1---- – soumya k Mar 27 '17 at 06:44
i found out the error ! SUBJECT='" & ComboBox1.SelectedItem.text & "' i changed it to ComboBox1.SelectedItem.ToString() and it dsnt show any error ! but its not getting any results from db – soumya k Mar 27 '17 at 06:47
there is no error reported whn i execute query ! but the dataset has no values in it ! i m not getting any results from data base – soumya k Mar 27 '17 at 06:54
CLASS SUBJECT LECTURER_NAME TIME[FROM] TIME[TO] YEAR MONTH DAY STUDENT_NAME REGISTER_NO ATTENDENCE I BCA A C++ arun123 9 10 2017 3 27 Sanvi 148040601 P I BCA A C++ arun123 9 10 2017 3 27 Vismaya 148040602 A I BCA A C++ arun123 9 10 2017 3 27 Medha 148040603 P I BCA A C++ arun123 9 10 2017 3 27 Vinayaka 148040604 P I BCA A C++ arun123 11 12 2017 3 28 Sanvi 148040601 P I BCA A C++ arun123 11 12 2017 3 28 Vismaya 148040602 P I BCA A C++ arun123 11 12 2017 3 28 Medha 148040603 P I BCA A C++ arun123 11 12 2017 3 28 Vinayaka 148040604 P – soumya k Mar 27 '17 at 06:54
@soumyak i have edited my answer. change the combox values from ComboBox1.Selecteditem.ToString() to ComboBox1.SelectedText.ToString(). – PRABA Mar 27 '17 at 07:02
not working !! is there any way to chnge the format of dayetimepicker in vb to yyyy-MM-dd – soumya k Mar 27 '17 at 07:10
will between clause work if there are multiple between ? – soumya k Mar 27 '17 at 07:14
@soumyak definitely between clause work with multiple fields. now i have update my answer. change the brackets from [] to {} for where clause fields. check and tell – PRABA Mar 27 '17 at 07:17
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 '} between 26 and 28) and ( {MONTH} between 3 and 3 ) and ( {YEAR} between 201' at line 1 – soumya k Mar 27 '17 at 07:29
@soumyak change your where clause fields like {DAY} to `DAY` see the answer – PRABA Mar 27 '17 at 09:23
how to add parameters to to my above query – soumya k Mar 27 '17 at 10:16
please go through this link and see the answer for adding parameters http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i – PRABA Mar 27 '17 at 10:20
ty ! and how to get database values to datagrid view – soumya k Mar 27 '17 at 11:09
@soumyak you using windows application or web application – PRABA Mar 27 '17 at 11:10
@soumyak use this code to bind database values to data grid view DataGridView1.DataSource = ds DataGridView1.databind() – PRABA Mar 27 '17 at 11:12
cannot use bind function ! its windows app ! not web – soumya k Mar 27 '17 at 11:23
@soumyak okay fine. leave the bind function :) – PRABA Mar 27 '17 at 11:29
the query select * from attendence_master ,it works and retrieves data to datagrid but if i use two or more conditions then it doesnt retrieve any value – soumya k Mar 27 '17 at 11:56
@soumyak because there is no records matches in database for the input given by you. check the database manually for the given input. – PRABA Mar 27 '17 at 11:59
Yeah.. now it works fine..as u said there was no record matching the value in the db – soumya k Apr 05 '17 at 09:21
Ahaan fine.. but too late:) @soumyak – PRABA Apr 05 '17 at 09:23

how to convert the format of datetime picker and store date in mysql data base

1 Answers1