Error: Incorrect syntax near 'OFFSET' RAZOR MVC WEBMATRIX

0

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();
    
asked by Atejada 16.11.2017 в 17:22
source

0 answers