Pass SQL value to a VB.Net variable?

4

I have this code that executes a 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 in a variable ( get the value of the column and pass it to a string )?

    
asked by anonymous 13.10.2017 / 01:37

2 answers

4

To bring this information use the class MySqlDataReader setting your 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 value of the column by positioning from 0 to the total number of columns, if it is two equal columns it is in SQL , it is 0 for Name and 1 to Password , and so on.

It's worth remembering that the type is very important so that you do not have problems in conversions, and the data types have to match the type that is in the columns in your return tables, the example below taken from the http://www.macoratti.net website, shows the correlative types and their respective accesses 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()   |
+------------------------------------------------------------------------------+

Available in: link

References:

13.10.2017 / 01:52
2

The method you are using to effectively execute the SQL statements is ExecuteNonQuery() . If you notice the documentation , it says that this method returns no line. It is used to do operations such as INSERT , UPDATE , CREATE , ALTER , DELETE , and those that do not return data. The method return you have used is the number of rows affected by script and only.

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

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

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

In my example I used 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 index 1.

13.10.2017 / 01:52