[RESOLVED] Update OleDB Code Error

I'm trying to updatesome records, currently my table is blank apart from the user id which is correct. but i get the error below.

My UserSettings table is as follows

ID is a Integer
MonthID is a Integer
YearID is a Integer
UserName is a string
Balance is a Integer
PhotoAddress is a string
GameSpeed is a Integer

This is my code and below that is my error in my ErrorTXT box.

Anyidea what is wrong?

vb.net Code:

Try
 
            Using connection As New OleDb.OleDbConnection(My.Settings.AirlineConnectionString)
                Using command As New OleDb.OleDbCommand("UPDATE UserSettings SET MonthID = @MonthID, YearID = @YearID, UserName = @UserName, Balance = @Balance, PhotoAddress = @PhotoAddress, GameSpeed = @GameSpeed WHERE UserID = " & CInt(GameMainWindow.ValuesUserIDTxt.Text), connection)
                    connection.Open()
 
                    command.Parameters.AddWithValue("@ID", CInt(GameMainWindow.ValuesUserIDTxt.Text))
                    command.Parameters.AddWithValue("@MonthID", CInt(GameMainWindow.ValuesGameMonthTxt.Text))
                    command.Parameters.AddWithValue("@YearID", CInt(GameMainWindow.ValuesGameYearTxt.Text))
                    command.Parameters.AddWithValue("@UserName", GameMainWindow.ValuesUserNameTxt.Text)
                    command.Parameters.AddWithValue("@Balance", CInt(GameMainWindow.ValuesBalanceTxt.Text))
                    command.Parameters.AddWithValue("@PhotoAddress", GameMainWindow.ValuesPhotoAddressTxt.Text)
                    command.Parameters.AddWithValue("@GameSpeed", CInt(GameMainWindow.ValuesGameSpeedTxt.Text))
 
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using
            End Using
 
        Catch ex As Exception
            GameMainWindow.ErrorTXT.Text = ex.ToString
        End Try

Code:

---

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
  at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
  at Airline_Sim.MainMod.SaveGame() in C:\Documents and Settings\Me\My Documents\Visual Studio 2008\Projects\Airliine Sim\Airliine Sim\GameData\MainMod.vb:line 25

---

I've updated my code and I get no errors, however the information is not saving into my database. Why?

vb.net Code:

Try
 
            Using connection As New OleDb.OleDbConnection(My.Settings.AirlineConnectionString)
                Using command As New OleDb.OleDbCommand("UPDATE UserSettings SET MonthID = @MonthID, YearID = @YearID, UserName = @UserName, Balance = @Balance, PhotoAddress = @PhotoAddress, GameSpeed = @GameSpeed WHERE UserID = @UserID", connection)
                    connection.Open()
 
                    command.Parameters.AddWithValue("@UserID", CInt(GameMainWindow.ValuesUserIDTxt.Text))
                    command.Parameters.AddWithValue("@MonthID", CInt(GameMainWindow.ValuesGameMonthTxt.Text))
                    command.Parameters.AddWithValue("@YearID", CInt(GameMainWindow.ValuesGameYearTxt.Text))
                    command.Parameters.AddWithValue("@UserName", GameMainWindow.ValuesUserNameTxt.Text)
                    command.Parameters.AddWithValue("@Balance", CInt(GameMainWindow.ValuesBalanceTxt.Text))
                    command.Parameters.AddWithValue("@PhotoAddress", GameMainWindow.ValuesPhotoAddressTxt.Text)
                    command.Parameters.AddWithValue("@GameSpeed", CInt(GameMainWindow.ValuesGameSpeedTxt.Text))
 
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using
            End Using
 
        Catch ex As Exception
            GameMainWindow.ErrorTXT.Text = ex.ToString
        End Try
    End Sub

You have added the parameters in the wrong order - you need to ensure that they are added in the same order as they appear in the SQL statement.

I have it in this order but still does not work.

vb.net Code:

Try
 
            Using connection As New OleDb.OleDbConnection(My.Settings.AirlineConnectionString)
                Using command As New OleDb.OleDbCommand("UPDATE UserSettings SET MonthID = @MonthID, YearID = @YearID, UserName = @UserName, Balance = @Balance, PhotoAddress = @PhotoAddress, GameSpeed = @GameSpeed WHERE UserID = @UserID", connection)
                    connection.Open()
 
                    command.Parameters.AddWithValue("@MonthID", CInt(GameMainWindow.ValuesGameMonthTxt.Text))
                    command.Parameters.AddWithValue("@YearID", CInt(GameMainWindow.ValuesGameYearTxt.Text))
                    command.Parameters.AddWithValue("@UserName", GameMainWindow.ValuesUserNameTxt.Text)
                    command.Parameters.AddWithValue("@Balance", CInt(GameMainWindow.ValuesBalanceTxt.Text))
                    command.Parameters.AddWithValue("@PhotoAddress", GameMainWindow.ValuesPhotoAddressTxt.Text)
                    command.Parameters.AddWithValue("@GameSpeed", CInt(GameMainWindow.ValuesGameSpeedTxt.Text))
                    command.Parameters.AddWithValue("@UserID", CInt(GameMainWindow.ValuesUserIDTxt.Text))
 
                    command.ExecuteNonQuery()
                    connection.Close()
                End Using
            End Using
 
        Catch ex As Exception
            GameMainWindow.ErrorTXT.Text = ex.ToString
        End Try

There is nothing obviously wrong there... try expanding on "does not work" to give us a clue what we should be looking for.

Well it looks okay in debugging until it gets to the ExecuteNonQuery and then it errors, says overflow?

vb Code:

System.Data.OleDb.OleDbException: Overflow
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Airline_Sim.MainMod.SaveGame()

This is the output?

Think of it like this: If you have a 10 gallpon bucket and you try to pour 12 gallons into it, what happens? It overflows. Same thing with data. If you try to insert a 45 character data value (your 12 gallons) into a spot only intended to hold 30 characters (your 10 gallon bucket) ... it overflows. In short, something is larger than it is allowed to be.

-tg

Ohmgod, I had a Integer and not a Large Integer as my Balance is in the hundreds of millions