frmproduct.frm

this code helps u to understand the basic thing to connect visual basic with sqlserver. this ll be v

VERSION 5.00
Begin VB.Form frmProduct 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Product"
   ClientHeight    =   3855
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   6345
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   Picture         =   "frmProduct.frx":0000
   ScaleHeight     =   3855
   ScaleWidth      =   6345
   StartUpPosition =   2  'CenterScreen
   Begin VB.CommandButton cmdSearch 
      Caption         =   "Sea&rch"
      DownPicture     =   "frmProduct.frx":BE60
      Height          =   615
      Left            =   3480
      Picture         =   "frmProduct.frx":C8AE
      Style           =   1  'Graphical
      TabIndex        =   9
      Top             =   120
      Width           =   735
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "E&xit"
      Height          =   735
      Left            =   5040
      Picture         =   "frmProduct.frx":CFA6
      Style           =   1  'Graphical
      TabIndex        =   8
      Top             =   2640
      Width           =   735
   End
   Begin VB.CommandButton cmdCancel 
      Caption         =   "&Cancel"
      Enabled         =   0   'False
      Height          =   735
      Left            =   3240
      Picture         =   "frmProduct.frx":D5C7
      Style           =   1  'Graphical
      TabIndex        =   7
      Top             =   2640
      Width           =   735
   End
   Begin VB.CommandButton cmdSave 
      Caption         =   "&Save"
      Enabled         =   0   'False
      Height          =   735
      Left            =   2520
      Picture         =   "frmProduct.frx":D7EF
      Style           =   1  'Graphical
      TabIndex        =   6
      Top             =   2640
      Width           =   735
   End
   Begin VB.CommandButton cmdDelete 
      Caption         =   "&Delete"
      Height          =   735
      Left            =   1800
      Picture         =   "frmProduct.frx":D97E
      Style           =   1  'Graphical
      TabIndex        =   5
      Top             =   2640
      Width           =   735
   End
   Begin VB.CommandButton cmdEdit 
      Caption         =   "&Edit"
      Height          =   735
      Left            =   1080
      Picture         =   "frmProduct.frx":DA3D
      Style           =   1  'Graphical
      TabIndex        =   4
      Top             =   2640
      Width           =   735
   End
   Begin VB.CommandButton cmdAdd 
      Caption         =   "&Add"
      Height          =   735
      Left            =   360
      Picture         =   "frmProduct.frx":DC96
      Style           =   1  'Graphical
      TabIndex        =   3
      Top             =   2640
      Width           =   735
   End
   Begin VB.TextBox txtUnit 
      BackColor       =   &H00C0E0FF&
      Enabled         =   0   'False
      Height          =   285
      Left            =   2040
      TabIndex        =   2
      Top             =   2040
      Width           =   1215
   End
   Begin VB.TextBox txtDescrr 
      BackColor       =   &H00C0E0FF&
      Enabled         =   0   'False
      Height          =   285
      Left            =   2040
      TabIndex        =   1
      Top             =   1080
      Width           =   3255
   End
   Begin VB.TextBox txtBrand 
      BackColor       =   &H00C0E0FF&
      Enabled         =   0   'False
      Height          =   285
      Left            =   2040
      TabIndex        =   0
      Top             =   1560
      Width           =   3255
   End
   Begin VB.Label lblPcode 
      BackColor       =   &H00C0E0FF&
      Height          =   255
      Left            =   2040
      TabIndex        =   14
      Top             =   360
      Width           =   1215
   End
   Begin VB.Line Line4 
      X1              =   5880
      X2              =   5880
      Y1              =   2520
      Y2              =   3480
   End
   Begin VB.Line Line3 
      X1              =   240
      X2              =   240
      Y1              =   2520
      Y2              =   3480
   End
   Begin VB.Line Line2 
      X1              =   240
      X2              =   5880
      Y1              =   3480
      Y2              =   3480
   End
   Begin VB.Line Line1 
      X1              =   240
      X2              =   5880
      Y1              =   2520
      Y2              =   2520
   End
   Begin VB.Label Label3 
      Caption         =   "Unit:"
      Height          =   255
      Left            =   480
      TabIndex        =   13
      Top             =   2040
      Width           =   1335
   End
   Begin VB.Label Label2 
      Caption         =   "Description:"
      Height          =   255
      Left            =   480
      TabIndex        =   12
      Top             =   1080
      Width           =   1335
   End
   Begin VB.Label Label1 
      Caption         =   "Product Code:"
      Height          =   255
      Left            =   480
      TabIndex        =   11
      Top             =   360
      Width           =   1335
   End
   Begin VB.Line Line5 
      X1              =   360
      X2              =   5760
      Y1              =   840
      Y2              =   840
   End
   Begin VB.Label Label4 
      Caption         =   "Brand:"
      Height          =   255
      Left            =   480
      TabIndex        =   10
      Top             =   1560
      Width           =   1455
   End
End
Attribute VB_Name = "frmProduct"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub cmdAdd_Click()
empty_obj
enabled_obj
Call open_conn
Set rs = cn.Execute("select * from tblproduct")
If Not rs.EOF Then          ' autonumbering to avoid productcode redundancy
    rs.MoveFirst            ' getting first the highest pcode value in the database
    Do While Not rs.EOF     ' then, add 1 and display the amt to the lblPcode
        If rs!pcode > ctr1 Then
            ctr1 = rs!pcode
        End If
        rs.MoveNext
    Loop
End If
Call close_conn
ctr1 = ctr1 + 1
lblPcode.Caption = ctr1
txtDescrr.SetFocus
End Sub

Private Sub cmdCancel_Click()
disabled_obj
ctr1 = ctr1 - 1
If BEdt = False Then
empty_obj
End If
Call open_conn
emptydb
Call close_conn
End Sub

Private Sub cmdDelete_Click()
If lblPcode.Caption = "" Then
    MsgBox "No current record!", vbCritical, "User Info"
Else
    Call open_conn
    rs.Open "Select * from tblsales where pcode='" + Trim(lblPcode.Caption) + "'", cn
    If rs.EOF = False Then
        MsgBox "The product is in use! Cannot be deleted!", vbInformation, "User Info"
        Exit Sub
    Else
        'n1 = Val(lblPcode.Caption)
        cn.Execute "Delete * from tblproduct where Pcode='" + Trim(lblPcode.Caption) + "'"
        MsgBox "Record Deleted!", vbInformation, "User Info"
        empty_obj
        'Set rs = cn.Execute("select * from tblproduct")
        'If Not rs.EOF Then
        '    rs.MoveFirst
        '    Do While Not rs.EOF
        '        If rs!pcode > n1 Then
        '            n1 = rs!pcode
        '            n1 = n1 - 1
        '            lblPcode.Caption = Str(n1)
        '            cn.Execute "update tblproduct set Pcode='" + Trim(lblPcode.Caption) + "' where pcode='" + cInvent + "'"
        '        End If
        '        rs.MoveNext
        '    Loop
        'End If
    End If
    Call emptydb
    Call close_conn
End If
End Sub

Private Sub cmdEdit_Click()
If lblPcode.Caption = "" Then
    MsgBox "No current record!", vbCritical, "User Info"
Else
    cInvent = Trim(lblPcode.Caption)
    BEdt = True
    enabled_obj
    cmdSave.Caption = "&Update"
End If
End Sub

Private Sub cmdExit_Click()
If MsgBox("Close this window", vbInformation + vbYesNo, "User Info") = vbYes Then
    Unload Me
    frmMenu.Show
End If
End Sub

Private Sub cmdSave_Click()
''''''''''''''''''validation of data'''''''''''''''''''''''
Call open_conn
If txtDescrr.Text = "" Then
MsgBox "Enter Description!", vbInformation, "User Info"
txtDescrr.SetFocus
Exit Sub
End If
If txtBrand.Text = "" Then
MsgBox "Enter Brand!", vbInformation, "User Info"
txtBrand.SetFocus
Exit Sub
End If
If txtUnit.Text = "" Then
MsgBox "Enter Unit!", vbInformation, "User Info"
txtUnit.SetFocus
Exit Sub
End If
''''''''''''''''''''''end of validation'''''''''''''''''

'''''''''saving files of bedt= false && updating files if bedt=true'''''''''''''
If BEdt = False Then
    cn.Execute "Insert into tblproduct values('" + Trim(lblPcode.Caption) + "','" + Trim(txtDescrr.Text) + "','" _
    + Trim(txtBrand.Text) + "','" + Trim(txtUnit.Text) + "')"
    MsgBox "New Record Saved!", vbInformation, "User Info"
    ctr1 = Val(lblPcode.Caption)
    disabled_obj
    close_conn
Else
    cn.Execute "update tblproduct set Pcode='" + Trim(lblPcode.Caption) + "',descrr='" + Trim(txtDescrr.Text) + _
    "',brand='" + Trim(txtBrand.Text) + "',unit='" + Trim(txtUnit.Text) + "' where Pcode='" + cInvent + "'"
    MsgBox "Record Updated!", vbInformation, "user Info"
    cmdSave.Caption = "&Save"
    BEdt = False
    disabled_obj
    close_conn
End If

End Sub

Private Sub cmdSearch_Click()
info = "aaa"
frmSearch.Show 1, Me
End Sub

Private Sub Form_Load()
Call open_conn
ctr1 = 0
Call emptydb
Call close_conn
End Sub

Private Sub txtunit_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
    cmdSave.SetFocus
End If
End Sub

Private Sub txtdescrr_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
txtBrand.SetFocus
End If
End Sub

Private Sub txtbrand_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
txtUnit.SetFocus
End If
End Sub

Private Sub lblpcode_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
txtDescrr.SetFocus
End If
End Sub

Public Sub enabled_obj()
cmdAdd.Enabled = False
cmdEdit.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = True
cmdCancel.Enabled = True
cmdExit.Enabled = False
cmdSearch.Enabled = False
lblPcode.Enabled = True
txtDescrr.Enabled = True
txtBrand.Enabled = True
txtUnit.Enabled = True
If BEdt = False Then
    empty_obj
End If
End Sub

Public Sub disabled_obj()
cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdDelete.Enabled = True
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdExit.Enabled = True
cmdSearch.Enabled = True
lblPcode.Enabled = False
txtDescrr.Enabled = False
txtBrand.Enabled = False
txtUnit.Enabled = False
End Sub

Public Sub empty_obj()
lblPcode.Caption = ""
txtDescrr.Text = ""
txtBrand.Text = ""
txtUnit.Text = ""
End Sub

Public Sub move_record()
Set rs = cn.Execute("select * from tblproduct")
If Not rs.EOF Then
    rs.MoveFirst
    lblPcode.Caption = rs!pcode
    txtDescrr.Text = rs!descrr
    txtBrand.Text = rs!brand
    txtUnit.Text = rs!unit
    
End If
End Sub

Public Sub emptydb()
Call move_record
If lblPcode = "" Then
    cmdEdit.Enabled = False
    cmdDelete.Enabled = False
End If
End Sub