Vb.Net MySql Update Command Problem 1

[Auguy]

The Physician table has PhysicianPk as Int(11). I'm getting an System.InvalidCastException: Conversion from string "physicianPK" to type Integer is not invalid. Why does it think PhysicianPk is a string? I also tried all of the Int16,24,32,64 types. I must be mssing something simple here.

Dim adapter As MySqlDataAdapter = New MySqlDataAdapter()

    ' Create the SelectCommand. 
    Dim command As MySqlCommand = New MySqlCommand( _
        "SELECT * FROM Physician", connection)

    ' Create the UpdateCommand.
    command = New MySqlCommand( _
        "UPDATE Physician SET LastName = @LastName, FirstName = @FirstName " & _
        "WHERE PhysicianPK = @oldPhysicianPK", connection)

    Try
      ' Add the parameters for the UpdateCommand.
      command.Parameters.Add("@PhysicianPK", MySqlDbType.Int24, "PhysicianPK")
      command.Parameters.Add("@LastName", MySqlDbType.VarChar, 20, "LastName")
      command.Parameters.Add("@FirstName", MySqlDbType.VarChar, 15, "FirstName")
      Dim parameter As MySqlParameter = command.Parameters.Add( _
          "@oldPhysicianPK", MySqlDbType.Int24, "PhysicianPK")
      parameter.SourceVersion = DataRowVersion.Original
    Catch ex As Exception
      MessageBox.Show(ex.ToString)
    End Try

Auguy
Sylvania/Toledo Ohio

 

[jebenson]

I see a few issues:

1) You must add the parameters to the command object in the same order they are declared in the SQL. So your code should read:

      command.Parameters.Add("@LastName", MySqlDbType.VarChar, 20, "LastName")
      command.Parameters.Add("@FirstName", MySqlDbType.VarChar, 15, "FirstName")
      Dim parameter As MySqlParameter = command.Parameters.Add( _
          "@oldPhysicianPK", MySqlDbType.Int24, "PhysicianPK")
      parameter.SourceVersion = DataRowVersion.Original
      command.Parameters.Add("@PhysicianPK", MySqlDbType.Int24, "PhysicianPK")

2) You are adding a parameter named "@PhysicianPK", but you do not have that parameter defined in the SQL.

3) @oldPhysicianPK and @PhysicianPK are both set to point to the same field (PhysicianPK)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Auguy]

Thanks, i will take a closer look later today.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

Got it working, thanks

Auguy
Sylvania/Toledo Ohio