SQL Statemet

[kimbred]

Can someone tell me what I'm doing wrong here? I'm trying to get this method to return the seqnbr.

        public override int GetSeqNbr(int EmpId)
        {
            DataSet ds = new DataSet();
            int SeqNbr = 0;
            using (SqlConnection conn = new SqlConnection(SelfServiceConn))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("DECLARE @seq int ");
                sb.Append("SET @seq = 1 ");
                sb.AppendFormat("WHILE(SELECT count(*) from {0}.f0115 where WPAN8 = " + @EmpId + " AND WPIDLN = 0 AND WPRCK7 = @seq) > 0",owner.DTA);
                sb.Append(" BEGIN ");
                sb.Append("SET @seq = @seq + 1");
                sb.Append(" END as SeqNbr");
                SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), conn);
                da.SelectCommand.Parameters.Add("@EmpId", SqlDbType.Int).Value = EmpId;
                da.Fill(ds);
                da.Dispose();
            }
            return SeqNbr;
        }

[tlhintoq]

Why do you beleive you are are doing something wrong? You made no mention of an error message or what you *are* getting that isn't what you expected.

But as a rough guess...
Line 4 you assign zero to SeqNbr
Line 19 you return SeqNbr
So you are probably getting zero returned, right?

[kimbred]

It's not returning the sequence number. Basically, it is supposed to step through the employees records and find the first available sequence number then return it to the calling code. This would allow me to enter new records using available numbers rather than having gaps in the sequence.

[tlhintoq]

So let's figure it out together. Unless someone here more experienced than I in SQL would like to jump in.

Its is supposed to step through all the employees.
I'm assuming lines 7-12 set up the SQL statement to create the loop going through the employes. It reads like it might behave like a C# 'While' loop.

I think you are setting @seq to 1, checking to see if there is an employee with that number, then incrementing @seq and checking again. If there is no employee with that number, then it should fall through to line 13.

So my question to you is this... at what point do you assign the value of @seq back to SeqNbr? The closest I see is line 13 with END as SeqNbr. Does 'end' somehow hold the value of @seq? Because if it doesn't, I don't see where SeqNbr is ever used except where I earlier mentioned.

Line 4 you assign zero to SeqNbr
Line 19 you return SeqNbr
So you are probably getting zero returned, right?

What value do you get returned right now by SeqNbr?


As a test, if you assign SeqNbr some weird value that you will recognize like

4. int SeqNbr = 1313;

and you get 1313 returned at the end of your run, then you know your query is not affecting SeqNbr in any way. If your query is affecting SeqNbr, then it won't matter what you initialize it to since you are going to change it anyway.

[kimbred]

Each employee has a series of phone records in the database. Each record is assigned a sequence number. Over time, records get changed or deleted so there are gaps in the sequence for a given emplooyee. My goal is to fill that gap. When a new phone record is entered for the employee, I would like to loop through the records to find the first available sequence number and assign it to the record being entered eventually fillling the gap in sequence. The SQL loops through the records and stops when it reaches an available number. I would like it to return that number so it can be used in the insert.

[tlhintoq]

Ok...

but that doesn't answer the earlier questions I asked you.

Did you try testing with a known identifieable number for your initialization of SeqNbr?

[kimbred]

Yes I did test it and it seems to work fine. I just need to know how to return the value of SeqNbr.

[tlhintoq]

@kimbred
Then I guess I am confused. You are already returning the value of SeqNbr in line 19 of your original code.

 return SeqNbr; 

I thought the problem was that SeqNbr wasn't being set to the returned value of your query.

[kimbred]

It always returns 0.

[tlhintoq]

It always returns zero... Even if you initialize it to some other number?
If you initialize on line 4 as int SeqNbr = 1313; it still returns zero?

That would mean that your query genuinely *is* changing the value. That's a good first step. At least we know that the query is doing *something* and that it *is* affecting the local variable. That's a positive step even if not the final answer.

[hajoabdul]

@kimbred
from the codes u wrote i reaslised that u used a string builder, and also that u initialised ur SeqNbr to zero, but i dont see u passing a value to the SeqNbr. if i got it wrong pls do expaling it more to me.

[bhupinder]

@kimbred
please check this query in sqlserver

[kimbred]

I've run this in the SQL manager and SEQN = 4. But I can't get it to return that value in my method.

public override int GetSeqNbr(int ABAN8)
{
int retVal = 0;
using (SqlConnection conn = new SqlConnection(SelfServiceConn))
{
StringBuilder sb = new StringBuilder();
sb.Append("DECLARE @seq int");
sb.Append(" SET @seq = 1");
sb.Append(" WHILE(SELECT count(*) from f0115 where WPAN8 = " + @ABAN8 + " AND WPIDLN = 0 AND WPRCK7 = @seq) > 0");
sb.Append(" BEGIN");
sb.Append(" SET @seq = @seq + 1");
sb.Append(" END");
sb.Append(" SELECT @seq AS SEQN");
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ABAN8", SqlDbType.Float).Value = ABAN8;
SqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException se)
{
retVal = 0;
trans.Rollback();
}
finally
{
cmd.Connection.Close();
}
PurgeCacheItems(CacheType.SelfService_Cache_);
return retVal;
}
}
}