[C#]need Help in MySql

03/27/2012 01:53 wolfvb#1

hi all i need some help in mysql things in C# i done the connection part
by this but ineed some help in how to get data from database

PHP Code:

MySqlConnection conn = new MySqlConnection(); conn = new MySqlConnection(); conn.ConnectionString = "server=" + cbServerSelect.Text + ";" + "user id=" + txtUserInput.Text + ";" + "password=" + txtPassInput.Text + ";" + "database=my"; if (cbServerSelect.Text == "" || txtUserInput.Text == "" || txtPassInput.Text == "") { MessageBox.Show("All Fields must be entered"); } else { try { conn.Open(); MessageBox.Show("Connection Opened Successfully"); conn.Close(); } catch (Exception ex) { MessageBox.Show("Error Connecting to MySQL DB" + "\r\n" + "\r\n" + ex.Message); } }

i need some code like how to get data from tables and insert in it
so pleas i need some help
i get a code to get data but it make a problem when get names get it like this

PHP Code:

System.Byte[]

the code

PHP Code:

MySqlDataReader Reader; MySqlCommand command = conn.CreateCommand(); command.CommandText = "select name from account"; conn.Open(); Reader = command.ExecuteReader(); while (Reader.Read()) { string thisrow = ""; for (int l = 0; l < Reader.FieldCount; l++) thisrow += Reader.GetValue(l) + ""; comboBox1.Items.Add(thisrow); listBox1.Items.Add(thisrow); } conn.Close();

Sorry For that because am not too good in Coding i learn every thing i know in it from internet

03/27/2012 09:21 cin0s#2

To insert data:

Code:

//How I open the connection:
ConnectionString = "SERVER=" + parmarr[0] + ";" +
                                  "DATABASE=" + parmarr[1] + ";" +
                                  "UID=" + parmarr[2] + ";" +
                                  "PASSWORD=" + parmarr[3] + ";";

MySqlConnection connection = new MySqlConnection(ConnectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();

//Here is the imortant part:
command.CommandText = "INSERT INTO mytable (column1,column2) VALUES ('test', 'test2')";
 command.ExecuteNonQuery();
//----

//Close the connection:
command.Dispose();
connection.Close();
connection.Dispose();

And to get data:

Code:

ConnectionString = "SERVER=" + parmarr[0] + ";" +
                                  "DATABASE=" + parmarr[1] + ";" +
                                  "UID=" + parmarr[2] + ";" +
                                  "PASSWORD=" + parmarr[3] + ";";

connection = new MySqlConnection(ConnectionString);
connection.Open();
command = connection.CreateCommand();

command.CommandText = "SELECT name FROM account;"
MySqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
//Whatever you want to do with the data...
string mystring = reader[0].ToString(); //I think this is what you need instead of Reader.GetValue()
}
reader.Close(); //Very important! You can't do another MySQL operation before you close the reader.
command.Dispose();
connection.Close();
connection.Dispose();

I haven't tested the code, but it should work...
I think the way you

03/27/2012 18:10 wolfvb#3

i will test it and see if it work any way work or no thanks for your time and for your help
regard wolfvb

PHP Code:


			
 while (Reader.Read())
                   {
                       // string to get the data in row
                       string thisrow = "";
                       //this loop work on columns
                       // in our example we have id,name,phone

                       string mystring = Reader[0].ToString(); //I think this is what you need instead of Reader.GetValue()
                           thisrow += Reader.GetValue(0) + "";
                       // we add the data in single row to listbox
                       comboBox1.Items.Add(thisrow);
                       listBox1.Items.Add(thisrow);
                   }
reader.Close(); //Very important! You can't do another MySQL operation before you close the reader.
command.Dispose();
connection.Close();
connection.Dispose();

its have same problem get names
in same way

PHP Code:


			
System.Byte[]

so any ideas its a privet server db Eo DB if this make any changes in codes
and there is a problem in this too

PHP Code:


			
MySqlConnection conn = new MySqlConnection();
            conn = new MySqlConnection();
            conn.ConnectionString = "server=" + cbServerSelect.Text + ";" + "user id=" + txtUserInput.Text + ";" + "password=" + txtPassInput.Text + ";" + "database=my";



            MySqlCommand command = conn.CreateCommand();
            //Here is the imortant part:
            command.CommandText = "UPDATE `cq_user` SET `name`='wolfvb[PM]' WHERE (`name`='[GM]~Ev!l~[PM]')";
            command.ExecuteNonQuery();
            //----

            //Close the connection:
            command.Dispose();
            conn.Close();
            conn.Dispose();

the problem in here

PHP Code:


			
command.ExecuteNonQuery();

03/27/2012 18:42 cin0s#4

Ok. You want to have all "name"s form the table "account" in a listbox. Right?

Try this:

Code:

command.CommandText = "SELECT name FROM account";
Reader = command.ExecuteReader();
while (Reader.Read())
{
listBox1.Items.Add(Reader[0].ToString());
}

I seriously don't know what you want to do with the for loop if you reade only one column from the database...

03/27/2012 19:11 wolfvb#5

damn the same problem i don't know why its get name like that
[Only registered and activated users can see links. Click Here To Register...]
the insert part working good now with no problem the only problem here now

03/27/2012 19:56 cin0s#6

Are you sure that the insert part work correctly? Please look with a tool like phpmyadmin or something else, if the data in the database is correct.
Maybe "System.Byte[]" is written in every Row of the table...

03/27/2012 20:42 wolfvb#7

i use Navicat to mage the db
and this is the table account and yeas the insert part work good i test it 3 times and work very good this a pic from table account
[Only registered and activated users can see links. Click Here To Register...]

03/27/2012 20:54 cin0s#8

You use the MySQL Connector Net Assemblie "MySql.Data.dll". Right?

Is "name" a varchar?
Try to read a integer from the db (for example "pointtime") [I think it's a a int...]

03/27/2012 21:15 wolfvb#9

its get numbers well with no problem so it get "id,etc.." well but in passsword and names this problem happen

03/27/2012 21:50 cin0s#10

Do you create the table in the code? For example:
"CREATE TABLE IF NOT EXISTS user (id INT NOT NULL AUTO_INCREMENT, name VARCHAR (256), password VARCHAR (256), PRIMARY KEY (id));" ?

Could you post this string?

03/28/2012 01:11 wolfvb#11

there is the string

PHP Code:


			
CREATE TABLE `account` (
  `id` int(4) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(32) binary NOT NULL default '',
  `password` varchar(32) binary NOT NULL default '',
  `type` int(4) unsigned NOT NULL default '0',
  `point` int(4) default '0',
  `pointtime` int(4) unsigned zerofill NOT NULL default '20020318',
  `online` int(4) unsigned zerofill NOT NULL default '0000',
  `reg_date` datetime default NULL,
  `licence` int(4) unsigned default '0',
  `reg_flag` bigint(12) unsigned default '3',
  `netbar_ip` varchar(15) default '127.0.0.1',
  `ip_mask` varchar(15) default '255.255.255.255',
  `add_type` smallint(1) unsigned zerofill NOT NULL default '0',
  `VIP` int(4) unsigned NOT NULL default '3',
  `offline` int(4) default NULL,
  `pwd` varchar(32) default NULL,
  `idnum` varchar(20) default NULL,
  `email` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_name_pass` (`name`,`password`),
  UNIQUE KEY `index_username` (`name`),
  KEY `index_type` (`type`),
  KEY `index_regtype` (`reg_flag`),
  KEY `index_isp` (`type`,`netbar_ip`),
  KEY `typereg` (`type`,`reg_date`)
) TYPE=MyISAM;

03/28/2012 08:33 cin0s#12

Quote:

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings.

[Only registered and activated users can see links. Click Here To Register...]

Try to convert the bytearray you get into a string.
listBox1.Items.Add(System.Text.ASCIIEncoding.GetSt ring(Reader[0])) or listBox1.Items.Add(System.Text.ASCIIEncoding.GetSt ring(Reader[0].ToString()))
(not sure which one) should do it...

If this doesn't work delte the word 'binary' in your CREATE TABLE string and create the table new.