Why is unavailable always returned?

Question

I must be missing something really simple here but why is "Unavailable" always returned? I have also tried this How to Get a Specific Column Value from a DataTable? by Seattle Leonard but still no luck.

                    int ID;
        string sQuery = "INSERT INTO dbo.tbl_personDetails " +
             "(" +
                 "EmployeeID " +
                 ",Title " +
                 ",Name " +
             ")" +
         "VALUES" +
             "(" +
                 "@EmployeeID " +
                 ",@Title " +
                 ",@Name " +
             ") " ;

    SqlCommand comm = new SqlCommand(sQuery);

        comm.Parameters.Add(new SqlParameter("@EmployeeID", ToolTip.getUserName(GetUser.GetNtLoginID())));
        comm.Parameters.Add(new SqlParameter("@Title", ddlTitle2.Text.ToString()));
        comm.Parameters.Add(new SqlParameter("@Name", Name.Text.ToString()));


        ID = DBConnect.InsertRecordGetID(comm, sQuery);
        if (ID > 0)
        {

         //complete the Reference Number
            string divCode;
            string sSql = 
            "SELECT TheDivision FROM       dbo.tbl_Incident_Details   WHERE ID = " + ID;
            DataTable dt = new DataTable();
            dt = DBConnect.DataReaderDataTable(sSql);
            if(dt.Rows.Count > 0)
            {
                  divCode = dt.Rows[0][0].ToString();
            }
            else
            {
                divCode = "Unavailable";
            }

            string TheDate = DateTime.Now.Year.ToString();
            string TheYear = TheDate.Substring(2, 2);
            string RefNo = "FAI/" + ID + "/" + divCode + "/" + TheYear;


            string sSqlRefNo = "UPDATE dbo.tbl_personDetails " +
                                "SET " +
                                    "RefNo = '" + RefNo + "' " +
                                "WHERE " +
                                    "ID = @ID";
                SqlCommand commRefNo = new SqlCommand(sSqlRefNo);

            commRefNo.Parameters.Add(new SqlParameter("@RefNo", RefNo));
            commRefNo.Parameters.Add(new SqlParameter("@ID", ID));

            if (DBConnect.CmdExecute(commRefNo, sSqlRefNo))
            {
                Response.Redirect("~/FileUpload.aspx?id=" + ID);
            }
            else
            {
            }
        }
        else { }

    }

}

Any advice?

EDIT: I have posted the full function now. Also not divCode is a varchar in the DB and not an int.

dt {} base {System.ComponentModel.MarshalByValueComponent}: {} CaseSensitive: false ChildRelations: {System.Data.DataRelationCollection.DataTableRelationCollection} Columns: {System.Data.DataColumnCollection} Constraints: {System.Data.ConstraintCollection} DataSet: null DefaultView: {System.Data.DataView} DisplayExpression: "" ExtendedProperties: Count = 0 HasErrors: false IsInitialized: true Locale: {en-GB} MinimumCapacity: 50 Namespace: "" ParentRelations: {System.Data.DataRelationCollection.DataTableRelationCollection} Prefix: "" PrimaryKey: {System.Data.DataColumn[0]} RemotingFormat: Xml Rows: {System.Data.DataRowCollection} Site: null TableName: ""

You don't get any values? Have you tried executing the exact script on your DB? Did you get a result there? — Dieter B, Oct 23 '15 at 14:32
Are you sure that ID does in fact exist? Are you sure that the SQL code is correct? Have you tried what Dieter suggested? — GreatAndPowerfulOz, Oct 23 '15 at 14:34
Yup. On my DB I get back values. For instance with this query works: SELECT TheDivision FROM dbo.tbl_Incident_Details WHERE ID = 3 — shucode, Oct 23 '15 at 14:35
In that case, is your "ID" variable in the code is being filled properly? I would try to log the complete SQL statement so you can make sure it is executing what you think you are executing. — Andrew Smith, Oct 23 '15 at 14:37
If you're sure that you're script is correct, then your sql connection string is not correct / not open — Dieter B, Oct 23 '15 at 14:38
The issue could also be somewhere in the "DBConnect.DataReaderDataTable()" method. It could have an error and is not returning the expected table either. — Andrew Smith, Oct 23 '15 at 14:38
Is your connection right? I don't recognize the method you're using. I'd expect to see something along the lines of [this](http://stackoverflow.com/questions/18961938/populate-data-table-from-data-reader) — devlin carnate, Oct 23 '15 at 14:41
Thanks Dieter,Gread.And.Powerful.Oz,Andrew, and devlin that's all my code but no it's not working. Could this be confusion as to what ID value is held in ID? — shucode, Oct 23 '15 at 14:49
If you place a breakpoint on the line "if(dt.Rows.Count > 0)" , what can you see in the dt variable? — Dieter B, Oct 23 '15 at 14:53
Dieter, I have pasted the result above in original question. — shucode, Oct 23 '15 at 15:01
I think I may need to add another DB connect to get the second ID from personDetails and then use this value for the TheDivision Select's Where clause. — shucode, Oct 23 '15 at 15:06

score 0 · Answer 1 · answered Oct 26 '15 at 11:13

I don't know how much of an answer this is but it solved it! I don't know what I was thinking on the Friday afternoon but the bloody logic wasn't good as I was trying to pull data before it is entered which is possible since Divisions did exists but what is the point of this when I already had another section on the front-end where the user selects the division, so no real world practicality! So enough chat but this did it for me:

 //Store what is selected from the Division's ddl
 string areaSelected = ddlArea.Text.ToString();

 //Then
  ID = DBConnect.InsertRecordGetID(comm, sQuery);
    if (ID > 0)
    {

        //complete the Reference Number
        //string divCode;
        //string sSql = 
        //"SELECT TheDivision FROM       dbo.tbl_Incident_Details   //WHERE ID = " + ID;
        //DataTable dt = new DataTable();
        //dt = DBConnect.DataReaderDataTable(sSql);
        //if(dt.Rows.Count > 0)
        //{
        //      divCode = dt.Rows[0][0].ToString();
        //}
        //else
        //{
        //    divCode = "Unavailable";
        //}

        string TheDate = DateTime.Now.Year.ToString();
        string TheYear = TheDate.Substring(2, 2);
        string RefNo = "FAI/" + ID + "/" + areaSelected + "/" + TheYear;

        //The Update and the rest then works neatly and I get a nicely formated string in my db ;-)

Why is unavailable always returned?

1 Answers1