question  Interacting with Database Connections and CSV files

UltraDangerLord (13)   Link to this post
Hello All,

So Recently I undertook a project to retrieve data (in the form of products and product prices) from excel and upload the data appropriately to my companies website.

(for more info)Reference: http://www.cplusplus.com/forum/windows/2133/



So what I realized was, that interacting with database, was the most straightforward way to update my products.

So i created this app to connect to a Mysql database then read and write a price value into the database.

This program was written in C#, not C++, (I found source code to start with in C#)

So for now I only have one problem... the app is not executing a part of my code correctly. It does update the database correctly, It just does'nt display the change in the log

This is the problem area:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
private void btnPriceUpdate_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("update `products` set `products_price`='" + txtPrice.Text + "' where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The New Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }


Later I would like to incorporate Excel files converted into CSV files store them in a array i think and just have the whole process automated.

Right now the app is setup for manual. But as I'm learning, I want to try and automate it more and more.

(any suggestions would be appreciated.)


Here is the full source code:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace CSharpToMySQL
{
	public partial class frmMain : Form
	{
		private System.Data.Odbc.OdbcConnection OdbcCon;
		private System.Data.Odbc.OdbcCommand OdbcCom;
		private System.Data.Odbc.OdbcDataReader OdbcDR;
		private string ConStr;
		private Form frmAbout;

		public frmMain()
		{
			InitializeComponent();
		}

		private void btnConnect_Click(object sender, EventArgs e)
		{
			ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + ";PORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + ";PWD=" + txtPassword.Text + ";OPTION=3";
			OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);
			btnListTables.Enabled = true;
            btnShowTableColumns.Enabled = true;

			try
			{
				txtLog.AppendText("Openning connection...\r\n");
				if (OdbcCon.State == ConnectionState.Closed)
				{
					OdbcCon.Open();
				}
				txtLog.AppendText("Connection opened\r\n");
			}
			catch (System.Data.Odbc.OdbcException Ex)
			{
				txtLog.AppendText(Ex.Message + "\r\n");
				MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void btnListTables_Click(object sender, EventArgs e)
		{
			if (OdbcCon.State == ConnectionState.Open)
			{
				OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES", OdbcCon);
				OdbcDR = OdbcCom.ExecuteReader();
				txtLog.AppendText("Tables inside " + txtDatabase.Text + ":\r\n");
				while (OdbcDR.Read())
				{
					txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
				}
			}
		}

        private void btnShowTablesColumns_Click(object sender, EventArgs e)
        {
            
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW COLUMNS FROM " + txtTable.Text, OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("Columns inside " + txtTable.Text + ":\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

        private void btnShowProductPrice_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("select `products_price` from `products` where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

        private void btnPriceUpdate_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("update `products` set `products_price`='" + txtPrice.Text + "' where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The New Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

		private void btnDisconnect_Click(object sender, EventArgs e)
		{
            try
            {
                txtLog.AppendText("Closing connection...\r\n");
                if (OdbcCon.State == ConnectionState.Open)
                {
                    OdbcCon.Close();
                }
                txtLog.AppendText("Connection Closed\r\n");
            }
            catch (System.Data.Odbc.OdbcException Ex)
            {
                txtLog.AppendText(Ex.Message + "\r\n");
                MessageBox.Show("Could not close the database.\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
		}

		private void mnuExit_Click(object sender, EventArgs e)
		{
			Application.Exit();
		}

		private void mnuAbout_Click(object sender, EventArgs e)
		{
			frmAbout = new frmAbout();
			frmAbout.ShowDialog(this);
			frmAbout.Dispose();
		}

        private void txtLog_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtDatabase_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtTable_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtProduct_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtPrice_TextChanged(object sender, EventArgs e)
        {

        }

        private void lblDatabase_Click(object sender, EventArgs e)
        {

        }

        private void lblTable_Click(object sender, EventArgs e)
        {

        }
	}
}



If you would like a copy of the program itself, just post a comment and I can provide a link to the exe or the project files.

Oh and Thanks for the help!

Peace Peace
Last edited on
Zaita (2112)   Link to this post
My understanding is that an update statement will not return the records you have edited. You will need to execute a 'select' statement afterwards.
UltraDangerLord (13)   Link to this post
Thanks For replying Zaita,

Your always helping me out =)

I kinda figured that was happening... I had tried this code below and nothing happened...same problem as above.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
private void btnPriceUpdate_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("update `products` set `products_price`='" + txtPrice.Text + "' where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcCom = new System.Data.Odbc.OdbcCommand("select `products_price` from `products` where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The New Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }


Should I place this line somewhere else ?


OdbcCom = new System.Data.Odbc.OdbcCommand("select `products_price` from `products` where `products_model`='" + txtProduct.Text + "'", OdbcCon);


Either way I guess its not a super big deal it still writes to the database properly. But it would be nice =)
Zaita (2112)   Link to this post
You are over-writing OdbcCom.

You should, create the com (update), execute it, create a new one (select), execute it.

Since this is in C# you shouldn't have to worry about freeing up the memory :)

Edit: You could also have a stored procedure on the database server. This is the best practice approach.
Last edited on
UltraDangerLord (13)   Link to this post
Actually I'm having a new problem... The log is'nt working correctly now.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
private void btnShowProductPrice_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("select `products_price` from `products` where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

        private void btnPriceUpdate_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("update `products` set `products_price`='" + txtPrice.Text + "' where `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The New Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }


these 2 buttons above are not working properly, these lines below do not print to the log. just for these 2 buttons

txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");

But they do go threw the while statement... I tested it.

Also i notice that the .sln file is not saving when i choose save all from the file menu.

The really wierd thing is that this worked yesterday..... and when i restarted my comp.... it did'nt

Any ideas ?
Last edited on
Zaita (2112)   Link to this post
Restart your computer :P
UltraDangerLord (13)   Link to this post
That was the problem..... sorry for the lag on the reply

This topic is archived - New replies not allowed.