jquery UI autocomplete with asp.net c#

Question

I am trying to do Autocomplete by taking values from database using jquery and c#.

This is my html form

<form action="Default.aspx"  method="post">
    <fieldset>
        <p class="ui-widget">
            <label for="state">State (abbreviation in separate field):
            </label>
            <input type="text" id="state"  name="state" /> 
            <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/>
        </p>
        <input type="hidden" id="state_id" name="state_id" />
        <p class="ui-widget">
            <label for="state_abbrev">State (replaced with abbreviation):
            </label>
            <input type="text" id="state_abbrev" name="state_abbrev" />
        </p>
        <p>
            <input type="submit" name="submit" value="Submit" />
        </p>
    </fieldset>
</form>

and my JQuery file is this

$(function () {
    $('#abbrev').val("");
    $("#state").autocomplete({
        source: "states.aspx",
        minLength: 2,
        select: function (event, ui) {
            $('#state_id').val(ui.item.id);
            $('#abbrev').val(ui.item.abbrev);
        }
    });
    $("#state_abbrev").autocomplete({
        source: "states_abbrev.aspx",
        minLength: 2
    });
});

.cs file is this

JavaScriptSerializer serializer;
public class State {
    public int id;
    public string value;
    public string abbrev;
}  
protected void Page_Load(object sender, EventArgs e) {
    serializer = new JavaScriptSerializer();
    Response.Write(JSONData(Request.QueryString["Term"]));
}   
private string JSONData(string term) {
    ArrayList stateArray = new ArrayList();
    int index = 0;
    SqlConnection objConn = new SqlConnection("YOUR-CONNECTION-STRING-HERE");
    DataSet myds = new DataSet("States");
    objConn.Open();
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT id, state, abbrev FROM states WHERE state like \'%\' + @ac_term + \'%\'", objConn);
    adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar);
    adapter.SelectCommand.Parameters["@ac_term"].Value = term;
    adapter.Fill(myds, "States");
    foreach (DataRow dr in myds.Tables[0].Rows) {
        State st = new State();
        st.id = dr["id"].ToString();
        st.value = dr["state"].ToString();
        st.abbrev = dr["abbrev"].ToString();
        stateArray.Add(st);
    }
    objConn.Close();
    return serializer.Serialize(stateArray);
}

but still im gettin error in this line

adapter.Fill(myds, "States");

can anyone help me out of this...

parameterized error..somethin lik value was null

vani
– vani

2012-03-19 18:00:28 +00:00
Commented Mar 19, 2012 at 18:00 — vani
– vani, Commented Mar 19, 2012 at 18:00

dcreight · Accepted Answer · 2012-05-01 18:20:07Z

1

I believe the problem is in this line:

adapter.SelectCommand.Parameters["@ac_term"].Value = term;

The error is telling you that this parameter does not have a value. You either need to ensure that term equals something or alter your stored procedure to not rely on this parameter.

From your code it appears that the variable you pass to the JSONData function comes from a Querystring, you should test to ensure that this string is what you expect it to be (i.e NOT null or undefined or something else you don't expect).

An easy way to do this would be:

if (string.IsNullOrEmpty(term) || term == "undefined") return;

This will cause the function to return if it encounters a null or an undefined querystring.

answered May 1, 2012 at 18:20

dcreight

6416 silver badges18 bronze badges

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

Comments

icesar · Accepted Answer · 2012-03-19 17:53:05Z

0

On first look, it seems like \'%\' is the problem - don't use escape characters - just: SELECT id, state, abbrev FROM states WHERE state like '%' + @ac_term + '%'

Cheers, Ivan

answered Mar 19, 2012 at 17:53

icesar

4963 silver badges14 bronze badges

1 Comment

vani Over a year ago

still im gettin tis exception The parameterized query '(@ac_term varchar(8000))SELECT regon_name FROM region_test WHERE' expects the parameter '@ac_term', which was not supplied.

D Stanley · Accepted Answer · 2012-03-19 18:05:29Z

0

Try accounting for a NULL value:

SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT id, state, abbrev FROM states WHERE @ac_term IS NULL OR state LIKE '%' + ISNULL(@ac_term,'') + '%'", objConn);

(using @ to eliminate the escape chars)

answered Mar 19, 2012 at 18:05

D Stanley

153k12 gold badges189 silver badges257 bronze badges

3 Comments

D Stanley Over a year ago

No I can't. I don't have your environment to test against. That's why I said "try it" :)

vani Over a year ago

The parameterized query '(@ac_term varchar(8000))SELECT regon_name FROM region_test WHERE' expects the parameter '@ac_term', which was not supplied. tis is exception im gettin

D Stanley Over a year ago

What kind of database are you using?

Collectives™ on Stack Overflow

jquery UI autocomplete with asp.net c#

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related