CODE

-----------------Create table

--create table info-- (-- id int,-- fname varchar(50),-- lname varchar(50),-- phone varchar(50),-- address varchar(50),-- city varchar(50),-- state varchar(50),-- zip varchar(50),--constraint info_pk primary key (id));
--create table Department-- (Dep_Id varchar(50),-- Dep_name varchar(50),--Constraint Department_PK primary key (Dep_Id));
--create table Course-- (Course_Id varchar(50),-- Course_No varchar(50),-- Descrip Varchar(50),-- Dep_Id Varchar(50),--Constraint Course_PK primary key(Course_Id),--Constraint Course_FK foreign key (Dep_Id) references Department(Dep_Id));

----------Insert data from other database

--use pracC--go--insert into info(fname,lname,phone,address,city,state,zip) --(--select au_fname,au_lname,phone,address,city,state,zip--from pubs.dbo.authors--)

------------Create procedure
--CREATE PROCEDURE addinfo-- @id varchar(50),-- @lname varchar(50),-- @fname varchar(50),-- @phone varchar(50),-- @add varchar(50),-- @city varchar(50),-- @state varchar(50),-- @zip varchar(50)-- --AS-- if not exists(select * from info where id = @id)-- -- begin -- insert into info-- (-- lname,-- fname,-- phone,-- address,-- city,-- state,-- zip-- )-- values-- (-- @lname,-- @fname,-- @phone,-- @add,-- @city,-- @state,-- @zip-- )-- -- end--else-- begin-- update info -- set -- lname = @lname,-- fname=@fname,-- phone = @phone,-- address = @add,-- city=@city,-- state = @state,-- zip = @zip-- where id like @id-- -- end

--create procedure exescalar

-- @id varchar(50)--as--begin-- select lname from info where id like @id--end

---ClsFunction----------

Imports System.WindowsImports System.Text.RegularExpressions
Public Class ClsFunction Inherits ClsConnection
Sub listview(ByVal red As SqlClient.SqlDataReader, ByVal lv As ListView) lv.Items.Clear() While red.Read Dim i As Integer = 1 Dim lvi As ListViewItem = New ListViewItem(red(0).ToString()) While i <= red.FieldCount - 1 lvi.SubItems.Add(red(i).ToString()) i = i + 1 End While lv.Items.Add(lvi) End While If lv.Items.Count > 0 Then lv.Items(0).Selected = True End If End Sub
Function reader(ByVal str As String) Return sqldatareader(str) End Function
Sub fillcombo(ByVal cbx As ComboBox, ByVal red As SqlClient.SqlDataReader, ByVal column As String) While red.Read cbx.Items.Add(red(column).ToString()) End While End Sub Sub clearfields(ByVal cnt As Control) For Each c As Control In cnt.Controls If TypeOf c Is TextBox Then c.Text = "" End If 'If TypeOf c Is ComboBox Then ' c.Text = -1 'End If Next End Sub
Sub validate(ByVal txtphone As TextBox) Dim valid As Boolean = True If Regex.Match(txtphone.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success Then MessageBox.Show("Please enter a valid last name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error) txtphone.Focus() valid = False Return End If 'bool valid = true;
' If (!Regex.Match(txtFname.Text, "^[A-Za-z]+$").Success) Then ' { ' valid = false; ' MessageBox.Show("Please enter a valid first name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtFname.Focus(); ' return; ' }
' If (!Regex.Match(txtLname.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid last name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtLname.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtAdres.Text, "^([0-9]+\\s+([a-zA-Z]+[a-zA-Z]+\\s[a-zA-z]+))*$").Success) Then ' { ' MessageBox.Show("Please enter a valid address", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtAdres.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtSurb.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid suburb", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtSurb.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtPcode.Text, "^([0-9]+)$").Success) Then ' { ' MessageBox.Show("Please enter a valid post code", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information); ' txtSurb.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtState.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid state", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtState.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtCountry.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid country", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtCountry.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtHometel.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtHometel.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtOfficetel.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtOfficetel.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtMob.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtMob.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtMail.Text, "^(([\\w-]+)@([\\w-]+\\.)+[A-Za-z]{2,3})*$").Success) Then ' { ' MessageBox.Show("Please enter a valid email address name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtMail.Focus(); ' valid = false; ' return; ' }
End Sub
Public Function number(ByVal Key As String) As Boolean If (Key >= 48 And Key <= 57) Or Key = 8 Or Key = 46 Or Key = 32 Then number = False Else number = True End If End Function Public Function character(ByVal Key As String) As Boolean If (Key >= 48 And Key <= 57) Then character = True Else character = False End If End FunctionEnd Class

-------ClsConnection------------
Public Class ClsConnection Public con As New SqlClient.SqlConnection Sub connection() con = New SqlClient.SqlConnection("Data Source=MIKEL;Initial Catalog=pracC;Integrated Security=True") ' con = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True")
End Sub
Function sqldatareader(ByVal str As String) Try connection() Dim cm As New SqlClient.SqlCommand(str, con) Dim red As SqlClient.SqlDataReader con.Open() red = cm.ExecuteReader() Return red Catch ex As Exception System.Windows.Forms.MessageBox.Show(ex.Message, "Error") End Try End Function
Sub execommand(ByVal str As String) Try connection() Dim cm As New SqlClient.SqlCommand(Str, con) con.Open() cm.ExecuteNonQuery() Finally If con.State = ConnectionState.Open Then con.Close() End If End Try
End SubEnd Class

----ClsProcedure------
Imports System.WindowsPublic Class ClsProcedure Inherits ClsConnection Public selectinfo As String Sub insertinfo(ByVal tfname As String, ByVal tlname As String, ByVal tphone As String, ByVal taddress As String, ByVal tcity As String, ByVal tstate As String, ByVal tzip As String, ByVal str As String) Try If str = "" Then execommand("insert into info values('" & tfname & "','" & tlname & "','" & tphone & "','" & taddress & "','" & tcity & "', '" & tstate & "','" & tzip & "')") MessageBox.Show("saved!!!") Else execommand("update info set lname = '" & tlname & "', fname = '" & tfname & "', phone = '" & tphone & "', address='" & taddress & "', state = '" & tstate & "', city = '" & tcity & "', zip = '" & tzip & "' where id like '" & str & "'") MessageBox.Show("Updated!!!") End If Catch ex As Exception MessageBox.Show(ex.Message, "error") End Try End Sub Sub deleteinfo(ByVal id As String) Try execommand("delete from info where id like '" & id & "'") MessageBox.Show("deleted!!!") Catch ex As Exception MessageBox.Show(ex.Message, "error") End Try End Sub Sub selectid(ByVal str As String) If str = "" Then selectinfo = "select * from info order by lname" Else selectinfo = "select * from info where id like '" & str & "'" End If End Sub
End Class

-----FORM CODE----------

Public Class Form1 Dim func As New ClsFunction Dim pro As New ClsProcedure Dim red As SqlClient.SqlDataReader Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load loads() Timer1.Start() End Sub
Private Sub Timer1_Tick_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick Me.ToolStripStatusLabel1.Text = "Date: " + Date.Today & vbCrLf & " Time: " + TimeOfDay End Sub
Sub loads() pro.selectid("") red = func.reader(pro.selectinfo) func.listview(red, Me.ListView1)
red = func.reader(pro.selectinfo) func.fillcombo(Me.ComboBox1, red, "lname") End Sub

Private Sub ButSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButSave.Click If MessageBox.Show("Add...?", "add", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then pro.insertinfo(Me.TextFirstName.Text, Me.TextLastName.Text, Me.TextPhone.Text, Me.TextAddress.Text, Me.TextCity.Text, Me.TextState.Text, Me.TextZip.Text, Me.TextBox8.Text) ButClear.PerformClick() loads() End If
End Sub
Private Sub Butdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butdel.Click pro.deleteinfo(Me.ListView1.SelectedItems.Item(0).Text) loads() End Sub
Private Sub ButClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButClear.Click func.clearfields(Me.GroupBox1) End Sub
Private Sub ListView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.DoubleClick pro.selectid(Me.ListView1.SelectedItems.Item(0).Text) red = func.reader(pro.selectinfo) While red.Read Me.TextBox8.Text = red("id").ToString() Me.TextFirstName.Text = red("fname").ToString() Me.TextLastName.Text = red("lname").ToString() Me.TextAddress.Text = red("address").ToString() Me.TextCity.Text = red("city").ToString() Me.TextPhone.Text = red("phone").ToString() Me.TextState.Text = red("state").ToString() Me.TextZip.Text = red("zip").ToString() End While End Sub
Private Sub TextPhone_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextPhone.KeyPress e.Handled = func.number(Asc(e.KeyChar)) End Sub
Private Sub TextLastName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextLastName.KeyPress e.Handled = func.character(Asc(e.KeyChar)) End SubEnd Class

----Select statemnt for login---
Select FName from user_t where UserName like '" & Me.TextBox1.Text & "' and Pass like '" & Me.TextBox2.Text & "' and UserType like '" & Me.ComboBox1.Text & "'

The insert command - VB.Net code and save to SQL Server 2005

A simple code on how to connect sql 2005 from VB.Net via SQL connection string, and the use of ExecuteNonQuery() method to execute the command and the try and catch to catch errors during runtime.

Example information:

SQL data:

Database name: user

Table name: user_t

Columns: user_id,lname,fname,username,password,usertype

Controls:

textboxes: textID = for Id

textlname = for last name

textfname = for first name

textusername = for user name

textpassword = for password

combobox: comboUsertype = for user type

Code:

Dim con As New SqlClient.SqlConnection

Dim strCon as string = "Data Source=MIKEL\SQLEXPRESS;Initial Catalog=user_t;Integrated Security=True"

Dim strCommand as string = “Insert into user_t (user_id, lname, fname, username, password, usertype) values (‘”& me.textID.text &”’,‘” & me.textlname.text &”’, ‘” &me.fname.text &”’, ‘”&me.textusername.text &”‘,‘”& me.textpassword.text &”’,‘”&me.comboUsertype.text &”’)”

Sub insert()

Try

con.ConnectionString = strCon

Dim cm As New SqlClient.SqlCommand(strCommand, con)

con.Open()

cm.ExecuteNonQuery()

MsgBox("Succesfully saved…")

Catch ex As Exception

MessageBox.Show(ex.Message, "Error")

Finally

If con.State = ConnectionState.Open Then

con.Close()

End If

End Try

End sub

Basic insert, delete, update command in SQL

Hi... these are just a basic insert, delete, and update command in database SQL (200, 2005) .

Example data

Database name: user

Table name: user_t

Columns: user_id,lname,fname,username,password,usertype

____________________________________________________________________________

Insert command

Insert into user_t (user_id, lname, fname, username, password, usertype) values (@parameter1, @parameter2, @parameter3, @parameter4, @parameter5, @parameter6)

It means, @parameter1 is a value that points to user_id, @parameter2 points to lname and so on…..

Update command

Update user_t set lname = @parameter1, fname = @parameter2, username = @parameter3, password = @parameter4, usertype = @parameter5 where user_id like @parameter6

Delete command

Delete from user_t where user_id like @parameter1

To download SQL server 2005 express edition and .Net framework 2.0 click here.

To download Visual Studio 2005 click here.

My start in .net programming

I am practicing .Net programming in short I am a beginner. Its quiet difficult for me without any helpful resources and friends to ask for, I know, to be a good programmer is not just a drag and drop like what I am doing in Visual studio. It takes time, effort and patience. On the start of my practice, I always get mystified and my mind is fully loaded of questions. I have no outline if where I should start and where to end, I am just applying ideas pop out in my mind.

I want to master .Net Programming and I am using Visual Studio 2005 as my IDE. For easy and for prompt learning, I joined online tutorials, forum and I have downloaded free e-books and videos. By the way, somebody knows where can I download free VB.Net tutorial videos? :-) I been trying to search in the internet but all are for sale. I have only downloaded the ASP.net free tutorials videos from ASP.net website.

For now, VB.Net is my PL and I am working with forms and controls and bind them with data from the database. I am using SQL Server 2005 as my backend database.

I have only three questions “what if”, “how” and “where to find the solution”.

Check out for my next post: my practice status and discoveries

My first Post

this would be my first post and this would be my personal blog.

Hello