ASP.NET MySQL Undefined Parameter

Question

Good day!

I have a code here that inserts, but never updates. There error I get is Parameter '@id_number' must be defined.

My code:

string query = "INSERT INTO pupil (`fname`, `lname`, `mi`) VALUES (@fn, @ln, @mi)";
MySqlCommand com = new MySqlCommand(query, connection);

com.Parameters.AddWithValue("@fn", txtAddFName.Text);
com.Parameters.AddWithValue("@ln", txtAddLName.Text);
com.Parameters.AddWithValue("@mi", txtAddMI.Text);
com.ExecuteNonQuery();

//create ID number
long lastID = com.LastInsertedId;
int length = 4;
string currentYear = DateTime.Now.Year.ToString("yy");
string result = lastID.ToString().PadLeft(length, '0');
string id_number = currentYear + "-" + result ;
string updateIDNumber = "UPDATE pupil SET id_number=@id_number WHERE pupil_id=@pupil_id";
MySqlCommand IDcom = new MySqlCommand(updateIDNumber, connection);

com.Parameters.AddWithValue("@id_number", id_number);
com.Parameters.AddWithValue("@pupil_id", lastID);
IDcom.ExecuteNonQuery();

The Insert code works, but the Update isn't. What I wanted to do is to get the last inserted ID and update it's field id_number with the last 2 digits of the current year + the last inserted ID with 4 leading 0's. For example if the last Inserted ID is 1, the value of lastID variable will be 16-0001.

The schema by the way is this:

pupil_id INT(11) AUTO_INCREMENT
fname VARCHAR(50)
lname VARCHAR(50)
mi VARCHAR(50)
id_number VARCHAR(255)

Any help would be much appreciated.

CodeNotFound · Accepted Answer · 2016-04-24 03:17:52Z

2

You are using the wrong command instance:

com.Parameters.AddWithValue("@id_number", id_number);
com.Parameters.AddWithValue("@pupil_id", lastID);

Instead of com just use IDcom

IDcom.Parameters.AddWithValue("@id_number", id_number);
IDcom.Parameters.AddWithValue("@pupil_id", lastID);

answered Apr 24, 2016 at 3:17

CodeNotFound

23.4k10 gold badges75 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wobsoriano Over a year ago

@cableload It's okay, still give you an up :)

wobsoriano Over a year ago

By the way guys, it works but I got yy-0001 instead or 16-0001

CodeNotFound Over a year ago

Use DateTime.Now.ToString("yy")without Yearproperty. Consider to accept as an answer if it works.

cableload · Accepted Answer · 2016-04-24 03:19:18Z

1

You declared a second MySqlCommand IDcom and so you should add parameters for that.You were adding params to the older command object.

IDcom.Parameters.AddWithValue("@id_number", id_number);
IDcom.Parameters.AddWithValue("@pupil_id", lastID);

answered Apr 24, 2016 at 3:19

cableload

4,4156 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

ASP.NET MySQL Undefined Parameter

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related