Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.?

Question

0.00/5 (No votes)

See more:

Okay I have put way too much time into what should have been a simple bit of coding, I can not figure out why this doesn't work. I am trying to create a save file for my program and for the most part it works but the one thing I can't save into a txt is the multi-column listview. Instead, i created a table in the database that gets wiped, and then rewritten with the new data when the user saves. However, for some reason, I am getting the error that's in the title.

My connection string:

VB

Public strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
System.Environment.CurrentDirectory & "\OmegaTestDatabase.mdb"

Save Code:

Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click

        Dim DbConnection As New OleDbConnection(strConnectionString)
        Dim SqlQry As New OleDbCommand("INSERT INTO Inventory VALUES (@ItemName, @ItemQuality, @SellPrice)", DbConnection)
        Dim DeleteCmd As New OleDbCommand("DELETE FROM Inventory WHERE [Sell Price] >= 0", DbConnection)

        DbConnection.Open()

        DeleteCmd.CommandType = CommandType.Text

        DeleteCmd.Connection = DbConnection

        DeleteCmd.ExecuteNonQuery()

        DeleteCmd.Dispose()

        DbConnection.Close()

        DbConnection.Open()

        For Each item As ListViewItem In ItemBagList.Items
            SqlQry.Parameters.AddWithValue("@ItemName", item.SubItems(0))
            SqlQry.Parameters.AddWithValue("@ItemQuality", item.SubItems(1))
            SqlQry.Parameters.AddWithValue("@SellPrice", item.SubItems(2))

            SqlQry.ExecuteNonQuery()

        Next

        DbConnection.Close()

        Dim sfd As New SaveFileDialog
        sfd.Filter = "Text Files *.txt|*.txt"
        sfd.Title = "Save File"
        sfd.ShowDialog()
        Dim myPath As String = sfd.FileName

        ' To save
        FileOpen(1, myPath, OpenMode.Output)
        PrintLine(1, PlaySTR.Value)
        PrintLine(1, PlayLUC.Value)
        PrintLine(1, PlayAGI.Value)
        PrintLine(1, PlayINT.Value)
        PrintLine(1, PlaySpirit.Value)
        PrintLine(1, PlayLife.Value)
        PrintLine(1, PlayHP.Value)
        PrintLine(1, PlaySP.Value)
        PrintLine(1, PlayMDEF.Value)
        PrintLine(1, PlayPDEF.Value)
        PrintLine(1, PlayRDEF.Value)
        PrintLine(1, PlayMATK.Value)
        PrintLine(1, PlayPATK.Value)
        PrintLine(1, PlayRATK.Value)
        PrintLine(1, WpnMinDmg.Value)
        PrintLine(1, WpnMaxDmg.Value)
        PrintLine(1, PlaySkillDmg.Value)
        PrintLine(1, PlayMAcc.Value)
        PrintLine(1, PlayPAcc.Value)
        PrintLine(1, PlayRAcc.Value)
        PrintLine(1, PlayCritChance.Value)
        PrintLine(1, PlayCritMult.Value)
        PrintLine(1, BlockValue.Value)
        PrintLine(1, PlayDodge.Value)
        PrintLine(1, PetSTR.Value)
        PrintLine(1, PetLUC.Value)
        PrintLine(1, PetAGI.Value)
        PrintLine(1, PetINT.Value)
        PrintLine(1, PetSpirit.Value)
        PrintLine(1, PetLife.Value)
        PrintLine(1, PetHP.Value)
        PrintLine(1, PetSP.Value)
        PrintLine(1, PetMDEF.Value)
        PrintLine(1, PetPDEF.Value)
        PrintLine(1, PetRDEF.Value)
        PrintLine(1, PetMATK.Value)
        PrintLine(1, PetPATK.Value)
        PrintLine(1, PetRATK.Value)
        PrintLine(1, PetMAcc.Value)
        PrintLine(1, PetPAcc.Value)
        PrintLine(1, PetRAcc.Value)
        PrintLine(1, PetCritChance.Value)
        PrintLine(1, PetCritMult.Value)
        PrintLine(1, PetDodge.Value)
        PrintLine(1, PetSkillDmg.Value)
        PrintLine(1, MobHP.Value)
        PrintLine(1, MobSP.Value)
        PrintLine(1, MobMDEF.Value)
        PrintLine(1, MobPDEF.Value)
        PrintLine(1, MobRDEF.Value)
        PrintLine(1, MobMATK.Value)
        PrintLine(1, MobPATK.Value)
        PrintLine(1, MobRATK.Value)
        PrintLine(1, MobMAcc.Value)
        PrintLine(1, MobPAcc.Value)
        PrintLine(1, MobRAcc.Value)
        PrintLine(1, MobCritChance.Value)
        PrintLine(1, MobCritMult.Value)
        PrintLine(1, MobDodge.Value)
        PrintLine(1, RaceCbo.SelectedItem)
        PrintLine(1, PlaySkillCheck.Checked)
        PrintLine(1, PetSkillCheck.Checked)
        PrintLine(1, MobSkillCheck.Checked)
        PrintLine(1, PetType.SelectedItem)
        PrintLine(1, PlaySkillLvl.Value)
        PrintLine(1, PetSkillLvl.Value)
        PrintLine(1, PetLevelNum.Value)
        PrintLine(1, MobCbo.SelectedItem)
        PrintLine(1, GoldValue.Value)
        PrintLine(1, SilverValue.Value)
        PrintLine(1, CopperValue.Value)
        PrintLine(1, SkillBar1.SelectedItem)
        PrintLine(1, SkillBar2.SelectedItem)
        PrintLine(1, SkillBar3.SelectedItem)
        PrintLine(1, SkillBar4.SelectedItem)
        PrintLine(1, SkillBar5.SelectedItem)
        PrintLine(1, SkillBar6.SelectedItem)
        PrintLine(1, SkillBar7.SelectedItem)
        PrintLine(1, SkillBar8.SelectedItem)
        PrintLine(1, SkillBar9.SelectedItem)
        PrintLine(1, SkillBar10.SelectedItem)
        PrintLine(1, SkillLvl1.Value)
        PrintLine(1, SkillLvl2.Value)
        PrintLine(1, SkillLvl3.Value)
        PrintLine(1, SkillLvl4.Value)
        PrintLine(1, SkillLvl5.Value)
        PrintLine(1, SkillLvl6.Value)
        PrintLine(1, SkillLvl7.Value)
        PrintLine(1, SkillLvl8.Value)
        PrintLine(1, SkillLvl9.Value)
        PrintLine(1, SkillLvl10.Value)
        'saves the values of each of the above controls into a txt file with one line per value
        FileClose(1)

    End Sub

I'm at a complete loss, someone please help me?

EDIT: The table and the listview both have 3 columns titled Item Name, Item Quality, and Sell Price. I have attempted to change the Sell Price column in the database from number, to Short Text, and back to Number and that didn't help, so the problem isn't with datatype.

The problem occurs during the first iteration of For Each... Next

Posted 19-Jun-15 22:00pm

KitsunePhoenix

Updated 19-Jun-15 22:49pm

v2

Add a Solution

Comments

Michael_Davies 20-Jun-15 4:33am

If you look at the table is there one record saved or none?

KitsunePhoenix 20-Jun-15 4:40am

none. The error comes in the first iteration of For... Each... Next.

Michael_Davies 20-Jun-15 5:30am

If you Google the error there are a lot of people had the issue, most answers talk about permissions, but as I take it that your delete works that may not be an issue.

SubQuery: Are there only three fields in the table?

Michael_Davies 20-Jun-15 5:43am

Ran a quick test, created databse1 with table1 containing three fields as per your table then:

Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\My Documents\Database1.mdb"
Dim DbConnection As New OleDbConnection(strConnectionString)
Dim SqlQry As New OleDbCommand("INSERT INTO Table1 VALUES (@ItemName, @ItemQuality, @SellPrice)", DbConnection)

DbConnection.Open()
SqlQry.Parameters.AddWithValue("@ItemName", "iName")
SqlQry.Parameters.AddWithValue("@ItemQuality", 10)
SqlQry.Parameters.AddWithValue("@SellPrice", 5.2)
SqlQry.ExecuteNonQuery()
DbConnection.Close()

Which worked absolutely fine. Try adding a single insert as above, if it works then there is something wrong with the data being submitted to the addparameter so then try:

DbConnection.Open()
SqlQry.Parameters.AddWithValue("@ItemName", ItemBagList.Items(0).SubItems(0).Text)
SqlQry.Parameters.AddWithValue("@ItemQuality", ItemBagList.Items(0).SubItems(1).Text)
SqlQry.Parameters.AddWithValue("@SellPrice", ItemBagList.Items(0).SubItems(2).Text)
SqlQry.ExecuteNonQuery()
DbConnection.Close()

Just realised as I type that the the .Text is missing from my original answer which means it is passing the SubItem object rather than it's content value...sorry.

KitsunePhoenix 20-Jun-15 6:04am

omg I feel stupid. I spent two days trying to find a solution and it was right there in front of me lmao. Thank you

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)

Michael_Davies · Answer 1 · 2015-06-20T02:36:00

Change loop to:

VB

For Each item As ListViewItem In ItemBagList.Items
           SqlQry.Parameters.AddWithValue("@ItemName", item.SubItems(0).Text)
           SqlQry.Parameters.AddWithValue("@ItemQuality", item.SubItems(1).Text)
           SqlQry.Parameters.AddWithValue("@SellPrice", item.SubItems(2).Text)

           SqlQry.ExecuteNonQuery()
       Next