Hi Bonnie.
Just a note to say thanks for sample code .
I have this working now and found your code extremely helpful to point me in the right direction.
regards,
Gerard




>Gerard,
>
>Try something like this:
>

>public long GetNextNumber(string Dept)
>{
>	// I typically have this.oConnection defined and instantiated elsewhere
>	// something like this (and typically the connection string wouldn't be
>	// hardcoded in the app, but would be in the web.config):
>	// this.oConnection = new SqlConnection("server=(local);database=MyDataBase;uid=sa;pwd=MyPassword");
>
>	this.oConnection.Open();
>	SqlTransaction oTrans = this.oConnection.BeginTransaction();
>	SqlCommand sc = new SqlCommand("Select NextNumber from NumberTable Where Dept = @Dept", this.oConnection);
>	sc.Parameters.AddWithValue("@Dept", Dept);
>	sc.Transaction = oTrans;
>
>	// You should probably use a DataReader instead of a DataAdapter, but I don't have much
>	// experience with them and don't have any sample code handy, so I'll leave that as an
>	// exercise for you to figure out (I would, but I'm pressed for time this morning).
>	SqlDataAdapter da = new SqlDataAdapter(sc);
>	DataSet ds = new DataSet();
>	da.Fill(ds);
>
>	// Number that we're going to return
>	long NextNumber;
>
>	// No Rows in the Table means there weren't any hits
>	if (ds.Tables[0].Rows.Count == 0)
>	{
>		NextNumber = 1;
>		// Insert commands go here, I'll leave this for you to do
>	}
>	else
>	{
>		NextNumber = (long)ds.Tables[0].Rows[0]["NextNumber"];
>		NextNumber++;
>		sc.CommandText = "Update NumberTable set NextNumber = @Number where Dept = @Dept";
>		sc.Parameters.AddWithValue("@Number", NextNumber);
>		sc.ExecuteNonQuery();
>	}
>
>	oTrans.Commit();
>	this.oConnection.Close();
>
>	return NextNumber;
>}
>

>
>~~Bonnie
>
>
>
>
>>Hi Bonnie.
>>I was really looking for pointers as to how this would be done in a Web form. My background is VFP, so in vfp itw ould bea simple Seek to get the exisitng no. and a Replace to update... more or less two lines.
>>I have not done this sort of thing in Dot Net as yet.
>>I will take on board your suggestions..thanks again for your help.
>>Regards,
>>Gerard
>>
>>
>>
>>
>>>Well, Gerard, since you don't want to use Stored Procs, it's a little more work in code, but it's do-able. You'll have to wrap the entire process of retrieving and updating the Number table in a transaction.
>>>
>>>Are you asking for help with actual SqlCommand and SqlDataAdapter code syntax or did you just want a general idea of how to do this? The steps you outlined below would work fine ... you'd basically need two calls to the database (one for retrieving the number and one for updating the number) wrapped in a SqlTransaction. That's it.
>>>
>>>~~Bonnie
>>>
>>>
>>>
>>>>Hi Bonnie. Thanks for your reply.
>>>>
>>>>For this partcuklara app, we are using Sql Express, but I would prefer to not use a Stored Procedure , for two reasons:
>>>>1. We may deploy this app in a site with a different databse , that perhaps does not have Stored Procedures.
>>>>2. I have never used stored procerdures and , at this point in time, dont want to go through that particular learning curve.
>>>>
>>>>Do you know if it is practical to do what I am trying to achieve using code ?
>>>>
>>>>Regards,
>>>>
>>>>Gerard
>>>>
>>>>
>>>>>Gerard,
>>>>>
>>>>>Since it looks like you're using SQL Server, I'd use a Stored Proc to handle the stuff with the Numbers table and return the next available number.
>>>>>
>>>>>~~Bonnie
>>>>>
>>>>>
>>>>>
>>>>>>Hi.
>>>>>>I have a form (Windows C#) which has a no. of fields on it, one field (Dept)which will be used as a key into a Numbers file, to assign next available sequential number for the particular department.
>>>>>>I also have a numbers file with just two fields:
>>>>>>Dept
>>>>>>PrevNum
>>>>>>..use to hold Prev sequential number for each department
>>>>>>
>>>>>>
>>>>>>What I want to do is:
>>>>>>Do a lookup on the Numbers File using the Dept as a Key
>>>>>>If it exists, add 1 to PrevNum, replace it and use this as the next number
>>>>>>If it does not exist, append record to Numbers file and move 1 into it
>>>>>>
>>>>>>I could do this in a few mins in VFP but would appeciate some assistance in the C #/ Sql code required here.
>>>>>>
>>>>>>(I have a Save Transaction button on the screen which does all the saving to the main transaction file using an Sqladapter)
>>>>>>
>>>>>>Thanks in advance
>>>>>>Regards,
>>>>>>Gerard