Saturday, September 24, 2011

It's a simple insert code using php, mysql and wamp

1. First you need to create user directory inside the C:\wamp64\www.

2. Second you need to create connect.php file inside the C:\wamp64\www\user directory for your connection to your database and paste this code below:

<?php
$dbServer="localhost";
$dbUser="root";
$dbUserPwd="";
$dbName="humanresource";

$dbLink =mysql_connect($dbServer,$dbUser,$dbUserPwd) or die(mysql_error());
mysql_select_db($dbName,$dbLink);
?>


3. Third you need to create index.php file inside the C:\wamp64\www\user directory for your form and insert code. Paste this code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Insert record</title>
</head>
<body>
<form name="form1" method="post" action="index.php">
<table width="296" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td>Name:</td>
<td><input name="name" type="text" id="name" size="20" maxlength="10"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Address</td>
<td><input name="address" type="text" id="address" size="20" maxlength="10"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><input name="submit" type="submit" id="submit" value="Save"></td>
<td>
<?php
include("connect.php");
if(isset($_POST['save']))
{
$name=$_POST['name'];
$address=$_POST['address'];
if($name=="" || $address=="")
{
echo'<font style="font-family:Tahoma;font-size:11px;color:#990000">&nbsp;&nbsp;Please input all fields</font>';
}
else
{     
$result = "INSERT INTO `tbl_test` (`name`, `address`) VALUES ('$name', '$address')" ;
$rs2 = mysql_query($result) or die("Invalid Query <br>$result");
echo'<font style="font-family:Tahoma;font-size:11px;color:#990000">&nbsp;&nbsp;Successfully inserted</font>';
}
}
?>
</td>
</tr>
</tbody>
</table>
</form>
</body>
</html>

4. Now you need to access http://localhost/user/ or it's depen on your wamp setting to your browse.




5. Now enter your name and address then clicksave button. Now open your Mysql Database to check if your data was saved.

Tuesday, August 30, 2011

GLOW CODE is a code for vb.net buttons


1. Copy paste the code to your form.

2. Find the OK.MouseLeave, Cancel.MouseLeave then replace it with the button names of your form you want to glow.


#Region "GLOW CODE"

    Private Sub MouseEnterEvent(ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles OK.MouseLeave, Cancel.MouseLeave

        ' A simple event handler that fires when the user's mouse arrow rolls
        ' over any of the buttons.  It passes the button's identity to the Glow sub.

        Dim ctrl As Control = CType(sender, Control)
        Glow(ctrl, True)

    End Sub

    Private Sub MouseLeaveEvent(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles OK.MouseLeave, Cancel.MouseLeave

        ' A simple event handler that fires when the user's mouse arrow leaves the
        ' area of any of the buttons.  It passes the button's identity to the Glow sub.

        Dim ctrl As Control = CType(sender, Control)
        Glow(ctrl, False)

    End Sub

    Private Sub Glow(ByVal ctrl As Control, ByVal Hovering As Boolean)

        Dim Sb_R, Sb_G, Sb_B As Int16 '           <--- RGB values for the starting backcolor
        Dim Sf_R, Sf_G, Sf_B As Int16 '           <--- RGB values for the starting forecolor
        Dim Eb_R, Eb_G, Eb_B As Int16 '           <--- RGB values for the ending backcolor
        Dim Ef_R, Ef_G, Ef_B As Int16 '           <--- RGB values for the ending forecolor

        Select Case Hovering  ' True for MouseEnter, False for MouseLeave
            Case True
                Sb_R = SystemColors.Control.R           ' Assign the variables the
                Sb_G = SystemColors.Control.G           ' appropriate values based
                Sb_B = SystemColors.Control.B           ' on system-defined colors
                '                                         for control and control
                Sf_R = SystemColors.ControlText.R       ' text.
                Sf_G = SystemColors.ControlText.G
                Sf_B = SystemColors.ControlText.B

                Eb_R = SystemColors.ActiveCaption.R     ' Assign the variables the
                Eb_G = SystemColors.ActiveCaption.G     ' appropriate values based
                Eb_B = SystemColors.ActiveCaption.B     ' on system-defined colors
                '                                         for the title bar and the
                Ef_R = SystemColors.ActiveCaptionText.R ' title bar's text.
                Ef_G = SystemColors.ActiveCaptionText.G
                Ef_B = SystemColors.ActiveCaptionText.B
            Case False
                Sb_R = SystemColors.ActiveCaption.R     ' Assign the variables the
                Sb_G = SystemColors.ActiveCaption.G     ' appropriate values based
                Sb_B = SystemColors.ActiveCaption.B     ' on system-defined colors
                '                                         for the title bar and the
                Sf_R = SystemColors.ActiveCaptionText.R ' title bar's text.
                Sf_G = SystemColors.ActiveCaptionText.G
                Sf_B = SystemColors.ActiveCaptionText.B

                Eb_R = SystemColors.Control.R           ' Assign the variables the
                Eb_G = SystemColors.Control.G           ' appropriate values based
                Eb_B = SystemColors.Control.B           ' on system-defined colors
                '                                         for control and control
                Ef_R = SystemColors.ControlText.R       ' text.
                Ef_G = SystemColors.ControlText.G
                Ef_B = SystemColors.ControlText.B
        End Select

        Dim b_RIncrement As Int16 = Round(((Eb_R - Sb_R) / 16), 0)      ' Find the increments that
        Dim b_GIncrement As Int16 = Round(((Eb_G - Sb_G) / 16), 0)      ' the RGB values will take;
        Dim b_BIncrement As Int16 = Round(((Eb_B - Sb_B) / 16), 0)      ' 1/16th of the difference
        '                                                                 between the start and end
        Dim f_RIncrement As Int16 = Round(((Ef_R - Sf_R) / 16), 0)      ' values, rounded to the
        Dim f_GIncrement As Int16 = Round(((Ef_G - Sf_G) / 16), 0)      ' nearest integer.
        Dim f_BIncrement As Int16 = Round(((Ef_B - Sf_B) / 16), 0)

        Dim bR As Int16 = Sb_R ' \
        Dim bG As Int16 = Sb_G '  }--  the RGB values for the backcolor as it changes.
        Dim bB As Int16 = Sb_B ' /

        Dim fR As Int16 = Sf_R ' \
        Dim fG As Int16 = Sf_G '  }--  the RGB values for the forecolor as it changes.
        Dim fB As Int16 = Sf_B ' /

        Dim count As Int16
        For count = 0 To 14
            bR += b_RIncrement          ' Add the appropriate increments to the
            bG += b_GIncrement          ' RGB values.  The result is a nifty
            bB += b_BIncrement          ' "cross-fade" effect.
            fR += f_RIncrement
            fG += f_GIncrement
            fB += f_BIncrement

            ctrl.BackColor = Color.FromArgb(bR, bG, bB)   ' Repaint the button using the
            ctrl.ForeColor = Color.FromArgb(fR, fG, fB)   ' current RGB values, and refresh
            ctrl.Refresh()                                ' the button.

            Threading.Thread.Sleep(30 - (count * 2))      ' Wait a certain number of milliseconds,
            '                                               which is a factor of the current count.
            '                                               I tweaked these numbers to get a decent
            '                                               effect; feel free to adjust these
            '                                               numbers to achieve an effect you like.
        Next
        Select Case Hovering
            Case True
                ctrl.BackColor = SystemColors.HighlightText          ' Finish the effect by painting
                ctrl.ForeColor = SystemColors.Highlight ' the control with it's final
            Case False                                              ' look, depending on whether
                ctrl.BackColor = SystemColors.Highlight               ' we're fading in or out.
                ctrl.ForeColor = SystemColors.HighlightText
        End Select
    End Sub
#End Region

Wednesday, July 20, 2011

Get ip code in vb and asp .net

vb.net


Dim sam As String 'System.Net.IPAddress
        'Dim comp As Object
        'Dim rtn As Boolean

        Dim sam1 As String
        With System.Net.Dns.GetHostEntry(System.Net.Dns.GetHostName())
            sam = .AddressList(0).ToString
            MsgBox(sam)
            sam1 = sam.ToString
        End With

asp.net

  Dim a As String
        Dim i As System.Net.IPHostEntry
        a = System.Net.Dns.GetHostName
        i = System.Net.Dns.GetHostEntry(a)
        Response.Write("The Name of Host is :" + i.HostName)
        Response.Write("----")
        For Each b As System.Net.IPAddress In i.AddressList
            Response.Write("The Ip Address of Host is" + b.ToString)
        Next

Tuesday, July 5, 2011

SQL INNER JOIN

strSQL = "SELECT tbl_stud.id, tbl_stud.name, tbl_stud.address, tbl_course.course FROM tbl_stud INNER JOIN tbl_course ON tbl_stud.id = tbl_course.id"
 DataGridView1.DataSource = objc.GetDataTable(strSQL, CommandType.Text)


SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Multiple datatables in crystal report


 Dataset2 has two datatables - tbl_stud and tbl_course


Dim daT1, daT2 As OleDbDataAdapter
Dim DataSet2 As DataSet
connection_open()

        strSQL = "SELECT * FROM tbl_stud"
        daT1 = New OleDbDataAdapter(strSQL, con)
        DataSet2 = New DataSet
        daT1.Fill(DataSet2, "tbl_stud")

        strSQL = "SELECT * FROM tbl_course"
        daT2 = New OleDbDataAdapter(strSQL, con)
        daT2.Fill(DataSet2, "tbl_course")

        Dim rpt As New CrystalReport1
        rpt.SetDataSource(DataSet2)
        Form5.CrystalReportViewer1.ReportSource = rpt

connection_close()

Monday, June 27, 2011

It is a simple code for asp.net that you can use for your login page


Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page
    Dim cn As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim dr As OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            With cn
                If .State = Data.ConnectionState.Open Then .Close()
                .ConnectionString = "Provider=microsoft.jet.oledb.4.0;data source=" & Server.MapPath("data\asp.mdb")

                .Open()
                'MsgBox("connected")
            End With
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click

        If txtUsername.Text = "" Or txtPassword.Text = "" Then Exit Sub

        With cmd
            .CommandText = "Select * from tbluser Where username = '" & Trim(txtUsername.Text) & "' and password = '" & Trim(txtPassword.Text) & "' "
            .Connection = cn
            dr = .ExecuteReader
        End With


        If dr.HasRows Then
            'MsgBox("Welcome", MsgBoxStyle.Information, "Welcome")
            Session("name") = txtUsername.Text
            Response.Redirect("default2.aspx")
        Else
            'MsgBox("Access Denied", MsgBoxStyle.Exclamation, "Error")
            Label1.Text = ("Pangit ka! " & txtUsername.Text)
        End If
    End Sub

    Protected Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        txtPassword.Text = Nothing
        txtUsername.Text = Nothing

    End Sub
End Class

Wednesday, June 15, 2011

mysql connection strings in vb.net

imports
Option Explicit On
Imports MySql.Data.MySqlClient
Imports System.Data


connnection string
Public con As String = "Server=localhost;Database=dbstud;user id=root;password="

Check all textboxes in form if they if they are empty(vb.net)

Public Field_Check As New TextHandling

Public Class TextHandling
    Function Empty_Checks(ByRef frm As Form) As Boolean
        Dim Form_Object As Control
        iTerminate = False
        For Each Form_Object In frm.Controls
            If TypeOf Form_Object Is TextBox Or TypeOf Form_Object Is ComboBox Then
                If Form_Object.Text = "" Then
                    MsgBox(Form_Object.Name & " is empty. Please complete the field.", vbExclamation)
                    iTerminate = True
                    Form_Object.Focus()
                    Exit Function
                End If
            End If
        Next Form_Object
    End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Field_Check.Empty_Checks(Me)
        If iTerminate = True Then
            iTerminate = False

            Exit Sub
        End If
    End Sub

Check all textboxes in form if they contain numbers only(vb.net)

Public Function Chk_If_Numeric(ByRef frm As Form) As Boolean
'the function check from the textboxes in a certain form'
Dim cControl As Control

Chk_If_Numeric = True  'set the variable to default value'
    For Each cControl In frm 'looping trough the form controls'
        If TypeOf cControl Is TextBox Then 'finding only textboxes'
            If Not IsNumeric(cControl.Text) Then 'check if contain only numbers'
                Chk_If_Numeric = False 'if one of them is not we return false'
            End If
        End If
    Next
End Function

Private Sub Command1_Click()
'a test button click '
    If Chk_If_Numeric(Form1) Then
        MsgBox "all numbers"
    Else
        MsgBox "not all are numeric"
    End If
End Sub

Sunday, June 12, 2011

Generate Crystal Report from List View in vb.net

 Private Sub cmdReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReport.Click
        'REPORT OBJECT
        Dim MyRpt As New CrystalReport1
        'DATASET, AND DATAROW OBJECTS NEEDED TO MAKE THE DATA SOURCE
        Dim row As DataRow = Nothing
        Dim DS As New DataSet

        'ADD A TABLE TO THE DATASET
        DS.Tables.Add("ListViewData")
        'ADD THE COLUMNS TO THE TABLE
        With DS.Tables(0).Columns
            .Add("Field1", Type.GetType("System.String"))
            .Add("Field2", Type.GetType("System.String"))
            .Add("Field3", Type.GetType("System.String"))
        End With

        'LOOP THE LISTVIEW AND ADD A ROW TO THE TABLE FOR EACH LISTVIEWITEM
        For Each LVI As ListViewItem In ListView1.Items
            row = DS.Tables(0).NewRow
            row(0) = LVI.SubItems(0).Text
            row(1) = LVI.SubItems(1).Text
            row(2) = LVI.SubItems(2).Text
            DS.Tables(0).Rows.Add(row)
        Next

        'SET THE REPORT SOURCE TO THE DATABASE
        MyRpt.SetDataSource(DS)

        'ASSIGN THE REPORT TO THE CRVIEWER CONTROL
        CRViewer.ReportSource = MyRpt

        'DISPOSE OF THE DATASET
        DS.Dispose()
        DS = Nothing
    End Sub

sample prog in vb.net using ms sql

Module1.vb codes
Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Module Module1
    Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"
    Public cnSQL As New OleDbConnection
    Public cmSQL As New OleDbCommand
    Public strSQL As String
    Public objRead As OleDbDataReader
    Public currenttab As Integer
    Public ds As New DataSet
    Public lv As ListViewItem

   
    Public Sub connection_open()
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
    End Sub

    Public Sub connection_close()
        cnSQL.Close()
        cnSQL.Dispose()
    End Sub
    Public Sub SaveEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "insert into tbl_stud(id,name,course) values('" & id & "','" & name & "','" & course & "')"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Sub updateEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "UPDATE tbl_stud SET name='" & name & "',course='" & course & "'where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Sub deleteEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "delete from tbl_stud where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub
    Public Function Getid(ByVal tablename As String, ByVal fieldname As String, Optional ByVal cond As String = "") As Double
        Dim cmd2 As New OleDbCommand

        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Try

            cmd2.Connection = cnSQL
            cmd2.CommandText = "select top 1 " & fieldname & " from " & tablename & IIf(cond <> "", " where " & cond, "") & " order by 1 desc"
            objRead = cmd2.ExecuteReader
            If objRead.Read = True Then
                Getid = objRead(0) + 1
            Else
                Getid = 1
            End If
            objRead.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
        cnSQL.Close()
    End Function
    Function AutoID(ByVal Tablename As String, ByVal Fieldname As String) As Int32
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Dim cmd1 As New OleDbCommand
        Dim dr1 As OleDbDataReader
        cmd1.Connection = cnSQL
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select top 1 " & Fieldname & " from " & Tablename & " Order by 1 Desc "
        dr1 = cmd1.ExecuteReader
        If dr1.Read = True Then
            AutoID = Microsoft.VisualBasic.Right(dr1(0), 4) + 1
        Else
            AutoID = 1
        End If
        dr1.Close()
        cnSQL.Close()
    End Function
End Module


Form1.vb codes


Option Explicit On
Imports System.Data.OleDb
Imports System.Data

Public Class Form1
    Dim myDA As New OleDbDataAdapter
    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        If btnsave.Text = "New" Then
            btnsave.Text = "Save"
            tab1()
            GroupBox1.Enabled = True
            txtname.Focus()
            Stud_autonum()
        ElseIf btnsave.Text = "Save" And txtname.Text <> "" And txtcourse.Text <> "" Then
            ' txtname.Text = txtname.Text & "-" & txtid.Text
            SaveEntry(txtid.Text, txtname.Text, txtcourse.Text)
            MsgBox("Student has been Successfully Added.", MsgBoxStyle.Information)
            btnsave.Text = "New"
            PopulateListView()
            clear()
            tab2()
            GroupBox1.Enabled = False
        ElseIf txtname.Text = "" Then
            MsgBox("Pleas Input Student Name.", MsgBoxStyle.Critical)
            txtname.Focus()
        ElseIf txtcourse.Text = "" Then
            MsgBox("Pleas Input Course.", MsgBoxStyle.Critical)
            txtcourse.Focus()
        End If

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        GroupBox1.Enabled = False
        currenttab = 0
        PopulateListView()
        ListView1.Items(0).Selected = True
        ListView1.Select()
        txtid.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
    End Sub
    Private Sub tab1()
        currenttab = TabControl1.SelectedIndex
        currenttab = currenttab + 1
        If (currenttab < TabControl1.TabCount) Then

            TabControl1.SelectedIndex = currenttab

        End If
    End Sub
    Private Sub tab2()
        currenttab = TabControl1.SelectedIndex
        currenttab = currenttab - 1
        If (currenttab > -1) Then

            TabControl1.SelectedIndex = currenttab

        End If
    End Sub
    Private Sub PopulateListView()
        ListView1.Items.Clear()
        connection_open()
        Try
            strSQL = "SELECT * FROM tbl_stud ORDER BY id Asc"
            cmSQL = New OleDbCommand(strSQL, cnSQL)
            objRead = cmSQL.ExecuteReader
            While objRead.Read
                lv = Me.ListView1.Items.Add(objRead("id") & "")
                lv.SubItems.Add(objRead("name") & "")
                lv.SubItems.Add(objRead("course") & "")
            End While
            objRead.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            '---------inserted code to count the number of columns
            ds = New DataSet
            myDA = New OleDbDataAdapter(strSQL, cnSQL)
            myDA.Fill(ds, "MyData")
            Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
            lblTotal.Text = "Total No. of Students : " + kRow1.ToString
            '------------------------end
            ListView1.Items.Clear()
            Dim i As Integer 'COLUMNS
            Dim iCol As Integer = ds.Tables("MyData").Columns.Count 'TOTAL COLUMN
            Dim k As Integer 'ROWS
            Dim kRow As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
            Dim ThisRow(4) As String '//=For each Loop of ROW

            For k = 0 To kRow - 1
                For i = 0 To iCol - 1
                    ThisRow(i) = ds.Tables("MyData").Rows(k).Item(i).ToString
                Next
                Dim lsvi As New ListViewItem(ThisRow, 2)

                '//=Start Alternate Color
                If k / 2 <> Int(k / 2) Then '//=Determine Alternate Row(odd or not)
                    lsvi.BackColor = Color.SkyBlue
                Else
                    lsvi.BackColor = Color.LightCyan


                End If
                '//=End Alternate Color
                ListView1.Items.Add(lsvi)
            Next
            connection_close()
        End Try
    End Sub
    Private Sub RefreshForm()
        ListView1.Items.Clear()
        Call searchListView()
    End Sub
    Private Sub searchListView()
        connection_open()
        Try
            strSQL = "SELECT * FROM tbl_stud where id like '" & txtsearch.Text & "%'"
            cmSQL = New OleDbCommand(strSQL, cnSQL)
            objRead = cmSQL.ExecuteReader
            While objRead.Read
                lv = Me.ListView1.Items.Add(objRead("id") & "")
                lv.SubItems.Add(objRead("name") & "")
                lv.SubItems.Add(objRead("course") & "")
            End While
            objRead.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            connection_close()
        End Try
    End Sub
    Private Sub ListView1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles ListView1.KeyPress
        If e.KeyChar = Chr(13) Then
            GroupBox1.Enabled = True
            btnupdate.Enabled = True
            btndelete.Enabled = True
            btnsave.Enabled = False
            tab1()
            With ListView1.SelectedItems(0)
                txtid.Text = .SubItems(0).Text
                txtname.Text = .SubItems(1).Text
                txtcourse.Text = .SubItems(2).Text
            End With
        End If
    End Sub
    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        GroupBox1.Enabled = True
        btnsave.Enabled = False
        tab1()
        ListView1_MouseDoubleClick(sender, e)
    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        GroupBox1.Enabled = True
        btnupdate.Enabled = True
        btndelete.Enabled = True
        btnsave.Enabled = False
        tab1()
        With ListView1.SelectedItems(0)
            txtid.Text = .SubItems(0).Text
            txtname.Text = .SubItems(1).Text
            txtcourse.Text = .SubItems(2).Text
        End With
    End Sub

    Private Sub txtsearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtsearch.KeyPress
        If e.KeyChar = Chr(13) Then
            ListView1.Items(0).Selected = True
            ListView1.Select()
        End If
    End Sub

    Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
        RefreshForm()
    End Sub

    Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        updateEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Udated.", MsgBoxStyle.Information)
        PopulateListView()
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Enabled = True
    End Sub
    Private Sub clear()
        txtid.Text = ""
        txtname.Text = ""
        txtcourse.Text = ""
    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        deleteEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Deleted.", MsgBoxStyle.Information)
        PopulateListView()
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Enabled = True
    End Sub
    Private Sub Stud_autonum()
        Dim year As String
        Dim studid As String
        year = Now.ToString("yyyy")
        studid = "" & Format(AutoID("tbl_stud", "id"), "0000")
        txtid.Text = year & studid
    End Sub

    Private Sub txtname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtname.KeyPress
        If e.KeyChar = Chr(13) Then
            txtcourse.Focus()
        End If
    End Sub

    Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        clear()
        tab2()
        GroupBox1.Enabled = False
        btnsave.Enabled = True
        btnupdate.Enabled = False
        btndelete.Enabled = False
        btnsave.Text = "New"
        txtsearch.Text = ""
    End Sub
   
End Class

Save,Update and Delete records using functions

Declare to a class 

 

Public Sub SaveEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "insert into tbl_stud(id,name,course) values('" & id & "','" & name & "','" & course & "')"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

    Public Sub updateEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "UPDATE tbl_stud SET name='" & name & "',course='" & course & "'where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

    Public Sub deleteEntry(ByVal id As String, ByVal name As String, ByVal course As String)
        Try

            connection_open()
            strSQL = "delete from tbl_stud where id='" & id & "'"

            cmSQL = New OleDbCommand(strSQL, cnSQL)
            cmSQL.ExecuteNonQuery()
            cmSQL.Dispose()

            connection_close()

        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

Declare to the form


 Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        deleteEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Deleted.", MsgBoxStyle.Information)
      End Sub

 Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        updateEntry(txtid.Text, txtname.Text, txtcourse.Text)
        MsgBox("Student has been Successfully Udated.", MsgBoxStyle.Information)
      
    End Sub

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
   
            SaveEntry(txtid.Text, txtname.Text, txtcourse.Text)
            MsgBox("Student has been Successfully Added.", MsgBoxStyle.Information)
    

    End Sub

public declaration of connetion open and close to the database vb.net

Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"
    Public cnSQL As New OleDbConnection
    Public cmSQL As New OleDbCommand
    Public strSQL As String
    Public objRead As OleDbDataReader
    Public currenttab As Integer
    Public ds As New DataSet
    Public lv As ListViewItem

   
    Public Sub connection_open()
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
    End Sub

    Public Sub connection_close()
        cnSQL.Close()
        cnSQL.Dispose()
    End Sub

MS sql/Vb.net(2005) connection string

Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"

Crystal report using sql query search with textbox

 the Project solution --> Add --> New Item --> .rpt File
*It will open the following window. Select the Report wizard

 Specifying the data --> Create new connection --> OLEDB(ADO) --> specify the data provider by following screen
*Now, after creating the .rpt file we need to write the code to fill the data source. pastse this code to the command button


Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Try
            connection_open()
            strSQL = "select * from tbl_stud where id = '" & TextBox4.Text & "'"
            ds = New DataSet
            myDA = New OleDbDataAdapter(strSQL, cnSQL)
            myDA.Fill(ds, "MyData")




            'Get the Report Location

            Dim strReportPath As String = "C:\Documents and Settings\joeYap\Desktop\exam\WindowsApplication1\WindowsApplication1\CrystalReport1.rpt"

            'Check file exists

            If Not IO.File.Exists(strReportPath) Then

                Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))

            End If



            'Assign the datasource and set the properties for Report viewer

            Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument



            rptDocument.Load(strReportPath)

            rptDocument.SetDataSource(ds.Tables("MyData"))


            CrystalReportForm.CrystalReportViewer1.ShowRefreshButton = False

            CrystalReportForm.CrystalReportViewer1.ShowCloseButton = False

            CrystalReportForm.CrystalReportViewer1.ShowGroupTreeButton = False

            CrystalReportForm.CrystalReportViewer1.ReportSource = rptDocument
            CrystalReportForm.Show()
        Catch Exp As OleDbException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        Finally
            connection_close()
        End Try

    End Sub

Friday, June 10, 2011

my sql using vb.net 2008

Imports System.ServiceProcess
Imports System
Imports Microsoft.Data.Odbc

Here is the VB code that will insert a record into a MySQL database table:

Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=hostname;" & _
"DATABASE=databasename;" & _
"UID=user;" & _
"PASSWORD=password;" & _
"OPTION=3;"
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "INSERT INTO tablename VALUES(NULL,"val1","val2","val3)"
MyCommand.ExecuteNonQuery()
MyConnection.Close() 
 
 
Public Sub retriveData()
        Try
            Dim query As String = "SELECT * FROM Country"
            Dim connection As New MySqlConnection(connStr)
            Dim cmd As New MySqlCommand(query, connection)
 
            connection.Open()
 
            Dim reader As MySqlDataReader
            reader = cmd.ExecuteReader()
 
            While reader.Read()
                Console.WriteLine((reader.GetString(0) & ", " & _
                    reader.GetString(1)))
            End While
 
            reader.Close()
            connection.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
End Sub


Function updateRecord(ByVal query As String) As Integer
        Try
            Dim rowsEffected As Integer = 0
            Dim connection As New MySqlConnection(connStr)
            Dim cmd As New MySqlCommand(query, connection)
 
            connection.Open()
 
            rowsEffected = cmd.ExecuteNonQuery()
 
            connection.Close()
 
            Return rowsEffected
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
End Function


 Dim conn As MySqlConnection
        Dim cnString As String
        cnString = My.Settings.constr
        sqlStatement = "INSERT INTO phpvms_fsspireps(pilotid, code, flightnum, depicao, arricao, route, route_details, distance, aircraft, flighttime, flighttime_stamp, landingrate, submitdate, accepted, log, load, fuelused, expenselist, source, exported,) VALUES(13, 'GRW', '1000', 'LGAV', 'LGTS', 'NEVRA UZ507 OSMOS', 'a:0:{}', '120', '4', '00.58', '00:58:00', -1015.85, '2010-05-01 14:15:56', 0, 'Greece Airways Va ACARS application Log Not yet imported!', 111, 3458, '[BLOB - 1Bytes]', 'GRW ACARS', 1,)"
        conn = New MySqlConnection(cnString)
        Try
            conn.Open()
            conn.BeginTransaction(sqlStatement)
        Catch ex As Common.DbException
            MsgBox(ex.ToString)
        Finally
            conn.Close()
        End Try
    End Sub 

ms sql server manipulation vb.net 2008

http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx

Using DataReaders, mySQL Server

The following code inserts a Record into the Jobs table in Pubs sample database. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form2 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
'integer holds the number of records inserted
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Insert into Jobs values 12,'IT Manager',100,300,_
myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("New Row Inserted" & ra)
myConnection.Close()
End Sub
End Class

Deleting a Record 
We will use Authors table in Pubs sample database to work with this code. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form3 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Delete from Authors where city='Oakland'",_
myConnection)
'since no value is returned we use ExecuteNonQuery
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class

Updating Records
We will update a row in Authors table. Drag a button onto the form and place the following code.
Imports System.Data.SqlClient
Public Class Form4 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Update Authors Set city='Oakland' where city=_
'San Jose' ",myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class

insert data mysql vb.net 2008

  1. Imports System.Data.Odbc
  2.         Dim con As New OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mydb;UID=root;PWD=mydb:OPTION=03")
  3.         Dim da As New OdbcDataAdapter
  4.         da.SelectCommand = New OdbcCommand("insert into emplogin values('MTI019','venkatesh','venkatesa','venkipinki',null)", con)
  5.         Dim ds As New DataSet
  6.         Dim builder As CommandBehavior = CommandBehavior.SingleRow
  7.         con.Open()
  8.  
  9. 'The Following Line Was Bolded
  10.         Dim builder As OdbcCommand builder
  11.         'Dim cmd As New OdbcCommand("insert into emplogin values()")
  12. da.Fill(ds, "emplogin")
  13.         da.Update(ds, "emplogin")

vbline vb.net 2008

Private Sub showsummary()
        txtSummary.Text = "Student ID: " & lblstudID.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "First Name: " & txtfname.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Middle Name: " & txtmname.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Last Name: " & txtlname.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Age: " & txtage.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Religion: " & txtreligion.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Gender: " & cmbgender.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Civil Status: " & cmbstatus.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Citizenship: " & txtcitizenship.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Birth Date: " & dtbday.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Blood Type: " & cmbblodtyp.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Contact No.: " & txtstudcont.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Place of Birth: " & txtPofB.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Home Address: " & cmbblodtyp.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "City Address: " & txtstudcont.Text
        If rdbnew.Checked = True Then
            txtSummary.Text = txtSummary.Text & vbNewLine & "Student Status: " & rdbnew.Text
        ElseIf rdbtrans.Checked = True Then
            txtSummary.Text = txtSummary.Text & vbNewLine & "Student Status: " & rdbtrans.Text
            txtSummary.Text = txtSummary.Text & vbNewLine & "Previous Course: " & txtPcrs.Text
            txtSummary.Text = txtSummary.Text & vbNewLine & "School: " & txtschl.Text
            txtSummary.Text = txtSummary.Text & vbNewLine & "Address: " & txttadd.Text
        End If
        txtSummary.Text = txtSummary.Text & vbNewLine & "Name: " & txtGname.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Contact No.: " & txtGcontact.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Address: " & txtGadd.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Name: " & txtGname.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Contact No.: " & txtGcontact.Text
        txtSummary.Text = txtSummary.Text & vbNewLine & "Address: " & txtGadd.Text


    End Sub

auto number

Imports System.Data
Imports System.Data.OleDb
Module Student

    Public Function Getid(ByVal tablename As String, ByVal fieldname As String, Optional ByVal cond As String = "") As Double
        Dim cmd2 As New OleDbCommand

        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Try

            cmd2.Connection = cnSQL
            cmd2.CommandText = "select top 1 " & fieldname & " from " & tablename & IIf(cond <> "", " where " & cond, "") & " order by 1 desc"
            dr = cmd2.ExecuteReader
            If dr.Read = True Then
                Getid = dr(0) + 1
            Else
                Getid = 1
            End If
            dr.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
        cnSQL.Close()
    End Function
    Function AutoID(ByVal Tablename As String, ByVal Fieldname As String) As Int32
        cnSQL = New OleDbConnection(con)
        cnSQL.Open()
        Dim cmd1 As New OleDbCommand
        Dim dr1 As OleDbDataReader
        cmd1.Connection = cnSQL
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select top 1 " & Fieldname & " from " & Tablename & " Order by 1 Desc "
        dr1 = cmd1.ExecuteReader
        If dr1.Read = True Then
            AutoID = Microsoft.VisualBasic.Right(dr1(0), 4) + 1
        Else
            AutoID = 1
        End If
        dr1.Close()
        cnSQL.Close()
    End Function


End Module







Private Sub Stud_autonum()
        Dim year As String
        Dim studid As String
        year = Now.ToString("yyyy")
        studid = "" & Format(AutoID("tblStudent", "StudentID"), "000000")
        lblstudID.Text = year & studid
    End Sub