Why is this not working? Does not execute code?

Question

I am trying to have a textBox auto complete with values from a database. When i bullet test the code will walk though until the if statement in the function below. Could some one please tell me why my code wont execute after that if?

I am trying to run the following but it keeps jumping out before the if:

public AutoCompleteStringCollection AutoCompleate(string dataBase, string procedure)
{
        AutoCompleteStringCollection namesCollection =
            new AutoCompleteStringCollection();

        SqlDataReader dReader;
        SqlCommand cmd = new SqlCommand(procedure, con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        dReader = cmd.ExecuteReader(); // This is the last place my bullet check 
                                       // gets before it hop's out!
        if (dReader.HasRows == true)
        {
            while (dReader.Read())
                namesCollection.Add(dReader["SystemUser"].ToString());
        }
        con.Close();
        dReader.Close();
        return namesCollection;
    }

Additional information, but please focus on the above!

Please ask if you need any info. :)

The Call to the above:

textBoxUser.AutoCompleteMode = AutoCompleteMode.Suggest;
textBoxUser.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBoxUser.AutoCompleteCustomSource = 
    DatabaseService.Instance.AutoCompleate("AuditIT", "AutoCompleate");

Stored Proc:

USE [AuditIT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Allow textboxs to have a autocomplete
 ALTER procedure [dbo].[AutoCompleate] 
 as

 Select distinct SystemUser from SchemaAudit
             order by SystemUser asc

If it "jumps out" it means that an exception was thrown. Have you tried running the code in the debugger? Are you using an exception handler outside this code? — Panagiotis Kanavos, Jun 15 '12 at 08:47
@AlexR. con = new SqlConnection(Properties.Settings.Default.DBConnectionString.ToString() ); the connection string works i have other functions done the same way :) — Pomster, Jun 15 '12 at 08:49
Another vote for running it through the debugger. What is the specific exception being returned? — dodexahedron, Jun 15 '12 at 08:50
not seems right way for auto complete, why don't you cache the records and do the auto complete? — indiPy, Jun 15 '12 at 08:52
@alliswell Doing a search for DB items, what better way to do a search then to list available options :D — Pomster, Jun 15 '12 at 09:04
I have got it all sorted thanks i made a edit on Felice Pollano's answer once he accepts the edit his answer will be the right one :) — Pomster, Jun 15 '12 at 09:20

score 3 · Accepted Answer · edited Jun 15 '12 at 09:33

There is an unhandled exception when you try to hit the database. Try to refactor the code as this:

try{

if (dataBase.Length > 0) { procedure = dataBase + ".." + procedure; } //Set procedure to DBNAME..ProcedureName

SqlDataReader dReader;
            SqlCommand cmd = new SqlCommand(procedure, con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            dReader = cmd.ExecuteReader(); // This is the last place my bullet check gets before it hop's out!
            if (dReader.HasRows == true)
            {
                while (dReader.Read())
                    namesCollection.Add(dReader["SystemUser"].ToString());
            }
            con.Close();
            dReader.Close();
}
catch(Exception e)
{
    // handle the exception better than me :)
    Console.WriteLine(e.Message);
}

Put a messagebox or a breakpoint on the console.WriteLine and see what's happen.

Why is this not working? Does not execute code?

1 Answers1