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="
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
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
'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
'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
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
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
*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.
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.
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
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
- Imports System.Data.Odbc
- Dim con As New OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mydb;UID=root;PWD=mydb:OPTION=03")
- Dim da As New OdbcDataAdapter
- da.SelectCommand = New OdbcCommand("insert into emplogin values('MTI019','venkatesh','venkatesa','venkipinki',null)", con)
- Dim ds As New DataSet
- Dim builder As CommandBehavior = CommandBehavior.SingleRow
- con.Open()
- 'The Following Line Was Bolded
- Dim builder As OdbcCommand builder
- 'Dim cmd As New OdbcCommand("insert into emplogin values()")
- da.Fill(ds, "emplogin")
- 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
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
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
Tab control(vb.net 2008)
currenttab = 0 ' Start with first tab
currenttab = TabControl1.SelectedIndex
currenttab = currenttab + 1
If (currenttab < TabControl1.TabCount) Then
TabControl1.SelectedIndex = currenttab
End If
currenttab = TabControl1.SelectedIndex
currenttab = currenttab - 1
If (currenttab > -1) Then
TabControl1.SelectedIndex = currenttab
End If
If currenttab = 0 Then
Button1.Enabled = False
ElseIf currenttab > 0 Then
Button1.Enabled = True
End If
currenttab = TabControl1.SelectedIndex
currenttab = currenttab + 1
If (currenttab < TabControl1.TabCount) Then
TabControl1.SelectedIndex = currenttab
End If
currenttab = TabControl1.SelectedIndex
currenttab = currenttab - 1
If (currenttab > -1) Then
TabControl1.SelectedIndex = currenttab
End If
If currenttab = 0 Then
Button1.Enabled = False
ElseIf currenttab > 0 Then
Button1.Enabled = True
End If
Thursday, June 9, 2011
pagenation and checkbox array
<style type="text/css">
<!--
.style7 {font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
font-size: 18px;
}
.style8 {color: #FFFFFF}
.style10 {color: #FFFFFF; font-weight: bold; }
-->
</style>
<table width="560" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td height="73" colspan="4"><div align="center"><span class="style7">Inbox</span></div></td>
</tr>
<tr>
<td colspan="4"></td>
</tr>
<tr>
<td width="77" bgcolor="#666666"><span class="style8"></span></td>
<td width="168" bgcolor="#666666"><span class="style10">Name</span></td>
<td width="183" bgcolor="#666666"><span class="style10">E-mail address</span></td>
<td width="132" bgcolor="#666666"><span class="style10"> Subject</span></td>
</tr>
<tr>
<td colspan="4"><div style="width:565px; height:auto; top:auto; border:#000000 1px solid">
<p> <?php
include("connect.php");
//Obtain the required page number
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
} // if
//Identify how many database rows are available
$query = "SELECT count(*) FROM tbl_inbox";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];
//Calculate number of $lastpage
$rows_per_page = 10;
$lastpage = ceil($numrows/$rows_per_page);
//Ensure that $pageno is within range
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
$pageno = $lastpage;
} // if
if ($pageno < 1) {
$pageno = 1;
} // if
//Construct LIMIT clause
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
//Issue the database query
$query = "SELECT * FROM tbl_inbox ORDER BY id DESC $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$rs = mysql_query($query) or die("Invalid Query <br>$sql");
$count=mysql_num_rows($rs);
if($count==0)
{
echo'<br/><center><font style="font-family:Tahoma;font-size:11px;color:#990000"> No Message in your Inbox</font></center><br/>';
}
while($row = mysql_fetch_array($result))
{
$rd=$row['rd'];
echo'<form name="form1" method="post" action="">';
if($rd=='y')
{
echo'<table>';
echo'<tr>';
echo'<td width="6"> </td>';
echo'<td width="60" ><input name="checkbox[]" type="checkbox" id="checkbox[]" value="'.$row['id'].'"> </td>';
echo'<td width="170"><font face="Tahoma" size="1" color="black">'.$row['name'].'</font></td>';
echo'<td width="175"><font face="Tahoma" size="1" color="black"><a href="inbox_cont.php?id='.$row['id'].'" >'.$row['email'].'</a></font></td>';
echo'<td width="116"><font face="Tahoma" size="1" color="black">'.$row['subject'].'</font></td>';
echo'</tr>';
echo'</table>';
echo'<hr align="center" width="100%"></hr>';
}
elseif($rd=='n')
{
echo'<table>';
echo'<tr>';
echo'<td width="6"> </td>';
echo'<td width="60" ><input name="checkbox[]" type="checkbox" id="checkbox[]" value="'.$row['id'].'"> </td>';
echo'<td width="170"><b><font face="Tahoma" size="1" color="black">'.$row['name'].'</font></b></td>';
echo'<td width="175"><font face="Tahoma" size="1" color="black"><a href="inbox_cont.php?id='.$row['id'].'" <B>'.$row['email'].'</B></a></font></td>';
echo'<td width="116"><b><font face="Tahoma" size="1" color="black">'.$row['subject'].'</font></b></td>';
echo'</tr>';
echo'</table>';
echo'<hr align="center" width="100%"></hr>';
}
}
echo'<br/>';
//Construct pagination hyperlinks
if ($pageno == 1) {
echo " ";
} else {
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'><img border='0' src='images/start.jpg' height='25' width='25'/></a> ";
$prevpage = $pageno-1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'><img border='0' src='images/arrow_left.jpg' height='25' width='25'/></a>";
} // if
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
echo "";
} else {
$nextpage = $pageno+1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'><img border='0' src='images/arrow_right.jpg' height='25' width='25'/></a>" ;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'><img border='0' src='images/end.jpg' height='25' width='25'/></a>";
} // if
?>
</div></td>
</tr>
<tr>
<td height="24" colspan="4" bgcolor="#666666"></p>
<input name="delete" type="submit" id="delete" value="Delete" /></td>
</tr>
<tr> </tr>
<tr>
<td colspan="4"><?php
if(isset($_POST['delete']))
{
$checkbox = $_POST['checkbox']; //from name="checkbox[]"
$countCheck = count($_POST['checkbox']);
for($i=0;$i<$countCheck;$i++)
{
$del_id = $checkbox[$i];
$sql = "DELETE FROM tbl_inbox WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result)
{
echo "<meta http-equiv=\"refresh\" content=\"0;URL=inbox_inbox.php\">";
}
}
?></td>
</tr>
</table>
<!--
.style7 {font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
font-size: 18px;
}
.style8 {color: #FFFFFF}
.style10 {color: #FFFFFF; font-weight: bold; }
-->
</style>
<table width="560" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td height="73" colspan="4"><div align="center"><span class="style7">Inbox</span></div></td>
</tr>
<tr>
<td colspan="4"></td>
</tr>
<tr>
<td width="77" bgcolor="#666666"><span class="style8"></span></td>
<td width="168" bgcolor="#666666"><span class="style10">Name</span></td>
<td width="183" bgcolor="#666666"><span class="style10">E-mail address</span></td>
<td width="132" bgcolor="#666666"><span class="style10"> Subject</span></td>
</tr>
<tr>
<td colspan="4"><div style="width:565px; height:auto; top:auto; border:#000000 1px solid">
<p> <?php
include("connect.php");
//Obtain the required page number
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
} // if
//Identify how many database rows are available
$query = "SELECT count(*) FROM tbl_inbox";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];
//Calculate number of $lastpage
$rows_per_page = 10;
$lastpage = ceil($numrows/$rows_per_page);
//Ensure that $pageno is within range
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
$pageno = $lastpage;
} // if
if ($pageno < 1) {
$pageno = 1;
} // if
//Construct LIMIT clause
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
//Issue the database query
$query = "SELECT * FROM tbl_inbox ORDER BY id DESC $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$rs = mysql_query($query) or die("Invalid Query <br>$sql");
$count=mysql_num_rows($rs);
if($count==0)
{
echo'<br/><center><font style="font-family:Tahoma;font-size:11px;color:#990000"> No Message in your Inbox</font></center><br/>';
}
while($row = mysql_fetch_array($result))
{
$rd=$row['rd'];
echo'<form name="form1" method="post" action="">';
if($rd=='y')
{
echo'<table>';
echo'<tr>';
echo'<td width="6"> </td>';
echo'<td width="60" ><input name="checkbox[]" type="checkbox" id="checkbox[]" value="'.$row['id'].'"> </td>';
echo'<td width="170"><font face="Tahoma" size="1" color="black">'.$row['name'].'</font></td>';
echo'<td width="175"><font face="Tahoma" size="1" color="black"><a href="inbox_cont.php?id='.$row['id'].'" >'.$row['email'].'</a></font></td>';
echo'<td width="116"><font face="Tahoma" size="1" color="black">'.$row['subject'].'</font></td>';
echo'</tr>';
echo'</table>';
echo'<hr align="center" width="100%"></hr>';
}
elseif($rd=='n')
{
echo'<table>';
echo'<tr>';
echo'<td width="6"> </td>';
echo'<td width="60" ><input name="checkbox[]" type="checkbox" id="checkbox[]" value="'.$row['id'].'"> </td>';
echo'<td width="170"><b><font face="Tahoma" size="1" color="black">'.$row['name'].'</font></b></td>';
echo'<td width="175"><font face="Tahoma" size="1" color="black"><a href="inbox_cont.php?id='.$row['id'].'" <B>'.$row['email'].'</B></a></font></td>';
echo'<td width="116"><b><font face="Tahoma" size="1" color="black">'.$row['subject'].'</font></b></td>';
echo'</tr>';
echo'</table>';
echo'<hr align="center" width="100%"></hr>';
}
}
echo'<br/>';
//Construct pagination hyperlinks
if ($pageno == 1) {
echo " ";
} else {
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'><img border='0' src='images/start.jpg' height='25' width='25'/></a> ";
$prevpage = $pageno-1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'><img border='0' src='images/arrow_left.jpg' height='25' width='25'/></a>";
} // if
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
echo "";
} else {
$nextpage = $pageno+1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'><img border='0' src='images/arrow_right.jpg' height='25' width='25'/></a>" ;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'><img border='0' src='images/end.jpg' height='25' width='25'/></a>";
} // if
?>
</div></td>
</tr>
<tr>
<td height="24" colspan="4" bgcolor="#666666"></p>
<input name="delete" type="submit" id="delete" value="Delete" /></td>
</tr>
<tr> </tr>
<tr>
<td colspan="4"><?php
if(isset($_POST['delete']))
{
$checkbox = $_POST['checkbox']; //from name="checkbox[]"
$countCheck = count($_POST['checkbox']);
for($i=0;$i<$countCheck;$i++)
{
$del_id = $checkbox[$i];
$sql = "DELETE FROM tbl_inbox WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result)
{
echo "<meta http-equiv=\"refresh\" content=\"0;URL=inbox_inbox.php\">";
}
}
?></td>
</tr>
</table>
listview with alternate color(vb.net 2008)
Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Public Class frmSupplier
Dim AddMode As Boolean
Dim Ctrl As Integer
Dim myConn As New OleDbConnection
Dim myCmd As New OleDbCommand
Dim myDA As New OleDbDataAdapter
Dim myDR As OleDbDataReader
Dim strSQL As String
Dim ds As DataSet
'"Declaration (Join Club)"
Dim Ctrl2 As Integer
'Dim myConn As New OleDbConnection
Dim myCmd2 As New OleDbCommand
Dim myDA2 As New OleDbDataAdapter
Dim myDR2 As OleDbDataReader
Dim strSQL2 As String
Dim ds2 As DataSet
Function IsConnected() As Boolean
Try
'Checks first if already connected to database,if connected, it will be disconnected.
If myConn.State = ConnectionState.Open Then myConn.Close()
myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\salesInventory.mdb;Persist Security Info=False;Jet OLEDB:Database Password = sales"
myConn.Open()
IsConnected = True
Catch ex As Exception
MsgBox(ex.Message, , "Connection")
End Try
End Function
Function IsExists() As Boolean
SearchSM(txtboxID.Text)
If myDR.HasRows = True Then
Return True
Else
Return False
End If
End Function
Sub SearchSM(ByVal SearchWord As String)
Try
If IsConnected() = True Then
'Queries to database
If tsbtnSave.Enabled = True Then
strSQL = "SELECT * FROM SUPPLIER WHERE S_ID LIKE '" & SearchWord & "%'"
Else
strSQL = "SELECT * FROM SUPPLIER WHERE S_Name LIKE '" _
& SearchWord & "%' OR S_Address LIKE '" _
& SearchWord & "%' OR S_Contactno LIKE '" _
& SearchWord & "%' ORDER BY S_Name"
End If
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myDA.SelectCommand = myCmd
myDR = myCmd.ExecuteReader()
'Display results to table
'lvwRecipients.Items.Clear()
While (myDR.Read())
With lvwSupplier.Items.Add(myDR("S_ID").ToString)
.SubItems.Add(myDR("S_Name"))
.SubItems.Add(myDR("S_Address"))
.SubItems.Add(myDR("S_Contactno"))
End With
End While
End If
Catch ex As Exception
MsgBox(ex.Message, , "SearchSM")
End Try
'---------inserted code to count the number of columns
ds = New DataSet
myDA = New OleDbDataAdapter(strSQL, myConn)
myDA.Fill(ds, "MyData")
Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
lblTotal.Text = "Total No. of Recipients : " + kRow1.ToString
'------------------------end
lvwSupplier.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
lvwSupplier.Items.Add(lsvi)
Next
End Sub
Sub SaveEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Try
If IsExists() = False Then
'Adds new record to the table
strSQL = "INSERT INTO SUPPLIER(S_ID, S_Name, S_Address, S_Contactno) VALUES('" _
& S_ID & "' ,'" _
& S_Name.ToUpper & "' ,'" _
& S_Address.ToUpper & "' ,'" _
& S_Contactno & "')"
Else
'Updates record from the table
strSQL = "UPDATE SUPPLIER SET S_ID = '" & S_ID & "'," _
& "S_Name = '" & S_Name & "'," _
& "S_Address = '" & S_Address & "'," _
& "S_Contactno = '" & S_Contactno & "'"
' & "WHERE S_ID = " & Ctrl & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnAdd.Enabled = True
tsbtnSave.Enabled = False
' LockTextboxes()
txtboxID.Text = String.Empty
txtBoxName.Text = String.Empty
txtBoxAddress.Text = String.Empty
txtBoxContact.Text = String.Empty
Catch ex As Exception
MsgBox(ex.Message, , "Save Recipient")
End Try
End Sub
Sub DeleteEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Dim confirmDelete As DialogResult
confirmDelete = MessageBox.Show("Are you sure you want to delete this Supplier?", _
"SMS Sytem", MessageBoxButtons.YesNoCancel, _
MessageBoxIcon.Warning)
If confirmDelete = Windows.Forms.DialogResult.Yes Then
Try
If IsExists() = True Then
'Updates record from the table
strSQL = "DELETE * FROM SUPPLIER WHERE S_ID = " & txtboxID.Text & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnCancel.PerformClick()
Catch ex As Exception
MsgBox(ex.Message, , "Delete Supplier")
End Try
ElseIf confirmDelete = Windows.Forms.DialogResult.Cancel Then
tsbtnCancel.PerformClick()
End If
End Sub
Sub Locked(ByVal Mode As Boolean)
For Each Ctrl As Control In Me.Controls
If TypeOf Ctrl Is TextBox Then
Ctrl.Enabled = Not Mode
Ctrl.Text = ""
If Ctrl.Name = "txtBoxID" Then
Ctrl.Focus()
End If
' ElseIf TypeOf Ctrl Is ComboBox Then
'Ctrl.Enabled = Not Mode
' If Ctrl.Name = "cboClubName" Then
' Ctrl.Focus()
' End If
'ElseIf TypeOf Ctrl Is Button Then
'Ctrl.Enabled = Not Mode
'If Ctrl.Name = "btnAddClub" Then
' Ctrl.Focus()
' Else
' Ctrl.Enabled = True
' End If
End If
Next Ctrl
End Sub
Sub SetSearchBox()
If tstbSearchSupplier.Text = "" Then
tstbSearchSupplier.Text = "Type here to search"
tstbSearchSupplier.ForeColor = Color.Gray
End If
End Sub
Private Sub frmSupplier_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DsClub.CLUB' table. You can move, or remove it, as needed.
SearchSM("")
Locked(True)
SetSearchBox()
End Sub
Private Sub tsbtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnAdd.Click
tsbtnAdd.Enabled = False
tsbtnSave.Enabled = True
tsbtnCancel.Enabled = True
AddMode = True
Locked(False)
End Sub
Private Sub tsbtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnSave.Click
'If txtBoxName.Text = "" Or txtBoxAddress.Text = _
' "" Or txtBoxContact.Text = "" Then
'MessageBox.Show("Please fill up all the data.", "Sales and Inventory Sytem", _
' MessageBoxButtons.OK, MessageBoxIcon.Information)
If IsConnected() = True Then
SaveEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
'End If
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
Locked(True)
End If
End Sub
Private Sub lvwSupplier_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseClick
lvwSupplier_MouseDoubleClick(sender, e)
End Sub
Private Sub lvwSupplier_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseDoubleClick
tsbtnSave.Enabled = True
tsbtnDelete.Enabled = True
tsbtnAdd.Enabled = True
tsbtnCancel.Enabled = True
Locked(False)
With txtboxID
.BackColor = Color.White
.ForeColor = Color.Red
.Enabled = True
End With
With lvwSupplier.SelectedItems(0)
Ctrl = .SubItems(0).Text
txtBoxName.Text = .SubItems(1).Text
txtBoxAddress.Text = .SubItems(2).Text
txtBoxContact.Text = .SubItems(3).Text
'txtYear_Level.Text = .SubItems(4).Text
End With
txtboxID.Text = Ctrl 'gets the Ctrl
' for the family name
'Dim strFamilyName As String = txtDummyName.Text
'Dim strArrFamilyName() As String
'Dim countFamilyName As Integer
'strArrFamilyName = strFamilyName.Split(",")
'For countFamilyName = 0 To strArrFamilyName.Length - 1
'txtFamilyName.Text = (strArrFamilyName(countFamilyName))
'Exit For
'Next
'for the family name
'for the first name
'Dim strFirstName As String = txtDummyName.Text
'Dim strArrFirstName() As String
'Dim countFirstName As Integer
'Dim test As String
'strArrFirstName = strFirstName.Split(", ")
' For countFirstName = 1 To strArrFirstName.Length - 1
'test = (strArrFirstName(countFirstName)).TrimStart.TrimEnd
'Dim myString As String = test
'Dim myChar As Char() = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, _
' "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
' "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c, "."c}
'Dim NewString As String = myString.TrimEnd(myChar)
'txtFirstName.Text = NewString.TrimEnd
'Exit For
'Next
' for the first name
'for the middle name
'Dim strMiddleInitial As String = Microsoft.VisualBasic.Right(txtDummyName.Text, 2)
'txtMiddleInitial.Text = strMiddleInitial.TrimStart
'for the middle name
End Sub
Private Sub tsbtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnDelete.Click
If IsConnected() = True Then
DeleteEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
End If
tsbtnAdd.Enabled = True
tsbtnEdit.Enabled = True
tsbtnSave.Enabled = False
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
End Sub
Private Sub tsbtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnCancel.Click
Locked(True)
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
tsbtnSave.Enabled = False
tsbtnAdd.Enabled = True
End Sub
Private Sub tstbSearchSupplier_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.Click
If tstbSearchSupplier.Text = "Type here to search" Then
tstbSearchSupplier.Text = ""
tstbSearchSupplier.ForeColor = Color.Black
ElseIf tstbSearchSupplier.Text <> "" Then
tstbSearchSupplier.SelectAll()
End If
End Sub
Private Sub tstbSearchSupplier_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles tstbSearchSupplier.KeyDown
End Sub
Private Sub tstbSearchSupplier_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tstbSearchSupplier.KeyPress
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
'Checks first if system is connected then if true, continues to search
If IsConnected() = True Then Call SearchSM(tstbSearchSupplier.Text)
e.Handled = True
ElseIf e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Back) Then
SearchSM("")
'e.Handled = True
End If
End Sub
Private Sub txtboxID_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtboxID.KeyPress
Dim strAllowableChars As String
strAllowableChars = "0123456789"
If InStr(strAllowableChars, e.KeyChar) = 0 And Asc(e.KeyChar) <> 8 Then
e.Handled = True
End If
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
txtBoxName.Focus()
e.Handled = True
End If
End Sub
Private Sub txtBoxName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxName.KeyPress
End Sub
Private Sub txtBoxAddress_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxAddress.KeyPress
End Sub
Private Sub txtBoxContact_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxContact.KeyPress
End Sub
Private Sub tstbSearchSupplier_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.LostFocus
SetSearchBox()
End Sub
Private Sub tstbSearchSupplier_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.TextChanged
Try
Dim sItem As ListViewItem = lvwSupplier.FindItemWithText(tstbSearchSupplier.Text, True, 0)
If Not sItem Is Nothing Then
With lvwSupplier.Items(sItem.Index)
.EnsureVisible()
.Selected = True
End With
End If
Catch ex As Exception
End Try
End Sub
Private Sub lvwSupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvwSupplier.SelectedIndexChanged
End Sub
Private Sub ToolStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ToolStrip1.ItemClicked
End Sub
End Class
Imports System.Data.OleDb
Imports System.Data
Public Class frmSupplier
Dim AddMode As Boolean
Dim Ctrl As Integer
Dim myConn As New OleDbConnection
Dim myCmd As New OleDbCommand
Dim myDA As New OleDbDataAdapter
Dim myDR As OleDbDataReader
Dim strSQL As String
Dim ds As DataSet
'"Declaration (Join Club)"
Dim Ctrl2 As Integer
'Dim myConn As New OleDbConnection
Dim myCmd2 As New OleDbCommand
Dim myDA2 As New OleDbDataAdapter
Dim myDR2 As OleDbDataReader
Dim strSQL2 As String
Dim ds2 As DataSet
Function IsConnected() As Boolean
Try
'Checks first if already connected to database,if connected, it will be disconnected.
If myConn.State = ConnectionState.Open Then myConn.Close()
myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\salesInventory.mdb;Persist Security Info=False;Jet OLEDB:Database Password = sales"
myConn.Open()
IsConnected = True
Catch ex As Exception
MsgBox(ex.Message, , "Connection")
End Try
End Function
Function IsExists() As Boolean
SearchSM(txtboxID.Text)
If myDR.HasRows = True Then
Return True
Else
Return False
End If
End Function
Sub SearchSM(ByVal SearchWord As String)
Try
If IsConnected() = True Then
'Queries to database
If tsbtnSave.Enabled = True Then
strSQL = "SELECT * FROM SUPPLIER WHERE S_ID LIKE '" & SearchWord & "%'"
Else
strSQL = "SELECT * FROM SUPPLIER WHERE S_Name LIKE '" _
& SearchWord & "%' OR S_Address LIKE '" _
& SearchWord & "%' OR S_Contactno LIKE '" _
& SearchWord & "%' ORDER BY S_Name"
End If
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myDA.SelectCommand = myCmd
myDR = myCmd.ExecuteReader()
'Display results to table
'lvwRecipients.Items.Clear()
While (myDR.Read())
With lvwSupplier.Items.Add(myDR("S_ID").ToString)
.SubItems.Add(myDR("S_Name"))
.SubItems.Add(myDR("S_Address"))
.SubItems.Add(myDR("S_Contactno"))
End With
End While
End If
Catch ex As Exception
MsgBox(ex.Message, , "SearchSM")
End Try
'---------inserted code to count the number of columns
ds = New DataSet
myDA = New OleDbDataAdapter(strSQL, myConn)
myDA.Fill(ds, "MyData")
Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
lblTotal.Text = "Total No. of Recipients : " + kRow1.ToString
'------------------------end
lvwSupplier.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
lvwSupplier.Items.Add(lsvi)
Next
End Sub
Sub SaveEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Try
If IsExists() = False Then
'Adds new record to the table
strSQL = "INSERT INTO SUPPLIER(S_ID, S_Name, S_Address, S_Contactno) VALUES('" _
& S_ID & "' ,'" _
& S_Name.ToUpper & "' ,'" _
& S_Address.ToUpper & "' ,'" _
& S_Contactno & "')"
Else
'Updates record from the table
strSQL = "UPDATE SUPPLIER SET S_ID = '" & S_ID & "'," _
& "S_Name = '" & S_Name & "'," _
& "S_Address = '" & S_Address & "'," _
& "S_Contactno = '" & S_Contactno & "'"
' & "WHERE S_ID = " & Ctrl & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnAdd.Enabled = True
tsbtnSave.Enabled = False
' LockTextboxes()
txtboxID.Text = String.Empty
txtBoxName.Text = String.Empty
txtBoxAddress.Text = String.Empty
txtBoxContact.Text = String.Empty
Catch ex As Exception
MsgBox(ex.Message, , "Save Recipient")
End Try
End Sub
Sub DeleteEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Dim confirmDelete As DialogResult
confirmDelete = MessageBox.Show("Are you sure you want to delete this Supplier?", _
"SMS Sytem", MessageBoxButtons.YesNoCancel, _
MessageBoxIcon.Warning)
If confirmDelete = Windows.Forms.DialogResult.Yes Then
Try
If IsExists() = True Then
'Updates record from the table
strSQL = "DELETE * FROM SUPPLIER WHERE S_ID = " & txtboxID.Text & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnCancel.PerformClick()
Catch ex As Exception
MsgBox(ex.Message, , "Delete Supplier")
End Try
ElseIf confirmDelete = Windows.Forms.DialogResult.Cancel Then
tsbtnCancel.PerformClick()
End If
End Sub
Sub Locked(ByVal Mode As Boolean)
For Each Ctrl As Control In Me.Controls
If TypeOf Ctrl Is TextBox Then
Ctrl.Enabled = Not Mode
Ctrl.Text = ""
If Ctrl.Name = "txtBoxID" Then
Ctrl.Focus()
End If
' ElseIf TypeOf Ctrl Is ComboBox Then
'Ctrl.Enabled = Not Mode
' If Ctrl.Name = "cboClubName" Then
' Ctrl.Focus()
' End If
'ElseIf TypeOf Ctrl Is Button Then
'Ctrl.Enabled = Not Mode
'If Ctrl.Name = "btnAddClub" Then
' Ctrl.Focus()
' Else
' Ctrl.Enabled = True
' End If
End If
Next Ctrl
End Sub
Sub SetSearchBox()
If tstbSearchSupplier.Text = "" Then
tstbSearchSupplier.Text = "Type here to search"
tstbSearchSupplier.ForeColor = Color.Gray
End If
End Sub
Private Sub frmSupplier_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DsClub.CLUB' table. You can move, or remove it, as needed.
SearchSM("")
Locked(True)
SetSearchBox()
End Sub
Private Sub tsbtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnAdd.Click
tsbtnAdd.Enabled = False
tsbtnSave.Enabled = True
tsbtnCancel.Enabled = True
AddMode = True
Locked(False)
End Sub
Private Sub tsbtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnSave.Click
'If txtBoxName.Text = "" Or txtBoxAddress.Text = _
' "" Or txtBoxContact.Text = "" Then
'MessageBox.Show("Please fill up all the data.", "Sales and Inventory Sytem", _
' MessageBoxButtons.OK, MessageBoxIcon.Information)
If IsConnected() = True Then
SaveEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
'End If
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
Locked(True)
End If
End Sub
Private Sub lvwSupplier_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseClick
lvwSupplier_MouseDoubleClick(sender, e)
End Sub
Private Sub lvwSupplier_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseDoubleClick
tsbtnSave.Enabled = True
tsbtnDelete.Enabled = True
tsbtnAdd.Enabled = True
tsbtnCancel.Enabled = True
Locked(False)
With txtboxID
.BackColor = Color.White
.ForeColor = Color.Red
.Enabled = True
End With
With lvwSupplier.SelectedItems(0)
Ctrl = .SubItems(0).Text
txtBoxName.Text = .SubItems(1).Text
txtBoxAddress.Text = .SubItems(2).Text
txtBoxContact.Text = .SubItems(3).Text
'txtYear_Level.Text = .SubItems(4).Text
End With
txtboxID.Text = Ctrl 'gets the Ctrl
' for the family name
'Dim strFamilyName As String = txtDummyName.Text
'Dim strArrFamilyName() As String
'Dim countFamilyName As Integer
'strArrFamilyName = strFamilyName.Split(",")
'For countFamilyName = 0 To strArrFamilyName.Length - 1
'txtFamilyName.Text = (strArrFamilyName(countFamilyName))
'Exit For
'Next
'for the family name
'for the first name
'Dim strFirstName As String = txtDummyName.Text
'Dim strArrFirstName() As String
'Dim countFirstName As Integer
'Dim test As String
'strArrFirstName = strFirstName.Split(", ")
' For countFirstName = 1 To strArrFirstName.Length - 1
'test = (strArrFirstName(countFirstName)).TrimStart.TrimEnd
'Dim myString As String = test
'Dim myChar As Char() = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, _
' "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
' "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c, "."c}
'Dim NewString As String = myString.TrimEnd(myChar)
'txtFirstName.Text = NewString.TrimEnd
'Exit For
'Next
' for the first name
'for the middle name
'Dim strMiddleInitial As String = Microsoft.VisualBasic.Right(txtDummyName.Text, 2)
'txtMiddleInitial.Text = strMiddleInitial.TrimStart
'for the middle name
End Sub
Private Sub tsbtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnDelete.Click
If IsConnected() = True Then
DeleteEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
End If
tsbtnAdd.Enabled = True
tsbtnEdit.Enabled = True
tsbtnSave.Enabled = False
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
End Sub
Private Sub tsbtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnCancel.Click
Locked(True)
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
tsbtnSave.Enabled = False
tsbtnAdd.Enabled = True
End Sub
Private Sub tstbSearchSupplier_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.Click
If tstbSearchSupplier.Text = "Type here to search" Then
tstbSearchSupplier.Text = ""
tstbSearchSupplier.ForeColor = Color.Black
ElseIf tstbSearchSupplier.Text <> "" Then
tstbSearchSupplier.SelectAll()
End If
End Sub
Private Sub tstbSearchSupplier_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles tstbSearchSupplier.KeyDown
End Sub
Private Sub tstbSearchSupplier_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tstbSearchSupplier.KeyPress
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
'Checks first if system is connected then if true, continues to search
If IsConnected() = True Then Call SearchSM(tstbSearchSupplier.Text)
e.Handled = True
ElseIf e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Back) Then
SearchSM("")
'e.Handled = True
End If
End Sub
Private Sub txtboxID_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtboxID.KeyPress
Dim strAllowableChars As String
strAllowableChars = "0123456789"
If InStr(strAllowableChars, e.KeyChar) = 0 And Asc(e.KeyChar) <> 8 Then
e.Handled = True
End If
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
txtBoxName.Focus()
e.Handled = True
End If
End Sub
Private Sub txtBoxName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxName.KeyPress
End Sub
Private Sub txtBoxAddress_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxAddress.KeyPress
End Sub
Private Sub txtBoxContact_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxContact.KeyPress
End Sub
Private Sub tstbSearchSupplier_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.LostFocus
SetSearchBox()
End Sub
Private Sub tstbSearchSupplier_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.TextChanged
Try
Dim sItem As ListViewItem = lvwSupplier.FindItemWithText(tstbSearchSupplier.Text, True, 0)
If Not sItem Is Nothing Then
With lvwSupplier.Items(sItem.Index)
.EnsureVisible()
.Selected = True
End With
End If
Catch ex As Exception
End Try
End Sub
Private Sub lvwSupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvwSupplier.SelectedIndexChanged
End Sub
Private Sub ToolStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ToolStrip1.ItemClicked
End Sub
End Class
Subscribe to:
Posts (Atom)