Mysql change values

Accepted answer

Use something like this in your codefor selectingthe tickets according to the ID
"Select tickets from test where id = '" + trade.OtherSID + "'";

meinenr14y ago

It works now!
Thanks for your help!

            string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            connection.Open();
            command.CommandText = "Select tickets from test where id = '" + trade.OtherSID + "'";
            Reader = command.ExecuteReader();
            int Tickets = 0;

            if(Reader.HasRows)
            {
                while(Reader.Read())
                {
                    string row = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        row += Reader.GetValue(i).ToString() + "";
                    Console.WriteLine(row);
                    trade.SendMessage("You already have: " + row + " Tickets." );
                    Tickets = Convert.ToInt32(row) + ScrapPutUp;
                    }
                trade.SendMessage("Now you have: " + Tickets + " Tickets." );
                //command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";
                command.CommandText = "Update test Set tickets = '" + Tickets + "'" + "where ID = '" + trade.OtherSID + "'";
                Reader.Close();

                command.ExecuteNonQuery();
            }
            else
            {
                command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";
Reader.Close();
command.ExecuteNonQuery();
}
connection.Close();

meinenr14y ago

I have write my script in my last post. But It dosen't work. My database has the column's 'id' and 'tickets'. I yust wanted to know, how i can retrieve the value of 'tickets' in the row, where the value of "'id' = 'XYZ'".

Sukesh Marla14y ago

meinenr its very difficult to to understand becuase conversation list is very long/..

Do just one thing, Upload the source code and Database script(fo table), I will check and revert back

Shivanand Arur14y ago

Hey meinenr.... Will get back to you in some time...

meinenr14y ago

            string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM test";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string row = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    row += Reader.GetValue(i).ToString() + ", ";
                Console.WriteLine(row);
            }
            Reader.Close();

            command.CommandText = "Select tickets from test where ID = '" + trade.OtherSID + "'";
            Reader = command.ExecuteReader();
            int Tickets = 0;

            if(Reader.HasRows)
            {
                while(Reader.Read())
                {
                    Tickets = Int32.Parse(Reader[0].ToString());
                }
                Reader.Close();
                Tickets = Tickets + ScrapPutUp;
                command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";
                command.ExecuteNonQuery();
            }
            else
            {
                command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";
Reader.Close();
command.ExecuteNonQuery();
}
connection.Close();

Shivanand Arur14y ago

Can you please post the code which you have written currently?

meinenr14y ago

But earlier it was working???

No!?!
It doesen't work earlier and it doesen't work now.

If your id dosen't exist, it create a new row,
but if your id already exist, it get an error.



Dosen't work with Varchar(500) too.

Shivanand Arur14y ago

But earlier it was working??? Try changing the Id DataType to Varchar(500)

meinenr14y ago

ERROR:

The Type of the Column 'id' in my database is 'text'
The Type of the Column 'tickets' in my database is 'int'

Shivanand Arur14y ago

OK... M Sorry...

But is it working fine now???

meinenr14y ago

You always write:

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

but it must be:

command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";

:D

Shivanand Arur14y ago

Please use this code....

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);

MySqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT * FROM test";

MySqlDataReader Reader;

connection.Open();

Reader = command.ExecuteReader();

while (Reader.Read())

{

string row = "";

for (int i = 0; i < Reader.FieldCount; i++)

row += Reader.GetValue(i).ToString() + ", ";

Console.WriteLine(row);

}

Reader.Close();

command.CommandText = "Select tickets from test where ID = '" + trade.OtherSID + "'";

Reader = command.ExecuteReader();

int Tickets = 0;

if(Reader.HasRows)

{

while(Reader.Read())

{

Tickets = Int32.Parse(Reader[0].ToString());

}

Reader.Close();

Tickets = Tickets + ScrapPutUp;

command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";

command.ExecuteNonQuery();

}

else

{

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

Reader.Close();

command.ExecuteNonQuery();

}

connection.Close();

meinenr14y ago

I have used this Code:

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM test";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
int Tickets = 0;

if(Reader.HasRows)
{
while(Reader.Read())
{
Tickets = Int32.Parse(Reader[0].ToString());
}
Reader.Close();
Tickets = Tickets + ScrapPutUp;
command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";
command.ExecuteNonQuery();
}
else
{
command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";
Reader.Close();
command.ExecuteNonQuery();
}
connection.Close();

Here you can see the Errors(sorry, it's in German):

Translate:
German: Die Eingabezeichenfolge hat das falsche Format.
It mean: The input string has the wrong format.

Shivanand Arur14y ago

Please write the code where you are getting the error....

meinenr14y ago

ERROR: The input string has the wrong format.

Shivanand Arur14y ago

Let me know, if there are any more errors!!! According to me it should work fine now.

meinenr14y ago

Ok, Try it now.

Shivanand Arur14y ago

Ok, now does it works fine with "ToInt32" as you have posted above?

Shivanand Arur14y ago

Try using....
Tickets = Int32.Parse(Reader[0].ToString());

meinenr14y ago

Oh it mustn't be: toInt32 It must be: ToInt32

ERROR: The input character string has the wrong format.

Shivanand Arur14y ago

Let me know, if there are any errors.

meinenr14y ago

So this must be my full script:

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM test";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
int Tickets = 0;

if(Reader.HasRows)
{
while(Reader.Read())
{
Tickets = Convert.toInt32(Reader[0].ToString());
}
Reader.Close();
Tickets = Tickets + ScrapPutUp;
command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";
command.ExecuteNonQuery();
}
else
{
command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";
Reader.Close();
command.ExecuteNonQuery();
}
connection.Close();

Than he has an Error in line:
Tickets = Convert.toInt32(Reader[0].ToString());

'System.Convert' does not cantain a definition for 'toInt32'

Shivanand Arur14y ago

ACtually I was saying, copy the bold portion script and paste it wherever it has to... Do not delete the other portion of the code....

Anyways.... do one thing... Copy paste the entire code I have recently written and make changes wherever you have to.

meinenr14y ago

How I should only test this part:

int Tickets = 0;

if(Reader.HasRows)

{

while(Reader.Read())

{

Tickets = Convert.toInt32(Reader[0].ToString());

}

Reader.Close();

Tickets = Tickets + ScrapPutUp;

command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";

command.ExecuteNonQuery();

}

else

{

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

Reader.Close();

command.ExecuteNonQuery();

}

connection.Close();

without any ConnectionString etc. ??

Shivanand Arur14y ago

Try this out
Copy the Bold script in your code and try running your application!!! Do not copy the entire code... Only copy the bold portion in your code....

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);

MySqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT * FROM test";

MySqlDataReader Reader;

connection.Open();

Reader = command.ExecuteReader();

while (Reader.Read())

{

string row = "";

for (int i = 0; i < Reader.FieldCount; i++)

row += Reader.GetValue(i).ToString() + ", ";

Console.WriteLine(row);

}

Reader.Close();

command.CommandText = "Select tickets from test where ID = '" + trade.OtherSID + "'";

Reader = command.ExecuteReader();

int Tickets = 0;

if(Reader.HasRows)

{

while(Reader.Read())

{

Tickets = Convert.toInt32(Reader[0].ToString());

}

Reader.Close();

Tickets = Tickets + ScrapPutUp;

command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";

command.ExecuteNonQuery();

}

else

{

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

Reader.Close();

command.ExecuteNonQuery();

}

connection.Close();

Copy the Bold script in your code and try running your application!!!

meinenr14y ago

Ok...

Shivanand Arur14y ago

Hey Meinenr please wait, there is some problem in the code... Will revert back to you in some time....

meinenr14y ago

It display all the values of all the rows.
Like this:
Steam:123,1
Steam:856,5
Steam:652,2

Shivanand Arur14y ago

Hey Meinenr, does this part of the script works fine???

MySqlConnection connection = new MySqlConnection(myConnectionString);

MySqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT * FROM test";

MySqlDataReader Reader;

connection.Open();

Reader = command.ExecuteReader();

while (Reader.Read())

{

string row = "";

for (int i = 0; i < Reader.FieldCount; i++)

row += Reader.GetValue(i).ToString() + ", ";

Console.WriteLine(row);

}

Does it display's the row values???

meinenr14y ago

It create the new user in the Database, but if the User already Exist, it get this Error:

Shivanand Arur14y ago

You should keep it as "int" and on which line are you getting this error?

meinenr14y ago

The Type of the column 'tickets in my databese must be 'text' ot 'int'???

ERROR:
Invaild attemp to acces a field before calling Read()

Shivanand Arur14y ago

Try adding this....
Tickets = Convert.ToInt32(Reader[0].ToString());

meinenr14y ago

I have changed the following lines:

command.CommandText = "Update test Set tickets = " Tickets + "where ID = '" + trade.OtherSID + "'";
INTO
command.CommandText = "Update test Set tickets = " + Tickets + "where ID = '" + trade.OtherSID + "'";

AND

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

INTO

command.CommandText = "Insert into test(id,tickets) Values('"+trade.OtherSID+"', '"+ScrapPutUp+"')";

But i can't fix the last ERROR:
Tickets = Reader[0].ToString();
-> An implicit conversion of type "string" to "int" is not possible.

Shivanand Arur14y ago

I am not sure whether this will workout, but still try this and let me know if there are any issues...

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);

MySqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT * FROM test";

MySqlDataReader Reader;

connection.Open();

Reader = command.ExecuteReader();

while (Reader.Read())

{

string row = "";

for (int i = 0; i < Reader.FieldCount; i++)

row += Reader.GetValue(i).ToString() + ", ";

Console.WriteLine(row);

}

Reader.Close();

command.CommandText = "Select tickets from test where ID = '" + trade.OtherSID + "'";

Reader = command.ExecuteReader();

int Tickets = 0;

if(Reader.HasRows)

{

Tickets = Reader[0].ToString();

Tickets = Tickets + ScrapPutUp;

Reader.Close();

command.CommandText = "Update test Set tickets = " Tickets + "where ID = '" + trade.OtherSID + "'";

command.ExecuteNonQuery();

}

else

{

command.CommandText = "Insert into test(id,tickets) Values('" + trade.OtherSID + "'", "'" + ScrapPutUp + "'")";

Reader.Close();

command.ExecuteNonQuery();

}

connection.Close();

meinenr14y ago

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

THE FULL SCRIPT WITH THE VARIABLES:

Variable for 'id' = trade.OtherSID

Variable for 'Tickets' = ScrapPutUp

Id the user 'trade.OtherSID' dosen't exist in the column 'id', add the User and set his 'tickets' to 'ScrapPutUp'.

Else if the user 'trade.OtherSID' exist in the column 'id', add 'ScrapPutUp' tickets to his account.

Shivanand Arur14y ago

Here you have written a comment....
"If the user not exist, cteate a new line with the 'id' from the User and 1 ticket."

So Where are you creating the ID from??? Are you generating it manually, or passing it from another table???

Now for example.... If I do not have a User in my table(he is a new User), then from where are you adding the ID for that user???

Also tell me the name of you table and the connection string you are using for your app....

meinenr14y ago

OK

id	tickets
STEAM:123	6
STEAM:915	2

A user get a ticket:
If the user exist in the column 'id', add the ticket in the column 'tickets'.
If the user not exist, cteate a new line with the 'id' from the User and 1 ticket.

Shivanand Arur14y ago

Hello Meinenr... Sorry for the delay, but can you please explain the entire application again... The code you have posted in partly incorrect....

1. What exactly do you wanna do???
2. How many tables do you have in your database???
3. How many columns are there in the table where you are storing the Tickets???

Elaborate your requirements and the answers for the above mentioned questions correctly and properly.... I am really confused with what you are trying to do

meinenr14y ago

Now I only need to know,
how to get the Value of the column 'tickets' where the value of the column 'id = XYZ'

id	tickets
XYZ	?

-> I wanted to get the Value of '?'

meinenr14y ago

It just was an Example...

I haven't look, how i have named it in my table ;)

The username column is the id column.

Sorry I have named it column 'id' now...

Shivanand Arur14y ago

Then where is the Username Column???

meinenr14y ago

Column's: id, tickets

Variablesfor Column's:
id = trade.OtherSID
tickets = ScrapPutUp

Shivanand Arur14y ago

I think your logic for this query will change entirely... What columns do u have in your Test Table????

meinenr14y ago

ERROR:

Line: TicketValue = Convert.ToInt32(dr[0].ToString());

-> The name 'dr' does not exist in the ccurrent context

The script should be do:

If a user get 2 tickets:

If the user exist{
add 2 tickets to his account
}
ELSE
{
Create a account and set his tickets to 0
}

Shivanand Arur14y ago

You have written the script, but haven't mentioned what exactly is the issue or the error... I think you have again mixed up the code... What exactly do you wanna do??? Elaborate it.

1. Do you have the Username and the Ticket information in the Test Table???
2. What exactly do you wanna do?
3. If you have written this code, then where are you getting the issue?

meinenr14y ago

I don't understand...

I have used this script:

string myConnectionString = "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=;";

MySqlConnection connection = new MySqlConnection(myConnectionString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM test";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
int TicketValue = 0;
while (Reader.Read())
{
TicketValue = Convert.ToInt32(dr[0].ToString());
string row = "";
for (int i = 0; i < Reader.FieldCount; i++)
row += Reader.GetValue(i).ToString() + ", ";
Console.WriteLine(row);
}
Reader.Close();
TicketValue = TicketValue + 4;
command.CommandText = "Select 1 from test where ID = '" + trade.OtherSID + "'";
Reader = command.ExecuteReader();

if(Reader.HasRows)
{
PrintConsole("ALREADY EXIST!!", ConsoleColor.Red);
command.CommandText = "Update test Set tickets = '" + TicketValue + "'" + "where id = '" + trade.OtherSID + "'";
Reader.Close();
command.ExecuteNonQuery();
}
else
{
command.CommandText = "INSERT INTO test (id, tickets, level) Values ('"+trade.OtherSID+"', '"+ScrapPutUp+"', '0')";
Reader.Close();
command.ExecuteNonQuery();
}
connection.Close();

Can you say me now, how i can fiy the Error?

Shivanand Arur14y ago

Change it to the SqlDataReader Object name that you have written in your code... for example... in the above code the datareader object name is "Reader"...

meinenr14y ago

ERROR:

Line: TicketValue = Convert.ToInt32(dr[0].ToString());

-> The name 'dr' does not exist in the ccurrent context

I have make something wrong?

Shivanand Arur14y ago

Try this out...

MySqlConnection connection = new MySqlConnection("Your Connection String");

MySqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT Tickets FROM YourTableName where Username = 'User2'";

MySqlDataReader Reader;

connection.Open();

Reader = command.ExecuteReader();

int TicketValue = 0;

while (Reader.Read())