Tables

40         private void btnLoc_Click(object sender, EventArgs e)
41         {
42             SqlConnection con = dal.GetConnect();
43             con.Open();
44             string sql = "select *from SinhVien where MaLop=@MaLop";
45             SqlDataAdapter da = new SqlDataAdapter(sql, con);
46             da.SelectCommand.Parameters.AddWithValue("@MaLop", cmbMaLop.SelectedValue.ToString());
47             DataTable dt = new DataTable();
48             da.Fill(dt);
49             crBaoCao cr = new crBaoCao();
50             ConnectionInfo crConnectionInfo = new ConnectionInfo();
51             crConnectionInfo.ServerName = "Ty-PC";
52             crConnectionInfo.DatabaseName = "CNPM_1";
53             crConnectionInfo.UserID = "sa";
54             crConnectionInfo.Password = "04091990";
55             TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
56             foreach (CrystalDecisions.CrystalReports.Engine.Table table in cr.Database.Tables)
57             {
58                 TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
59                 crTableLogonInfo.TableName = table.Name;
60                 crTableLogonInfo.ConnectionInfo = crConnectionInfo;
61                 crTableLogonInfos.Add(crTableLogonInfo);
62                 table.ApplyLogOnInfo(crTableLogonInfo);
63
64             }
65             crvHocKyHocPhan.LogOnInfo = crTableLogonInfos;
66             cr.SetDataSource(dt);
67             ParameterValues a = new ParameterValues();
68             ParameterDiscreteValue b = new ParameterDiscreteValue();
69             b.Value = cmbMaLop.SelectedValue.ToString();
70             a.Add(b);
71             cr.DataDefinition.ParameterFields["txtMaLop"].ApplyCurrentValues(a);
72             crvHocKyHocPhan.ReportSource = cr;
73             crvHocKyHocPhan.Show();
74         }

19         private void Report_Load(object sender, EventArgs e)
21             {
22                 TS a = new TS();
23                 String strCon = "server=.\\SQLEXPRESS;database=QL_Thitracnghiem;integrated security=true";
24                 SqlConnection con = new SqlConnection(strCon);
25                 SqlCommand cmd = new SqlCommand("select * from THISINH", con);
26                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
27                 adapter.Fill(a, "THISINH");
28                 rptThisinh1.SetDataSource(a.Tables["THISINH"]);
29                 // Lấy dữ liệu từ dataset chèn vào rptThisinh
30
31             }

254   public void NavRecords()
255   {
256
257    STUDID.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][1]);
258    TextBox1.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][2]);
259    TextBox2.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][3]);
260    TextBox3.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][4]);
261    TextBox4.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][5]);
262    TextBox5.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][6]);
263    TextBox6.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][7]);
264    TextBox7.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][8]);
265    TextBox8.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][9]);
266    TextBox9.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][10]);
267    TextBox10.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][11]);
268    TextBox11.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][12]);
269    TextBox12.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][13]);
270    TextBox13.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][14]);
271    TextBox14.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][15]);
272    TextBox15.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][16]);
273    TextBox16.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][17]);
274    TextBox17.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][18]);
275    TextBox18.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][19]);
276    TextBox19.Text = (string) (dsgrade.Tables["grade1"].Rows[inc][20]);
277
278   }

314   public void LAST_Click(System.Object sender, System.EventArgs e)
315   {
316    dsgrade = gradeclass.DataModule.navigate();
317    MaxRows = dsgrade.Tables["grade1"].Rows.Count;
318    if (inc != MaxRows)
319    {
320     inc = MaxRows - 1;
321     NavRecords();
322    }
323    else
324    {
325     MessageBox.Show("Last Record");
326    }
327   }

185   public DataSet navigate(string sortfield)
186   {
187
188    System.Data.OleDb.OleDbConnection conn = GetConnection();
189    DataSet ds = new DataSet();
190
191    try
192    {
193
194     int inc;
195     int MaxRows;
196
197     string sql = "select * from table1";
198     System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
199
200     try
201     {
202      da.Fill(ds, "grade1");
203      MaxRows = ds.Tables["grade1"].Rows.Count;
204      inc = -1;
205
206
207     }
208     finally
209     {
210      da.Dispose();
211     }
212
213     return ds;
214    }
215    finally
216    {
217     conn.Close();
218     conn.Dispose();
219    }
220   }

131         private void Autocomplete()
132         {
133             try
134             {
135                 con = new SqlConnection(cs.DBConn);
136                 con.Open();
137                 SqlCommand cmd = new SqlCommand("SELECT distinct Categoryname FROM Category", con);
138                 DataSet ds = new DataSet();
139                 SqlDataAdapter da = new SqlDataAdapter(cmd);
140                 da.Fill(ds, "Category");
141                 AutoCompleteStringCollection col = new AutoCompleteStringCollection();
142                 int i = 0;
143                 for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
144                 {
145                     col.Add(ds.Tables[0].Rows[i]["Categoryname"].ToString());
146
147                 }
148                 txtCategoryName.AutoCompleteSource = AutoCompleteSource.CustomSource;
149                 txtCategoryName.AutoCompleteCustomSource = col;
150                 txtCategoryName.AutoCompleteMode = AutoCompleteMode.Suggest;
151
152                 con.Close();
153             }
154             catch (Exception ex)
155             {
156                 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
157             }
158         }

93         private void Button1_Click(object sender, EventArgs e)
94         {
95             try
96             {
97             GroupBox3.Visible = true;
98             con = new SqlConnection(cs.DBConn);
99             con.Open();
100             cmd = new SqlCommand("SELECT RTRIM(invoiceNo) as [Order No],RTRIM(InvoiceDate) as [Order Date],RTRIM(Invoice_Info.CustomerID) as [Customer ID],RTRIM(CustomerName) as [Customer Name],RTRIM(SubTotal) as [SubTotal],RTRIM(VATPer) as [Vat+ST %],RTRIM(VATAmount) as [VAT+ST Amount],RTRIM(DiscountPer) as [Discount %],RTRIM(DiscountAmount) as [Discount Amount],RTRIM(GrandTotal) as [Grand Total],RTRIM(TotalPayment) as [Total Payment],RTRIM(PaymentDue) as [Payment Due],RTRIM(PaymentType) as [Payment Type],RTRIM(Status) as [Status],Remarks from Invoice_Info,Customer where Invoice_Info.CustomerID=Customer.CustomerID and InvoiceDate between @d1 and @d2 and Customer.CustomerID='" + txtCustomerID.Text + "' order by InvoiceDate desc", con);
101             cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateFrom.Value.Date;
102             cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateTo.Value.Date;
103             SqlDataAdapter myDA = new SqlDataAdapter(cmd);
104             DataSet myDataSet = new DataSet();
105             myDA.Fill(myDataSet, "Invoice_Info");
106             myDA.Fill(myDataSet, "Customer");
107             DataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
108             DataGridView1.DataSource = myDataSet.Tables["Invoice_Info"].DefaultView;
109             Int64 sum = 0;
110             Int64 sum1 = 0;
111             Int64 sum2 = 0;
112
113             foreach (DataGridViewRow r in this.DataGridView1.Rows)
114             {
115                 Int64 i = Convert.ToInt64(r.Cells[9].Value);
116                 Int64 j = Convert.ToInt64(r.Cells[10].Value);
117                 Int64 k = Convert.ToInt64(r.Cells[11].Value);
118                 sum = sum + i;
119                 sum1 = sum1 + j;
120                 sum2 = sum2 + k;
121
122             }
123             TextBox1.Text = sum.ToString();
124             TextBox2.Text = sum1.ToString();
125             TextBox3.Text = sum2.ToString();
126
127             con.Close();
128             }
129         catch (Exception ex)
130             {
131             MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
132             }
133         }

184         private void button4_Click(object sender, EventArgs e)
185         {
186             try
187             {
188                 GroupBox4.Visible = true;
189                 con = new SqlConnection(cs.DBConn);
190                 con.Open();
191                 cmd = new SqlCommand("SELECT RTRIM(invoiceNo) as [Order No],RTRIM(InvoiceDate) as [Order Date],RTRIM(Invoice_Info.CustomerID) as [Customer ID],RTRIM(CustomerName) as [Customer Name],RTRIM(SubTotal) as [SubTotal],RTRIM(VATPer) as [Vat+ST %],RTRIM(VATAmount) as [VAT+ST Amount],RTRIM(DiscountPer) as [Discount %],RTRIM(DiscountAmount) as [Discount Amount],RTRIM(GrandTotal) as [Grand Total],RTRIM(TotalPayment) as [Total Payment],RTRIM(PaymentDue) as [Payment Due],RTRIM(PaymentType) as [Payment Type],RTRIM(Status) as [Status],Remarks from Invoice_Info,Customer where Invoice_Info.CustomerID=Customer.CustomerID and Customer.CustomerID='" + txtCustomerID.Text + "' order by CustomerName,InvoiceDate", con);
192                 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
193                 DataSet myDataSet = new DataSet();
194                 myDA.Fill(myDataSet, "Invoice_Info");
195                 myDA.Fill(myDataSet, "Customer");
196                 DataGridView3.DataSource = myDataSet.Tables["Customer"].DefaultView;
197                 DataGridView3.DataSource = myDataSet.Tables["Invoice_Info"].DefaultView;
198                 Int64 sum = 0;
199                 Int64 sum1 = 0;
200                 Int64 sum2 = 0;
201
202                 foreach (DataGridViewRow r in this.DataGridView3.Rows)
203                 {
204                     Int64 i = Convert.ToInt64(r.Cells[9].Value);
205                     Int64 j = Convert.ToInt64(r.Cells[10].Value);
206                     Int64 k = Convert.ToInt64(r.Cells[11].Value);
207                     sum = sum + i;
208                     sum1 = sum1 + j;
209                     sum2 = sum2 + k;
210                 }
211                 TextBox6.Text = sum.ToString();
212                 TextBox5.Text = sum1.ToString();
213                 TextBox4.Text = sum2.ToString();
214
215                 con.Close();
216             }
217             catch (Exception ex)
218             {
219                 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
220             }
221         }

25         public void GetData()
26         {
27                 try{
28                 con = new SqlConnection(cs.DBConn);
29                 con.Open();
30                 cmd = new SqlCommand( "SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer order by CustomerName", con);
31                 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
32                 DataSet myDataSet = new DataSet();
33                 myDA.Fill(myDataSet, "Customer");
34                 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
35                 con.Close();
36             }
37             catch (Exception ex)
38             {
39                 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
40             }
41        }

65         private void txtCustomers_TextChanged(object sender, EventArgs e)
66         {
67             try
68             {
69                 con = new SqlConnection(cs.DBConn);
70                 con.Open();
71                 cmd = new SqlCommand("SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer where CustomerName like '" + txtCustomers.Text + "%' order by CustomerName", con);
72                 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
73                 DataSet myDataSet = new DataSet();
74                 myDA.Fill(myDataSet, "Customer");
75                 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
76
77                 con.Close();
78             }
79             catch (Exception ex)
80             {
81                 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
82             }
83         }