AddWithValue MySqlType.Decimal-CodePudding

Unfortunately, I can't get any further. Working with vs2019 and vb. Created a cascading DropDownList (country, state, region). It works perfectly for country where I don't have to use AddWithValue (where). With state where I have to use AddWithValue (where), I don't get it baked. All IDs are defined as integers. The relevant code:

Protected Sub idLand_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    DropDownBundesland.Items.Clear()
    DropDownBundesland.Items.Add(New ListItem("--Select Country--", ""))
    DropDownRegion.Items.Clear()
    DropDownRegion.Items.Add(New ListItem("--Select City--", ""))

    DropDownBundesland.AppendDataBoundItems = True
    Dim strConnString As [String] = ConfigurationManager _
               .ConnectionStrings("conString").ConnectionString
    Dim strQuery As [String] = "Select Bundesland, idBundesland, idLand from Campingplatz ORDER BY Bundesland ASC where idLand = @LandID Group BY Bundesland"
    Dim con As New MySqlConnection(strConnString)
    Dim cmd As New MySqlCommand()
    cmd.Parameters.AddWithValue("@LandID", MySqlDbType.Decimal).Value = DropDownLand.SelectedItem.Value
    cmd.CommandType = CommandType.Text
    cmd.CommandText = strQuery
    cmd.Connection = con
    Try
        con.Open()
        DropDownBundesland.DataSource = cmd.ExecuteReader()
        DropDownBundesland.DataTextField = "Bundesland"
        DropDownBundesland.DataValueField = "idBundesland"
        DropDownBundesland.DataBind()
        If DropDownBundesland.Items.Count > 1 Then
            DropDownBundesland.Enabled = True
        Else
            DropDownBundesland.Enabled = False
            DropDownRegion.Enabled = False
        End If
    Catch ex As Exception
        'Throw ex
    Finally
        con.Close()
        con.Dispose()
    End Try
End Sub

Please help!

CodePudding user response：

this query is wrong:

Dim strQuery As [String] = "Select Bundesland, idBundesland, idLand from Campingplatz ORDER BY Bundesland ASC where idLand = @LandID Group BY Bundesland"

you must change the order of WHERE, ORDER BY and GROUP BY like:

Dim strQuery As [String] = "Select Bundesland, idBundesland, idLand from Campingplatz WHERE idLand = @LandID Group BY Bundesland ORDER BY Bundesland ASC"

Here you can see the Ssyntax: https://mariadb.com/kb/en/select/

CodePudding user response：

I don't like to mix user interface code with database code. The database code should be independent of the kind of application where it is used.

Connections, Commands and DataReaders need to have their Dispose methods called so they can release unmanaged resources. You are provided with Using...End Using blocks to accomplish this and also close the connection.

You are mixing apples and oranges building the parameters collection. I showed both Add and AddWithValue in the code.

Please see my notes in the Catch block.

I would make the strConString a class level variable if the database is accessed by any other methods in the class.

Protected Sub idLand_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    DropDownBundesland.Items.Clear()
    DropDownBundesland.Items.Add(New ListItem("--Select Country--", ""))
    DropDownRegion.Items.Clear()
    DropDownRegion.Items.Add(New ListItem("--Select City--", ""))
    DropDownBundesland.AppendDataBoundItems = True
    Dim dt As DataTable = Nothing
    Try
        dt = GetDropDownData(CDec(DropDownLand.SelectedItem.Value))
    Catch ex As Exception
        'Where would you be throwing it to? This is an event procedure so is not "called"
        'Alert the user, maybe log the error
        Exit Sub
    End Try
    DropDownBundesland.DataSource = dt
    DropDownBundesland.DataTextField = "Bundesland"
    DropDownBundesland.DataValueField = "idBundesland"
    DropDownBundesland.DataBind()
    If DropDownBundesland.Items.Count > 1 Then
        DropDownBundesland.Enabled = True
    Else
        DropDownBundesland.Enabled = False
        DropDownRegion.Enabled = False
    End If
End Sub

Private strConnString As [String] = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Private Function GetDropDownData(LandID As Decimal) As DataTable
    Dim dt As New DataTable
    Dim strQuery As [String] = "Select Bundesland, idBundesland, idLand 
                               From Campingplatz 
                               Where idLand = @LandID 
                               Group BY Bundesland 
                               ORDER BY Bundesland ASC "
    Using con As New MySqlConnection(strConnString),
            cmd As New MySqlCommand(strQuery, con)
        cmd.Parameters.Add("@LandID", MySqlDbType.Decimal).Value = LandID
        'or cmd.Parameters.AddWithValue("@LandID", LandID)
        con.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function