ExecuteScalar() Only works once...?

[MichaelWClark]

I have 3 dropdown lists, catagory, name, model. Select catagory to enable name and model. select a name or a model and the coresponding name/model will autofill in the other dropdown based upon results of executeScalar. If i have the catagory "Cellular" it works fine with ALL my products. If I switch the catagory to anything else it stops working. The code is exactly the same.... i dont get it.

Try
            If Not ddlProductName.SelectedValue = "-Select Product-" Then
                Dim SelectQuery As String = _
                "SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='" + ddlProductName.SelectedValue + "'"
            
                Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
                SQLConn.Open()
                Dim SqlCom As New SqlCommand(SelectQuery, SQLConn)
                Dim RowCnt = SqlCom.ExecuteScalar()
                SQLConn.Close()
                SQLConn.Dispose()
                
                Dim res As String
                res = RowCnt.ToString              
                ddlModelName.Items.FindByText(ddlModelName.SelectedValue.ToString).Selected = False
                ddlModelName.Items.FindByText(res).Selected = True
            Else
                ddlModelName.Items.FindByText(ddlModelName.SelectedValue.ToString).Selected = False
                ddlModelName.Items.FindByText("-Select Model Number-").Selected = True
            End If
        Catch ex As Exception
            'HttpContext.Current.Response.Write("Error: <br><br>" & ex.ToString)
        End Try

Also note, in debugging I stop and pull my SQL Query from the code and run it in my SQL Studio...pulls one row back just like I want... WHY wont the execute scalar do it ...grr.. frustrating, makes no sence... please help :)

[finito]

I am a little confused are you complaining that ExecuteScaler() only gets one value?

If so that is it's function.

Could you clarify what you are trying to do?

What I see is You are getting the ddlProductName.SelectedValue value from the Database, then you are deselecting what ever was selected in ddlModelName and Selecting ddlProductName.SelectedValue, and if it is not in ddlModelName then you are selecting ("-Select Model Number-".

I don't see any problem.

Tip you can replace all this

If Not ddlProductName.SelectedValue = "-Select Product-" Then
                Dim SelectQuery As String = _
                "SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='" + ddlProductName.SelectedValue + "'"
            
                Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
                SQLConn.Open()
                Dim SqlCom As New SqlCommand(SelectQuery, SQLConn)
                Dim RowCnt = SqlCom.ExecuteScalar()
                SQLConn.Close()
                SQLConn.Dispose()
                
                Dim res As String
                res = RowCnt.ToString

with this

Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
        SQLConn.Open()
        Dim res = SqlCommand("SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='" + ddlProductName.SelectedValue + "'", SQLConn).ExecuteScalar().ToString
        SQLConn.Close()
        SQLConn.Dispose()

[dnanetwork]

use ExecuteReaser() or ExecuteNonQuery()

[finito]

use ExecuteReaser() or ExecuteNonQuery()

you mean ExecuteReader()

ExecuteNonReader will not Return anything.

[MichaelWClark]

I am aware that ExecuteScalar only retrieves the first column of the first row. This is all I need. This issue is very confusing to me. The ExecuteScalar works when I use the "cellular" category, but if i switch it to say "computer" and then fire OnSelectedIndexChange for one of my model/product name drop downs and run the aforementioned code. It will not work.

Let me give a little more code/query to hopefully rectify the situation.

With "Cellular" category selected then I change my Product Name (which is bound to the product table) then my query string for the ExecuteScalar will become:

"SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='CellName'"

The ExecuteScalar then returns the corresponding Product_Model from the DB.

With the "Computer catagory selected then my SQL Query becomes:

"SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='CompName'"

(note when run directly in SQL this will return 1 row)

This results in :

Error: 

System.NullReferenceException: Object reference not set to an instance of an object. at ASP.pnlSelectNewAssignment.ddlProductName_SelectedIndexChanged(Object sender, EventArgs e) in %filepath%:line 86

Line 78-86

Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
                SQLConn.Open()
                Dim SqlCom As New SqlCommand(SelectQuery, SQLConn)
                Dim RowCnt = SqlCom.ExecuteScalar()
                SQLConn.Close()
                SQLConn.Dispose()
                
                Dim res As String
                res = RowCnt.ToString

I just don't understand why it will work ONLY with the cellular category.... nothing changes here. . . Please anyone who can shed a bit of light on this..It would be most appreciated.

Thanks in advance! :)

[MichaelWClark]

Update:

Changed code to:

        If Not ddlProductName.SelectedValue = "-Select Product-" Then

            Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
            SQLConn.Open()
            Dim SqlCom As New SqlCommand("SELECT [PRODUCT_MODEL] FROM BHInventory.dbo.PRODUCT WHERE [PRODUCT_NAME] ='" + ddlProductName.SelectedValue + " ' AND [PRODUCT_CATAGORY] = '" + ddlCatagory.SelectedValue + "'", SQLConn)
            Dim RowRead As SqlDataReader = SqlCom.ExecuteReader()
            Dim res As String
            While RowRead.Read
                res = RowRead(0)
            End While
            RowRead.Close()

            ddlModelName.Items.FindByText(ddlModelName.SelectedValue.ToString).Selected = False
            ddlModelName.Items.FindByText(res).Selected = True
        Else
            ddlModelName.Items.FindByText(ddlModelName.SelectedValue.ToString).Selected = False
            ddlModelName.Items.FindByText("-Select Model Number-").Selected = True
        End If

just to try somethign else. Same results. If catagory = cellular all is well, any of the other catagories it will error out on line 87 for null ref. :(

[MichaelWClark]

Okay so....NO Clue what was going on... I scripted out my DB from my SQL Express and put it onto a full version on my test VM and everything is working now....what a waste of time... ASP.net needs to impliment the error:

ASP.NET Error Your SQL Server is lame.

Thanks for the help all!

[dnanetwork]

sorry i mean comm.ExecuteReader();

[crishjeny]

Hi

public static T ExecuteNullableScalar<T>(this SqlCommand cmd)
    where T : struct
{
    var result = cmd.ExecuteScalar();
    if (result == DBNull.Value) return default(T);
    return (T)result;
}