Good I have the following error and I do not know where it will be originated
Incorrect syntax near 'OFFSET'. Invalid use of the NEXT option in the FETCH statement.
eh tried everything but I can not solve the problem, here I leave the code
string searchText = Request["srch"] ?? "";
string ipp = Request["ipp"] ?? "5";
string pg = Request["pg"] ?? "0";
int pgNumber = 1;
int.TryParse(pg, out pgNumber);
if (pgNumber < 0)
{
pgNumber = -pgNumber;
}
else if (pgNumber == 0)
{
pgNumber = 1;
}
string pmin = Request["pmin"];
string pmax = Request["pmax"];
float preciomin = -1f, preciomax = -1f;
float.TryParse(pmin, out preciomin);
if (preciomin < 0)
{
preciomin = -preciomin;
}
else if (preciomin == 0)
{
preciomin = -1;
}
float.TryParse(pmax, out preciomax);
if (preciomax < 0)
{
preciomax = -preciomax;
}
else if (preciomax == 0)
{
preciomax = -1;
}
string categoria = Request["cat"];
if (categoria == String.Empty)
{
categoria = null;
}
string categoriaHija = Request["cath"];
if (categoriaHija == String.Empty)
{
categoriaHija = null;
}
string provincia = Request["prov"];
if (provincia == String.Empty)
{
provincia = null;
}
string ciudad = Request["ciud"];
if (ciudad == String.Empty)
{
ciudad = null;
}
int itemsPP = 5;
int.TryParse(ipp, out itemsPP);
if (itemsPP < 0)
{
itemsPP = -itemsPP;
}
else if (itemsPP == 0)
{
itemsPP = 5;
}
var db = Database.Open("Restate1320");
searchText = searchText.Replace(',', ' ').Replace('.', ' ').Replace("'", "''");
string[] spl = searchText.Split(' ');
System.Text.StringBuilder query = new System.Text.StringBuilder();
int limit = spl.Length;
string part;
string not = string.Empty;
int order = 1;
string condicionPrecio;
if(preciomin == -1f && preciomax == -1f)
{
condicionPrecio = string.Empty;
}
else if (preciomin == -1f && preciomax != -1f)
{
condicionPrecio = string.Format("Precio < {0} AND ", preciomax);
}
else if (preciomin != -1f && preciomax == -1f)
{
condicionPrecio = string.Format("Precio > {0} AND ", preciomin);
}
else
{
condicionPrecio = string.Format("Precio BETWEEN {0} AND {1} AND ", preciomin, preciomax);
}
while (limit > 0)
{
part = string.Empty;
for (int i = 0; i < limit; i++)
{
part += spl[i] + " ";
}
part = part.Remove(part.Length - 1);
query.AppendFormat("SELECT *, {0} AS Orden FROM VArticulos WHERE {1} {2} {3} {4} {5} (Titulo COLLATE Latin1_General_CI_AI LIKE '%{6}%' OR Descripcion COLLATE Latin1_General_CI_AI LIKE '%{6}%') {7} UNION ", order++, categoria != null ? string.Format("Categoria = {0} AND ", categoria) : "", provincia != null ? string.Format("IdProvincia = {0} AND ", provincia) : "", ciudad != null ? string.Format("IdCiudad = {0} AND ", ciudad) : "", condicionPrecio, categoriaHija != null ? string.Format("UsuarioP = {0} AND ", categoriaHija) : "", part, not);
not += string.Format("AND NOT (Titulo LIKE '%{0}%' OR Descripcion LIKE '%{0}%') ", part);
limit--;
}
query = query.Remove(query.Length - 6, 5);
string qt = query.ToString();
var nresults = db.QueryValue(string.Format("SELECT ISNULL(COUNT(*), 0) FROM ({0}) AS Result", qt));
while ((pgNumber - 1) * itemsPP > nresults)
{
pgNumber--;
}
string q = string.Format("SELECT * FROM ({0}) AS Result ORDER BY Orden OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", qt, (pgNumber - 1) * itemsPP, itemsPP);
var result = db.Query(q);
pgNumber.ToString();
db.Close();