Reznicencu Bogdan Reznicencu Bogdan - 7 months ago 61
SQL Question

Select single row from database using mysql connector .NET

      I've just heard about the mysql connector and I want now to take a single row from my database(every row is unique). I don't want to use a loop because it would be useless(only for one row?..). I know how to connect to a database but I just can't figure out how to select a single row. This is my code :

''Register user in dtb
cmd = New MySqlCommand("INSERT INTO `users`(`name`, `ip`, `status`) VALUES ('" & My.User.Name.Split("\")(1) & "','" & ip_temp & "','1')", conn)
cmd.ExecuteNonQuery()


''Get user's Id from dtb -->this is where I'm having troubles
cmd = New MySqlCommand("SELECT 'id' FROM `users` WHERE name='" & My.User.Name.Split("\")(1) & "' ", conn)
dtb_data = cmd.ExecuteScalar

Debug.Print(dtb_data(0))



       It doesn't work.
      The error is: "Unable to cast object of type 'System.String' to type 'MySql.Data.MySqlClient.MySqlDataReader'." (of course...). I know that it must look something like "dtb_data['id']" (from php) but this command doesn't exist in VB.Net. What should I do?

Answer

ExecuteScalar returns single value from single row. Therefore in your case dtb_data must be declared as String. Most probably this is mistake and it should be declared as Int or Long as you probably want to return id, not 'id' as literal.

So the full example should be:

    Dim dtb_data as Long

    cmd = New MySqlCommand("SELECT id FROM `users` WHERE name='" & My.User.Name.Split("\")(1) & "' ", conn)
    dtb_data = cmd.ExecuteScalar

    Debug.Print(dtb_data)
Comments