Problem to insert data from DateTimePicker to Access database
-
I want to insert data from DateTimePicker to Access database but have error "Data type mismatch in criteria expression" this is the code Dim aCon As OleDbConnection Dim aCmd As OleDbCommand Dim strCon As String strAcessFilePath = System.Windows.Forms.Application.StartupPath &; "\rabotnici.mdb" 'create connection with mdb file strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password=" aCon = New OleDbConnection(strCon) aCon.Open() strQuery = "insert into data_pr values(" + " '" + DateTimePicker1.Value + "'" + ")" aCmd = New OleDbCommand(strQuery, aCon) Dim da As New OleDbDataAdapter da.SelectCommand = aCmd aCmd.ExecuteNonQuery() aCon.Close() access database field format is Date/time Can You help me thanks
-
I want to insert data from DateTimePicker to Access database but have error "Data type mismatch in criteria expression" this is the code Dim aCon As OleDbConnection Dim aCmd As OleDbCommand Dim strCon As String strAcessFilePath = System.Windows.Forms.Application.StartupPath &; "\rabotnici.mdb" 'create connection with mdb file strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password=" aCon = New OleDbConnection(strCon) aCon.Open() strQuery = "insert into data_pr values(" + " '" + DateTimePicker1.Value + "'" + ")" aCmd = New OleDbCommand(strQuery, aCon) Dim da As New OleDbDataAdapter da.SelectCommand = aCmd aCmd.ExecuteNonQuery() aCon.Close() access database field format is Date/time Can You help me thanks
Instead of concatenating values to sql strings, use parameters. That way you will avoid conversion problems (among many other things such as sql injections etc). Have a look at: OleDbParameter Class[^]
The need to optimize rises from a bad design.My articles[^]
-
Instead of concatenating values to sql strings, use parameters. That way you will avoid conversion problems (among many other things such as sql injections etc). Have a look at: OleDbParameter Class[^]
The need to optimize rises from a bad design.My articles[^]
-
Yes it does and also it can be done for dates and numbers. Access accepts dates in format
#mm/dd/yyyy#. So for example today would be #01/25/2009# (without quotes). However, I hope that you don't take this shortcut since it's not the proper way to go. I already mentioned sql injections which is prevented by using parameters. To give a few more examples that would be problematic:- numbers, what if decimal separator is comma (as it is for example in my case). When updating a column you would have for example UPDATE ... SET ColumnName = 49,6 ... This would lead to an error
- text, what if your text contains a quote, for example: UPDATE ... SET ColumnName = 'Guns 'n' Roses'. Error again
- dates, input date is in different format. For example I use 25.1.2009 (Finnish format) etc.
So what I'm trying to say is that it's few more lines of code, but the quality and robustness of your application will greatly improve when you use parameters.
The need to optimize rises from a bad design.My articles[^]
-
Yes it does and also it can be done for dates and numbers. Access accepts dates in format
#mm/dd/yyyy#. So for example today would be #01/25/2009# (without quotes). However, I hope that you don't take this shortcut since it's not the proper way to go. I already mentioned sql injections which is prevented by using parameters. To give a few more examples that would be problematic:- numbers, what if decimal separator is comma (as it is for example in my case). When updating a column you would have for example UPDATE ... SET ColumnName = 49,6 ... This would lead to an error
- text, what if your text contains a quote, for example: UPDATE ... SET ColumnName = 'Guns 'n' Roses'. Error again
- dates, input date is in different format. For example I use 25.1.2009 (Finnish format) etc.
So what I'm trying to say is that it's few more lines of code, but the quality and robustness of your application will greatly improve when you use parameters.
The need to optimize rises from a bad design.My articles[^]
-
ivo75 wrote:
are you sure that work
See for yourself, that's what they are for :) You could try something like this:
...
strQuery = "insert into data_pr values(?)"
aCmd = New OleDbCommand(strQuery, aCon)
aCmd.Parameters.AddWithValue("ColumName", DateTimePicker1.Value)
aCmd.ExecuteNonQuery()
...The need to optimize rises from a bad design.My articles[^]
-
ivo75 wrote:
are you sure that work
See for yourself, that's what they are for :) You could try something like this:
...
strQuery = "insert into data_pr values(?)"
aCmd = New OleDbCommand(strQuery, aCon)
aCmd.Parameters.AddWithValue("ColumName", DateTimePicker1.Value)
aCmd.ExecuteNonQuery()
...The need to optimize rises from a bad design.My articles[^]
-
ivo75 wrote:
"Data type mismatch in criteria expression"
This just means that the data type of the field in your database is not the same as the value that you are trying to write into it. Did you try parametric method as suggested by Mika?
-
I want to insert data from DateTimePicker to Access database but have error "Data type mismatch in criteria expression" this is the code Dim aCon As OleDbConnection Dim aCmd As OleDbCommand Dim strCon As String strAcessFilePath = System.Windows.Forms.Application.StartupPath &; "\rabotnici.mdb" 'create connection with mdb file strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password=" aCon = New OleDbConnection(strCon) aCon.Open() strQuery = "insert into data_pr values(" + " '" + DateTimePicker1.Value + "'" + ")" aCmd = New OleDbCommand(strQuery, aCon) Dim da As New OleDbDataAdapter da.SelectCommand = aCmd aCmd.ExecuteNonQuery() aCon.Close() access database field format is Date/time Can You help me thanks
-
I want to insert data from DateTimePicker to Access database but have error "Data type mismatch in criteria expression" this is the code Dim aCon As OleDbConnection Dim aCmd As OleDbCommand Dim strCon As String strAcessFilePath = System.Windows.Forms.Application.StartupPath &; "\rabotnici.mdb" 'create connection with mdb file strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password=" aCon = New OleDbConnection(strCon) aCon.Open() strQuery = "insert into data_pr values(" + " '" + DateTimePicker1.Value + "'" + ")" aCmd = New OleDbCommand(strQuery, aCon) Dim da As New OleDbDataAdapter da.SelectCommand = aCmd aCmd.ExecuteNonQuery() aCon.Close() access database field format is Date/time Can You help me thanks
Tahnk You very much everybody this is the code who work strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password=" aCon = New OleDbConnection(strCon) aCon.Open() strQuery = "insert into rabotnici values(?,?,?)" param1 = New OleDbParameter("@rabotnik_name", OleDbType.VarWChar, 100) param1.Value = ComboBox1.Text param2 = New OleDbParameter("@obekt_name", OleDbType.VarWChar, 100) param2.Value = ComboBox2.Text param3 = New OleDbParameter("@data_rabota", OleDbType.Date) param3.Value = DateTimePicker1.Value.ToShortDateString() aCmd = New OleDbCommand(strQuery, aCon) aCmd.Parameters.Add(param1) aCmd.Parameters.Add(param2) aCmd.Parameters.Add(param3) aCmd.ExecuteNonQuery() aCon.Close() this is work with parameters, again thanks