get value from SQL table and set condition on it

Question

I have a table named tbDepartment (id, title, abbreviation) and a GridView containing two columns Title and Abbreviation

I need to know if the Title inserted by the user in the gridView is equal to the one in the table tbDepartment. If true I want to get the id, otherwise I need to create new user.

What I wrote is not entering the if statement. Can someone tell me how to correct it?

string sql2 = "Insert into tbDepartment ([Title], [Abbreviation]) values (@Desc, @DepCode)";

string sqlDep = "Select [ID] from tbDepartment where Title = '" + gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]).ToString() +"'" ;
SqlCommand cmdDep = new SqlCommand(sql2, connection);

if (sqlDep == 0)
{
    cmdDep.Parameters.Add("@Desc", SqlDbType.VarChar,50);
    cmdDep.Parameters.Add("@DepCode", SqlDbType.VarChar, 50);
    cmdDep.Parameters["@Desc"].Value = Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepDesc]));
    cmdDep.Parameters["@DepCode"].Value = Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]));
}

You set an instance of string to `sqlDep` then after checking if it is `null` ? — Soner Gönül, Feb 13 '13 at 15:21
I'll just throw this out there: `SQL + String concatenation = BAD` — Mike G, Feb 13 '13 at 15:25
I think I see the problem now. Do you think that the result of the query you wrote will be in the sqlDep variable? — Sashenka, Feb 13 '13 at 15:25
This question makes zero sense where are you executing the code for the select query..? I can see the Insert command being executed.. you need to read up on how to execute queries in C# — MethodMan, Feb 13 '13 at 15:33
u're DJ.. I know that I'm writing wrong things it's just that I don't know how to make the query return a variable that's why I wrote my question.. Anw Thanks for your help — Nejthe, Feb 14 '13 at 06:28

score 3 · Answer 1 · edited May 23 '17 at 10:24

First execute a query to get the ID with ExecuteScalar. If it returns a not null value (meaning that you have found the Title) you have your ID. Else execute the insert of the new data.

Meanwhile, do not use string concatenation to execute sql commands. (SQL Injections)

string sql2 = "Insert into tbDepartment ([Title], [Abbreviation]) values (@Desc, @DepCode)";
string sqlDep = "Select [ID] from tbDepartment where Title = @title" 

SqlCommand cmdDep = new SqlCommand(sqlDep, connection);
cmdDep.Parameters.AddWithValue("@title", gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]).ToString();
object result = cmdDep.ExecuteScalar();
if(result != null)
{
    cmdDep.Parameters.Clear();
    cmdDep.CommandText = sql2;
    cmdDep.Parameters.AddWithValue("@Desc", Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepDesc]);
    cmdDep.Parameters.AddWithValue("@DepCode", Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]));
    cmdDep.ExecuteNonQuery();
}
else
    int id = Convert.ToInt32(result);

A part from this, there is something that is not quite correct in your query above. You get the value to search for Title from gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]) But in the insert query you use this same grid cell to update the value for the column Abbreviation while the Title column is updated using another cell gridView3.GetRowCellValue(i, gridView3.Columns[DepDesc])) probably the correct insert text is:

string sql2 = "Insert into tbDepartment ([Title], [Abbreviation]) values (@DepCode, @Desc)";

+1 for using parameters and advising against string concatenation in sql commands. — Sashenka, Feb 13 '13 at 15:32

score 1 · Answer 2 · answered Feb 13 '13 at 15:25

SqlCommand.ExecuteScalar returns the first column of the first row in the result set returned by the query. This is how you get your id or null otherwise.

string sql2 = "Insert into tbDepartment ([Title], [Abbreviation]) values (@Desc, @DepCode)";

string sqlDep = "Select [ID] from tbDepartment where Title = '" + gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]).ToString() +"'" ;
SqlCommand cmdDep = new SqlCommand(sql2, connection);
SqlCommand cmdUser = new SqlCommand(sqlDep , connection);

var id = cmdUser.ExecuteScalar() as int?;

if (id == null) //nothing found
{
   //add new
    cmdDep.Parameters.Add("@Desc", SqlDbType.VarChar,50);
    cmdDep.Parameters.Add("@DepCode", SqlDbType.VarChar, 50);
    cmdDep.Parameters["@Desc"].Value = Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepDesc]));
    cmdDep.Parameters["@DepCode"].Value = Convert.ToString(gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]));
}
else
{
   //edit
}

score 1 · Accepted Answer · answered Feb 13 '13 at 15:37

Make sure you open the connection before executing. Using using will make sure to dispose the connection after using it. First execute a DateReader to check any records and then execute a NonQuery to insert. Also, you can do this in a single query or using a stored procedure.

using(SqlConnection con = new SqlConnection("YourConnectionString"))
{
   string selectQuery = "Select [ID] from tbDepartment where Title = @Title";
   SqlCommand cmd = new SqlCommand(selectQuery, con);

   cmd.Parameters.AddWithValues("@Title", "Title_Value");
   con.Open();
   SqlDataReader reader = cmd.ExecuteReader();

   if(!reader.HasRows){
       string insertQuery = "Insert into tbDepartment ([Title], [Abbreviation]) " +
                            "values (@Title, @Abb)";

       cmd.CommandText = insertQuery;
       cmd.Parameters.AddWithValues("@Abb", "Abb_Value");
       cmd.ExecuteNonQuery();
   }

}

that work so correctly.. Great Kaf !! – Nejthe Feb 14 '13 at 07:22 — Nejthe, Feb 14 '13 at 07:22

score 0 · Answer 4 · answered Feb 13 '13 at 15:20

0

But sqlDep is set to an instance of a string. It will never be null. You need to re-evaluate your logic.

answered Feb 13 '13 at 15:20

Daniel Kelley

7,579
6
42
50

I'm new in c# i don't know how to set a string containing the id – Nejthe Feb 13 '13 at 15:21
@Nejthe What are you trying to achieve with the line `string sqlDep = ...`? Do you mean the who thing will be null if `gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]).ToString()` is null? – Daniel Kelley Feb 13 '13 at 15:22
if the title in the table is not equal the gridView3.GetRowCellValue(i, gridView3.Columns[DepCode]).ToString() i won't be getting any ID so the string will be null .. That's how i'm thinking – Nejthe Feb 13 '13 at 15:25

get value from SQL table and set condition on it

4 Answers4