示例#1
0
 private void cBank_SelectedIndexChanged(object sender, EventArgs e)
 {
     tBank.Text     = MyModules.GetIt4Me(cBank.Text, " - ");
     tBankCode.Text = MyModules.Mid(cBank.Text, Microsoft.VisualBasic.Strings.Len(tBank.Text) + 3, -1);
 }
示例#2
0
        private void cmdAttach_Click(object sender, EventArgs e)
        {
            try
            {
                string connectStr = null;
                if (string.IsNullOrEmpty(txtDBName.Text.Trim()))
                {
                    MessageBox.Show("Pls. select the database", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (chkWinAuthen.Checked)
                {
                    connectStr = "workstation id=" + cboServerName.Text + ";packet size=4096;data source=" + cboServerName.Text + ";Integrated Security=True;initial catalog=master";
                }
                else
                {
                    connectStr = "workstation id=" + cboServerName.Text + ";packet size=4096;user id=" + txtUserID.Text + ";pwd=" + txtPassword.Text + ";data source=" + cboServerName.Text + ";persist security info=False;initial catalog=master";
                }

                SqlConnection SqlCn = new SqlConnection(connectStr);
                SqlCn.Open();
                if (txtAttachName.Text.Trim().Length == 0)
                {
                    MessageBox.Show("Pls. choose a valid data file", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                // ERROR: Not supported in C#: OnErrorStatement

                //'Dim myTrans As SqlTransaction
                //'myTrans = SqlCn.BeginTransaction(IsolationLevel.Serializable, "MyTrans")
                SqlCommand myCommand = new SqlCommand("FetchUserAccessByPwd", SqlCn);



                //'myCommand.Transaction = myTrans

                myCommand.CommandText = "EXEC sp_detach_db @dbname = '" + cboavaliableDB.Text + "'";
                myCommand.ExecuteNonQuery();

                // ERROR: Not supported in C#: OnErrorStatement

                myCommand.CommandText = "EXEC sp_attach_db @dbname = N'" + txtAttachName.Text + "',@filename1 = N'" + txtDBName.Text.Trim() + "',@filename2 = N'" + MyModules.Mid((txtDBName.Text.Trim()), 1, Microsoft.VisualBasic.Strings.InStr(txtDBName.Text.Trim(), ".") - 1) + ".ldf" + "'";
                myCommand.ExecuteNonQuery();
                //'myTrans.Commit()

                myCommand.Connection.Close();
                myCommand.Dispose();
                SqlCn.Close();
                SqlCn.Dispose();
                MessageBox.Show("Successfully Attached", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
示例#3
0
        private void cmdUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (RadStudent.Checked == true)
                {
                    if (Dgrid.Columns.Count != DGridReal.Columns.Count - 3)
                    {
                        MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
                }
                else
                {
                    if (Dgrid.Columns.Count != DGridReal.Columns.Count - 2)
                    {
                        MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
                }
                if (cGrp.Text == "" && RadStudent.Checked == true)
                {
                    MessageBox.Show("Please select school", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (Dgrid.RowCount < 1)
                {
                    MessageBox.Show("No record to upload", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                SqlTransaction myTrans = null;
                SqlConnection  cnSQL   = new SqlConnection(MyModules.strConnect);
                SqlCommand     cmSQL   = new SqlCommand();
                cmSQL.Connection = cnSQL;
                cnSQL.Open();


                if (RadStudent.Checked == true)
                {
                    myTrans           = cnSQL.BeginTransaction();
                    cmSQL.Transaction = myTrans;
                    for (int i = 0; i < Dgrid.RowCount - 1; i++)
                    {
                        cmSQL.Parameters.Clear();
                        cmSQL.CommandText = "InsertRegisterStudent";
                        cmSQL.CommandType = CommandType.StoredProcedure;
                        cmSQL.Parameters.AddWithValue("@Sn", 0);

                        cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@State", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[10, i].Value == null) ? "" : Dgrid[10, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[11, i].Value == null) ? "" : Dgrid[11, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[12, i].Value == null) ? "" : Dgrid[12, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[13, i].Value == null) ? "" : Dgrid[13, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[14, i].Value == null) ? "" : Dgrid[14, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@InActive", false);
                        cmSQL.Parameters.AddWithValue("@SchoolID", MyModules.GetIt4Me(cGrp.Text, " - "));
                        cmSQL.Parameters.AddWithValue("@Degree", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@CourseOfStudy", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@StartYear", ((Dgrid[4, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[4, i].Value).ToString()));
                        cmSQL.Parameters.AddWithValue("@EndYear", ((Dgrid[5, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[5, i].Value).ToString()));
                        cmSQL.Parameters.AddWithValue("@LGA", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@SchName", MyModules.Mid(cGrp.Text, Microsoft.VisualBasic.Strings.Len(MyModules.GetIt4Me(cGrp.Text, " - ")) + 3, -1));
                        cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());

                        cmSQL.ExecuteNonQuery();
                    }
                }
                if (RadSchool.Checked == true)
                {
                    myTrans           = cnSQL.BeginTransaction();
                    cmSQL.Transaction = myTrans;
                    for (int i = 0; i < Dgrid.RowCount - 1; i++)
                    {
                        cmSQL.Parameters.Clear();



                        cmSQL.CommandText = "InsertRegisterSchool";
                        cmSQL.CommandType = CommandType.StoredProcedure;
                        cmSQL.Parameters.AddWithValue("@Sn", 0);

                        cmSQL.Parameters.AddWithValue("@SchName", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@SchAddress", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@State", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@InActive", false);
                        cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());

                        cmSQL.ExecuteNonQuery();
                    }
                }

                if (RadVendor.Checked == true)
                {
                    myTrans           = cnSQL.BeginTransaction();
                    cmSQL.Transaction = myTrans;
                    for (int i = 0; i < Dgrid.RowCount - 1; i++)
                    {
                        cmSQL.Parameters.Clear();
                        cmSQL.CommandText = "InsertRegisterVendor";
                        cmSQL.CommandType = CommandType.StoredProcedure;
                        cmSQL.Parameters.AddWithValue("@Sn", 0);
                        cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Source", "VENDOR");
                        cmSQL.Parameters.AddWithValue("@InActive", false);
                        cmSQL.Parameters.AddWithValue("@Category", "");
                        cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());
                        cmSQL.ExecuteNonQuery();
                    }
                }

                if (radStaff.Checked == true)
                {
                    myTrans           = cnSQL.BeginTransaction();
                    cmSQL.Transaction = myTrans;
                    for (int i = 0; i < Dgrid.RowCount - 1; i++)
                    {
                        cmSQL.Parameters.Clear();
                        cmSQL.CommandText = "InsertRegisterVendor";
                        cmSQL.CommandType = CommandType.StoredProcedure;
                        cmSQL.Parameters.AddWithValue("@Sn", 0);
                        cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@Source", "STAFF");
                        cmSQL.Parameters.AddWithValue("@InActive", false);
                        cmSQL.Parameters.AddWithValue("@Category", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
                        cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());

                        cmSQL.ExecuteNonQuery();
                        //                str = "SELECT [Sn] AS [RefNo],[Name]0 ,[TINNo]1,[Address]2,[EmailAddress]3 AS [Email Address]
                        //                    ,[Telephone]4 ,[BankName]5,[BankAcctNo]6,[BankCode]7 AS [Bank Code],[BankAddress] 8AS [Bank Address],
                        //[Remark],[InActive] FROM RegisterVendor ORDER BY [Name]";
                    }
                }

                myTrans.Commit();

                if (RadStudent.Checked)
                {
                    LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - "));
                }
                else
                {
                    LoadRealData("");
                }
                MessageBox.Show("Successfull!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                if (ex.ToString().Contains("Violation of PRIMARY KEY constraint"))
                {
                    MessageBox.Show("Duplicate record exist", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    // myTrans.Rollback();
                }
                else
                {
                    MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }