Pass SQL value to a VB.Net variable?

Question

I have this code that runs an SQL:

strsql = "select Nome, Senha from users where Nome=@field1 and Senha=@field2"
objcmd = New Data.MySqlClient.MySqlCommand(strsql, objconn)
With objcmd
    .Parameters.AddWithValue("@field1", "valor1")
    .Parameters.AddWithValue("@field2", "valor2")

End With
objcmd.ExecuteNonQuery()
objdr = objcmd.ExecuteReader
If (objdr.Read()) Then
    MsgBox("Sucesso.")
Else
    MsgBox("Erro desconhecido.")
End If
objcmd.Dispose()
objdr.Close()

I want to know, how can I get a value, for example the Name and put into a variable (take the value of the column and move to a string)?

Answer 1

To bring this information use the class Mysqldatareader setting its variable with the return of the Mysqlcommand .ExecuteReader() , example:

strsql = "select Nome, Senha from users where Nome=@field1 and Senha=@field2"
objcmd = New Data.MySqlClient.MySqlCommand(strsql, objconn)
With objcmd
    .Parameters.AddWithValue("@field1", "valor1")
    .Parameters.AddWithValue("@field2", "valor2")
End With

Dim objdr As MySqlDataReader
objdr = objcmd.ExecuteReader

If (objdr.Read()) Then // se existe registro.
    Dim nome as string = objdr.GetString(0);
    Dim senha as string = objdr.GetString(1);
Else
    MsgBox("Erro desconhecido.")
End If
objcmd.Dispose()
objdr.Close()

The objdr.GetString returns the column value by positioning from 0 to the total number of columns, if it is two equal columns it is in the SQL, is 0 for Name and 1 for Password, and so on.

It is worth remembering that the type is very important so that there are no problems in the conversions, and the data types have to correspond to the type that is in the columns in their return tables, the example below taken from the http://www.macoratti.net website, shows the correlative types and their respective access to the database SQL Server:

+------------------------------------------------------------------------------+
| SQL Server       |.NET Framework | Acessor .NET Framework | SQLType          |
| -----------------------------------------------------------------------------|
| binary           | Byte[]        | GetBytes()             | GetSqlBinary()   |
| -----------------------------------------------------------------------------|
| bit              | Boolean       | GetBoolean()           | GetSqlBit()      |
| -----------------------------------------------------------------------------|
| char             | String Char[] | GetString() GetChars() | GetSqlString()   |
| -----------------------------------------------------------------------------|
| datetime         | DateTime      | GetDateTime()          | GetSqlDateTime() |
| -----------------------------------------------------------------------------|
| decimal          | Decimal       | GetDecimal()           | GetSqlDecimal()  |
| -----------------------------------------------------------------------------|
| float            | Double        | GetDouble()            | GetSqlDouble()   |
| -----------------------------------------------------------------------------|
| image            | Byte[]        | GetBytes()             | GetSqlBinary()   |
|------------------------------------------------------------------------------|
| int              | Int32         | GetInt32()             | GetSqlInt32()    |
|------------------------------------------------------------------------------|
| money            | Decimal       | GetDecimal()           | GetSqlMoney()    |
|------------------------------------------------------------------------------|
| nchar            | String Char[] | GetString() GetChars() | GetSqlString()   |
|------------------------------------------------------------------------------|
| ntext            | String Char[] | GetString() GetChars() | GetSqlString()   |
|------------------------------------------------------------------------------|
| numeric          | Decimal       | GetDecimal()           | GetSqlDecimal()  |
|------------------------------------------------------------------------------|
| nvarchar         | String Char[] | GetString() GetChars() | GetSqlString()   |
|------------------------------------------------------------------------------|
| real             | Single        | GetFloat()             | GetSqlSingle()   |
|------------------------------------------------------------------------------|
| smalldatetime    | DateTime      | GetDateTime()          | GetSqlDateTime() |
|------------------------------------------------------------------------------|
| smallint         | Int16         | Int16                  | GetSqlInt16()    |
|------------------------------------------------------------------------------|
| smallmoney       | Decimal       | GetDecimal()           | GetSqlDecimal()  |
|------------------------------------------------------------------------------|
| sql_variant      | Object        | GetValue()             | GetSqlValue()    |
|------------------------------------------------------------------------------|
| text             | String Char[] | GetString() GetChars() | GetSqlString()   |
|------------------------------------------------------------------------------|
| timestamp        | Byte[]        | GetBytes()             | GetSqlBinary()   |
|------------------------------------------------------------------------------|
| tinyint          | Byte          | GetByte()              | GetSqlByte()     |  
|------------------------------------------------------------------------------|
| uniqueidentifier | Guid          | GetGuid()              | GetSqlGuid()     |
|------------------------------------------------------------------------------|
| varbinary        | Byte[]        | GetBytes()             | GetSqlBinary()   |
|------------------------------------------------------------------------------|
| varchar          | String Char[] | GetString() GetChars() | GetSqlString()   |
+------------------------------------------------------------------------------+

_{Downloadable in: http://www.macoratti.net/12/11/ado_mdes.htm}

References:

• 4.1.2 The Mysqlcommand Object
• Sqldatareader.Getstring method (Int32)
• Mysqlcommand(). Executereader(). Getstring() does not work
• Dbdatareader.Getstring method (Int32)
• Dbdatareader.Read method ()
• ADO . NET - Datareader - Improving Performance (C#)

Answer 2

The method you are using to effectively execute the SQL statements is the ExecuteNonQuery(). Get noticed in the documentation, it says that this method does not return any lines. It is used to do operations like INSERT, UPDATE, CREATE, ALTER, DELETE and those that do not return data. The return of the method you have used is the number of lines affected by script and only.

As what you want is to return bank lines using the SELECT, have to use another method. This will be the ExecuteReader().

Dim reader = objcmd.ExecuteReader()
While reader.Read()
    Console.WriteLine(reader.GetInt32(0))
End While

In the reader.GetInt32(Integer) is where I take the value of a numeric column returned by SELECT and manipulate somehow. In this example I printed on the console, but you can store it in a variable. The argument 0 is the index of the column.

In my example I used the GetInt32(Integer), but as you may need other types, there are other methods like GetDouble, GetGuid, GetDecimal, GetString, etc..

In the example

select Nome, Senha from users where Nome=@field1 and Senha=@field2

Nome would be index 0 and Senha the index 1.

• MSDN: "How to create and execute an SQL statement that returns rows?"