Filling a text field value based on another text field from the database

Question

I am trying to fill in the value of a text field based on another text field, but I cannot fill in another text field. I am sharing my code, please help me with a better solution

Action method:

public JsonResult AgreementNo(string id) { string no; string _str = id; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()); SqlCommand cmd = new SqlCommand("SELECT top(1) num from loan where id=@str ", con); cmd.Parameters.AddWithValue("@str",id); cmd.CommandType = CommandType.Text; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); no = ds.Tables[0].Rows[0]["num"].ToString(); return Json(new { no = no }, JsonRequestBehavior.AllowGet); } 

Script:

  $("#BarrowerName").blur(function () { $.ajax({ url: '@Url.Action("AgreementNo", "Home")', // url: '@Url.Action("AgreementNo", "Home")', dataType: "json", data: JSON.stringify({ id: $("#BarrowerName").val() }), type:"POST", async: false, contentType: 'application/json,charset=utf-8', sucess: function (data) { $("#AgreementNo").val(data.no) response(data); } }); }); 

It throws an error, for example: Conversion error when converting nvarchar '' value to int data type.

Answer 1

First of all, your error in this line: -

 cmd.Parameters.AddWithValue("@str",id); 

Since you are trying to pass an integer value to an NVARCHAR column, change your code as follows: -

 cmd.Parameters.Parameters.Add("@str",SqlDbType.NVarChar).Value = id; 

Please read the following: Can we stop using AddWithValue

Now, as soon as this is fixed, change the jQuery code from sucess to success and it should work!

In addition, use the instructions to automatically distribute the resources of your values ​​as follows: -

 string CS = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using(SqlConnection con = new SqlConnection(CS)) using(SqlCommand cmd = new SqlCommand("SELECT top(1) num from loan where id=@str ", con)) { cmd.Parameters.Parameters.Add("@str",SqlDbType.NVarChar).Value = id; cmd.CommandType = CommandType.Text; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); no = ds.Tables[0].Rows[0]["num"].ToString(); return Json(new { no = no }, JsonRequestBehavior.AllowGet); } 

Answer 2

You pass the string to Parameters.AddWithValue method, but an int is expected. Convert the id variable to int .

 int intID = int.Parse(id); SqlCommand cmd = new SqlCommand("SELECT top(1) num from loan where id=@str ", con); cmd.Parameters.AddWithValue("@str", intID ); 

EDIT

Here is the complete code you can copy / paste

 public JsonResult AgreementNo(string id) { string no; int intId = int.Parse(id); SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()); SqlCommand cmd = new SqlCommand("SELECT top(1) num from loan where id=@str ", con); cmd.Parameters.AddWithValue("@str", intId); cmd.CommandType = CommandType.Text; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); no = ds.Tables[0].Rows[0]["num"].ToString(); return Json(new { no = no }, JsonRequestBehavior.AllowGet); } } 

But there is a better solution if you expect an integer id in the AgreementNo(string id) method.

Just change the parameter type to int :

 public JsonResult AgreementNo(int id)