Get rows that have a date greater or less than a certain date

Question

Get rows that have a date greater or less than a certain date

Navigation

#1 by (1 votes)

0

I have an application in which I want to select from 2 timePickers the minor and major date, and then make a select * from using the dates as conditions.

I have this:

var select = "SELECT * from inventario where ifechaCompra > "+diarioFecha1.Text+" and ifechaCompra < "+diarioFecha2.Text+"";
            SqlCeConnection con = new SqlCeConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conexionDB"].ConnectionString);
            var dataAdapter = new SqlCeDataAdapter(select, con);
            var commandBuilder = new SqlCeCommandBuilder(dataAdapter);
            var ds = new DataSet();
            dataAdapter.Fill(ds);
            tablaInventario.ReadOnly = true;
            tablaInventario.DataSource = ds.Tables[0];

The strange thing is that if in the place of the second date I remove the diarioFecha.Text and I enter one like this: '01/01/2014' yes it works.

It does not give me any error, simply by updating the dgv it leaves me empty.

PS: I know I should use parameters but when I have to fill out a dgv I do not know how to do it, I just know to insert.

c# sql-server date select sql-server-ce

asked by Barri 28.07.2017 в 22:01

source

1 answer

How to dump an array in a multiple select change values when selecting an item in a combobox

score 1 · Accepted Answer

Do not give an error and simply do not bring you data, it may be because with the indicated filters there is no information, or as you have formatted the values the engine "confuse" days per month. I suggest you:

First of all, it's usually not a good idea to use this format '01 / 01/2014 'to indicate a date, how does the engine for identify the day of the month? it is preferable to use the long ISO format yyyymmdd ie 20140101 . Maybe you want to build a "normalizing" function that receives the value of daily Date1.Text and format the date to the iso format, unless diarioFecha1 already offers you some way to return the date with that format.

The other issue is that in your query you must separate with quotes the Date parameters as follows:

var select = "SELECT * from inventario where ifechaCompra > '"+diarioFecha1.Text+"' and ifechaCompra < '"+diarioFecha2.Text+"'";

But much better is to process the parameters to avoid code injection problems, like this:

var select = "SELECT * from inventario where ifechaCompra > @FechaDesde and  ifechaCompra < @FechaHasta";
SqlCeConnection con = new SqlCeConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conexionDB"].ConnectionString);

dataAdapter.SelectCommand.Parameters.AddWithValue("@FechaDesde",diarioFecha1.Text);
dataAdapter.SelectCommand.Parameters.AddWithValue("@FechaHasta",diarioFecha2.Text);

var dataAdapter = new SqlCeDataAdapter(select, con);

Finally, and this is debatable, in my experience (in Argentina at least) the limits are clearer if they are inclusive, ie >= or <= , usually a user if you want to see starting from 01/01/2014, expect to see data from that day too. But that will depend on the culture of each country or organization.

And as a last recommendation, applicable to any query: never use a select * , always indicate the fields that we will specifically use, this often has a very high impact on network traffic.