CODEWITHSUNDEEP
c#sql.net
natso
natso

Reputation: 97

Retrieve the id value from a table, to use it later

I am tryig to retrieve the value P_id (auto increment) from table P_identity to store it in table Communication. It appears that kind of error

"Error :System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value 'SELECT P_id FROM P_identity WHERE Amka = '15598970695'' to data type int."

How can I resolve it?

try
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn.Open();

            string pat_id = "SELECT P_id FROM P_identity WHERE Amka  = '" + TextBoxAmka.Text + "'";
            SqlCommand com2 = new SqlCommand(pat_id, conn);

            com2.ExecuteNonQuery();
            Response.Write("");

            string insert_pacom = "insert into Communication (P_id,Address,Region,Town,TK,Mobile_number,Country,House_number,Email) values (@pa_id, @add, @nomos, @poli, @tk, @kinito, @xora, @oikia, @email) ";
            SqlCommand com = new SqlCommand(insert_pacom, conn);
            com.Parameters.AddWithValue("@pa_id", pat_id.ToString());
            com.Parameters.AddWithValue("@add", txtadd.Text);
            com.Parameters.AddWithValue("@nomos", txtnomos.Text);
            com.Parameters.AddWithValue("@poli", txttown.Text);
            com.Parameters.AddWithValue("@tk", txttk.Text);
            com.Parameters.AddWithValue("@xora", txtxora.Text);
            com.Parameters.AddWithValue("@kinito", txtkinito.Text);
            com.Parameters.AddWithValue("@oikia", txtoikia.Text);
            com.Parameters.AddWithValue("@email", txtemail.Text);


            com.ExecuteNonQuery();

            Response.Write("<script>alert('Τα στοιχεία αποθηκεύτηκαν επιτυχώς!')</script>");

            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write("Error :" + ex.ToString());
        }

Upvotes: 0

Views: 50

Answers (1)

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98810

Sounds like you just need to delete single quotes from

WHERE Amka  = '" + TextBoxAmka.Text + "'"

part as

WHERE Amka  = " + TextBoxAmka.Text

which Amka column seems as a numerical value. And using ExecuteNonQuery seems pointless for a SELECT statement. You can use ExecuteReader if you wanna get the result values from your statement.

Or if your SELECT statement returns only one value, you can use ExecuteScalar which is exactly fits the situation.

int id = (int)com2.ExecuteScalar();

A few things more;

  • Use parameterized queries for your SELECT statement as you did for INSERT statement.
  • Use using statement to dispose your connection and commands automatically instead of calling Close method manually.
  • Do not use AddWithValue method. It may generate unexpected results sometimes. Use Add method overload to specify your parameter type and it's size.

Upvotes: 1

Related Questions