Name Count

I have an sql command for when you add a new name to the database it counts to see how many of the name entered to the textbox exist in the database. If the count is 0 then it will add the name to the table. Else it displays an error message.

This works fine for inserting a new name but on my update page where you may update the name I have the same code which on button click counts to see how many exist. But if you leave the textbox the same value and click the button the count obviously results in 1 and brings up an error message.

Is there a way I can do a count but not including the name that is currently the value of the textbox?

protected void UpdateSharedArea(object sender, EventArgs e) { SqlConnection connection =new SqlConnection(docShare_ConString);//Count the amount of area names that are the same as typed by user SqlCommand existCheck =new SqlCommand("SELECT COUNT(doc_area_name) FROM document_area WHERE doc_area_name = @.doc_area_name", connection); SqlParameter areaname =new SqlParameter("@.doc_area_name", SqlDbType.VarChar); areaname.Value = AreaText.Text; existCheck.Parameters.Add(areaname); connection.Open();int count = (int)existCheck.ExecuteScalar(); connection.Close();//If the area name does not exist within the tableif (count == 0) {//Update name

Cheers, Mark

You will need to to filter your count by the ID of the record you are updating. So when you load the name to display in your textbox, also load the ID of the record and keep it somewhere (Session state or viewstate for example). Then when you do your count, filter on the ID, e.g.

SELECT COUNT(*) FROM NameList WHERE Name = @.Name AND ID != @.Id

|||

Brilliant, thanks very much for the reply.

Mark

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>