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);
}
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);
}
}
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);
}
}
}