The correct use of a variable in a SQL QUERY

Question

I would like to know as to use a variable in a SQL QUERY. I have a variable called NOMEGENERO that contains the name "JOSE" for example. what is the correct SQL QUERY using this variable. I'm trying to use: thisAdapter = new OleDbDataAdapter( "SELECT n_genero FROM genero WHERE genero = '+ nomegenero'", dbBanco.thisConnection);). But is wrong. How can i use the variable NOMEGENERO correctly? Debtor for the attention Andre

Answer 1

Good, the code is well optimized

Answer 2

Thanks for your reply. I sort out the problem. by createing another class i.e.

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

public class AddCountry

{

private int _countryID;

private string _countryName;

public int CountryID { get { return _countryID; } }

public string CountryName { get { return _countryName; } }

public AddCountry(int id, string name)

{

_countryID = id;

_countryName = name;

}

}

Then modifying the following code. I used this sample from a code written by Bill Jevens of microsoft on Personal Web Starter Kit.

using (SqlDataReader reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)){

while (reader.Read()){

AddCountry temp = new AddCountry((int)reader["CountryID"], (string)reader["CountryName"]);

list.Add(temp);}}

return list;

Answer 3

OK abdul if you want to use DataReader and displaying all member then replace the loop

while (objReader.Read())

{

objCountry.CountryID = (int)objReader["CountryID"];

objCountry.CountryName = (string)objReader["CountryName"];

list.Add(objCountry);

}

by

do

{

objCountry.CountryID = (int)objReader["CountryID"];

objCountry.CountryName = (string)objReader["CountryName"];

list.Add(objCountry);

objReader.NextResult();

}

while (objReader.Read())

 

Answer 4

Thanks for your reply. please refine your reply sir, i didn't get your point.

Answer 5

The Data reader is  read-only not udate could be done, forward-only , which means we cannot go back to the previous record which was accessed.
It is worth is your case to use a data adapter that fills a data set within the data and then you can use the cached data to leaverage what ever you want, if any changes are done then use adapter.update();