I want to keep all the value of a field in my table to the Upper Case, i.e.
even if the user keyed 'abc', it will be stored as 'ABC'. I tried using
UPPER(fieldvalue) in my update statement, but still the value is stored as
lower case. What can I do?

Upper should work. See below.

CREATE TABLE #Test(SomeText VARCHAR(10))
INSERT INTO #Test VALUES('abc')
INSERT INTO #Test VALUES('Pqr')
INSERT INTO #Test VALUES('xyZ')

UPDATE #Test
SET SomeText = UPPER(SomeText)

SELECT * FROM #Test

DROP TABLE #Test

Show quoteHide quote

I tried that in my stored procedure.

UPDATE tablename
SET [field1] = UPPER(@value1)

but it doesn't work. If I key 'abc', it will still be saved as 'abc',
instead of 'ABC'...

Show quoteHide quote

It should be

UPDATE tablename
SET [field1] = UPPER([field1] )


Show quoteHide quote

UPDATE <> INSERT

Are you inserting data or updating it?


ML

---
http://milambda.blogspot.com/

I am updating it. I am using SQL Server 2005 by the way.

Show quoteHide quote

I tried it with SQL Server 2000, and it does not work as well. This is how my
stored procedure look like,

CREATE PROCEDURE sp_UpdateActProc# (
@UserID varchar(9),
@ActProc# varchar(3))
AS
UPDATE [Member]
SET [ActProc#] = Upper(@ActProc#)
WHERE [UserID] = @UserID
GO

When I typed 'abc' for @ActProc#, it is still updated as 'abc', instead of
'ABC' which I am expecting for.

Show quoteHide quote

Sorry, I tried executing the procedure at the SQL query analyzer and it
worked. But when I call it in my application (using .NET framework 2.0), it
didn't work out. This is how my codes look like,

            Dim dbConnection As SqlConnection
            dbConnection = dbManager.getConnection()

            Dim dbCommand As SqlClient.SqlCommand
            dbCommand = New SqlClient.SqlCommand
            dbCommand.CommandType = CommandType.StoredProcedure
            dbCommand.CommandText = "sp_UpdateActProc#"
            dbCommand.Connection = dbConnection

            Dim IDParam As SqlClient.SqlParameter
            IDParam = New SqlClient.SqlParameter
            IDParam.ParameterName = "@UserID"
            IDParam.SqlDbType = SqlDbType.VarChar
            dbCommand.Parameters.Add(IDParam)

            Dim ActProcParam As SqlClient.SqlParameter
            ActProcParam = New SqlClient.SqlParameter
            ActProcParam.ParameterName = "@ActProc#"
            ActProcParam.SqlDbType = SqlDbType.VarChar
            dbCommand.Parameters.Add(ActProcParam)

            dbCommand.Parameters("@UserID").Value = UserID
            dbCommand.Parameters("@ActProc#").Value = ActProc

            dbConnection.Open()
            dbCommand.ExecuteNonQuery()
            dbConnection.Close()

Where went wrong?

Show quoteHide quote

Are you connecting to the same server/database?


ML

---
http://milambda.blogspot.com/

I have no blog
Trigger logic - this ok
Top 2 max operations
How to implement transactions
best way to bulk update