Categories
Visual Basic 6.0

Login with SQL Server Database in Vb6

This is a sample login application that use in your project application system in VB6. Let’s start coding.

Create a Module copy the codes below for database connection in order to connect to the SQL Server database and put the following code inside it.

Option Explicit

'variables for ADODB
Global Connect As New ADODB.Connection

Public Sub SQLDB(adoObj As Adodc, AdoRec As String) 'for SQL Recordsource
'Connection string: workstation id=koroikadb.mssql.somee.com;packet size=4096;user id=xxxxxx;pwd=yyyyyy;data source=koroikadb.mssql.somee.com;persist security info=False;initial catalog=koroikadb
    'Loads the database and provides the database password
    adoObj.ConnectionString = "Provider=sqloledb;user id=;pwd=;Data Source=(local);persist security info=False;Initial Catalog=Tutorial;Trusted_Connection=Yes;"
    
    'Sets the command type to Table
    adoObj.CommandType = adCmdText
    
    'Loads the source table of info
    adoObj.RecordSource = AdoRec

    'refreshes database status
     adoObj.Refresh
End Sub

Sub Main()
On Error Resume Next
Call SQLDB(Form1.Adodc1, "Select * from tblSecurity ")
If Form1.Adodc1.Recordset.RecordCount = 0 Then
    Form1.Adodc1.Recordset.AddNew
    Form1.Adodc1.Recordset.Fields("USERNAME") = "admin"
    Form1.Adodc1.Recordset.Fields("PASSWORD") = "admin"
    Form1.Adodc1.Recordset.Update
End If

Form1.Show
End Sub

After creating module we need to create form to your project and double click the form to enter the code window Copy the code below and paste then save it.

Private Sub Image1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.Image1.Picture = Me.Image3.Picture
End Sub

Private Sub Image2_Click()
On Error Resume Next
If Me.Text1.Text = "" Or Me.Text2.Text = "" Then
MsgBox "Login and password should not be blank", vbInformation, "Login"
Exit Sub
End If

Call SQLDB(Me.Adodc1, "Select * from tblSecurity where USERNAME='" & Me.Text1.Text & "' and PASSWORD='" & Me.Text2.Text & "'")
If Me.Adodc1.Recordset.Fields("USERNAME") = Me.Text1.Text Then
   MsgBox "Please input correct username and password", vbCritical, "Login"
Else
    Unload Me
    Form2.Show
End If
End Sub

Private Sub Image2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.Image1.Picture = Me.Picture1.Picture
End Sub

For more information about the system. You can contact me @
Email – larrydaveemol@gmail.com
FB Account – https://www.facebook.com/larrydavelacapagemol

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Save/Retrieve Image in SQL Server Database Using VB.Net

In this program you’ll learn how to save and retrieve an image that stored in database with VB.NET and MySQL Database.

We need to create a Module copy the codes below for database connection in order to connect to the SQL Server database and put the following code inside it.

  Imports System.Data.SqlClient


Module Module1
  Public conn As SqlConnection
  Public query As String
  Public DA As SqlDataAdapter
  Public DS As DataSet
  Public cmd As SqlCommand

  Sub connect()
  conn = New SqlConnection
  conn.ConnectionString = "data source=(local); initial catalog=Tutorial;Trusted_Connection=Yes; user=; password="

  Try
  If conn.State = ConnectionState.Closed Then
  conn.Open()
  MsgBox("Connection Success")
  End If
  Catch ex As Exception
  MsgBox("Error Connection" & Err.Description)
  End Try
  End Sub
End Module

Create form to your project and double click the form to enter the code window Copy the code below and paste then save it.

 Imports System.Data.SqlClient
Imports System.IO

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
        DS = New DataSet
        query = "Select * from tblInformation"
        DA = New SqlDataAdapter(query, conn)
        DA.Fill(DS)

        DataGridView1.DataSource = DS.Tables(0)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql As String = "INSERT INTO tblInformation VALUES(@FIRSTNAME,@MIDDLENAME,@LASTNAME,@AGE,@PHOTO)"
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@FIRSTNAME", TextBox1.Text)
        cmd.Parameters.AddWithValue("@MIDDLENAME", TextBox2.Text)
        cmd.Parameters.AddWithValue("@LASTNAME", TextBox2.Text)
        cmd.Parameters.AddWithValue("@AGE", TextBox2.Text)
        Dim ms As New MemoryStream()
        PictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)
        Dim data As Byte() = ms.GetBuffer()
        Dim p As New SqlParameter("@PHOTO", SqlDbType.Image)
        p.Value = data
        cmd.Parameters.Add(p)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Data & Image has been saved", "Save", MessageBoxButtons.OK)
        LoadData()
    End Sub

    Sub LoadData()
        DS = New DataSet
        query = "Select * from tblInformation"
        DA = New SqlDataAdapter(query, conn)
        DA.Fill(DS)
        Me.DataGridView1.DataSource = DS.Tables(0)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        cmd = New SqlCommand
        cmd.Connection = conn

        query = "update tblInformation set FIRSTNAME='" & TextBox1.Text & "',MIDDLENAME='" & TextBox2.Text & "',LASTNAME='" & TextBox3.Text & "',AGE='" & TextBox4.Text & "' where ID='" & Me.TextBox5.Text & "'"
        cmd.CommandText = query
        cmd.ExecuteNonQuery()
        LoadData()
        MsgBox("Successfully Updated", MsgBoxStyle.Information, "Updated")
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim i As Integer
        i = DataGridView1.CurrentRow.Index

        Me.TextBox1.Text = DataGridView1.Item(1, i).Value
        Me.TextBox2.Text = DataGridView1.Item(2, i).Value
        Me.TextBox3.Text = DataGridView1.Item(3, i).Value
        Me.TextBox4.Text = DataGridView1.Item(4, i).Value
        Me.TextBox5.Text = DataGridView1.Item(0, i).Value
    End Sub

    
    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As  _
                 System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        cmd = New SqlCommand("select PHOTO from tblInformation where FIRSTNAME='" & _
                   DataGridView1.CurrentRow.Cells(1).Value() & "'", conn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
                PictureBox1.Image = Image.FromStream(ms, True)
            End Using
        End If

        Me.TextBox7.Text = DataGridView1.CurrentRow.Cells(0).Value()
    End Sub
    
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If MsgBox("Do you want to delete data", vbInformation + vbYesNo) = vbYes Then
            cmd = New SqlCommand
            cmd.Connection = conn

            query = "Delete tblInformation where ID='" & Me.TextBox5.Text & "'"
            cmd.CommandText = query
            cmd.ExecuteNonQuery()
            LoadData()
            MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
        End If
    End Sub

    Private Sub TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged
        Filterdata(TextBox6.Text)
    End Sub

    Sub Filterdata(ByVal valueToSearch As String)
        Dim searchQuery As String = "Select * from tblInformation where FIRSTNAME like '%" & Me.TextBox6.Text & "%' OR LASTNAME like '%" & Me.TextBox6.Text & "%' OR MIDDLENAME like '%" & Me.TextBox6.Text & "%'"

        Dim command As New SqlCommand(searchQuery, conn)
        Dim adapter As New SqlDataAdapter(command)
        Dim table As New DataTable()

        adapter.Fill(table)

        Me.DataGridView1.DataSource = table
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.PictureBox1.Image = Nothing
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
        End If
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com
FB Account – https://www.facebook.com/larrydavelacapagemol

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
VB.Net

Stored Login and out time with Registration in VB.Net & SQL Server

This is a sample login application that catch duplicate login and out in the system in VB.NET and MySQL Database. Let’s start coding.

Create form to your project and double click the form to enter the code window Copy the code below and paste then save.

 Public Class Form1

    Private Sub Logins()
        Try
            query = "SELECT * FROM tblsecurity WHERE USERNAME = '" & txtUser.Text & "' AND PASSWORD = '" & txtPwd.Text & "'"
            cmd = New SqlClient.SqlCommand(query, conn)
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read = True Then
                dr.Close()
                cmd.Dispose()
                conn.Close()
                timein()
            Else
                MsgBox("Incorrect username or password!", MsgBoxStyle.Critical, "Login")
                txtPwd.Focus()
                dr.Close()
                cmd.Dispose()
                conn.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
    End Sub

    Private Sub btnLogin_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Logins()
    End Sub

    Private Sub btncancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        If MsgBox("Are you sure you want to close?", MsgBoxStyle.YesNo, "Close Window") = MsgBoxResult.Yes Then
            End
        End If
        txtUser.Focus()
    End Sub

    Private Sub txtPwd_GotFocus1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPwd.GotFocus
        AcceptButton = btnLogin
    End Sub

    Private Sub txtUser_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtUser.GotFocus
        AcceptButton = btnLogin
    End Sub

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

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If (txtid.Text <> vbNullString And txtfn.Text <> vbNullString And _
               txtmn.Text <> vbNullString And txtln.Text <> vbNullString And _
               txtun.Text <> vbNullString And txtp.Text <> vbNullString And _
               txtrp.Text <> vbNullString) Then

            If Me.txtp.Text = Me.txtrp.Text = False Then
                MsgBox("Password didn't match!")
                txtp.Text = ""
                txtrp.Text = ""
            Else
                Dim DS As DataSet = New DataSet
                Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter

                DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblsecurity WHERE ID='" & txtid.Text & "'", conn)
                DS.Clear()
                DA.Fill(DS)

                If DS.Tables(0).Rows.Count = 0 Then
                    query = "INSERT INTO tblsecurity VALUES ('" & Me.txtid.Text & "','" & txtfn.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & _
                                txtun.Text & "','" & txtp.Text & "')"
                    cmd = New SqlClient.SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()

                    MsgBox("New Information Added", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Success")
                    Me.txtfn.Text = ""
                    Me.txtmn.Text = ""
                    Me.txtln.Text = ""
                    Me.txtun.Text = ""
                    Me.txtp.Text = ""
                    Me.txtrp.Text = ""
                Else
                    MsgBox("The ID Number entered is already found? Please check information." & vbCrLf & vbCrLf & "If you think this is an error, " & _
                    "Please inform the Administrator.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate ID Number found")
                    txtid.Focus()
                    Exit Sub
                End If

            End If
        Else
            MsgBox("Some fields are left blank. Please fill-up all information.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Blank Fields found")
            txtid.Focus()
        End If
    End Sub

    Sub timein()
        conn.Open()
        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        If DS.Tables(0).Rows.Count = 0 Then

            TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "INSERT INTO tblDTR (Userx, Datex, Timein) VALUES ('" & txtUser.Text & "', '" & Format(Now, "MM/d/yyyy") & "', '" & TextBox1.Text & "')"
            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            Dim fx As New Form2
            Me.Hide()
            fx.Show()

        Else

            dDR = DS.Tables(0).Rows(0)
            TextBox2.Text = dDR("Timein")

            If Me.TextBox2.Text = "" Then
            Else
                MsgBox("Wag kang tanga nka Login kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Login")
                txtid.Focus()
                Exit Sub
            End If
            
        End If

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Me.Label1.Text = Format(Now, "MMMM d, yyyy  h:mm:ss tt")
    End Sub
End Class
Public Class Form2
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim DS As DataSet = New DataSet
        Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
        Dim dDR As DataRow

        DA.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tblDTR WHERE Userx='" & Form1.txtUser.Text & "' and Datex='" & Format(Now, "MM/d/yyyy") & "'", conn)
        DS.Clear()
        DA.Fill(DS)

        dDR = DS.Tables(0).Rows(0)
        Form1.TextBox2.Text = dDR("Timeout").ToString

        If Form1.TextBox2.Text = "" Then
        Else
            MsgBox("Wag kang tanga nka Logout kana :D !", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error: Duplicate Logout")
            Exit Sub
        End If

            Form1.TextBox1.Text = Format(Now, "h:mm:ss tt")
            query1 = "UPDATE tblDTR SET Timeout='" & Form1.TextBox1.Text & "'"

            cmd = New SqlClient.SqlCommand(query1, conn)
            cmd.ExecuteNonQuery()
        cmd.Dispose()
        MsgBox("Logout Successfuly!", MsgBoxStyle.Information, "Logout")

    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub
End Class

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Categories
Visual Basic 6.0

VB.NET CRUD with SQL Server Database

I would like to share my basic program you’ll learn from this on how to build a CRUD application with VB.Net and SQL Server Database. This system will help you specially for beginners. It is fully working.

Features :

  • Can add/update/delete/search info.
  • Can view info.

Back-end:
SQL Server Management Studio

Download the complete source code for more information about the system. You can contact me @
Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!