Guys,
Some one please change this DB stuff to MySQL thing. I m doing all rounder work here Design, Deveop, Test, Frontend, Backend, Architect..
Module1.vb
Module Module1
Public con As New OleDb.OleDbConnection
Public cmd As New OleDb.OleDbCommand
Public da As New OleDb.OleDbDataAdapter
Public ds As New DataSet
Public constr As String
Public str_empid As String
Sub connect_to_db()
constr = "Provider=msdaora;Data Source=eqcarod2;User Id=car_emea_dbo;Password=car_emea_dbo;"
con.ConnectionString = constr
cmd.Connection = con
End Sub
Sub init()
str_empid = "C:\Temp\"
End Sub
Sub clear_text(ByVal frm As Form)
Dim c As Control
For Each c In frm.Controls
If TypeOf c Is TextBox Then
c.Text = ""
End If
Next
End Sub
Public Function f_gen_emp_no() As Char
cmd.CommandType = CommandType.Text
cmd.CommandText = "select F_GET_EMP_NO from dual"
cmd.Parameters.Add("retrn_empno", OleDb.OleDbType.LongVarChar)
da.SelectCommand = cmd
da.SelectCommand.Connection = con
da.Fill(ds)
MsgBox("emp no=" & (ds.Tables(0).Rows(0).Item(0)))
Return ds.Tables(0).Rows(0).Item(0)
End Function
End Module
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim p, in_FNAME, in_LNAME, in_MI, IN_DOB, in_Hphn, in_Ophn, IN_MOB, in_Pemail, _
in_Oemail, in_addr1, in_addr2, in_city, in_dist, in_state, in_country, in_zip, _
IN_DOJ, in_img_locn As New OleDb.OleDbParameter
cmd.Parameters.Add("in_FNAME", OleDb.OleDbType.LongVarChar).Value = txtFname.Text
cmd.Parameters.Add("in_LNAME", OleDb.OleDbType.LongVarChar).Value = txtLname.Text
cmd.Parameters.Add("in_MI", OleDb.OleDbType.LongVarChar).Value = txtMname.Text
cmd.Parameters.Add("IN_DOB", OleDb.OleDbType.DBDate).Value = Convert.ToDateTime(dt_DOB.Text)
cmd.Parameters.Add("in_Hphn", OleDb.OleDbType.Integer).Value = Convert.ToInt32(txtHomePhn.Text)
cmd.Parameters.Add("in_Ophn", OleDb.OleDbType.Integer).Value = Convert.ToInt32(txtOffPhn.Text)
cmd.Parameters.Add("in_Pemail", OleDb.OleDbType.LongVarChar).Value = txtPersnlEmail.Text
cmd.Parameters.Add("in_Oemail", OleDb.OleDbType.LongVarChar).Value = txtOffEmail.Text
cmd.Parameters.Add("in_addr1", OleDb.OleDbType.LongVarChar).Value = txtAddr1.Text
cmd.Parameters.Add("in_addr2", OleDb.OleDbType.LongVarChar).Value = txtAddr2.Text
cmd.Parameters.Add("in_city", OleDb.OleDbType.LongVarChar).Value = txtCity.Text
cmd.Parameters.Add("in_dist", OleDb.OleDbType.LongVarChar).Value = txtDist.Text
cmd.Parameters.Add("in_state", OleDb.OleDbType.LongVarChar).Value = txtState.Text
cmd.Parameters.Add("in_country", OleDb.OleDbType.LongVarChar).Value = txtCntry.Text
cmd.Parameters.Add("in_zip", OleDb.OleDbType.Integer).Value = Convert.ToInt32(txtZip.Text)
cmd.Parameters.Add("IN_DOJ", OleDb.OleDbType.DBDate).Value = Convert.ToDateTime(dt_DOJ.Text)
' cmd.Parameters.Add("IN_SCH_EST_DT", OleDb.OleDbType.DBDate).Value = Convert.ToDateTime(dt_tm_Schestdt.Text)
cmd.Parameters.Add("in_img_locn", OleDb.OleDbType.LongVarChar).Value = str_empid
'For Each p In cmd.Parameters
' p.Direction = ParameterDirection.Input
'Next
con.Open()
cmd.ExecuteNonQuery()
con.Close()
MsgBox("One employee addded ", MsgBoxStyle.Information, "SIS")
End Sub
___________________________________________________
CREATE OR REPLACE FUNCTION F_GET_EMP_NO RETURN VARCHAR2 IS V_MAX_EMPNO NUMBER;
v_empno varchar2(10):='E0001';
BEGIN
SELECT SUBSTR(MAX(Emp_No),2) INTO V_MAX_EMPNO FROM EMPLOYEE_DETAILS;
if V_MAX_EMPNO is null then
RETURN v_empno;
end if;
V_MAX_EMPNO:=V_MAX_EMPNO+1;
RETURN 'E00' V_MAX_EMPNO ;
EXCEPTION
WHEN NO_DATA_FOUND then
RETURN v_empno;
END;
------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION F_CALC_AGE (in_dob date) RETURN number IS
v_age number;
begin
select extract(year from sysdate) - extract(year from in_dob) into v_age from dual;
return v_age;
end;
------------------------------------------------------------------------------------------------
create table Employee_Details(
Emp_No varchar2(10),
F_NAME varchar2(20),
L_NAME varchar2(20),
M_NAME varchar2(20),
age number(2),
Dt_DOB DATE,
Home_Phone number,
Office_Phone number,
Mobile number,
personal_eMail varchar2(20),
official_eMail varchar2(20),
Address1 varchar2(20),
Address2 varchar2(20),
City varchar2(20),
District varchar2(20),
State varchar2(20),
Country varchar2(20),
ZIP number,
dt_DOJ date,
dt_DOT date,
Flg_Actv CHAR(1),
img_locn varchar2(500));
create or replace procedure insert_EMPLOYEE_details ( in_FNAME varchar2, in_LNAME varchar2, in_MI varchar2, IN_DOB DATE, in_Hphn number, in_Ophn number, IN_MOB NUMBER, in_Pemail varchar2, in_Oemail varchar2, IN_ADDR1 VARCHAR2,IN_ADDR2 VARCHAR2, in_city varchar2, in_dist varchar2, in_state varchar2, in_country varchar2, in_zip number, IN_DOJ DATE, in_img_locn varchar2) is
begin
--EXEC Insert_EMPLOYEE_details ('in_FNAME', 'in_LNAME', 'in_MI' ,SYSDATE, 1234, 4321, 99860,'in_Pemail','in_Oemail','IN_ADDR1','IN_ADDR2','in_city' , 'in_dist' , 'in_state', 'in_country' , 560085, SYSDATE, 'in_img_locn');
insert into EMPLOYEE_details values(F_GET_EMP_NO(), in_FNAME,in_LNAME ,in_MI , F_CALC_AGE(IN_DOB), IN_DOB , in_Hphn ,in_Ophn , IN_MOB , in_Pemail , in_Oemail , in_addr1, in_addr2, in_city, in_dist, in_state, in_country, in_zip, IN_DOJ ,NULL,'A', in_img_locn);
COMMIT;
end;
