After adding to hosted mysql base how to read vbasic 2012

Question

I'm working on simple project to figure out how databases work.

I created mysql base on my host and used this code to connect to it

Private mysql_host = "myhost"
Private mysql_user = "myuser"
Private mysql_pass = "mypw"
Private mysql_db = "mydb"

Private SQLConnect As String = "Server=" + mysql_host + ";" + "User Id=" + mysql_user + ";" + "Password=" + mysql_pass + ";" + "Database=" + mysql_db
Private SQLConnection As New MySqlConnection

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    sqlConnection.ConnectionString = SQLConnect

    Try
        If sqlConnection.State = ConnectionState.Closed Then
            sqlConnection.Open()
            MsgBox("Connected")
        Else
            sqlConnection.Close()
            MsgBox("Not Connected")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

After that i used this code to add record into database.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim SQLStatement As String = "INSERT into  tCodes(Code) VALUES ('" & TextBox1.Text & "')"
        Dim cmd As New MySqlCommand
        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With
        MsgBox("Added")
    End Sub

On the other form i want to check if record exist in database. How to do that . I tried with code

Dim SQLStatement As String = "SELECT * From tCodes WHERE Code '" & TextBox1.Text & "'")

Start by not concatenating SQL string and use parameters instead ([See Little Bobby Tables](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work)). You can use a `dataAdapter` but in some cases, a `datareader` may be all you need. [This example](http://stackoverflow.com/a/23377684/1070452) uses OleDB but there is not much difference; it also uses parameters — Ňɏssa Pøngjǣrdenlarp, May 27 '14 at 14:11

score 0 · Answer 1 · answered May 27 '14 at 12:10

For read . Me using this

Private host As String = "host"                   'Host DB
Private user As String = "user"                   'User DB
Private pass As String = "pass"                   'Pass DB
Private base As String = "base"                   'Base


Private conn As String = "Database=" & base & ";Data Source=" & host & ";User Id=" & _
user & ";Password=" & pass 'Connection

Private Connection As New MySqlConnection(conn)                  'Connection
Private readData As MySqlDataReader                             'Data Reader
Private adaptsData As New MySqlDataAdapter                      'Data Adapter
Private command As New MySqlCommand                             'command 
Private ds As New DataSet                                       'DataSet

After

Public Function __select(Optinal table as String = "tCodes") As String


        Try
            Connection.Open()
            Dim query As String = "SELECT * FROM " & table
            command.CommandText = query
            command.Connection = conexiune
            adaptsData.SelectCommand = comanda
            adaptsData.Fill(ds, tabla)

            Dim newvalue As String = ds.Tables(tabla).Rows(0).Item(item)

            ds.Dispose()
            ds.Clear()
            Connection.Close()

            Return newvalue
        Catch ex As Exception
            ds.Dispose()
            ds.Clear()
            conexiune.Close()
            msgbox(ex.message)
        End Try

    End Function

score 0 · Answer 2 · answered May 27 '14 at 14:51

Dim conn As MySqlConnection
Dim sqlquery = "SELECT * FROM tCodes WHERE Code = '" + txtCode.Text + "'"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        'start query
        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()
        'see if user exists
        If myData.HasRows = 0 Then
            MsgBox("Kod je nevazeci")
            conn.Close()
        Else
            MsgBox("Kod je vazeci")
        End If

That was the solution of this problem

After adding to hosted mysql base how to read vbasic 2012

2 Answers2