Programming for Everybody

Visual Basic.net: retrieve values from database in row datagridview based on cell in Same row

Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown

If e.KeyCode = Keys.Right Then

For i As Integer = 0 To DataGridView1.Rows.Count - 1

Dim constring1 As String = "data source=.;initial catalog=users;integrated security=true"

Dim query1 As String = " select id,name,country from table2 where id='" & DataGridView1.Rows(i).Cells(0).Value & "'"

Dim cn1 As SqlConnection = New SqlConnection(constring1)

Dim cmd1 As SqlCommand = New SqlCommand(query1, cn1)

cn1.Open()

If True Then

Using read1 As SqlDataReader = cmd1.ExecuteReader()

While read1.Read()

DataGridView1.Rows(i).Cells(1).Value = (read1("name"))

DataGridView1.Rows(i).Cells(2).Value = (read1("country"))

End While

End Using

End If

cn1.Close()

End If

End Sub

VB.net: filter dates from access database between two datetimepickers and display in datagridview with source code

Imports System.Data.OleDb

Public Class Form4

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\dates.accdb")

Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

d1.Format = DateTimePickerFormat.Custom

d1.CustomFormat = "MM/dd/yyyy"

d2.Format = DateTimePickerFormat.Custom

d2.CustomFormat = "MM/dd/yyyy"

DataGridView1.BackgroundColor = System.Drawing.SystemColors.Control

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd1 As New OleDbCommand(" select id,date1,username from table1", conn)

Dim da As New OleDbDataAdapter

Dim dt As New DataTable

da.SelectCommand = cmd1

dt.Clear()

da.Fill(dt)

DataGridView1.DataSource = dt

DataGridView1.Columns(1).DefaultCellStyle.Format = "dd/MM/yyyy"

DataGridView1.Columns(0).HeaderText = "ID"

DataGridView1.Columns(1).HeaderText = "Start date"

DataGridView1.Columns(2).HeaderText = "Username"

DataGridView1.EnableHeadersVisualStyles = False

With DataGridView1.ColumnHeadersDefaultCellStyle

.Font = New Font("arial", 12, FontStyle.Italic)

.BackColor = Color.Black

.ForeColor = Color.White

End With

DataGridView1.Columns(0).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Bold)

DataGridView1.Columns(1).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Italic)

DataGridView1.Columns(2).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Underline)

DataGridView1.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

DataGridView1.Columns(0).DefaultCellStyle.BackColor = Color.Yellow

DataGridView1.Columns(1).DefaultCellStyle.BackColor = Color.Brown

DataGridView1.Columns(2).DefaultCellStyle.BackColor = Color.Green

DataGridView1.Rows(0).Cells(0).Selected = False

DataGridView1.Rows(2).Cells(1).Selected = False

End Sub

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim dtdate1 As DateTime = DateTime.Parse(d1.Text)

Dim dtdate2 As DateTime = DateTime.Parse(d2.Text)

Dim cmd1 As OleDbCommand = New OleDbCommand("select id,date1,username from table1 where date1 between #" &

dtdate1.ToString("MM/dd/yyyy") & "# and #" &

dtdate2.ToString("MM/dd/yyyy") & "# order by date1 desc", conn)

Dim da As New OleDbDataAdapter

da.SelectCommand = cmd1

Dim dt As New DataTable

dt.Clear()

da.Fill(dt)

DataGridView1.DataSource = dt

conn.Close()

End Sub

End Class

How to create login form with multi users and permissions in VB.net using Sql database[with Source code]

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim conn As New SqlConnection("Data source=.;initial catalog=login3;integrated security=true")

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New SqlCommand("Select * from Admin where username='admin' and password=@password", conn)

cmd.Parameters.AddWithValue("password", TextBox1.Text)

Dim myreader As SqlDataReader = cmd.ExecuteReader

If (myreader.Read()) Then

username_v = myreader("username")

Form2.Show()

Me.Hide()

Else

MsgBox("Error Password")

End If

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

Me.Close()

End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

Dim conn As New SqlConnection("Data source=.;initial catalog=login3;integrated security=true")

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New SqlCommand("Select * from users where username=@username and password=@password", conn)

cmd.Parameters.AddWithValue("username", TextBox3.Text)

cmd.Parameters.AddWithValue("password", TextBox2.Text)

Dim myreader As SqlDataReader = cmd.ExecuteReader

If (myreader.Read()) Then

username_v = myreader("username")

If Not myreader.IsDBNull(myreader.GetOrdinal("insert")) Then

CheckBox1.Checked = myreader("insert")

End If

If Not myreader.IsDBNull(myreader.GetOrdinal("update")) Then

CheckBox2.Checked = myreader("update")

End If

If Not myreader.IsDBNull(myreader.GetOrdinal("delete")) Then

CheckBox3.Checked = myreader("delete")

End If

If CheckBox1.Checked = False Then

Form2.Button1.Visible = False

End If

If CheckBox2.Checked = False Then

Form2.Button2.Visible = False

End If

If CheckBox3.Checked = False Then

Form2.Button3.Visible = False

End If

Form2.Show()

Me.Hide()

Else

MsgBox("Error Password")

End If

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

Me.Close()

End Sub

End Class

ـــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــ

Public Class Form2

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

Label1.Text = "Welcome, " & username_v

End Sub

End Class

ـــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــ

Module Module1

Public username_v As String

End Module

Backup delete and restore sql server database in VB. net

Imports System.Data.SqlClient

Public Class Form3

Dim conn As New SqlConnection("Data source=.;Integrated security=true")

Private Sub databases()

conn.Open()

ComboBox1.Items.Clear()

Dim cmd1 As New SqlCommand("Select * from sysdatabases order by name", conn)

Dim myreader As SqlDataReader = cmd1.ExecuteReader

While myreader.Read

ComboBox1.Items.Add(myreader(0))

ComboBox1.Text = "Select database"

End While

conn.Close()

End Sub

Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load

databases()

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

If ComboBox1.Text = "Select database" Then

MessageBox.Show("Please Select database")

Return

Else

SaveFileDialog1.FileName = ComboBox1.SelectedItem

If SaveFileDialog1.ShowDialog = DialogResult.OK Then

Dim lfolder As String

lfolder = SaveFileDialog1.FileName

Dim cmd2 As New SqlCommand("BACKUP Database " & ComboBox1.Text & " To disk='" & lfolder & "'", conn)

conn.Open()

cmd2.ExecuteNonQuery()

conn.Close()

MessageBox.Show("database backed up successfully")

Else

MessageBox.Show("Please Save database")

End If

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

If ComboBox1.Text = "Select database" Then

MessageBox.Show("Please Select database")

Return

Else

Dim cmd3 As New SqlCommand("Drop database " & ComboBox1.Text & "", conn)

conn.Open()

cmd3.ExecuteNonQuery()

conn.Close()

MessageBox.Show("database deleted successfully")

databases()

End If

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

Dim dbname As String

dbname = InputBox("Input database name you want to restore", "Database Name")

Dim cmd4 As New SqlCommand("Select * from sysdatabases where name = '" & dbname & "'", conn)

conn.Open()

Dim myreader2 As SqlDataReader = cmd4.ExecuteReader

If myreader2.Read Then

MessageBox.Show("Database exists in sql server")

conn.Close()

Else

conn.Close()

If OpenFileDialog1.ShowDialog = DialogResult.OK Then

Dim cmd3 As New SqlCommand("Restore database " & dbname & " from disk='" & OpenFileDialog1.FileName & "'", conn)

conn.Open()

cmd3.ExecuteReader()

conn.Close()

databases()

MessageBox.Show("database restored successfully")

Else

MessageBox.Show("Please Select database you want to restore")

End If

End Sub

End Class

Programming VB.net: Video connect access database with datagridview, image, music and video( source code)

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

OpenFileDialog1.Filter = "Image|*.png;*.jpg;*.bmp"

If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

TextBox2.Text = OpenFileDialog1.FileName

PictureBox1.Image = Image.FromFile(TextBox2.Text)

End If

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

OpenFileDialog1.Filter = "Voice|*.mp3;*.wma;*.Mp4"

If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

TextBox3.Text = OpenFileDialog1.FileName

AxWindowsMediaPlayer1.URL = TextBox3.Text

End If

End Sub

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

'TODO: This line of code loads data into the 'DictionaryDataSet.Table1' table. You can move, or remove it, as needed.

Me.Table1TableAdapter.Fill(Me.DictionaryDataSet.Table1)

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

Table1BindingSource.AddNew()

PictureBox1.Image = Nothing

AxWindowsMediaPlayer1.currentPlaylist.clear()

End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

Table1BindingSource.EndEdit()

Table1TableAdapter.Update(DictionaryDataSet)

Me.Table1TableAdapter.Fill(Me.DictionaryDataSet.Table1)

End Sub

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(2).Value.ToString)) Then

PictureBox1.Image = Nothing

Else

PictureBox1.Image = Image.FromFile(DataGridView1.CurrentRow.Cells(2).Value.ToString)

End If

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(3).Value.ToString)) Then

AxWindowsMediaPlayer1.currentPlaylist.clear()

Else

AxWindowsMediaPlayer1.URL = DataGridView1.CurrentRow.Cells(3).Value.ToString

End If

End Sub

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

Table1TableAdapter.Search(DictionaryDataSet.Table1, TextBox1.Text)

End Sub

Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click

Table1BindingSource.RemoveCurrent()

Table1TableAdapter.Update(DictionaryDataSet)

PictureBox1.Image = Nothing

AxWindowsMediaPlayer1.currentPlaylist.clear()

End Sub

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click

Table1BindingSource.MovePrevious()

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(2).Value.ToString)) Then

PictureBox1.Image = Nothing

Else

PictureBox1.Image = Image.FromFile(DataGridView1.CurrentRow.Cells(2).Value.ToString)

End If

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(3).Value.ToString)) Then

AxWindowsMediaPlayer1.currentPlaylist.clear()

Else

AxWindowsMediaPlayer1.URL = DataGridView1.CurrentRow.Cells(3).Value.ToString

End If

End Sub

Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click

Table1BindingSource.MoveNext()

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(2).Value.ToString)) Then

PictureBox1.Image = Nothing

Else

PictureBox1.Image = Image.FromFile(DataGridView1.CurrentRow.Cells(2).Value.ToString)

End If

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(3).Value.ToString)) Then

AxWindowsMediaPlayer1.currentPlaylist.clear()

Else

AxWindowsMediaPlayer1.URL = DataGridView1.CurrentRow.Cells(3).Value.ToString

End If

End Sub

Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click

Table1BindingSource.MoveFirst()

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(2).Value.ToString)) Then

PictureBox1.Image = Nothing

Else

PictureBox1.Image = Image.FromFile(DataGridView1.CurrentRow.Cells(2).Value.ToString)

End If

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(3).Value.ToString)) Then

AxWindowsMediaPlayer1.currentPlaylist.clear()

Else

AxWindowsMediaPlayer1.URL = DataGridView1.CurrentRow.Cells(3).Value.ToString

End If

End Sub

Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click

Table1BindingSource.MoveLast()

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(2).Value.ToString)) Then

PictureBox1.Image = Nothing

Else

PictureBox1.Image = Image.FromFile(DataGridView1.CurrentRow.Cells(2).Value.ToString)

End If

If (String.IsNullOrEmpty(DataGridView1.CurrentRow.Cells(3).Value.ToString)) Then

AxWindowsMediaPlayer1.currentPlaylist.clear()

Else

AxWindowsMediaPlayer1.URL = DataGridView1.CurrentRow.Cells(3).Value.ToString

End If

End Sub

Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click

Close()

End Sub

End Class

VB. net Tutorial import data from Excel to SQL server

Imports System.Data.OleDb

Imports System.Data.SqlClient

Public Class Form15

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

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &

"Data Source=F:\names2.xlsx;Extended Properties= 'Excel 8.0;HDR=False'")

conn.Open()

Dim cmd As New OleDbCommand("Select * from [sheet4$]", conn)

Dim da As New OleDbDataAdapter

da.SelectCommand = cmd

Dim dt As New DataTable

dt.Clear()

da.Fill(dt)

DataGridView1.DataSource = dt

conn.Close()

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim conn2 As New SqlConnection("Data source=.;initial catalog=names2;integrated security=true")

For Each row As DataGridViewRow In DataGridView1.Rows

Dim cmd2 As New SqlCommand("Insert into table2(id,name1,age,telephone)Values(@id,@name1,@age,@telephone)", conn2)

cmd2.Parameters.AddWithValue("id", row.Cells("id").Value.ToString)

cmd2.Parameters.AddWithValue("name1", row.Cells("name1").Value.ToString)

cmd2.Parameters.AddWithValue("age", row.Cells("age").Value.ToString)

cmd2.Parameters.AddWithValue("telephone", row.Cells("telephone").Value.ToString)

conn2.Open()

cmd2.ExecuteNonQuery()

conn2.Close()

MessageBox.Show("All Data inserted successfully")

End Sub

End Class

How do you create register form and relate it with user form in VB.net [with source code]

Form1>>>Register form

Imports System.IO

Imports System.Data.SqlClient

Public Class Form1

Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged

If CheckBox1.Checked = True Then

password.UseSystemPasswordChar = False

Else

password.UseSystemPasswordChar = True

End If

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

OpenFileDialog1.ShowDialog()

image_path.Text = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\" & Path.GetFileName(OpenFileDialog1.FileName)

PictureBox1.Image = Image.FromFile(image_path.Text)

End Sub

Private Sub lastname_KeyDown(sender As Object, e As KeyEventArgs) Handles lastname.KeyDown

If e.KeyCode = Keys.Enter Then

username.Text = firstname.Text + lastname.Text

End If

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

If username.Text = "" Then

MessageBox.Show("Please enter username")

Return

End If

If m1.Checked = False And f1.Checked = False Then

MessageBox.Show("Please select gender")

Return

End If

Dim conn As New SqlConnection("Data source=.;initial catalog=register2;integrated security=true")

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New SqlCommand("Select username from table1 where username=@username", conn)

cmd.Parameters.AddWithValue("username", username.Text)

Dim myreader As SqlDataReader = cmd.ExecuteReader

If (myreader.Read()) Then

MessageBox.Show("Username inserted before")

conn.Close()

Return

Else

conn.Close()

Dim cmd2 As New SqlCommand("Insert into table1(firstname,lastname,username,password,phone,date_birth,gender,image1)Values(@firstname,@lastname,@username,@password,@phone,@date_birth,@gender,@image1)", conn)

cmd2.Parameters.AddWithValue("firstname", firstname.Text)

cmd2.Parameters.AddWithValue("lastname", lastname.Text)

cmd2.Parameters.AddWithValue("username", username.Text)

cmd2.Parameters.AddWithValue("password", password.Text)

cmd2.Parameters.AddWithValue("phone", phone.Text)

cmd2.Parameters.AddWithValue("date_birth", date_birth.Text)

Dim gender_v As Boolean

If m1.Checked = True Then

gender_v = 1

End If

If f1.Checked = True Then

gender_v = 0

End If

cmd2.Parameters.AddWithValue("gender", gender_v)

cmd2.Parameters.AddWithValue("image1", image_path.Text)

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

cmd2.ExecuteNonQuery()

conn.Close()

username_v = username.Text

Form2.Show()

End If

End Sub

end class

Form2>>>user form

Imports System.Data.SqlClient

Public Class Form2

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

username.Text = username_v

Dim conn As New SqlConnection("Data source=.;initial catalog=register2;integrated security=true")

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New SqlCommand("Select * from table1 where username=@username", conn)

cmd.Parameters.AddWithValue("username", username.Text)

Dim myreader As SqlDataReader = cmd.ExecuteReader

If (myreader.Read()) Then

firstname.Text = myreader("firstname")

lastname.Text = myreader("lastname")

phone.Text = myreader("phone")

password.Text = myreader("password")

Dim gender_v As Boolean = myreader("gender")

If gender_v = True Then

m1.Checked = True

Else

f1.Checked = True

End If

image_path.Text = myreader("image1")

PictureBox1.Image = Image.FromFile(image_path.Text)

date_birth.Text = myreader("date_birth")

conn.Close()

End If

End Sub

End Class