How to Converting SqlCommand Type To Int Type

Question

I Want To Convert SqlCommand Result into Int Value How Can i Do that?? Please Help With That What I Have Tried IS Below:

The stp_no in Table Have Set Identity Property. And I want To insert That Auto Generated Numbers into Another Table But It Shows Always Error Like "Connot Convert SqlCommand Type to Int Type"

    SqlCommand dvgcmd, snocmd;
    snocmd = new SqlCommand("SELECT stp_no FROM MaterialTestMaster", con);
    dvgcmd = new SqlCommand("INSERT INTO MaterialTestDetail(stp_no,test_no,test_name,test_type,test_spec,high_limit,low_limit)" +
                                                     "VALUES('"+ snocmd +"','" + @matTstDataGridView.Rows[j].Cells[0].Value + "'," +
                                                             " '" + @matTstDataGridView.Rows[j].Cells[1].Value + "'," +
                                                             " '" + @matTstDataGridView.Rows[j].Cells[2].Value + "'," +
                                                             " '" + @matTstDataGridView.Rows[j].Cells[3].Value + "'," + 
                                                             " '" + @matTstDataGridView.Rows[j].Cells[4].Value + "'," +
                                                             " '" + @matTstDataGridView.Rows[j].Cells[5].Value + "')", con);

Please Help M to Solve This Problem :)

score 0 · Answer 1 · answered May 12 '12 at 06:44

SqlCommand dvgcmd;
dvgcmd = new SqlCommand("INSERT INTO MaterialTestDetail(stp_no,test_no,test_name,test_type,test_spec,high_limit,low_limit)" +
                                                 "VALUES((SELECT stp_no FROM MaterialTestMaster),'" + @matTstDataGridView.Rows[j].Cells[0].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[1].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[2].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[3].Value + "'," + 
                                                         " '" + @matTstDataGridView.Rows[j].Cells[4].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[5].Value + "')", con);

It Gives Error "Sub queries Not Allowed In Context. Ony Scaler Expression Are Allowed" — mitul, May 13 '12 at 02:50

score 0 · Answer 2 · answered May 12 '12 at 06:50

The answer of juergen should work, you have to execute first your command, then use the result as next:

int id = int.Parse(snocmd.ExecuteScalar().ToString());
dvgcmd = new SqlCommand("INSERT INTO MaterialTestDetail(stp_no,test_no,test_name,test_type,test_spec,high_limit,low_limit)" +
                                                 "VALUES('"+ id +"','" + @matTstDataGridView.Rows[j].Cells[0].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[1].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[2].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[3].Value + "'," + 
                                                         " '" + @matTstDataGridView.Rows[j].Cells[4].Value + "'," +
                                                         " '" + @matTstDataGridView.Rows[j].Cells[5].Value + "')", con);

ExecuteScaler() will insert Only 1st Record Only So It'll Give Primary Key Constrain Error When I Try to insert Another Record Because MaterialTestDetail hve stp_no is primary key. Can You Sort It Out? — mitul, May 13 '12 at 02:57
You can change your command to: snocmd = new SqlCommand("SELECT MAX(stp_no) FROM MaterialTestMaster", con); But it can give you the same problems if at least is not inserted first one MaterialsMaster for each MaterialTestDetail that you want insert. — Marco Medrano, May 17 '12 at 04:12

How to Converting SqlCommand Type To Int Type

2 Answers2