Auto complete extender error Asp.net

Question

3.00/5 (2 votes)

See more:

Hello fellow developer. Got a few tweakings I like to ask. So im using auto complete extender for my project, but getting an error based on selected criteria (basically I want to 'Where')
So here is the background of my project:
I have a dropdown list where the text is string and the value is integer
now on my auto complete extender I want 2 where
here is my trial code

<WebMethod()> _
   Public Function Discipline() As String()

        Dim sqlConn As New SqlConnection(_connString)
        Dim count As Integer = 10
        Dim prefixText As String = Nothing
        Dim DisciplineID As Integer = Nothing
        Dim sql As String = "Select Distinct CourseName, CourseCode from Course Where DisciplineID=@DisciplineID And ([CourseCode] like @prefixText or [CourseName] like @prefixText)"
        Dim da As New SqlDataAdapter(sql, sqlConn)
        da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = "%" + prefixText + "%"
        da.SelectCommand.Parameters.Add("@DisciplineID", SqlDbType.VarChar, 50).Value = DisciplineID.ToString
        Dim dt As New DataTable()

        da.Fill(dt)

        Dim items As String() = New String(dt.Rows.Count - 1) {}
        Dim i As Integer = 0
        For Each dr As DataRow In dt.Rows
            items.SetValue(dr("CourseCode").ToString() & " | " & dr("CourseName").ToString(), i)

            i += 1
        Next
        Return items

    End Function

Using break points I see that my error is on this on
Dim prefixText As String = Nothing
Dim DisciplineID As Integer = Nothing

Cause it says on the parameters nothing.

My question is can I use two where cause on an autocomplete extender where the primary clause will be based on dropdownlist.selectedvalue??

Thanks and more power.

Posted 11-Dec-11 18:40pm

janwel

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Al Moje · Answer 1 · 2011-12-11T19:37:00

Hi,

You have may have to create a function to return reading of your data row (dr) an empty string in case your data row (dr) contents null.

Try to change your code for loop as sample below:

C#

For Each dr As DataRow In dt.Rows
   Items.SetValue(EmptyIfNull(dr("CourseCode")) & " | " & EmptyIfNull(dr("CourseName")), i)
   i += 1
Next

and here is the function:

C#

Public Shared Function EmptyIfNull(ByVal p As Object) As String
       Dim retValue As String = String.Empty
       Try
           If Not (p.Equals(DBNull.Value)) Then
               'retValue = DirectCast(p, String)
               retValue = CType(p, String)
           End If
       Catch ex As Exception
           retValue = String.Empty
       End Try
       Return retValue
End Function

You may also use such query as example: ( This is the recomended solution...)

C#

Select Distinct case when CourseName = null  then '' else CourseName end, 
 case when CourseCode = null then '' else CourseCode end from Course 
Where DisciplineID=@DisciplineID And ([CourseCode] like @prefixText or [CourseName] like @prefixText)

Hope this could help...

Regards,