0

I want to get the multiple field from parameterized select query in web method using AJAX in asp.net C#.

Here is my web method query:

[WebMethod]
[ScriptMethod]
public static string GetSchoolName(string schoolName)
{
    try
    {
        using (var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (var cmd = new SqlCommand("select abc, xyz, pqr from tbl_schoolregistration where name = @schoolname", con))
        {
            cmd.Parameters.Add("@schoolname", SqlDbType.NVarChar).Value = schoolName;
            con.Open();
            object val = cmd.ExecuteScalar();
            return val == DBNull.Value ? "" : ((Int32)val).ToString();
        }
    }
    catch (Exception ex)
    {
        return ex.ToString();
    }
}

Now in this query only abc is getting that is int. While the xyz and pqr is not getting (I have checked this using debug mode).

Here is client side code

var c = $find("<%=cmbobx_search.ClientID %>");
$.ajax({
    type: "POST",
    url: "schoolregistration.aspx/GetSchoolName",
    data: JSON.stringify({ schoolName: c.get_textBoxControl().value }),
    contentType: "application/json; charset=utf-8",
    datatype: "json",
    success: OnSuccessGetSchoolName,
    failure: function () {
        alert("error! try again...");
    }
});

function OnSuccessGetSchoolName(response) {
    var c = $find("<%=hdn_abc.ClientID %>");
    var d = $find("<%=txt_xyz.ClientID %>");
    var e = $find("<%=txt_pqr.ClientID %>");      
    document.getElementById('<%= hdn_abc.ClientID%>').value = response.c;
    document.getElementById('<%= txt_xyz.ClientID %>').value = d.get_textBoxControl().value
    document.getElementById('<%= txt_pqr.ClientID %>').value = e.get_textBoxControl().value
}

So, what I want to do for getting all field which is in select query. May I use the loop or any other thing?

halfer
  • 19,824
  • 17
  • 99
  • 186
s.k.Soni
  • 1,139
  • 1
  • 19
  • 37
  • I'm not sure what your goal is. You're storing references to the elements in `d` and `e` respectively, then you're trying to get the same elements again and set the values to what they already are...? – Rory McCrossan Jul 13 '17 at 07:17
  • why are you using ExecuteScalar() instead you should used ExecuteDataSet(). – Ankush Jain Jul 13 '17 at 07:18
  • @AnkushJain when I am doing ExecuteDataSet at that time it showing error i.e. 'System.data.sqlclient.sqlcommand' does not contain a defination for 'ExecuteDataSet'. – s.k.Soni Jul 13 '17 at 07:22
  • @RoryMcCrossan My goal is that I want to get data in json and from that the regarding filed want to fill by their response. And that response is coming from the static method and in that static method query only one field is coming that is abc and remaining xyz and pqr is coming so from that I cant fill the data in xyz and pqr textbox. I think now you understand that what I am telling. – s.k.Soni Jul 13 '17 at 07:31
  • Why don't you try ExecuteReader()? ExecuteScalar gives you the value of first row and first column - means only single value, not a row with multiple columns. So use ExecuteReader() , – Sujith Jul 13 '17 at 07:31
  • Please read up on the different methods for querying databases, starting here: https://stackoverflow.com/questions/2974154/what-is-the-difference-between-executescalar-executereader-and-executenonquery. And I don't think you're using mysql as suggested by your tag, but SqlServer, judging by your use of `SqlConnection` – Stephen Jul 13 '17 at 07:40
  • @Sujith if I do the ExcuteReader() then how can I return the value in json? – s.k.Soni Jul 13 '17 at 07:41
  • Build an anonymous object with those three properties or you can have a model class which contains those three Properties and peform Read() on the reader object to iterate through each row. You can select each column value using the column ordinal. Set the Model object/anonymous object properties to the read values from the reader and return that object. – Sujith Jul 13 '17 at 07:45
  • @Sujith can you show me the method that how the query will be done and after that how json can get? Please. – s.k.Soni Jul 13 '17 at 07:47
  • continuation to above comment: example var items = reader.Select(r => new { CatName = r.GetString(0), CarDOB = r.GetDateTime(1), CatStatus = r.GetInt32(2) }); – Sujith Jul 13 '17 at 07:47
  • You can also browse and can find tons of articles/pages on ExecuteReader() and how you read values of each row and column – Sujith Jul 13 '17 at 07:48
  • @Sujith when I am doing excutereader at that time it showing the error i.e. "System.InvalidCastException: Specified cast is not valid" – s.k.Soni Jul 13 '17 at 07:50
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/149082/discussion-between-sujith-and-s-k-soni). – Sujith Jul 13 '17 at 07:52
  • @Sujith what happened? – s.k.Soni Jul 13 '17 at 08:04

0 Answers0