Member Avatar for benhowdle89

I'm developing a very simple VB app in Visual Basic 2008, where i have a textbox being dynamically updated with a value every second, i need this value to be inserted into a mySQL DB, i have made the connection ok but not too sure how to structure the VB code around the SQL Query??

Here's my code so far...

Public Class MainForm

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        TextBox1.Text = EthernetIPforSLCMicro1.ReadAny("F18:0")
    End Sub

    Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As New Odbc.OdbcConnection
        Dim connString As String = "DRIVER={MySQL ODBC 5.1 Driver};" _
                                & "SERVER=localhost;" _
                                & "PORT=3306;" _
                                & "DATABASE=test;" _
                                & "UID=root;PWD=; OPTION =3;"

        If conn.State = ConnectionState.Open Then conn.Close()

        With conn
            .ConnectionString = connString
            .Open()
        End With
    End Sub
End Class

Any help, very much appreciated!!!

  • 2 Contributors
  • 1 Reply
  • 125 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by sergb

Dani AI

Generated

Short version: do not run the insert on the UI thread every tick. is right to use parameters, but for a per-second sample stream it's better to buffer values on the UI thread and flush them in a background worker (or thread-pool timer) in small batches. Also prefer the MySQL Connector/NET provider (MySql.Data) instead of ODBC for simpler, faster code and better tooling (see the Connector/NET docs).

The pattern below shows a safe, VB.NET-friendly producer/consumer: the UI enqueues each reading, a background timer dequeues a batch and writes it with a prepared, parameterized statement inside a transaction. Replace GetSensorReading() with your existing read call and set connectionString appropriately.

' Requires MySql.Data (Connector/NET)
Imports MySql.Data.MySqlClient
Imports System.Threading

Private readings As New Queue(Of String)()
Private readingsLock As New Object()
Private flushTimer As Timer
Private connectionString As String = "YOUR_MYSQL_CONNECTION_STRING"

' start flush timer on load
flushTimer = New Timer(AddressOf FlushCallback, Nothing, 2000, 2000)

' called from UI timer tick: quick enqueue only
SyncLock readingsLock
    readings.Enqueue(GetSensorReading())
End SyncLock

' background flush (runs on thread-pool thread)
Private Sub FlushCallback(state As Object)
    Dim batch As New List(Of String)()
    SyncLock readingsLock
        While readings.Count > 0 AndAlso batch.Count < 100
            batch.Add(readings.Dequeue())
        End While
    End SyncLock
    If batch.Count = 0 Then Return

    Try
        Using cn As New MySqlConnection(connectionString)
            cn.Open()
            Using tx As MySqlTransaction = cn.BeginTransaction()
                Using cmd As New MySqlCommand("INSERT INTO your_table (val, ts) VALUES (@v, NOW())", cn)
                    cmd.Transaction = tx
                    cmd.Parameters.Add("@v", MySqlDbType.VarChar)
                    cmd.Prepare()
                    For Each v In batch
                        cmd.Parameters("@v").Value = v
                        cmd.ExecuteNonQuery()
                    Next
                End Using
                tx.Commit()
            End Using
        End Using
    Catch ex As Exception
        ' log and consider retry/backoff
    End Try
End Sub

Notes and troubleshooting:

  • Keep DB work off the UI thread to avoid freezes. Enqueue quickly and return.
  • Reuse prepared statements and wrap batch inserts in a transaction for throughput.
  • Tune batch size and flush interval to match your throughput and latency needs.
  • If you plan many rows/sec, consider MySqlBulkLoader or LOAD DATA INFILE for best performance.
  • Install the MySQL Connector/NET package and refer to the docs for provider-specific details: Connector/NET documentation.

This approach ties back to 's parameterized-query suggestion while adding thread-safety, batching, and a connector that simplifies the implementation for a steady stream of readings.

Member Avatar for sergb

I am not good with VB.NET, so please fix any errors you might encounter. Also, substitute your SQL statement and connection string.

'Declare variables
Dim connString as String
Dim conn as OdbcConnection
Dim cmd as OdbcCommand
Dim prm as OdbcParameter

Private Sub MainForm_Load(ByVal sender as Object, ByVal e as EventArgs)
Handles MyBase.Load
    connString = "[your_connection_string]"
    conn = New OdbcConnection(connString)
    conn.Open()
    cmd = conn.CreateCommand()
    cmd.CommandText = "insert into table1 (column1) values (?)"
    prm = cmd.CreateParameter()
    prm.Type = OdbcType.VarChar   'set correct type here
    prm.Size = 10                 'set correct size here
End Sub

Private Sub MainForm_Unload(ByVal sender as Object, ByVal e as EventArgs)
Handles MyBase.Unload
    cmd.Dispose()
    conn.Close()
    conn.Dispose()
End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Timer1.Tick
    Dim s as String
    s = EthernetIPforSLCMicro1.ReadAny("F18:0")
    TextBox1.Text = s
    
    'Set parameter value and execute the command
    prm.Value = s
    cmd.ExecuteNonQuery()
End Sub
Edited by sergb because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.