vb.netMSSQL

//create project
//create db

//add module

1.module1.vb

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
	Public DR As SqlDataReader



    Sub connect()

        conn = New SqlConnection
        conn.ConnectionString = "data source=APEPURIL\SQLEXPRESS;initial catalog=test;user=paulpaul003;password=paul091725"

        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                MsgBox("connected")
            End If
        Catch ex As Exception

            MsgBox("connection closed" & Err.Description)

        End Try

    End Sub

End Module



2. SQLSERVER = right click Security/login , Server roles = check sysadmin


3. form1.vb

Imports System.Data.SqlClient
Public Class Form1

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

        displayData()


    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

        cmd = New SqlCommand
        cmd.Connection = conn

        Query = "INSERT INTO tbl_info VALUES('" & txtFirstname.Text & "', '" & txtLastname.Text & "', '" & txtAge.Text & "') "
        cmd.CommandText = Query
        cmd.ExecuteNonQuery()

        displayData()



    End Sub

    Sub displayData()
        DS = New DataSet
        Query = "SELECT * FROM tbl_info"
        DA = New SqlDataAdapter(Query, conn)
        DA.Fill(DS)

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

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        cmd = New SqlCommand
        cmd.Connection = conn

        Query = "UPDATE tbl_info SET firstname='" & txtFirstname.Text & "', lastname='" & txtLastname.Text & "', age='" & txtAge.Text & "' WHERE id='" & lblId.Text & "'"
        cmd.CommandText = Query
        cmd.ExecuteNonQuery()

        displayData()

    End Sub

    Private Sub dgvInfo_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvInfo.CellClick

        Dim i As Integer
        i = dgvInfo.CurrentRow.Index

        lblId.Text = dgvInfo(0, i).Value
        txtFirstname.Text = dgvInfo(1, i).Value
        txtLastname.Text = dgvInfo(2, i).Value
        txtAge.Text = dgvInfo(3, i).Value



    End Sub



    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

        If MsgBox("Delete Confirmation", vbInformation + vbYesNo) = vbYes Then

            cmd = New SqlCommand
            cmd.Connection = conn

            Query = "DELETE FROM tbl_info WHERE id='" & lblId.Text & "'"
            cmd.CommandText = Query
            cmd.ExecuteNonQuery()

            displayData()

        End If

    End Sub
End Class


//Crystal Report
4. Project/projectname properties/compile/advance compile/tager framework = .net framework 4

	add new item Reporting/Crystal Report

	Create new connection/ OLE DB(ADO)/ provider for SQL SERVER

	form2.show()

//form2
5. create form2.vb

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data.SqlClient
Public Class Form2


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

        Dim reportPath As String = "C:\testproject\testproject\CrystalReport1.rpt"

        Dim report As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        DS = New DataSet

        Query = "SELECT * FROM tbl_info WHERE id='" & Form1.lblId.Text & "'"

        DA = New SqlDataAdapter(Query, conn)

        DA.Fill(DS)

        report.Load(reportPath)
        report.SetDataSource(DS.Tables(0))

        CrystalReportViewer1.ReportSource = report
        CrystalReportViewer1.Refresh()




    End Sub
End Class

//app.config
6.
<startup useLegacyV2RuntimeActivationPolicy="true">


7.login.vb

//create new tbl_users from sql server

Imports System.Data.SqlClient

Public Class login

    Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click


        DS = New DataSet



        cmd = New SqlCommand
        cmd.Connection = conn

        Query = "SELECT * FROM tbl_users WHERE username= '" & txtUsername.Text & "' and password= '" & md5(txtPassword.Text) & "'"

        cmd.CommandText = Query

        DR = cmd.ExecuteReader()


        If DR.HasRows Then

            MsgBox("success")
            Form1.Show()
            Me.Hide()
        Else
            MsgBox("error")

        End If





    End Sub

    Private Function md5(sPassword As String) As String
        Dim x As New System.Security.Cryptography.MD5CryptoServiceProvider()
        Dim bs As Byte() = System.Text.Encoding.UTF8.GetBytes(sPassword)
        bs = x.ComputeHash(bs)
        Dim s As New System.Text.StringBuilder()
        For Each b As Byte In bs
            s.Append(b.ToString("x2").ToLower())
        Next
        Return s.ToString()
    End Function

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

    Private Sub btnRegister_Click(sender As Object, e As EventArgs) Handles btnRegister.Click
        register.show()
        Me.Hide()

    End Sub
End Class


8. register user

Imports System.Data.SqlClient


Public Class register


    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        login.Show()
        Me.Hide()


    End Sub

    Private Sub btnOk_Click(sender As Object, e As EventArgs) Handles btnOk.Click

        If txtpassword.Text = txtRepassword.Text Then

            cmd = New SqlCommand
            cmd.Connection = conn



            Query = "INSERT INTO tbl_users VALUES('" & txtUsername.Text & "', '" & md5(txtpassword.Text) & "')"
            cmd.CommandText = Query
            cmd.ExecuteNonQuery()

            MsgBox("success register")
            login.Show()
            Me.Hide()


        End If


    End Sub

    Private Function md5(sPassword As String) As String
        Dim x As New System.Security.Cryptography.MD5CryptoServiceProvider()
        Dim bs As Byte() = System.Text.Encoding.UTF8.GetBytes(sPassword)
        bs = x.ComputeHash(bs)
        Dim s As New System.Text.StringBuilder()
        For Each b As Byte In bs
            s.Append(b.ToString("x2").ToLower())
        Next
        Return s.ToString()
    End Function

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

    End Sub
End Class