9 Replies - 1142 Views - Last Post: 22 January 2013 - 10:30 AM Rate Topic: -----

#1 pendaz  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 75
  • Joined: 14-August 12

Strange MySql Exception

Posted 22 January 2013 - 02:48 AM

I'm attempting to read values from a database into my application, i have a table with a number of entries all of Type: Text

heres what im using to read the values:


        frmMain.Cursor = Cursors.WaitCursor


        Dim conn As New MySqlConnection
        conn.ConnectionString = "*************"
        'Get user data
        Try
            conn.Open()
            'Dim stm As String = "SELECT Nickname, Enabled, License, Version, Password, email, news, logincount, lastloggedin FROM Users WHERE Username='" & SELusername & "'"
            Dim stm As String = "SELECT * FROM Users WHERE Username='" & SELusername & "'"
            Dim cmd As MySqlCommand = New MySqlCommand(stm, conn)
            Dim reader As MySqlDataReader = cmd.ExecuteReader()

            With frmMain

                username = SELusername
                frmMain.txtUsername.Text = username


                reader.Read()


                nickname = reader.GetString("nickname")
                oldnickname = reader.GetString("nickname")
                .txtNickname.Text = nickname



                Enabled = reader.GetString("enabled")
                oldEnabled = reader.GetString("enabled")
                frmMain.cmbEnabled.Text = Enabled
                If Enabled = "1" Then
                    .cmbEnabled.Text = "Yes"
                ElseIf Enabled = "0" Then
                    .cmbEnabled.Text = "No"
                End If

                License = reader.GetString("license")
                oldLicense = reader.GetString("license")
                .txtLicense.Text = License

                Version = reader.GetString("version")
                oldVersion = reader.GetString("version")
                .txtVersion.Text = Version


                Password = reader.GetString("password")
                oldPassword = reader.GetString("password")
                frmMain.txtPassword.Text = Password


                logincount = reader.GetString("LoginCount")
                .txtLoginCount.Text = logincount

                lastloggedin = reader.GetString("lastloggedin")
                .txtLastLoggedIn.Text = lastloggedin

                news = reader.GetString("news")
                oldNews = reader.GetString("news")
                If news = "1" Then
                    .cmbNews.Text = "Yes"
                ElseIf news = "0" Then
                    .cmbNews.Text = "No"
                End If

                email = reader.GetString("email")
                oldEmail = reader.GetString("email")
                .txtEmail.Text = email
                reader.Close()
                frmMain.Cursor = Cursors.Default

            End With
        Catch ex As MySqlException
            MsgBox("Error: " & ex.ToString())
        Finally
            conn.Close()
        End Try





Now for some reason i can't seem to understand, some of the entries in the table read perfectly, yet others throw the following error:

Quote

Error: MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid attempt to access a field before calling read()
at MySql.Data.MySqlClient.ResultSet.get_item(int32 index)
at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(int32 index, Boolean checkNull)
at MySql.Data.MySqlClient.MySqlDataReader.Getstring(int32 i)
at MySql.Data.MySqlClient.MySqlDataReader.Getstring(String Column)



It seems strange to me that i'm getting this error on only some of the records in the table - i've reviewed the data in table and can't seem to find any kind of pattern.

any suggestions?

Thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Strange MySql Exception

#2 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Strange MySql Exception

Posted 22 January 2013 - 03:03 AM

i think

Quote

Dim stm As String = "SELECT * FROM Users WHERE Username='" & SELusername & "'"


should be like

 Dim stm As String = "SELECT * FROM Users WHERE Username='" & SELusername.text & "'"


check out..
My link

This post has been edited by kai_itz me: 22 January 2013 - 03:05 AM

Was This Post Helpful? 0
  • +
  • -

#3 pendaz  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 75
  • Joined: 14-August 12

Re: Strange MySql Exception

Posted 22 January 2013 - 05:00 AM

View Postkai_itz me, on 22 January 2013 - 03:03 AM, said:

i think

Quote

Dim stm As String = "SELECT * FROM Users WHERE Username='" & SELusername & "'"


should be like

 Dim stm As String = "SELECT * FROM Users WHERE Username='" & SELusername.text & "'"


check out..
My link



Thanks for the reply but SELUsername is a variable not a textbox, its assigned when the user selects a username from a list
Was This Post Helpful? 0
  • +
  • -

#4 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 255
  • View blog
  • Posts: 1,386
  • Joined: 11-May 12

Re: Strange MySql Exception

Posted 22 January 2013 - 06:35 AM

You could try using SELusername.ToString()
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Strange MySql Exception

Posted 22 January 2013 - 08:19 AM

Not real familiar with mySQL but is it failing on the second attempt to read the same field?
Does GetString advance the cursor which would not allow you to read it again in the same row? (Forward only)

If that's the case it appears you're storing the current value (in case of change) so why not try
       Enabled = reader.GetString("enabled")
       oldEnabled = Enabled 'Stores currently read value.
       frmMain.cmbEnabled.Text = Enabled



Other than that, maybe remark out the Try, Catch and End Try lines and it should show what line failed with this exception.

This post has been edited by CharlieMay: 22 January 2013 - 08:26 AM

Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Strange MySql Exception

Posted 22 January 2013 - 08:32 AM

I just Googled your error and this was interesting.
It appears there are some case-sensitivity issues happening. Maybe try making sure the strings you have in your GetString("Fields") matches the database exactly.
Was This Post Helpful? 0
  • +
  • -

#7 pendaz  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 75
  • Joined: 14-August 12

Re: Strange MySql Exception

Posted 22 January 2013 - 08:35 AM

View PostCharlieMay, on 22 January 2013 - 08:19 AM, said:

Not real familiar with mySQL but is it failing on the second attempt to read the same field?
Does GetString advance the cursor which would not allow you to read it again in the same row? (Forward only)

If that's the case it appears you're storing the current value (in case of change) so why not try
       Enabled = reader.GetString("enabled")
       oldEnabled = Enabled 'Stores currently read value.
       frmMain.cmbEnabled.Text = Enabled



Other than that, maybe remark out the Try, Catch and End Try lines and it should show what line failed with this exception.



Thanks for the reply mate, im out at the moment but i'll give your suggetion a try when i get home shortly

The exception reports line 100 which in my code is:

100: nickname = reader.GetString("nickname")
101: oldnickname = reader.GetString("nickname")
102: .txtNickname.Text = nickname



but if i comment out line 100 it throws an error on 101, 102, and so on
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Strange MySql Exception

Posted 22 January 2013 - 08:38 AM

Check out my last post. It appears others are having similar issues with a certain release. I know it talks about stored procedures but on down it does state that it doesn't have to be ran from a stored procedure to reproduce the bug. It's more a case-sensitivity issue with names.
Was This Post Helpful? 0
  • +
  • -

#9 pendaz  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 75
  • Joined: 14-August 12

Re: Strange MySql Exception

Posted 22 January 2013 - 08:42 AM

View PostCharlieMay, on 22 January 2013 - 08:38 AM, said:

Check out my last post. It appears others are having similar issues with a certain release. I know it talks about stored procedures but on down it does state that it doesn't have to be ran from a stored procedure to reproduce the bug. It's more a case-sensitivity issue with names.


ah yes i missed that post, i'll look into it when im home

thanks gain mate
Was This Post Helpful? 0
  • +
  • -

#10 pendaz  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 75
  • Joined: 14-August 12

Re: Strange MySql Exception

Posted 22 January 2013 - 10:30 AM

ok i set all my tables to lower case and my code all called in lower case, still getting the same error


i'm not exactly sure what version of the connector im using so im going to update to the latest and report back if it makes any difference
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1