CODEWITHSUNDEEP
c#mysqlasp.net.net
wobsoriano
wobsoriano

Reputation: 13434

ASP.NET MySQL Undefined Parameter

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.

Upvotes: 1

Views: 107

Answers (2)

cableload
cableload

Reputation: 4375

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);

Upvotes: 1

CodeNotFound
CodeNotFound

Reputation: 23200

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);

Upvotes: 2

Related Questions