sql query inside controller returns null

Question

I am trying to generate a value first and then I try to pass that value into SQL query but my query returns null. I tried debugging my code I have value inside myvalue variable.

I am not sure it can be done the way I am doing. Can pass I the value like below? if not any suggestions, please.

public ActionResult Index(View model)
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_MYTABLE_CONNECTION_STRING"].ConnectionString))
    {
        var myvalue = MyUtil.GenerateKey(model.Name, model.phonenumber);

        con.Open();
        string query = "select count(*) from customer where key = @myvalue";


        using (var cmd = new SqlCommand(query, con))
        {
            int rowsAmount = (int)cmd.ExecuteScalar();
        }
    }
}

You need to pass command parameters, which in this case is @myvalue cmd.Parameters.Add() — Nilesh
– Nilesh, Commented Mar 21, 2019 at 20:58

TheEvilPenguin · Accepted Answer · 2019-03-21 20:59:22Z

1

You need to pass the SqlCommand the parameter name and value:

public ActionResult Index(View model)
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_MYTABLE_CONNECTION_STRING"].ConnectionString))
    {

        var myvalue = MyUtil.GenerateKey(model.Name, model.phonenumber);

        con.Open();
        string query = "select count(*) from customer where  key=@myvalue";

        using (var cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.Add("@myvalue", myvalue);
            int rowsAmount = (int)cmd.ExecuteScalar();
        }
    }
}

answered Mar 21, 2019 at 20:59

TheEvilPenguin

5,7022 gold badges30 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark W. Mitchell · Accepted Answer · 2019-03-21 21:39:43Z

0

The ActionResult Index(View Model) says you are passing in the model to the Controller -- if you have the data in the Model, you should do your lookup in the Model and have the result as part of your View Model

Data Access in my code is usually by database in its class within the Models - and ANYTHING that hits the database is usually in a stored procedure in the database - to avoid SQL injection. Create your View Model to pass the right data for the controller to pass to the view.

Test your stored proc in the database and then use in a Data Access Layer, and store it into a Model class to inject it into your controllers.

answered Mar 21, 2019 at 21:39

Mark W. Mitchell

7791 gold badge6 silver badges8 bronze badges

Collectives™ on Stack Overflow

sql query inside controller returns null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related