@Parameter Has No Default Value

Question

0.00/5 (No votes)

See more:

I have a feeling it's just a matter of rearranging a few lines but I can't figure it out myself. The idea behind this section of code is simple.

I have a listview that serves as an item bag, when you gain an item (through the use of another part of the program) it gets put into the listview. The listview has 3 columns: Item Name, Item Quality, and Sell Price. When you click the use button, it's supposed to find the selected item in the database, and assign it to a slot in the equipment screen based on the item class. However, a code layout that I've used before with no problem, is suddenly giving me problems.

VB

Dim DbConnection As New OleDbConnection(DbString)
'creates the datatable for the Item List
Dim ItemSelect As New OleDbCommand("SELECT * FROM [Item List] WHERE [Item Name] = ? AND [Item Quality] = ?", DbConnection)

Dim ItemNameParam As New OleDbParameter("?", OleDbType.VarChar)
ItemSelect.Parameters.Add(ItemNameParam)

Dim ItemQualityParam As New OleDbParameter("?", OleDbType.VarChar)
ItemSelect.Parameters.Add(ItemQualityParam)

Dim item As ListViewItem

Dim ItemClass As Integer

Dim itemda As New OleDbDataAdapter(ItemSelect)
Dim itemds As New DataSet

itemda.Fill(itemds, "Item List")

Dim itemdt As DataTable = itemds.Tables("Item List")
Dim itemrow As DataRow

If ItemBagList.SelectedItems.Count > 1 Then
    MsgBox("You can only use/equip one item at a time")

ElseIf ItemBagList.SelectedItems.Count = 1 Then

    For Each item In ItemBagList.SelectedItems

        For Each itemrow In itemdt.Rows
            ItemNameParam.Value = item.SubItems(0)
            ItemQualityParam.Value = item.SubItems(1)
            ItemClass = itemrow("Item Class")

            If ItemClass = 2 Or ItemClass = 22 Then
                EquipItems.HelmetEquip.Text = ItemNameParam.Value
                EquipItems.HelmetQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 3 Or ItemClass = 23 Then
                EquipItems.ArmorEquip.Text = ItemNameParam.Value
                EquipItems.ArmorQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 4 Or ItemClass = 24 Then
                EquipItems.LeggingsEquip.Text = ItemNameParam.Value
                EquipItems.LeggingsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 5 Or ItemClass = 25 Then
                EquipItems.BootsEquip.Text = ItemNameParam.Value
                EquipItems.BootsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 6 Or ItemClass = 26 Then
                EquipItems.GauntletsEquip.Text = ItemNameParam.Value
                EquipItems.GauntletsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 29 Then
                If EquipItems.RRingEquip.Text = "None" Then
                    EquipItems.RRingEquip.Text = ItemNameParam.Value
                    EquipItems.RRingQuality.SelectedItem = ItemQualityParam.Value

                ElseIf EquipItems.RRingEquip.Text <> "None" And EquipItems.LRingEquip.Text = "None" Then
                    EquipItems.LRingEquip.Text = ItemNameParam.Value
                    EquipItems.LRingQuality.SelectedItem = ItemQualityParam.Value

                ElseIf EquipItems.RRingEquip.Text <> "None" And EquipItems.LRingEquip.Text <> "None" Then
                    Dim RingEquip As DialogResult = MessageBox.Show("Click Yes to equip to right hand, click No to equip to left hand", _
                                                                "Important Query", MessageBoxButtons.YesNoCancel)

                    If RingEquip = DialogResult.Yes Then
                        EquipItems.RRingEquip.Text = ItemNameParam.Value
                        EquipItems.RRingQuality.SelectedItem = ItemQualityParam.Value

                    ElseIf RingEquip = DialogResult.No Then
                        EquipItems.LRingEquip.Text = ItemNameParam.Value
                        EquipItems.LRingQuality.SelectedItem = ItemQualityParam.Value

                    End If
                End If

            ElseIf ItemClass = 30 Then
                EquipItems.NecklaceEquip.Text = ItemNameParam.Value
                EquipItems.NecklaceQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 31 Then
                EquipItems.EarringEquip.Text = ItemNameParam.Value
                EquipItems.EarringQuality.SelectedItem = ItemQualityParam

            ElseIf ItemClass = 8 Or ItemClass = 10 Or ItemClass = 11 Or ItemClass = 13 Or ItemClass = 16 Or ItemClass = 17 Or ItemClass = 18 Then
                Dim WepEquip As DialogResult = MessageBox.Show("Click Yes to equip to right hand, click No to equip to left hand", _
                                                                "Important Query", MessageBoxButtons.YesNoCancel)

                If WepEquip = DialogResult.Yes Then
                    EquipItems.RWeaponEquip.Text = ItemNameParam.Value

                ElseIf WepEquip = DialogResult.No Then
                    EquipItems.LWeaponEquip.Text = ItemNameParam.Value

                ElseIf WepEquip = DialogResult.Cancel Then

                End If

            ElseIf ItemClass = 9 Or ItemClass = 12 Or ItemClass = 14 Or ItemClass = 15 Then
                EquipItems.RWeaponEquip.Text = ItemNameParam.Value
                EquipItems.LWeaponEquip.Text = ItemNameParam.Value
                EquipItems.RWeaponQuality.Text = ItemQualityParam.Value
                EquipItems.LWeaponQuality.Text = ItemQualityParam.Value

            ElseIf ItemClass = 19 Or ItemClass = 20 Or ItemClass = 21 Then
                EquipItems.LWeaponEquip.Text = ItemNameParam.Value
                EquipItems.LWeaponQuality.SelectedItem = ItemQualityParam

            Else

                For Each i As ListViewItem In ItemBagList.SelectedItems
                    i.Remove()
                Next

            End If
        Next
    Next
End If

When I run the debugger it points to the data adapter .Fill command and says that @ItemName has no default value. The only way it works is if the value is assigned before the .Fill command, but I can't put the values up there because item has no value up there. And I can't move the .Fill command down to the value call because dt needs to be filled before I can call on its rows. I am sure the answer is staring me in the face but I just don't see it.

EDIT: I found a way to fix the original problem but it generates another problem. I moved the da, ds, and dt calls inside the first For Each and moved the parameter value calls to the first For Each, so now the data adapter can see the values. However, it doesn't seem capable of comprehending the value.

Dim DbConnection As New OleDbConnection(DbString)
'creates the datatable for the Item List
Dim ItemSelect As New OleDbCommand("SELECT * FROM [Item List] WHERE [Item Name] = ? AND [Item Quality] = ?", DbConnection)



If ItemBagList.SelectedItems.Count > 1 Then
    MsgBox("You can only use/equip one item at a time")

ElseIf ItemBagList.SelectedItems.Count = 1 Then

    For Each item In ItemBagList.SelectedItems
        Dim ItemNameParam As New OleDbParameter("?", OleDbType.VarChar)
        ItemSelect.Parameters.Add(ItemNameParam)

        Dim ItemQualityParam As New OleDbParameter("?", OleDbType.VarChar)
        ItemSelect.Parameters.Add(ItemQualityParam)

        ItemNameParam.Value = item.SubItems(0)
        ItemQualityParam.Value = item.SubItems(1)

        Dim ItemClass As Integer

        Dim itemda As New OleDbDataAdapter(ItemSelect)
        Dim itemds As New DataSet

        itemda.Fill(itemds, "Item List")

        Dim itemdt As DataTable = itemds.Tables("Item List")
        Dim itemrow As DataRow

        For Each itemrow In itemdt.Rows
            ItemClass = itemrow("Item Class")

            If ItemClass = 2 Or ItemClass = 22 Then
                EquipItems.HelmetEquip.Text = ItemNameParam.Value
                EquipItems.HelmetQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 3 Or ItemClass = 23 Then
                EquipItems.ArmorEquip.Text = ItemNameParam.Value
                EquipItems.ArmorQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 4 Or ItemClass = 24 Then
                EquipItems.LeggingsEquip.Text = ItemNameParam.Value
                EquipItems.LeggingsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 5 Or ItemClass = 25 Then
                EquipItems.BootsEquip.Text = ItemNameParam.Value
                EquipItems.BootsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 6 Or ItemClass = 26 Then
                EquipItems.GauntletsEquip.Text = ItemNameParam.Value
                EquipItems.GauntletsQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 29 Then
                If EquipItems.RRingEquip.Text = "None" Then
                    EquipItems.RRingEquip.Text = ItemNameParam.Value
                    EquipItems.RRingQuality.SelectedItem = ItemQualityParam.Value

                ElseIf EquipItems.RRingEquip.Text <> "None" And EquipItems.LRingEquip.Text = "None" Then
                    EquipItems.LRingEquip.Text = ItemNameParam.Value
                    EquipItems.LRingQuality.SelectedItem = ItemQualityParam.Value

                ElseIf EquipItems.RRingEquip.Text <> "None" And EquipItems.LRingEquip.Text <> "None" Then
                    Dim RingEquip As DialogResult = MessageBox.Show("Click Yes to equip to right hand, click No to equip to left hand", _
                                                                "Important Query", MessageBoxButtons.YesNoCancel)

                    If RingEquip = DialogResult.Yes Then
                        EquipItems.RRingEquip.Text = ItemNameParam.Value
                        EquipItems.RRingQuality.SelectedItem = ItemQualityParam.Value

                    ElseIf RingEquip = DialogResult.No Then
                        EquipItems.LRingEquip.Text = ItemNameParam.Value
                        EquipItems.LRingQuality.SelectedItem = ItemQualityParam.Value

                    End If
                End If

            ElseIf ItemClass = 30 Then
                EquipItems.NecklaceEquip.Text = ItemNameParam.Value
                EquipItems.NecklaceQuality.SelectedItem = ItemQualityParam.Value

            ElseIf ItemClass = 31 Then
                EquipItems.EarringEquip.Text = ItemNameParam.Value
                EquipItems.EarringQuality.SelectedItem = ItemQualityParam

            ElseIf ItemClass = 8 Or ItemClass = 10 Or ItemClass = 11 Or ItemClass = 13 Or ItemClass = 16 Or ItemClass = 17 Or ItemClass = 18 Then
                Dim WepEquip As DialogResult = MessageBox.Show("Click Yes to equip to right hand, click No to equip to left hand", _
                                                                "Important Query", MessageBoxButtons.YesNoCancel)

                If WepEquip = DialogResult.Yes Then
                    EquipItems.RWeaponEquip.Text = ItemNameParam.Value

                ElseIf WepEquip = DialogResult.No Then
                    EquipItems.LWeaponEquip.Text = ItemNameParam.Value

                ElseIf WepEquip = DialogResult.Cancel Then

                End If

            ElseIf ItemClass = 9 Or ItemClass = 12 Or ItemClass = 14 Or ItemClass = 15 Then
                EquipItems.RWeaponEquip.Text = ItemNameParam.Value
                EquipItems.LWeaponEquip.Text = ItemNameParam.Value
                EquipItems.RWeaponQuality.Text = ItemQualityParam.Value
                EquipItems.LWeaponQuality.Text = ItemQualityParam.Value

            ElseIf ItemClass = 19 Or ItemClass = 20 Or ItemClass = 21 Then
                EquipItems.LWeaponEquip.Text = ItemNameParam.Value
                EquipItems.LWeaponQuality.SelectedItem = ItemQualityParam

            Else

                For Each i As ListViewItem In ItemBagList.SelectedItems
                    i.Remove()
                Next

            End If
        Next
    Next
End If

Posted 29-Jun-15 10:47am

KitsunePhoenix

Updated 29-Jun-15 12:53pm

v3

Add a Solution

Comments

Andy Lanng 30-Jun-15 4:30am

Ok - good to see progress being made. I understand what you are trying to do now.
What do you mean "it doesn't seem capable of comprehending the value." How does this problem manifest?

KitsunePhoenix 30-Jun-15 15:38pm

it says "Failed to convert parameter value from a ListViewSubItem to a String."

Andy Lanng 1-Jul-15 4:00am

oic. you'll have to pick the attribute of the ListViewSubItem you want to use. Unlike most List objects, it doesn't have a ToString overload so using it as a parameter directly wont work. Try using item.Text instead.
More on ListViewSubItems here

KitsunePhoenix 1-Jul-15 10:43am

either the solution isn't there or I just can't see it. Using item.Text didn't work either, though it didn't give an error. it didn't give any effect either. I clicked the button and got nothing.

I also tried item.Text(0) and item.Text(1) with the same result.

Andy Lanng 1-Jul-15 10:47am

ItemNameParam.Value = item.SubItems(0).Text

KitsunePhoenix 1-Jul-15 11:37am

that did it! Thank you so much! and thank you for being patient with me. Edit your solution and I'll mark it as answered

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)

Andy Lanng · Accepted Answer · 2015-06-29T10:57:00

Solution 1

OLEDb parameterized queries are a little different to SQLClient queries. This is to keep them generic for other connection drivers.

Read this article to find out more
Parameters - SqlCommand vs. OledbCommand and OdbcCommand[^]

good luck ^_^

UPDATE: OP has corrected the parameters but still gets error:

You are adding parameters but you need to give them values too:

C#

Dim ItemNameParam As New OleDbParameter("?", OleDbType.VarChar)
//I can't check the syntax as I'm at home, but something like:
ItemNameParam.Value = "SomeString";
ItemSelect.Parameters.Add(ItemNameParam)

This solution did answer the original question, but here's the solution to the latest issue:
ItemNameParam.Value = item.SubItems(0) does not work.

SubItems(0) is a ListViewItem.ListViewSubItem.[^] To get the displayed value you should use the Text property:

VB

"ItemNameParam.Value = item.SubItems(0).Text

Posted 29-Jun-15 10:57am

Andy Lanng

Updated 1-Jul-15 5:42am

v3

Comments

KitsunePhoenix 29-Jun-15 17:15pm

that was good information to know but doesn't help with my specific problem. My problem is with giving it a value. I tried giving the parameter a placeholder value, figuring that when the code ran it would give it a new value but that didn't work either.

Andy Lanng 29-Jun-15 17:24pm

in that case, please update the question with the green "Improve Question" button below your post and update the code. Guess work can only get us so far :P
I'll either update or retract my solution then ^_^

KitsunePhoenix 29-Jun-15 18:03pm

updated though all I changed was to make the named parameters into ?

Andy Lanng 29-Jun-15 18:09pm

ah - ok - i see it.
The biggest error in the code always stands out so much it's hard to see others. It's just the way I see the code :)

Updating solution

KitsunePhoenix 29-Jun-15 18:33pm

The problem is, the point where I supply the value is the earliest I can (I supply the values in the inner For Each statement). I've done similar code before and it worked, but I can't figure out why it's not working now. If I supply a value for the parameters before the For Each statement, then it tries to use that value instead of the value I supply in the For Each.