{"id":858,"date":"2017-01-31T21:32:50","date_gmt":"2017-01-31T12:32:50","guid":{"rendered":"http:\/\/okamurax.com\/?p=858"},"modified":"2017-02-22T08:06:53","modified_gmt":"2017-02-21T23:06:53","slug":"c-%e7%a9%8d%e7%ae%97%e3%83%bb%e5%8e%9f%e4%be%a1%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=858","title":{"rendered":"C# VBA \u7a4d\u7b97\u30fb\u539f\u4fa1\u7ba1\u7406"},"content":{"rendered":"

Form1.cs<\/p>\n

using System;\r\nusing System.Collections.Generic;\r\nusing System.ComponentModel;\r\nusing System.Data;\r\nusing System.Drawing;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Windows.Forms;\r\nusing System.Data.OleDb;\r\n\r\nnamespace \u7a4d\u7b97_\u539f\u4fa1\u7ba1\u7406\r\n{\r\n    public partial class Form1 : Form\r\n    {\r\n        Boolean isLoad = false;\r\n\r\n        private List GetControls(string pattern)\r\n        {\r\n            List l = new List();\r\n            foreach (Control c in this.Controls)\r\n            {\r\n                if (pattern == \"DecimalText\")\r\n                {\r\n                    switch (c.Name)\r\n                    {\r\n                        case \"textBox14\":\r\n                        case \"textBox15\":\r\n                        case \"textBox6\":\r\n                        case \"textBox8\":\r\n                        case \"textBox9\":\r\n                        case \"textBox10\":\r\n                        case \"textBox11\":\r\n                        case \"textBox12\":\r\n                        case \"textBox13\":\r\n                            l.Add(c);\r\n                            break;\r\n                    }\r\n                }\r\n                else if(pattern == \"ProductInputs\")\r\n                {\r\n                    switch (c.Name)\r\n                    {\r\n                        case \"textBox1\":\r\n                        case \"comboBox1\":\r\n                        case \"comboBox2\":\r\n                        case \"comboBox3\":\r\n                        case \"textBox14\":\r\n                        case \"textBox15\":\r\n                        case \"textBox4\":\r\n                        case \"textBox5\":\r\n                        case \"textBox6\":\r\n                        case \"textBox7\":\r\n                        case \"textBox8\":\r\n                        case \"textBox9\":\r\n                        case \"textBox10\":\r\n                        case \"textBox11\":\r\n                        case \"textBox12\":\r\n                        case \"textBox13\":\r\n                        case \"textBox16\":\r\n                            l.Add(c);\r\n                            break;\r\n                    }\r\n                }\r\n                else if (pattern == \"PartsInput\")\r\n                {\r\n                    switch (c.Name)\r\n                    {\r\n                        case \"textBox2\":\r\n                        case \"textBox18\":\r\n                        case \"textBox19\":\r\n                        case \"textBox20\":\r\n                        case \"textBox21\":\r\n                        case \"textBox3\":\r\n                        case \"textBox17\":\r\n                            l.Add(c);\r\n                            break;\r\n                    }\r\n                }\r\n            }\r\n            return l;\r\n        }\r\n\r\n        private string BuildQuery(string pattern)\r\n        {\r\n            if (pattern == \"\u65b0\u898f\")\r\n            {\r\n                return \"INSERT INTO \u88fd\u54c1 (\u5411\u5148,\u578b\u5f0f,\u88fd\u4f5c\u5148,\u8ca9\u58f2\u4fa1\u683c,\u55b6\u696d\u624b\u914d\u5206,\u5916\u6ce8\u8cbb,\u8f38\u9001\u8cbb\u8377\u53d7,\u8f38\u9001\u8cbb\u7d0d\u54c1,\u8a66\u9a13\u8cbb,\u54c1\u8a3c\u8cbb\u7528,\u54c1\u8a3c\u7d4c\u8cbb,\u793e\u5185\u88fd\u4f5c\u5206,\u5099\u8003) VALUES (\" +\r\n                        \"'\" + comboBox1.Text + \"',\" +\r\n                        \"'\" + comboBox2.Text + \"',\" +\r\n                        \"'\" + comboBox3.Text + \"',\" +\r\n                        \"'\" + textBox14.Text + \"',\" +\r\n                        \"'\" + textBox15.Text + \"',\" +\r\n                        \"'\" + textBox6.Text + \"',\" +\r\n                        \"'\" + textBox8.Text + \"',\" +\r\n                        \"'\" + textBox9.Text + \"',\" +\r\n                        \"'\" + textBox10.Text + \"',\" +\r\n                        \"'\" + textBox11.Text + \"',\" +\r\n                        \"'\" + textBox12.Text + \"',\" +\r\n                        \"'\" + textBox13.Text + \"',\" +\r\n                        \"'\" + textBox16.Text + \"')\";\r\n            }\r\n            else if (pattern == \"\u66f4\u65b0\")\r\n            {\r\n                return \"UPDATE \u88fd\u54c1 SET \" +\r\n                        \"\u5411\u5148 = '\" + comboBox1.Text + \"',\" +\r\n                        \"\u578b\u5f0f = '\" + comboBox2.Text + \"',\" +\r\n                        \"\u88fd\u4f5c\u5148 = '\" + comboBox3.Text + \"',\" +\r\n                        \"\u8ca9\u58f2\u4fa1\u683c = '\" + textBox14.Text + \"',\" +\r\n                        \"\u55b6\u696d\u624b\u914d\u5206 = '\" + textBox15.Text + \"',\" +\r\n                        \"\u5916\u6ce8\u8cbb = '\" + textBox6.Text + \"',\" +\r\n                        \"\u8f38\u9001\u8cbb\u8377\u53d7 = '\" + textBox8.Text + \"',\" +\r\n                        \"\u8f38\u9001\u8cbb\u7d0d\u54c1 = '\" + textBox9.Text + \"',\" +\r\n                        \"\u8a66\u9a13\u8cbb = '\" + textBox10.Text + \"',\" +\r\n                        \"\u54c1\u8a3c\u8cbb\u7528 = '\" + textBox11.Text + \"',\" +\r\n                        \"\u54c1\u8a3c\u7d4c\u8cbb = '\" + textBox12.Text + \"',\" +\r\n                        \"\u793e\u5185\u88fd\u4f5c\u5206 = '\" + textBox13.Text + \"',\" +\r\n                        \"\u5099\u8003 = '\" + textBox16.Text + \"' \" +\r\n                        \"WHERE ID = \" + textBox1.Text + \"\";\r\n            }\r\n            else if (pattern ==\"\u524a\u9664\")\r\n            {\r\n                return \"DELETE FROM \u88fd\u54c1 WHERE ID = \" + textBox1.Text + \"\";\r\n            }\r\n            return \"\";\r\n        }\r\n\r\n        private void InitGridView()\r\n        {\r\n            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;\r\n            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;\r\n            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;\r\n            dataGridView1.AllowUserToAddRows = false;\r\n            \/\/dataGridView1.ReadOnly = true;\r\n\r\n            dataGridView1.Font = new Font(\"MS UI Gothic\", 12);\r\n\r\n            dataGridView1.Columns.Add(\"\u90e8\u54c1\u8868ID\", \"\u90e8\u54c1\u8868ID\");\r\n            dataGridView1.Columns.Add(\"\u96c6\u8a08ID\", \"\u96c6\u8a08ID\");\r\n            dataGridView1.Columns.Add(\"\u90e8\u54c1ID\", \"\u90e8\u54c1ID\");\r\n            dataGridView1.Columns.Add(\"\u540d\u79f0\", \"\u540d\u79f0\");\r\n            dataGridView1.Columns.Add(\"\u6750\u8cea\", \"\u6750\u8cea\");\r\n            dataGridView1.Columns.Add(\"\u5bf8\u6cd5\", \"\u5bf8\u6cd5\");\r\n            dataGridView1.Columns.Add(\"\u5358\u4fa1\", \"\u5358\u4fa1\");\r\n            dataGridView1.Columns.Add(\"\u6570\u91cf\", \"\u6570\u91cf\");\r\n\r\n            DataGridViewButtonColumn col = new DataGridViewButtonColumn();\r\n            col.Name = \"\u66f4\u65b0\";\r\n            col.UseColumnTextForButtonValue = true;\r\n            col.Text = \"\u66f4\u65b0\";\r\n            dataGridView1.Columns.Add(col);\r\n\r\n            col = new DataGridViewButtonColumn();\r\n            col.Name = \"\u524a\u9664\";\r\n            col.UseColumnTextForButtonValue = true;\r\n            col.Text = \"\u524a\u9664\";\r\n            dataGridView1.Columns.Add(col);\r\n\r\n            dataGridView1.Columns[\"\u90e8\u54c1\u8868ID\"].Visible = false;\r\n        }\r\n\r\n        public Form1()\r\n        {\r\n            InitializeComponent();\r\n\r\n            LoadMaster();\r\n            InitGridView();\r\n\r\n            \/\/\u8aad\u8fbc\r\n            button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable(\"\u8aad\u8fbc\"); });\r\n            \/\/\u691c\u7d22\r\n            button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable(\"\u691c\u7d22\"); });\r\n            \/\/\u4e00\u89a7\r\n            button8.Click += new EventHandler((object sender, EventArgs e) => {\r\n                Form f = new Form3(this);\r\n                f.StartPosition = FormStartPosition.Manual;\r\n                f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);\r\n                f.ShowDialog();\r\n            });\r\n\r\n            \/\/\u65b0\u898f\r\n            button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges(\"\u65b0\u898f\"); });\r\n            \/\/\u66f4\u65b0\r\n            button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges(\"\u66f4\u65b0\"); });\r\n            \/\/\u524a\u9664\r\n            button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges(\"\u524a\u9664\"); });\r\n\r\n            \/\/\u90e8\u54c1ID\u691c\u7d22\r\n            button5.Click += new EventHandler((object sender, EventArgs e) => {\r\n                Form f = new Form2(this);\r\n                f.StartPosition = FormStartPosition.Manual;\r\n                f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);\r\n                f.ShowDialog();\r\n            });\r\n\r\n            \/\/\u90e8\u54c1\u8ffd\u52a0\r\n            button3.Click += new EventHandler(AddRow);\r\n\r\n            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);\r\n\r\n            textBox1.ForeColor = Color.Red;\r\n\r\n            textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {\r\n                isLoad = false;\r\n                textBox1.ForeColor = Color.Red;\r\n                \r\n            });\r\n        }\r\n\r\n        private Boolean TryDecimal()\r\n        {\r\n            try\r\n            {\r\n                foreach (Control c in GetControls(\"DecimalText\"))\r\n                {\r\n                    if (c.Text == \"\") c.Text = \"0\";\r\n                    Decimal.Parse(c.Text);\r\n                }\r\n                return true;\r\n            }\r\n            catch\r\n            {\r\n                return false;\r\n            }\r\n        }\r\n        \r\n        private Boolean ProductsInputCheck(string sender)\r\n        {\r\n            if (sender == \"\u65b0\u898f\")\r\n            {\r\n                if (textBox1.Text != \"\")\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u5165\u529b\u3055\u308c\u3066\u3044\u307e\u3059\u3002\");\r\n                    return false;\r\n                }\r\n                if (comboBox1.Text == \"\" || comboBox2.Text == \"\" || comboBox3.Text == \"\")\r\n                {\r\n                    MessageBox.Show(\"\u5411\u5148\u30fb\u578b\u5f0f\u30fb\u88fd\u4f5c\u5148\u304c\u7a7a\u767d\u3067\u3059\");\r\n                    return false;\r\n                }\r\n                if (!TryDecimal())\r\n                {\r\n                    MessageBox.Show(\"\u91d1\u984d\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\");\r\n                    return false;\r\n                }\r\n            }\r\n            else if (sender == \"\u66f4\u65b0\")\r\n            {\r\n                if (textBox1.Text == \"\")\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u5165\u529b\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002\");\r\n                    return false;\r\n                }\r\n                if (comboBox1.Text == \"\" || comboBox2.Text == \"\" || comboBox3.Text == \"\")\r\n                {\r\n                    MessageBox.Show(\"\u5411\u5148\u30fb\u578b\u5f0f\u30fb\u88fd\u4f5c\u5148\u304c\u7a7a\u767d\u3067\u3059\");\r\n                    return false;\r\n                }\r\n                if (!isLoad)\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u6b63\u3057\u304f\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\");\r\n                    return false;\r\n                }\r\n                if (!TryDecimal())\r\n                {\r\n                    MessageBox.Show(\"\u91d1\u984d\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\");\r\n                    return false;\r\n                }\r\n            }\r\n            else if (sender == \"\u524a\u9664\")\r\n            {\r\n                if (textBox1.Text == \"\")\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u5165\u529b\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002\");\r\n                    return false;\r\n                }\r\n                if (!isLoad)\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u6b63\u3057\u304f\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\");\r\n                    return false;\r\n                }\r\n            }\r\n            return true;\r\n        }\r\n\r\n        private void DataChanges(string sender)\r\n        {\r\n            if (!ProductsInputCheck(sender)) return;\r\n\r\n            using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n                    OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);\r\n                    cmd.ExecuteNonQuery();\r\n\r\n                    if (sender == \"\u65b0\u898f\")\r\n                    {\r\n                        cmd = new OleDbCommand(\"SELECT @@IDENTITY\", con);\r\n                        using (OleDbDataReader dr = cmd.ExecuteReader())\r\n                        {\r\n                            if (dr.Read()) textBox1.Text = dr[0].ToString();\r\n                        }\r\n                    }\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                    return;\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n            }\r\n\r\n            LoadMaster();\r\n\r\n            if (sender == \"\u524a\u9664\")\r\n            {\r\n                MessageBox.Show(\"\u524a\u9664\u3057\u307e\u3057\u305f\u3002\");\r\n                InputClear();\r\n            }\r\n            else\r\n            {\r\n                LoadTable(sender);\r\n            }\r\n        }\r\n\r\n        private void dgvClick(object sender, DataGridViewCellEventArgs e)\r\n        {\r\n            int col = e.ColumnIndex;\r\n            int row = e.RowIndex;\r\n\r\n            \/\/\u524a\u9664\r\n            if (col == 9 && row >= 0)\r\n            {\r\n                if (!isLoad)\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u6b63\u3057\u304f\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\");\r\n                    return;\r\n                }\r\n\r\n                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n\r\n                using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n                {\r\n                    try\r\n                    {\r\n                        con.Open();\r\n                        OleDbCommand cmd = new OleDbCommand(\"DELETE FROM \u90e8\u54c1\u8868 WHERE \u90e8\u54c1\u8868ID = \" + id + \"\", con);\r\n                        cmd.ExecuteNonQuery();\r\n                    }\r\n                    catch\r\n                    {\r\n                        MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                        return;\r\n                    }\r\n                    finally\r\n                    {\r\n                        con.Close();\r\n                    }\r\n                }\r\n                LoadTable(\"\u524a\u9664\");\r\n            }\r\n\r\n            \/\/\u66f4\u65b0\r\n            if (col == 8 && row >= 0)\r\n            {\r\n                if (!isLoad)\r\n                {\r\n                    MessageBox.Show(\"\u88fd\u54c1ID\u304c\u6b63\u3057\u304f\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\");\r\n                    return;\r\n                }\r\n\r\n                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n                string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();\r\n                string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();\r\n\r\n                try\r\n                {\r\n                    decimal.Parse(quantity);\r\n                    int i = int.Parse(calculateId);\r\n                    if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show(\"\u96c6\u8a08ID\u30fb\u6570\u91cf\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\"); return; } using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( \"UPDATE \u90e8\u54c1\u8868 SET \u96c6\u8a08ID = \" + calculateId + \", \u6570\u91cf = \" + quantity + \" WHERE \u90e8\u54c1\u8868ID = \" + id + \"\", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\"); InputClear(); return; } finally { con.Close(); } } LoadTable(\"\u66f4\u65b0\"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls(\"ProductInputs\")) { c.Text = \"\"; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {\"\u5411\u5148\",\"\u578b\u5f0f\",\"\u88fd\u4f5c\u5148\" }) { cmd = new OleDbCommand(\"SELECT DISTINCT \" + str +\" FROM \u88fd\u54c1\", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == \"\u5411\u5148\") this.comboBox1.Items.Add(dr[\"\u5411\u5148\"].ToString()); if(str == \"\u578b\u5f0f\") this.comboBox2.Items.Add(dr[\"\u578b\u5f0f\"].ToString()); if(str == \"\u88fd\u4f5c\u5148\") this.comboBox3.Items.Add(dr[\"\u88fd\u4f5c\u5148\"].ToString()); } } } catch { MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == \"\" || textBox2.Text == \"\" || textBox3.Text == \"\" || textBox17.Text == \"\") { MessageBox.Show(\"\u88fd\u54c1ID\u30fb\u90e8\u54c1ID\u30fb\u96c6\u8a08ID\u30fb\u6570\u91cf\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"); return false; } if (!isLoad) { MessageBox.Show(\"\u88fd\u54c1ID\u304c\u6b63\u3057\u304f\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\"); return false; } if (!TryInt()) { MessageBox.Show(\"\u96c6\u8a08ID\u30fb\u6570\u91cf\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand(\"INSERT INTO \u90e8\u54c1\u8868 (\u88fd\u54c1ID,\u96c6\u8a08ID,\u90e8\u54c1ID_,\u6570\u91cf) VALUES (\" + textBox1.Text + \",\" + textBox3.Text + \",\" + textBox2.Text + \",\" + textBox17.Text + \")\", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\"); return; } finally { con.Close(); } } foreach (Control c in GetControls(\"PartsInput\")) { c.Text = \"\"; } LoadTable(\"\u8ffd\u52a0\"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == \"\u8aad\u8fbc\") { if (this.textBox1.Text == \"\") { MessageBox.Show(\"ID\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"); InputClear(); return; } } else if (sender == \"\u691c\u7d22\") { if (this.comboBox1.Text == \"\" || this.comboBox2.Text == \"\" || this.comboBox3.Text == \"\") { MessageBox.Show(\"\u5411\u5148\u30fb\u578b\u5f0f\u30fb\u88fd\u4f5c\u5148\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\")) { try { con.Open(); string q = \"\"; switch (sender) { case \"\u691c\u7d22\": case \"\u65b0\u898f\": q = \"SELECT * FROM \u88fd\u54c1 WHERE \u5411\u5148 = '\" + comboBox1.Text + \"' AND \u578b\u5f0f = '\" + comboBox2.Text + \"' AND \u88fd\u4f5c\u5148 = '\" + comboBox3.Text + \"'\"; break; case \"\u8aad\u8fbc\": case \"\u524a\u9664\": case \"\u8ffd\u52a0\": case \"\u66f4\u65b0\": q = \"SELECT * FROM \u88fd\u54c1 WHERE ID = \" + this.textBox1.Text + \"\"; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show(\"\u30c7\u30fc\u30bf\u304c\u3042\u308a\u307e\u305b\u3093\u3067\u3057\u305f\u3002\"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr[\"ID\"].ToString(); this.comboBox1.Text = dr[\"\u5411\u5148\"].ToString(); this.comboBox2.Text = dr[\"\u578b\u5f0f\"].ToString(); this.comboBox3.Text = dr[\"\u88fd\u4f5c\u5148\"].ToString(); this.textBox14.Text = string.Format(\"{0:f0}\", dr[\"\u8ca9\u58f2\u4fa1\u683c\"]); this.textBox15.Text = string.Format(\"{0:f0}\", dr[\"\u55b6\u696d\u624b\u914d\u5206\"]); this.textBox6.Text = string.Format(\"{0:f0}\", dr[\"\u5916\u6ce8\u8cbb\"]); this.textBox8.Text = string.Format(\"{0:f0}\", dr[\"\u8f38\u9001\u8cbb\u8377\u53d7\"]); this.textBox9.Text = string.Format(\"{0:f0}\", dr[\"\u8f38\u9001\u8cbb\u7d0d\u54c1\"]); this.textBox10.Text = string.Format(\"{0:f0}\", dr[\"\u8a66\u9a13\u8cbb\"]); this.textBox11.Text = string.Format(\"{0:f0}\", dr[\"\u54c1\u8a3c\u8cbb\u7528\"]); this.textBox12.Text = string.Format(\"{0:f0}\", dr[\"\u54c1\u8a3c\u7d4c\u8cbb\"]); this.textBox13.Text = string.Format(\"{0:f0}\", dr[\"\u793e\u5185\u88fd\u4f5c\u5206\"]); this.textBox16.Text = dr[\"\u5099\u8003\"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show(\"\u5411\u5148\u30fb\u578b\u5f0f\u30fb\u88fd\u4f5c\u5148\u306e\u7d44\u5408\u308f\u305b\u304c\u91cd\u8907\u3057\u3066\u3044\u308b\u306e\u3067\u6ce8\u610f\u3057\u3066\u304f\u3060\u3055\u3044\u3002\");\r\n                    }\r\n\r\n                    cmd = new OleDbCommand(\"SELECT * FROM \u90e8\u54c1\u8868 LEFT OUTER JOIN \u90e8\u54c1 ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID WHERE \u88fd\u54c1ID = \" + this.textBox1.Text + \"\" , con);\r\n                    dr = cmd.ExecuteReader();\r\n\r\n                    while(dr.Read())\r\n                    {\r\n                        dataGridView1.Rows.Add(\r\n                            dr[\"\u90e8\u54c1\u8868ID\"].ToString(),\r\n                            dr[\"\u96c6\u8a08ID\"].ToString(),\r\n                            dr[\"\u90e8\u54c1ID\"].ToString(),\r\n                            dr[\"\u540d\u79f0\"].ToString(),\r\n                            dr[\"\u6750\u8cea\"].ToString(),\r\n                            dr[\"\u5bf8\u6cd5\"].ToString(),\r\n                            dr[\"\u5358\u4fa1\"].ToString(),\r\n                            string.Format(\"{0:f2}\", dr[\"\u6570\u91cf\"]), \"\");\r\n                    }\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                    InputClear();\r\n                    return;\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n\r\n                try\r\n                {\r\n                    decimal ManHour = 0;\r\n                    decimal PaintingCost = 0;\r\n                    decimal PartsCost1 = 0;\r\n                    decimal PartsCost2 = 0;\r\n                    decimal PartsCost3 = 0;\r\n                    decimal YieldRatio = 0;\r\n\r\n                    for (int k = 0; k < dataGridView1.Rows.Count; k++)\r\n                    {\r\n                        decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());\r\n                        decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());\r\n\r\n                        switch (dataGridView1.Rows[k].Cells[1].Value.ToString())\r\n                        {\r\n                            case \"1\":\r\n                                ManHour += UnitPrice * Quantity;\r\n                                break;\r\n                            case \"2\":\r\n                                PaintingCost += UnitPrice * Quantity;\r\n                                break;\r\n                            case \"3\":\r\n                                PartsCost1 += UnitPrice * Quantity;\r\n                                break;\r\n                            case \"4\":\r\n                                PartsCost2 += UnitPrice * Quantity;\r\n                                break;\r\n                            case \"5\":\r\n                                PartsCost3 += UnitPrice * Quantity;\r\n                                break;\r\n                        }\r\n                    }\r\n                    textBox4.Text = string.Format(\"{0:f0}\", ManHour * (decimal)4000);\r\n                    textBox5.Text = string.Format(\"{0:f0}\", PaintingCost);\r\n                    textBox22.Text = string.Format(\"{0:f0}\", PartsCost1);\/\/\u92fc\u6750(3)\r\n                    textBox23.Text = string.Format(\"{0:f0}\", PartsCost2);\/\/\u8cfc\u5165\u54c1(4)\r\n                    textBox25.Text = string.Format(\"{0:f0}\", PartsCost3);\/\/\u305d\u306e\u4ed6(5)\r\n                    YieldRatio = PartsCost1 * (decimal)0.2;\r\n                    textBox24.Text = string.Format(\"{0:f0}\", YieldRatio);\/\/\u6b69\u7559\r\n                    textBox7.Text = string.Format(\"{0:f0}\", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u90e8\u54c1\u306e\u5358\u4fa1\u30fb\u6570\u91cf\u304c\u4e0d\u6b63\u306a\u5024\u3067\u3059\u3002\u4fee\u6b63\u3057\u3066\u304f\u3060\u3055\u3044\u3002\");\r\n                }\r\n                switch (sender)\r\n                {\r\n                    case \"\u8aad\u8fbc\":\r\n                    case \"\u691c\u7d22\":\r\n                        MessageBox.Show(\"\u53d6\u5f97\u3057\u307e\u3057\u305f\u3002\");\r\n                        break;\r\n                    case \"\u524a\u9664\":\r\n                        MessageBox.Show(\"\u524a\u9664\u3057\u307e\u3057\u305f\u3002\");\r\n                        break;\r\n                    case \"\u8ffd\u52a0\":\r\n                        MessageBox.Show(\"\u8ffd\u52a0\u3057\u307e\u3057\u305f\u3002\");\r\n                        break;\r\n                    case \"\u65b0\u898f\":\r\n                        MessageBox.Show(\"\u4f5c\u6210\u3057\u307e\u3057\u305f\u3002\");\r\n                        break;\r\n                    case \"\u66f4\u65b0\":\r\n                        MessageBox.Show(\"\u66f4\u65b0\u3057\u307e\u3057\u305f\u3002\");\r\n                        break;\r\n                }\r\n\r\n                isLoad = true;\r\n\r\n                dataGridView1.Columns[0].ReadOnly = true;\r\n                dataGridView1.Columns[1].ReadOnly = false;\r\n                dataGridView1.Columns[2].ReadOnly = true;\r\n                dataGridView1.Columns[3].ReadOnly = true;\r\n                dataGridView1.Columns[4].ReadOnly = true;\r\n                dataGridView1.Columns[5].ReadOnly = true;\r\n                dataGridView1.Columns[6].ReadOnly = true;\r\n                dataGridView1.Columns[7].ReadOnly = false;\r\n                dataGridView1.Columns[8].ReadOnly = true;\r\n                dataGridView1.Columns[8].ReadOnly = true;\r\n\r\n                textBox1.ForeColor = Color.Blue;\r\n            }\r\n        }\r\n    }\r\n}\r\n\r\n<\/pre>\n
Form2.cs<\/p>\n
using System;\r\nusing System.Collections.Generic;\r\nusing System.ComponentModel;\r\nusing System.Data;\r\nusing System.Drawing;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Windows.Forms;\r\nusing System.Data.OleDb;\r\n\r\nnamespace \u7a4d\u7b97_\u539f\u4fa1\u7ba1\u7406\r\n{\r\n    public partial class Form2 : Form\r\n    {\r\n        Form f1;\r\n        public Form2(Form f)\r\n        {\r\n            InitializeComponent();\r\n\r\n            f1 = f;\r\n\r\n            button1.Click += new EventHandler(FindRows);\r\n            button3.Click += new EventHandler(addRow);\r\n\r\n            this.KeyPreview = true;\r\n            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {\r\n                if (e.KeyCode == Keys.Escape) this.Close();\r\n            });\r\n\r\n            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);\r\n            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;\r\n            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;\r\n            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;\r\n            dataGridView1.AllowUserToAddRows = false;\r\n            dataGridView1.Font = new Font(\"MS UI Gothic\", 12);\r\n\r\n            dataGridView1.Columns.Add(\"\u90e8\u54c1ID\", \"\u90e8\u54c1ID\");\r\n            dataGridView1.Columns[\"\u90e8\u54c1ID\"].ReadOnly = true;\r\n\r\n            dataGridView1.Columns.Add(\"\u540d\u79f0\", \"\u540d\u79f0\");\r\n            dataGridView1.Columns.Add(\"\u6750\u8cea\", \"\u6750\u8cea\");\r\n            dataGridView1.Columns.Add(\"\u5bf8\u6cd5\", \"\u5bf8\u6cd5\");\r\n            dataGridView1.Columns.Add(\"\u5358\u4fa1\", \"\u5358\u4fa1\");\r\n\r\n            DataGridViewButtonColumn col = new DataGridViewButtonColumn();\r\n            col.Name = \"\u66f4\u65b0\";\r\n            col.UseColumnTextForButtonValue = true;\r\n            col.Text = \"\u66f4\u65b0\";\r\n            dataGridView1.Columns.Add(col);\r\n\r\n            col = new DataGridViewButtonColumn();\r\n            col.Name = \"\u524a\u9664\";\r\n            col.UseColumnTextForButtonValue = true;\r\n            col.Text = \"\u524a\u9664\";\r\n            dataGridView1.Columns.Add(col);\r\n\r\n            LoadMaster();\r\n        }\r\n\r\n        private void dgvClick(object sender, DataGridViewCellEventArgs e)\r\n        {\r\n            int col = e.ColumnIndex;\r\n            int row = e.RowIndex;\r\n\r\n            if(col == -1 && row >= 0)\r\n            {\r\n                f1.Controls[\"textBox2\"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n                f1.Controls[\"textBox18\"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();\r\n                f1.Controls[\"textBox19\"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();\r\n                f1.Controls[\"textBox20\"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();\r\n                f1.Controls[\"textBox21\"].Text = dataGridView1.Rows[row].Cells[4].Value.ToString();\r\n                f1.Controls[\"textBox3\"].Text = \"\";\r\n                f1.Controls[\"textBox17\"].Text = \"\";\r\n            }\r\n\r\n            if (col == 5 && row >= 0)\r\n            {\r\n                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n                string name = dataGridView1.Rows[row].Cells[1].Value.ToString();\r\n                string material = dataGridView1.Rows[row].Cells[2].Value.ToString();\r\n                string size = dataGridView1.Rows[row].Cells[3].Value.ToString();\r\n                string price = dataGridView1.Rows[row].Cells[4].Value.ToString();\r\n\r\n                using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n                {\r\n                    try\r\n                    {\r\n                        con.Open();\r\n\r\n                        OleDbCommand cmd = new OleDbCommand(\"UPDATE \u90e8\u54c1 SET \" + \r\n                            \"\u540d\u79f0 = '\" + name + \"', \u6750\u8cea = '\" + material + \"', \u5bf8\u6cd5 = '\" + size + \"', \u5358\u4fa1 = \" + price + \" WHERE \u90e8\u54c1ID = \" + id + \"\", con);\r\n                        cmd.ExecuteNonQuery();\r\n                    }\r\n                    catch\r\n                    {\r\n                        MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                        return;\r\n                    }\r\n                    finally\r\n                    {\r\n                        con.Close();\r\n                    }\r\n                }\r\n                LoadMaster();\r\n                BaseFormClear();\r\n                MessageBox.Show(\"\u66f4\u65b0\u3057\u307e\u3057\u305f\u3002\");\r\n            }\r\n\r\n            if (col == 6 && row >= 0)\r\n            {\r\n                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n\r\n                using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n                {\r\n                    try\r\n                    {\r\n                        con.Open();\r\n\r\n                        OleDbCommand cmd = new OleDbCommand(\"DELETE FROM \u90e8\u54c1 WHERE \u90e8\u54c1ID = \" + id + \"\", con);\r\n                        cmd.ExecuteNonQuery();\r\n                    }\r\n                    catch\r\n                    {\r\n                        MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                        return;\r\n                    }\r\n                    finally\r\n                    {\r\n                        con.Close();\r\n                    }\r\n                }\r\n                LoadMaster();\r\n                BaseFormClear();\r\n                MessageBox.Show(\"\u524a\u9664\u3057\u307e\u3057\u305f\u3002\");\r\n            }\r\n        }\r\n        private void BaseFormClear()\r\n        {\r\n            foreach (Control c in f1.Controls)\r\n            {\r\n                if (c is TextBox) c.Text = \"\";\r\n                if (c is ComboBox) c.Text = \"\";\r\n            }\r\n            ((DataGridView)f1.Controls[\"dataGridView1\"]).Rows.Clear();\r\n        }\r\n\r\n        private void FindRows(object sender, EventArgs e)\r\n        {\r\n            if (textBox1.Text == \"\")\r\n            {\r\n                LoadMaster();\r\n            }\r\n            else\r\n            {\r\n                LoadMaster(\"SELECT * FROM \u90e8\u54c1 WHERE \u540d\u79f0 LIKE '%\" + textBox1.Text + \"%' OR \u6750\u8cea LIKE '%\" + textBox1.Text + \"%'\");\r\n                textBox1.Text = \"\";\r\n            }\r\n        }\r\n\r\n        private void addRow(object sender, EventArgs e)\r\n        {\r\n            if(textBox18.Text == \"\" || textBox4.Text == \"\")\r\n            {\r\n                MessageBox.Show(\"\u540d\u79f0\u30fb\u5358\u4fa1\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\");\r\n                LoadMaster();\r\n                return;\r\n            }\r\n\r\n            using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n\r\n                    OleDbCommand cmd = new OleDbCommand(\"INSERT INTO \u90e8\u54c1 (\u540d\u79f0,\u6750\u8cea,\u5bf8\u6cd5,\u5358\u4fa1) VALUES ('\" + \r\n                        textBox18.Text + \"','\" + textBox2.Text + \"','\" + textBox3.Text + \"','\" + textBox4.Text + \"')\", con);\r\n                    cmd.ExecuteNonQuery();\r\n\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                    return;\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n            }\r\n            this.textBox18.Text = \"\";\r\n            this.textBox2.Text = \"\";\r\n            this.textBox3.Text = \"\";\r\n            this.textBox4.Text = \"\";\r\n\r\n            LoadMaster();\r\n            MessageBox.Show(\"\u8ffd\u52a0\u3057\u307e\u3057\u305f\u3002\");            \r\n        }\r\n\r\n        private void LoadMaster(string q = \"SELECT * FROM \u90e8\u54c1\")\r\n        {\r\n            f1.Controls[\"textBox2\"].Text = \"\";\r\n            f1.Controls[\"textBox18\"].Text = \"\";\r\n            f1.Controls[\"textBox19\"].Text = \"\";\r\n            f1.Controls[\"textBox20\"].Text = \"\"; \r\n            f1.Controls[\"textBox21\"].Text = \"\";\r\n\r\n            dataGridView1.Rows.Clear();\r\n\r\n            using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n\r\n                    OleDbCommand cmd = new OleDbCommand(q, con);\r\n                    OleDbDataReader dr = cmd.ExecuteReader();\r\n\r\n                    while (dr.Read())\r\n                    {\r\n                        this.dataGridView1.Rows.Add(\r\n                            dr[\"\u90e8\u54c1ID\"].ToString(),\r\n                            dr[\"\u540d\u79f0\"].ToString(),\r\n                            dr[\"\u6750\u8cea\"].ToString(),\r\n                            dr[\"\u5bf8\u6cd5\"].ToString(),\r\n                            dr[\"\u5358\u4fa1\"].ToString(),\"\",\"\");\r\n                    }\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                    return;\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n            }\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
Form3.cs<\/p>\n
using System;\r\nusing System.Collections.Generic;\r\nusing System.ComponentModel;\r\nusing System.Data;\r\nusing System.Drawing;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Windows.Forms;\r\n\r\nusing System.Data.OleDb;\r\n\r\nnamespace \u7a4d\u7b97_\u539f\u4fa1\u7ba1\u7406\r\n{\r\n    public partial class Form3 : Form\r\n    {\r\n        Form f1;\r\n        public Form3(Form f)\r\n        {\r\n            InitializeComponent();\r\n\r\n            f1 = f;\r\n\r\n            button1.Click += new EventHandler(FindRows);\r\n\r\n            this.KeyPreview = true;\r\n            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {\r\n                if (e.KeyCode == Keys.Escape) this.Close();\r\n            });\r\n\r\n            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);\r\n\r\n            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;\r\n            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;\r\n            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;\r\n            dataGridView1.AllowUserToAddRows = false;\r\n\r\n            dataGridView1.Font = new Font(\"MS UI Gothic\", 12);\r\n            dataGridView1.ReadOnly = true;\r\n\r\n            dataGridView1.Columns.Add(\"ID\", \"ID\");\r\n            dataGridView1.Columns.Add(\"\u5411\u5148\", \"\u5411\u5148\");\r\n            dataGridView1.Columns.Add(\"\u578b\u5f0f\", \"\u578b\u5f0f\");\r\n            dataGridView1.Columns.Add(\"\u88fd\u4f5c\u5148\", \"\u88fd\u4f5c\u5148\");\r\n\r\n            LoadMaster();\r\n        }\r\n\r\n        private void dgvClick(object sender, DataGridViewCellEventArgs e)\r\n        {\r\n            int col = e.ColumnIndex;\r\n            int row = e.RowIndex;\r\n\r\n            if (col == -1 && row >= 0)\r\n            {\r\n                f1.Controls[\"textBox1\"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();\r\n                f1.Controls[\"comboBox1\"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();\r\n                f1.Controls[\"comboBox2\"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();\r\n                f1.Controls[\"comboBox3\"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();\r\n            }\r\n        }\r\n\r\n        private void FindRows(object sender, EventArgs e)\r\n        {\r\n            if (textBox1.Text == \"\")\r\n            {\r\n                LoadMaster();\r\n            }\r\n            else\r\n            {\r\n                LoadMaster(\"SELECT ID,\u5411\u5148,\u578b\u5f0f,\u88fd\u4f5c\u5148 FROM \u88fd\u54c1 WHERE \u5411\u5148 LIKE '%\" + textBox1.Text + \"%' OR \u578b\u5f0f LIKE '%\" + textBox1.Text + \"%' OR \u88fd\u4f5c\u5148 LIKE '%\" + textBox1.Text + \"%'\");\r\n                textBox1.Text = \"\";\r\n            }\r\n        }\r\n\r\n        private void LoadMaster(string q = \"SELECT ID,\u5411\u5148,\u578b\u5f0f,\u88fd\u4f5c\u5148 FROM \u88fd\u54c1\")\r\n        {\r\n            f1.Controls[\"textBox1\"].Text = \"\";\r\n            f1.Controls[\"comboBox1\"].Text = \"\";\r\n            f1.Controls[\"comboBox2\"].Text = \"\";\r\n            f1.Controls[\"comboBox3\"].Text = \"\";\r\n\r\n            dataGridView1.Rows.Clear();\r\n\r\n            using (OleDbConnection con = new OleDbConnection(@\"Provider=Microsoft.JET.OLEDB.4.0;Data Source=\" + Application.StartupPath + @\"\\genka_db_2.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n\r\n                    OleDbCommand cmd = new OleDbCommand(q, con);\r\n                    OleDbDataReader dr = cmd.ExecuteReader();\r\n\r\n                    while (dr.Read())\r\n                    {\r\n                        this.dataGridView1.Rows.Add(dr[\"ID\"].ToString(),dr[\"\u5411\u5148\"].ToString(),dr[\"\u578b\u5f0f\"].ToString(), dr[\"\u88fd\u4f5c\u5148\"].ToString());\r\n                    }\r\n                }\r\n                catch\r\n                {\r\n                    MessageBox.Show(\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002\");\r\n                    return;\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n            }\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
\u53d6\u308a\u51fa\u3057\u306fVBA\u306b\u3066<\/p>\n
Sub func_down()\r\n\r\nOn Error GoTo e\r\n\r\nym = MsgBox(\"\u4e00\u89a7\u8868\u3067\u5b9f\u884c\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\" & Chr(13) & \"\u5b9f\u884c\u3057\u307e\u3059\u304b\uff1f\", vbYesNo)\r\nIf ym = vbNo Then Exit Sub\r\n\r\nDim cn As Object\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\n\r\nDim rs As Object\r\nSet rs = CreateObject(\"ADODB.Recordset\")\r\n\r\ncn.Provider = \"Microsoft.JET.OLEDB.4.0\"\r\ncn.Properties(\"Data Source\").Value = ThisWorkbook.Path & \"\\genka_db_2.mdb\"\r\ncn.Open\r\ncn.CursorLocation = 3\r\n\r\nc = 1\r\n\r\nWith ActiveSheet\r\n\r\nFor r = 7 To .UsedRange.Rows.Count\r\n\r\n  Customer = .Cells(r, c + 1).Value\r\n  ProductName = .Cells(r, c + 2).Value\r\n  Supplier = .Cells(r, c + 3).Value\r\n  \r\n  If Customer <> \"\" And ProductName <> \"\" Then\r\n  \r\n    q = \"SELECT ID,\u5411\u5148,\u578b\u5f0f,\u8ca9\u58f2\u4fa1\u683c,\u55b6\u696d\u624b\u914d\u5206,\u5de5\u6570,\u5857\u88c5\u8cbb,\u5916\u6ce8\u8cbb,\u6750\u6599\u8cbb,\u8f38\u9001\u8cbb\u8377\u53d7,\u8f38\u9001\u8cbb\u7d0d\u54c1,\u8a66\u9a13\u8cbb,\u54c1\u8a3c\u8cbb\u7528,\u54c1\u8a3c\u7d4c\u8cbb,\u793e\u5185\u88fd\u4f5c\u5206,\u5099\u8003 \" & _\r\n    \"FROM \u88fd\u54c1 WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"'\"\r\n    Debug.Print q\r\n    \r\n    rs.Open q, cn\r\n\r\n    If rs.RecordCount = 1 Then\r\n\r\n      .Cells(r, c + 4).Value = rs!\u8ca9\u58f2\u4fa1\u683c\r\n      .Cells(r, c + 5).Value = rs!\u55b6\u696d\u624b\u914d\u5206\r\n      \r\n      .Cells(r, c + 11).Value = rs!\u5916\u6ce8\u8cbb\r\n      .Cells(r, c + 13).Value = rs!\u8f38\u9001\u8cbb\u8377\u53d7\r\n      .Cells(r, c + 14).Value = rs!\u8f38\u9001\u8cbb\u7d0d\u54c1\r\n      .Cells(r, c + 15).Value = rs!\u8a66\u9a13\u8cbb\r\n      .Cells(r, c + 21).Value = rs!\u54c1\u8a3c\u8cbb\u7528\r\n      .Cells(r, c + 22).Value = rs!\u54c1\u8a3c\u7d4c\u8cbb\r\n      .Cells(r, c + 23).Value = rs!\u793e\u5185\u88fd\u4f5c\u5206\r\n      .Cells(r, c + 33).Value = rs!\u5099\u8003\r\n      If rs.State = 1 Then rs.Close\r\n      \r\n      '\u5de5\u6570\r\n      \r\n      Dim manHour As Currency: manHour = 0\r\n\r\n      q = \"SELECT \" & _\r\n      \"\u90e8\u54c1\u8868.\u96c6\u8a08ID, \" & _\r\n      \"Sum(\u90e8\u54c1\u8868.\u6570\u91cf * \u90e8\u54c1.\u5358\u4fa1) AS \u5408\u8a08 \" & _\r\n      \"FROM \u90e8\u54c1\u8868 LEFT JOIN \u90e8\u54c1 \" & _\r\n      \"ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID \" & _\r\n      \"WHERE (((\u90e8\u54c1\u8868.[\u88fd\u54c1ID])=(SELECT ID \" & _\r\n      \"FROM \u88fd\u54c1 \" & _\r\n      \"WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"' \" & _\r\n      \"))) AND \u96c6\u8a08ID = \" & 1 & \" \" & _\r\n      \"GROUP BY \u90e8\u54c1\u8868.\u96c6\u8a08ID;\"\r\n      rs.Open q, cn\r\n      If rs.RecordCount = 1 Then manHour = rs!\u5408\u8a08\r\n      If rs.State = 1 Then rs.Close\r\n      \r\n      .Cells(r, c + 6).Value = manHour\r\n      \r\n      '\u5857\u88c5\u8cbb\r\n      \r\n      Dim paintingCost As Currency: paintingCost = 0\r\n      \r\n      q = \"SELECT \" & _\r\n      \"\u90e8\u54c1\u8868.\u96c6\u8a08ID, \" & _\r\n      \"Sum(\u90e8\u54c1\u8868.\u6570\u91cf * \u90e8\u54c1.\u5358\u4fa1) AS \u5408\u8a08 \" & _\r\n      \"FROM \u90e8\u54c1\u8868 LEFT JOIN \u90e8\u54c1 \" & _\r\n      \"ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID \" & _\r\n      \"WHERE (((\u90e8\u54c1\u8868.[\u88fd\u54c1ID])=(SELECT ID \" & _\r\n      \"FROM \u88fd\u54c1 \" & _\r\n      \"WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"' \" & _\r\n      \"))) AND \u96c6\u8a08ID = \" & 2 & \" \" & _\r\n      \"GROUP BY \u90e8\u54c1\u8868.\u96c6\u8a08ID;\"\r\n      rs.Open q, cn\r\n      If rs.RecordCount = 1 Then paintingCost = rs!\u5408\u8a08\r\n      If rs.State = 1 Then rs.Close\r\n      \r\n      .Cells(r, c + 9).Value = paintingCost\r\n      \r\n      '\u6750\u6599\u8cbb1\r\n      \r\n      Dim partsCost1 As Currency: partsCost1 = 0\r\n      Dim partsCost2 As Currency: partsCost2 = 0\r\n      Dim partsCost3 As Currency: partsCost3 = 0\r\n      \r\n      q = \"SELECT \" & _\r\n      \"\u90e8\u54c1\u8868.\u96c6\u8a08ID, \" & _\r\n      \"Sum(\u90e8\u54c1\u8868.\u6570\u91cf * \u90e8\u54c1.\u5358\u4fa1) AS \u5408\u8a08 \" & _\r\n      \"FROM \u90e8\u54c1\u8868 LEFT JOIN \u90e8\u54c1 \" & _\r\n      \"ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID \" & _\r\n      \"WHERE (((\u90e8\u54c1\u8868.[\u88fd\u54c1ID])=(SELECT ID \" & _\r\n      \"FROM \u88fd\u54c1 \" & _\r\n      \"WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"' \" & _\r\n      \"))) AND \u96c6\u8a08ID = \" & 3 & \" \" & _\r\n      \"GROUP BY \u90e8\u54c1\u8868.\u96c6\u8a08ID;\"\r\n      rs.Open q, cn\r\n      \r\n      If rs.RecordCount = 1 Then partsCost1 = rs!\u5408\u8a08\r\n      If rs.State = 1 Then rs.Close\r\n      \r\n      '\u6750\u6599\u8cbb2\r\n      q = \"SELECT \" & _\r\n      \"\u90e8\u54c1\u8868.\u96c6\u8a08ID, \" & _\r\n      \"Sum(\u90e8\u54c1\u8868.\u6570\u91cf * \u90e8\u54c1.\u5358\u4fa1) AS \u5408\u8a08 \" & _\r\n      \"FROM \u90e8\u54c1\u8868 LEFT JOIN \u90e8\u54c1 \" & _\r\n      \"ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID \" & _\r\n      \"WHERE (((\u90e8\u54c1\u8868.[\u88fd\u54c1ID])=(SELECT ID \" & _\r\n      \"FROM \u88fd\u54c1 \" & _\r\n      \"WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"' \" & _\r\n      \"))) AND \u96c6\u8a08ID = \" & 4 & \" \" & _\r\n      \"GROUP BY \u90e8\u54c1\u8868.\u96c6\u8a08ID;\"\r\n      rs.Open q, cn\r\n      \r\n      If rs.RecordCount = 1 Then partsCost2 = rs!\u5408\u8a08\r\n      If rs.State = 1 Then rs.Close\r\n      \r\n      '\u6750\u6599\u8cbb3\r\n      q = \"SELECT \" & _\r\n      \"\u90e8\u54c1\u8868.\u96c6\u8a08ID, \" & _\r\n      \"Sum(\u90e8\u54c1\u8868.\u6570\u91cf * \u90e8\u54c1.\u5358\u4fa1) AS \u5408\u8a08 \" & _\r\n      \"FROM \u90e8\u54c1\u8868 LEFT JOIN \u90e8\u54c1 \" & _\r\n      \"ON \u90e8\u54c1\u8868.\u90e8\u54c1ID_ = \u90e8\u54c1.\u90e8\u54c1ID \" & _\r\n      \"WHERE (((\u90e8\u54c1\u8868.[\u88fd\u54c1ID])=(SELECT ID \" & _\r\n      \"FROM \u88fd\u54c1 \" & _\r\n      \"WHERE \u5411\u5148 = '\" & Customer & \"' AND \u578b\u5f0f = '\" & ProductName & \"' AND \u88fd\u4f5c\u5148 = '\" & Supplier & \"' \" & _\r\n      \"))) AND \u96c6\u8a08ID = \" & 5 & \" \" & _\r\n      \"GROUP BY \u90e8\u54c1\u8868.\u96c6\u8a08ID;\"\r\n      rs.Open q, cn\r\n      \r\n      If rs.RecordCount = 1 Then partsCost3 = rs!\u5408\u8a08\r\n      If rs.State = 1 Then rs.Close\r\n\r\n      .Cells(r, c + 12).Value = partsCost1 + (partsCost1 * 0.2) + partsCost2 + partsCost3\r\n    \r\n    ElseIf rs.RecordCount > 1 Then\r\n    \r\n        MsgBox \"\u5411\u5148\u30fb\u578b\u5f0f\u304c\u91cd\u8907\u3057\u3066\u3044\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\" & Chr(13) & r & \"\u884c\u76ee\"\r\n    \r\n    End If\r\n    \r\n    If rs.State = 1 Then rs.Close\r\n    \r\n  End If\r\n\r\nNext r\r\n\r\nEnd With\r\n\r\nIf rs.State = 1 Then rs.Close\r\nSet rs = Nothing\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nMsgBox \"\u7d42\u4e86\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nExit Sub\r\n\r\ne:\r\n\r\n  If rs.State = 1 Then rs.Close\r\n  Set rs = Nothing\r\n  \r\n  If cn.State = 1 Then cn.Close\r\n  Set cn = Nothing\r\n  \r\n  MsgBox \"\u4e0d\u5177\u5408\u304c\u767a\u751f\u3057\u3066\u3044\u307e\u3059\u3002\u7ba1\u7406\u8005\u3078\u9023\u7d61\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n
\u5373\u5e2d\u767b\u9332<\/p>\n
\r\nPrivate Sub CommandButton1_Click()\r\n\r\nOn Error GoTo e\r\n\r\nDim cn As Object\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\n\r\ncn.Provider = \"Microsoft.JET.OLEDB.4.0\"\r\ncn.Properties(\"Data Source\").Value = ThisWorkbook.Path & \"\\genka_db_2.mdb\"\r\ncn.Open\r\n\r\nWith ActiveSheet\r\n\r\nr = 2\r\nDo While .Cells(r, 1) <> \"\"\r\n\r\n  q = \"INSERT INTO \u90e8\u54c1 (\u540d\u79f0,\u6750\u8cea,\u5bf8\u6cd5,\u5358\u4fa1) VALUES ('\" & _\r\n  .Cells(r, 1).Value & \"','\" & .Cells(r, 2).Value & \"','\" & .Cells(r, 3).Value & \"','\" & .Cells(r, 4).Value & \"')\"\r\n  cn.Execute q: Debug.Print q\r\n  \r\nr = r + 1\r\nLoop\r\n\r\nEnd With\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nMsgBox \"\u7d42\u4e86\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nExit Sub\r\n\r\ne:\r\n\r\n  If cn.State = 1 Then cn.Close\r\n  Set cn = Nothing\r\n\r\n  MsgBox \"\u4e0d\u5177\u5408\u304c\u767a\u751f\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n  \r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Form1.cs using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using … <\/p>\n
“C# VBA \u7a4d\u7b97\u30fb\u539f\u4fa1\u7ba1\u7406” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[9,5],"class_list":["post-858","post","type-post","status-publish","format-standard","hentry","category-1","tag-c-net","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/858","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=858"}],"version-history":[{"count":7,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/858\/revisions"}],"predecessor-version":[{"id":974,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/858\/revisions\/974"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=858"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}