MySQL database connectivity using c#

Question

0.00/5 (No votes)

See more:

Hi..
I m Connecting to mysql using c#.I want to get last inserted id.I want to use that last inserted id+1 when next time i will run application..
But whenever i will run my application get id 1 everytime..
Thanks..

Posted 26-Sep-13 18:50pm

Rohini Shirke

Add a Solution

Comments

Aniket Yadav 27-Sep-13 1:46am

Will you please explain in detail what exactly you want to achieve? Example will be more descriptive

Rohini Shirke 27-Sep-13 1:59am

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas ktg · Answer 1 · 2013-09-26T20:16:00

Solution 1

This is to get the last inserted id from the database table when the ID is Auto-increment.

C#

string InsertedId = "";
RowAffected = cmd.ExecuteCommand();

//After inserting the record you call this function to get the last inserted id from the table.
string query="SELECT LAST_INSERT_ID() FROM studinfo";
if (RowAffected > 0)
       cmd= new MySqlCommand(query, Con);
       InsertedId = cmd.ExecuteScalar();

This is another way to get the lastinserted from the table.

C#

string filename = txtImage.Text;
        byte[] imagedata;
        fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
        br = new BinaryReader(fs);
        imagedata = br.ReadBytes((int)fs.Length);
        br.Close();
        fs.Close();

        string query = "SELECT max(ID) FROM studinfo";
        cmd = new MySqlCommand(query, con);
        InsertedId = cmd.ExecuteScalar();
        //When the first time executes InsertedId will be null or empty since there wont be any records stored in the table
        if (InsertedId == "")
        {
            //Assign default value as 1.
            //then pass it as parameters
            InsertedId = 1;
        }
        else
        {
            //it will return the last inserted id you make it plus 1 so that it will get increment to get a new id.
            //then pass it as parameters
            InsertedId = InsertedId + 1;
        }

        string cmdText = "Insert into studinfo values(@ID,@First,@Last,@address,@Image)";
        cmd = new MySqlCommand(cmdText, con);
        cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(InsertedId));
        cmd.Parameters.AddWithValue("@First", txtFirstName.Text);
        cmd.Parameters.AddWithValue("@Last", txtLastName.Text);
        cmd.Parameters.AddWithValue("@address", txtAddress.Text);
        cmd.Parameters.AddWithValue("@Image", imagedata);
        int rowaffected = cmd.ExecuteNonQuery();
        //string query = "SELECT LAST_INSERT_ID() FROM studinfo";
        if (rowaffected > 0)
        {
            MessageBox.Show("Inserted sucessfully");
            Clear();
            txtFirstName.Focus();
        }

Hope this helps you.

Posted 26-Sep-13 20:16pm

Thomas ktg

Updated 26-Sep-13 22:10pm

v5

Comments

Rohini Shirke 27-Sep-13 2:39am

hi Thomas..
Thanks for reply but i will give error in follwing statement
InsertedId = cmd.ExecuteScalar("SELECT LAST_INSERT_ID() FROM studinfo");
error will be :"No overload for 'ExecuteScalar' takes 1 arguement"

Thomas ktg 27-Sep-13 3:07am

Updated my solution look at this.

Rohini Shirke 27-Sep-13 3:20am

Hello Sir..
I have write this code but it will give error "Input string is not in correct format"
try
{
con = new MySqlConnection(cs);
con.Open();
if (txtFirstName.Text.Length > 0 && txtLastName.Text.Length > 0)
{
string filename = txtImage.Text;
byte[] imagedata;
fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
br = new BinaryReader(fs);
imagedata = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();

string cmdText = "Insert into studinfo values(@ID,@First,@Last,@address,@Image)";
cmd = new MySqlCommand(cmdText, con);

cmd.Parameters.AddWithValue("@ID",Convert.ToInt32(InsertedId));
cmd.Parameters.AddWithValue("@First", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Last", txtLastName.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Image", imagedata);
int rowaffected = cmd.ExecuteNonQuery();
string query = "SELECT LAST_INSERT_ID() FROM studinfo";
if (rowaffected > 0)
{
cmd = new MySqlCommand(query, con);
InsertedId = (string)cmd.ExecuteScalar();

MessageBox.Show("Inserted sucessfully");

Clear();
txtFirstName.Focus();
}

}
else
{
MessageBox.Show("Incomplete Data", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtFirstName.Focus();
}

}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message);
}
finally
{
con.Close();
}

Thomas ktg 27-Sep-13 3:30am

I do not know whether I have understood wrong or you dont understand what i have given.
If the record is inserted only then you can get the last inserted id as per the solution i have given.
And most important thing is that you using the insertid before it is being executed from the database then how can you assign the value?
Ok tell what is the datatype of ID in the database?

Thomas ktg 27-Sep-13 4:11am

I have updated the solution from your point of view. Please let me know if there is any doubt.

Rohini Shirke 27-Sep-13 4:14am

Datatype of id is int..
consider case when i will first time run my c# application & insert two values in table studinfo..when next time i m executing application at that time i want to get ID 3..

Thomas ktg 27-Sep-13 4:18am

Did you find the updated solution helpful? I think this will suit your requirement.

Rohini Shirke 27-Sep-13 4:22am

No sir..
It will give error "Input string is not in correct format".
error will be on following line
cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(InsertedId));

Thomas ktg 27-Sep-13 4:26am

what is the value returned by cmd.ExecuteScalar()?
make it as Object and check like this
Object obj = cmd.ExecuteScalar();
if (obj != null)
InsertedId = obj.ToString();

Rohini Shirke 27-Sep-13 4:40am

First i have use insertedId & then assign value to insertedId variable after inserting one row..at first time what about value insertid.

Thomas ktg 27-Sep-13 4:44am

At first time when you execute the value will either be null or empty then you assign the default value to the insertedid. Next time it will get the last inserted id and you can increment with 1.

Rohini Shirke 27-Sep-13 4:24am

datatype of ID is int& in programming datatype of inserted id is string so i convert that value in int???is correct or not?

Thomas ktg 27-Sep-13 4:30am

It depends on the value the string holds.If it contains int value you can convert to integer using convert.ToInt() else you can use int.Tryparse to make the same it will not throw error even if it contains some string values.
http://www.dotnetperls.com/int-tryparse

Thomas ktg 27-Sep-13 4:31am

what is the value you get for InsertedId when you execute the statement?

Rohini Shirke 27-Sep-13 4:51am

when i run my application first time.Then it will insert correct value..but when i stop debugging & whenever next time i will run application then value for insertedId is one it will give error duplicate value for primary key..

Thomas ktg 27-Sep-13 4:55am

Next time when you run you increment the inserted id value by 1. It wont make duplicate.

Rohini Shirke 27-Sep-13 5:06am

At first time when i insert 3 rows..& stop debugging..
At next time inseertid have value 1 instead of 3...
Everytime whenever i will insert any number of rows next time value for insertid will be one..

Rohini Shirke 27-Sep-13 5:08am

I think this is because value of insertid will be updated after inserting atleast one row.
private void btnSave_Click(object sender, EventArgs e)
{

try
{
con = new MySqlConnection(cs);
con.Open();
if (txtFirstName.Text.Length > 0 && txtLastName.Text.Length > 0)
{
string filename = txtImage.Text;
byte[] imagedata;
fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
br = new BinaryReader(fs);
imagedata = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();

string cmdText = "Insert into studinfo values(@ID,@First,@Last,@address,@Image)";
cmd = new MySqlCommand(cmdText, con);

//object ID="Select count(*) from studinfo";

cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(InsertedId));

cmd.Parameters.AddWithValue("@First", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Last", txtLastName.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Image", imagedata);
int rowaffected = cmd.ExecuteNonQuery();
string query = "SELECT LAST_INSERT_ID() FROM studinfo";
if (rowaffected > 0)
{
cmd = new MySqlCommand(query, con);

Object obj = cmd.ExecuteScalar();
if (obj != null)
InsertedId = obj.ToString();

MessageBox.Show("Inserted sucessfully");

Clear();
txtFirstName.Focus();
}

}
else
{
MessageBox.Show("Incomplete Data", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtFirstName.Focus();
}

}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message);
}
finally
{
con.Close();
}

}

Thomas ktg 27-Sep-13 5:09am

i think the code may not be correct. please show the code to get the lastinserted id. when you use max(ID) it will return the lastinserted id only.

Thomas ktg 27-Sep-13 5:19am

I think you have not looked at the updated solution please refer the solution and make changes accordingly.

Rohini Shirke 27-Sep-13 5:23am

sir when i will run my application first time then it insert any number of rows without error..but next time it gives 1 everytime.Just i have use max(id) instead of last_insert_id.

Thomas ktg 27-Sep-13 5:29am

string filename = txtImage.Text;
byte[] imagedata;
fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
br = new BinaryReader(fs);
imagedata = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();

string query = "SELECT max(ID) FROM studinfo";
cmd = new MySqlCommand(query, con);
InsertedId = cmd.ExecuteScalar();
//When the first time executes InsertedId will be null or empty since there wont be any records stored in the table
if (InsertedId == "")
{
//Assign default value as 1.
//then pass it as parameters
InsertedId = 1;
}
else
{
//it will return the last inserted id you make it plus 1 so that it will get increment to get a new id.
//then pass it as parameters
InsertedId = InsertedId + 1;
}

string cmdText = "Insert into studinfo values(@ID,@First,@Last,@address,@Image)";
cmd = new MySqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(InsertedId));
cmd.Parameters.AddWithValue("@First", txtFirstName.Text);
cmd.Parameters.AddWithValue("@Last", txtLastName.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Image", imagedata);
int rowaffected = cmd.ExecuteNonQuery();
//string query = "SELECT LAST_INSERT_ID() FROM studinfo";
if (rowaffected > 0)
{
MessageBox.Show("Inserted sucessfully");
Clear();
txtFirstName.Focus();
}

This is how you have implemented your code or any other way?

Rohini Shirke 27-Sep-13 6:16am

Thank u so much...finally my code will run ..can i give my final code?

Thomas ktg 27-Sep-13 6:27am

if it runs well that is your effort to make it run. Ok

Rohini Shirke 27-Sep-13 6:32am

k..
But along with my effort your guidance is also important.

Thomas ktg 27-Sep-13 6:33am

Thank you

V.Lorz 27-Sep-13 5:10am

Beware that getting the value for the last inserted Id does not mean that it corresponds to the row you've just inserted if insertions are done concurrently from different processes, threads, or even computers.