W
wirejp
Guest
Hello Everyone,
I am a beginner in VB.Net programming. I will provide a brief overview of my database setup and connection to Visual Studio. I am using a MySql Database which is connected to Visual Studio 2017 Professional (Community Edition) software with the project being written in VB.net. I have installed the following extensions: - MySQL for Visual Studio 1.2.7 and MySQL ConnectorNet 8.0.11. I ensured that the DataSource is MySQL Database so that Visual Studio can connect to the MySQL database. I have managed to load/view the data, from the MySql database, into the DataGridView. On the Form1.vb [Design], a window (at the bottom) show icons for (i) my MySql Database and (ii) BindingSource1.
There are 5 radio buttons on the Form: (i)Today (ii) Tomorrow (iii) Yesterday (iv) Next 7 Days and (v) Last 7 Days which will allow an user to click on a specific radio button and retrieve the data for that specific date from the DataGridView. I have tried a few different methods to achieve this goal but they are not working. If I click on either radio button "Today" or "Tomorrow", the data in the DataGridView disappears and the DataGridView is blank. The other radio buttons do not elicit a result and nothing happens. I have attached my code below (forgive me for the code length, but I added the code to show the databinding to the datatable. The code with the problem is the case selection of the radio buttons at the bottom): -
Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports MySql.Data
Imports System.Windows.Forms
Imports System.Drawing
Imports System
Public Class Form1
Dim connection As New MySqlConnection("server=localhost; Port=3306; database=mydatabase; username=root; Password=mypassword")
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
Private connStr As String
Public Class Form1
Inherits System.Windows.Forms.Form
Private DataGridView As New DataGridView()
Private BindingSource As New BindingSource()
Private dataAdapter As New SqlDataAdapter()
Private WithEvents reloadButton As New Button()
Private WithEvents submitButton As New Button()
<STAThreadAttribute()>
Public Shared Sub Main()
Application.Run(New Form1())
End Sub
' Initialize the form.
Public Sub New()
Me.DataGridView.Dock = DockStyle.Fill
Me.reloadButton.Text = "reload"
Me.submitButton.Text = "submit"
Dim panel As New FlowLayoutPanel()
panel.Dock = DockStyle.Top
panel.AutoSize = True
panel.Controls.AddRange(New Control() {Me.reloadButton, Me.submitButton})
Me.Controls.AddRange(New Control() {Me.DataGridView, panel})
Me.Text = "DataGridView databinding and updating demo"
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load
' Bind the DataGridView to the BindingSource
' and load the data from the database.
GetData("select * from Employees")
Me.DataGridView.DataSource = Me.BindingSource
End Sub
Private Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles submitButton.Click
' Update the database with the user's changes.
Me.dataAdapter.Update(CType(Me.BindingSource.DataSource, DataTable))
End Sub
Private Sub GetData(ByVal selectCommand As String)
Try
' Specify a connection string. Replace the given value with a
' valid connection string for a mydatabase SQL Server sample
' database accessible to your system.
Dim connectionString As String =
"Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=mydatabase;Data Source=localhost"
' Create a new data adapter based on the specified query.
Me.dataAdapter = New SqlDataAdapter(selectCommand, connectionString)
' Create a command builder to generate SQL update, insert, and
' delete commands based on selectCommand. These are used to
' update the database.
Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapter)
' Populate a new data table and bind it to the BindingSource.
Dim table As New DataTable()
table.Locale = System.Globalization.CultureInfo.InvariantCulture
Me.dataAdapter.Fill(table)
Me.BindingSource.DataSource = table
' Resize the DataGridView columns to fit the newly loaded content.
Me.DataGridView.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
Catch ex As SqlException
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.")
End Try
End Sub
End Class
Private Sub SubmitButton_Click(sender As Object, e As EventArgs) Handles SubmitButton.Click
Dim dtEmployee As New DataTable()
Dim command As New MySqlCommand("SELECT * FROM Employees WHERE EmployeeDate BETWEEN @d1 And @d2", connection)
command.Parameters.Add("@d1", MySqlDbType.Date).Value = DateTimePicker1.Value
command.Parameters.Add("@d2", MySqlDbType.Date).Value = DateTimePicker2.Value
Dim adapter As New MySqlDataAdapter(command)
adapter.Fill(dtEmployee)
EmployeeDataGridView.DataSource = dtEmployee
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
EmployeeDataGridView.DataSource = GetEmployeeList()
End Sub
Private Sub ReloadButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ReloadButton.Click
Me.BindingSource1.DataSource = GetEmployeeList()
Me.EmployeeDataGridView.DataSource = Me.BindingSource1
End Sub
Private Function GetEmployeeList() As DataTable
Dim dtEmployee As New DataTable
Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
Using conn As New MySqlConnection(connString)
Using cmd As New MySqlCommand("SELECT * FROM Employees", conn)
conn.Open()
Dim reader As MySqlDataReader = cmd.ExecuteReader()
dtEmployee.Load(reader)
End Using
End Using
Return dtEmployee
End Function
Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged
Dim bs As New BindingSource
Dim dataEmployee As New DataTable
Dim MySqlCommand As New MySqlCommand
Dim thisDay As DateTime = DateTime.Today
Dim Tomorrow As DateTime = Today.AddDays(+1)
Dim Yesterday As DateTime = Today.AddDays(-1)
Dim NextSevenDays As DateTime = Today.AddDays(+7)
Dim LastSevenDays As DateTime = Today.AddDays(-7)
Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
bs.DataSource = dataEmployee
EmployeeDataGridView.DataSource = bs
MysqlConn = New MySqlConnection
Select Case True
Case rdoToday.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO thisDay"
Case rdoTomorrow.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Tomorrow"
Case rdoYesterday.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Yesterday"
Case rdoNext7days.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO NexttSevenDays"
Case rdoLast7days.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO LastSevenDays"
Case Else
MySqlCommand.CommandText = "SELECT * FROM Employees"
End Select
End Sub
End Class
Continue reading...
I am a beginner in VB.Net programming. I will provide a brief overview of my database setup and connection to Visual Studio. I am using a MySql Database which is connected to Visual Studio 2017 Professional (Community Edition) software with the project being written in VB.net. I have installed the following extensions: - MySQL for Visual Studio 1.2.7 and MySQL ConnectorNet 8.0.11. I ensured that the DataSource is MySQL Database so that Visual Studio can connect to the MySQL database. I have managed to load/view the data, from the MySql database, into the DataGridView. On the Form1.vb [Design], a window (at the bottom) show icons for (i) my MySql Database and (ii) BindingSource1.
There are 5 radio buttons on the Form: (i)Today (ii) Tomorrow (iii) Yesterday (iv) Next 7 Days and (v) Last 7 Days which will allow an user to click on a specific radio button and retrieve the data for that specific date from the DataGridView. I have tried a few different methods to achieve this goal but they are not working. If I click on either radio button "Today" or "Tomorrow", the data in the DataGridView disappears and the DataGridView is blank. The other radio buttons do not elicit a result and nothing happens. I have attached my code below (forgive me for the code length, but I added the code to show the databinding to the datatable. The code with the problem is the case selection of the radio buttons at the bottom): -
Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports MySql.Data
Imports System.Windows.Forms
Imports System.Drawing
Imports System
Public Class Form1
Dim connection As New MySqlConnection("server=localhost; Port=3306; database=mydatabase; username=root; Password=mypassword")
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
Private connStr As String
Public Class Form1
Inherits System.Windows.Forms.Form
Private DataGridView As New DataGridView()
Private BindingSource As New BindingSource()
Private dataAdapter As New SqlDataAdapter()
Private WithEvents reloadButton As New Button()
Private WithEvents submitButton As New Button()
<STAThreadAttribute()>
Public Shared Sub Main()
Application.Run(New Form1())
End Sub
' Initialize the form.
Public Sub New()
Me.DataGridView.Dock = DockStyle.Fill
Me.reloadButton.Text = "reload"
Me.submitButton.Text = "submit"
Dim panel As New FlowLayoutPanel()
panel.Dock = DockStyle.Top
panel.AutoSize = True
panel.Controls.AddRange(New Control() {Me.reloadButton, Me.submitButton})
Me.Controls.AddRange(New Control() {Me.DataGridView, panel})
Me.Text = "DataGridView databinding and updating demo"
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load
' Bind the DataGridView to the BindingSource
' and load the data from the database.
GetData("select * from Employees")
Me.DataGridView.DataSource = Me.BindingSource
End Sub
Private Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles submitButton.Click
' Update the database with the user's changes.
Me.dataAdapter.Update(CType(Me.BindingSource.DataSource, DataTable))
End Sub
Private Sub GetData(ByVal selectCommand As String)
Try
' Specify a connection string. Replace the given value with a
' valid connection string for a mydatabase SQL Server sample
' database accessible to your system.
Dim connectionString As String =
"Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=mydatabase;Data Source=localhost"
' Create a new data adapter based on the specified query.
Me.dataAdapter = New SqlDataAdapter(selectCommand, connectionString)
' Create a command builder to generate SQL update, insert, and
' delete commands based on selectCommand. These are used to
' update the database.
Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapter)
' Populate a new data table and bind it to the BindingSource.
Dim table As New DataTable()
table.Locale = System.Globalization.CultureInfo.InvariantCulture
Me.dataAdapter.Fill(table)
Me.BindingSource.DataSource = table
' Resize the DataGridView columns to fit the newly loaded content.
Me.DataGridView.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
Catch ex As SqlException
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.")
End Try
End Sub
End Class
Private Sub SubmitButton_Click(sender As Object, e As EventArgs) Handles SubmitButton.Click
Dim dtEmployee As New DataTable()
Dim command As New MySqlCommand("SELECT * FROM Employees WHERE EmployeeDate BETWEEN @d1 And @d2", connection)
command.Parameters.Add("@d1", MySqlDbType.Date).Value = DateTimePicker1.Value
command.Parameters.Add("@d2", MySqlDbType.Date).Value = DateTimePicker2.Value
Dim adapter As New MySqlDataAdapter(command)
adapter.Fill(dtEmployee)
EmployeeDataGridView.DataSource = dtEmployee
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
EmployeeDataGridView.DataSource = GetEmployeeList()
End Sub
Private Sub ReloadButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ReloadButton.Click
Me.BindingSource1.DataSource = GetEmployeeList()
Me.EmployeeDataGridView.DataSource = Me.BindingSource1
End Sub
Private Function GetEmployeeList() As DataTable
Dim dtEmployee As New DataTable
Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
Using conn As New MySqlConnection(connString)
Using cmd As New MySqlCommand("SELECT * FROM Employees", conn)
conn.Open()
Dim reader As MySqlDataReader = cmd.ExecuteReader()
dtEmployee.Load(reader)
End Using
End Using
Return dtEmployee
End Function
Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged
Dim bs As New BindingSource
Dim dataEmployee As New DataTable
Dim MySqlCommand As New MySqlCommand
Dim thisDay As DateTime = DateTime.Today
Dim Tomorrow As DateTime = Today.AddDays(+1)
Dim Yesterday As DateTime = Today.AddDays(-1)
Dim NextSevenDays As DateTime = Today.AddDays(+7)
Dim LastSevenDays As DateTime = Today.AddDays(-7)
Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
bs.DataSource = dataEmployee
EmployeeDataGridView.DataSource = bs
MysqlConn = New MySqlConnection
Select Case True
Case rdoToday.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO thisDay"
Case rdoTomorrow.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Tomorrow"
Case rdoYesterday.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Yesterday"
Case rdoNext7days.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO NexttSevenDays"
Case rdoLast7days.Checked
MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO LastSevenDays"
Case Else
MySqlCommand.CommandText = "SELECT * FROM Employees"
End Select
End Sub
End Class
Continue reading...