case sensitive SQL - pls help a noob

I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not!
How can I set my SQL Server 2000 to be case sensitive as well?Case sensitivity depends on the codepage you select at install.
If you want to do it afterwards you need to rebuild the master table.
See 'Rebuilding the master database' in the SQL Server
books online.

Regards
Fredr!k

Case Insensitivity

I have a SQL 2000 database. I have a ASP.NET web app that I use to
search this database. I need to make my data case insensitive,
espcially my last name column. How do I change this?

Thanks,
BrianI was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this...|||See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).

Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.

Simon|||There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:

ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_CI_AS

Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.

--
David Portas
SQL Server MVP
--|||I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.

Thanks,
Brian|||What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:

SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'

when it sounds like you want the wildcard after

SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'

Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstanding?

Stu

Case conversion with SQL or Stored Proc

Hi experts,

I m new in SQL stuff. I have to work out a fucntion with ASP.net for CSV import to the DB in MSSQL.

I would like to know for Stored Proc, is there any way that I can do the case conversion?

e.g

the Full_Name read from CSV file: Lennon, John

Then for the family name I need to convert into uppercase so the converted one: LENNON, John

Is there any way I can check those words before the comma? The CSV file is delimited with | instead of , ?

Can I use substring for that? And also do you have any online tutorial for Stored Procedure recommended? Thanks a lot!!!

Cheers,

KNVB

Hi,

Here is an example:
declare @.fullName varchar(100)

set @.fullName = 'Lennon, John'

select upper(left(@.fullName, charindex(',', @.fullName) - 1)) + right(@.fullName, len(@.fullName) - charindex(',', @.fullName) + 1)

Note: The first two lines of code are just for the sample

It might be better to implement this as a FUNCTION in case of a STORED PROCEDURE since functions can be used in your select statement.

References:

Creating stored procedures: http://www.sql-server-performance.com/tn_stored_procedures.asp|||

Merci beaucoup, Geert!

By the way, have you heard of a company called i4net from Namur?

|||

No problem, glad to help.

I didn’t know i4net. Is this your company maybe?

Greetz,

Geert

Case - When - Then - Else?

Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
LeeLee,

Why not just attach 'else' to your case expression...

case when 1=1 then 1
else 0 end

--
-oj
http://www.rac4sql.net

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Thanks for not starting the reply with HI STUPID as I was unaware that it was as simple as that!
Again Thanks,
Lee|||Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Sorry for the repeat post; somehow I had not seen the responses already in the browser. Very confused...

Regards,

Hugh

Originally posted by hmscott
Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!
Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!

Capturing a variable in ASP.NET

Hi all,

I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.

Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.

It seems easy to output the record, but how do I store it as a variable !!

This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable...

Function higher_manager_ein() As System.Data.IDataReader

Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader

End FunctionSince it sounds like all you are doing is returning one value, you might want to look at the dbCommand.ExecuteScalar method instead. That is made to return one value.

Now to explain the way you are currently doing it, you can think of the DataReader as an old ADO recordset that you used in ASP. So when this function returns the datareader, you then need to get the value out of the recordset. The firsts difference is that the DataReader object doesn't not start out at the first record. It starts right before it. So you need to call the DataReader.Read() method. That will return true or false depending on if it has read the next record. To get the value, you need to invoke the appropriate method depending on what type of value it is. So DataReader.GetString will return the value as a string. You can pass either the ordinal position of the column in the datareader or the column name.

Since what you are saying above though, I would go with the ExecuteScalar method off of the datareader instead. So again, if this is returning a string value, then you would do the following in place of the Dim dataReader As System.Data.IDataReader command above:

Dim myStringValue As String = CType( dbCommand.ExecuteScalar(), String )

You need to change the type to a string since the ExecuteScalar method returns the type Object.|||and use parameterized queries.

hth

Capacity of SQL Server 2005 Express Edition

I am just starting to build my apps in asp.net 2.0. I have build apss in v. 1.x before using sql server 2000. However, before i make the shift, I would like to know the advantages and disadvantages of using the sql server 2005 express edition then using the sql server 2000.

for example;
+ How many data can it store?
+ How many concurrent users it supports.
+ Advantages and disadvantages of using sql server 2005 express ed.
+ Any other relevant information that developers should know / beware / watch out for when using sql server 2005 express edition.

Note: I am not asking about the actual SQL Server 2005 but the express edition!

Regards and thanks in advance

Microsoft'sSQL Server home page is a good place to start your research. TheEditions link will bring you the theSQL Server 2005 Features Comparison table which will answer most of your questions.

Cant use stored procedures with a sqldatasource

Hi all, I've been trying to use the stored procedures created by asp (i.e. aspnet_membership_createuser)... but they don't show up when trying to use them in a sqldatasource. Are they blocked or am I doing something wrong?

Thanks in advance.The membership tables and stored procedures will be in the ASPNETDB database by default. This is the database you'll need to log into if you want to do this.

Having said that, I doubt it's particularly good practice to be calling these stored procedures directly. You'd be better off looking into using a CreateUserWizard control or adding an event to your page and using the Membership API.|||They are hidden from you, yes. You can still type them in if you want to, and they should still work.|||Thanks for the answers :), I''m going to try that, and I'll let you know how it goes...|||The wizards automatically hides any object that begins with "aspnet" (Ok, actually it hides tables that begin with aspnet, stored procedures that begin with aspnet, and views that begin with vw_aspnet). I think it was for convience so that beginners didn't get confused and try to access them directly. Moderate/Average users don't want them popping up because 99%+ of the time you don't want one of those and they just get in the way. Advanced users will figure out they can still use them if they need to, just don't use the wizard ;-)

Cant Update, Insert, or Delete rows

I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help.

I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types.

I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed.

I may upload a copy of the exact exception page, if someone thinks that it may help.

Here is the update command that was generated:

UPDATE [Record Information] SET [Speed] = @.Speed, [Recording Company] = @.Recording_Company, [Year] = @.Year, [Artist] = @.Artist, [Side 1 Track Title] = @.Side_1_Track_Title, [Side 1 Track Duration] = @.Side_1_Track_Duration, [Side 2 Track Title] = @.Side_2_Track_Title, [Side 2 Track Duration] = @.Side_2_Track_Duration, [Sleeve Description] = @.Sleeve_Description WHERE [Record Database ID] = @.original_Record_Database_ID

Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

What I recommend is that you click the Learn link at the top of the page and go through some of the videos or quickstart tutorials to familiarise yourself with how the SqlDataSource works. It doesn't, for example, generate stored procedures under any circumstances - which is why you can't find any. You also need to understand what Optimistic Concurrency is, how to manage it and when to use it. Finally, you should understand what datatypes are the most appropriate for your data. Having nothing but ints and text datatypes is not very likely to be appropriate.

can't update table owned by dbo with impersonation account

I am using an impersonation account to execute a procedure via
executenonquery in asp.net. If the table owner is dbo I get the following
error in .net:
input string was not in a correct format.
When I step through my code it actually is saying that permission is denied
on the table. I've given the impersonation account full priveledges on this
table and it still doesn't work. However, if I change the owner of the tabl
e
to somone else it works fine. Any suggestions on how to resolve this would
be greatly appreciated.Can you show us your query?
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>I am using an impersonation account to execute a procedure via
> executenonquery in asp.net. If the table owner is dbo I get the following
> error in .net:
> input string was not in a correct format.
> When I step through my code it actually is saying that permission is
> denied
> on the table. I've given the impersonation account full priveledges on
> this
> table and it still doesn't work. However, if I change the owner of the
> table
> to somone else it works fine. Any suggestions on how to resolve this
> would
> be greatly appreciated.|||Here you go.
TRUNCATE TABLE dbo.MYTABLE
INSERT INTO dbo. MYTABLE(THEID,ERRORFLAG,ERRORCODE,CREATI
ONDATE)
VALUES(@.IDValue ,'Y',@.LocalError,GETDATE())
When I run my code in asp.net I get the error "input string is not in the
correct format"
If I use
sp_changeobject 'MYTABLE', 'newowner'
and run it again it works fine?
"Uri Dimant" wrote:

> Can you show us your query?
> "ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
> news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>
>

Can't uninstall books Online CTP Preview

It shows in Add/Remove Programs. I had ASP.NET 2.0 installed and Add/Remove Programs tells me I need 2.0 before I can Install Books Online. But I'm trying to Uninstall Books Online. I uninstalled 2.0 with no effect. I reinstalled 2.0 with no effect. I searched Program Files and the Register and can't find Book Online. It won't go away. How to get rid of it? Thank you.

There is no Books Online dependency on ASP.NET. However, the CTP versions of SQL Server (including Books Online) do have a dependency on a specific version of .NET Framework 2.0. That is, the version of .NET Frameworks 2.0 required for the June CTP is different than the version of .NET Framework 2.0 required for the September CTP. The specific version of .NET Framework 2.0 must be installed and available when you uninstall SQL Server or Books Online. This is an unfortunate side effect of one beta application having a dependency on another beta application.
Please try these steps to get your CTP version of Books Online uninstalled.
1. Uninstall the version of .NET Frameworks you have installed now.
2 Install the .NET Frameworks 2.0 version that is appropriate for the CTP version of Books Online you have installed by using one of the following links.

For the September CTP, select the download link to the version of .NET Frameworks available from this site : http://www.microsoft.com/downloads/details.aspx?familyid=ADC75E35-7245-4038-9B8A-B8FABAEC16DA&displaylang=en

For the June CTP, select the download link to the version of .NET Frameworks available from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=f0d182c1-c3aa-4cac-b45c-bd15d9b072b7&DisplayLang=en

If you're not sure which CTP version of Books Online you have, right-click in any BOL topic and select View Source. Search for the words "Topic built:". The date after that phrase will either be a September or June date.

3. From Add/Remove Programs uninstall Books Online.
4. From Add/Remove Programs uninstall .NET Frameworks 2.0 installed in step 2.

Regards,

|||Thanks for taking the time to respond. After going to bed at 4 AM, again, and awakening from a nightmare I resolved to cut my losses, reformat the hard drive and start fresh. The first thing I installed, after Windows and all my drivers, was dot Net 2.0 and the SQL 2005 Server 180 day Evaluation program. Seems to be working fine.

I would recommend this to anyone if their situation permits. Now I have a clean machine, which provides a comfortable feeling. It will take many more hours to reinstall and reconfigure all my programs, but that dwarfs [[edit - oops, is dwarfed by ]] the time already wasted. I'm not blaming Microsoft, and I understand that this was Beta software. This 919 Meg download takes an hour and a half, but it includes the 2005 version of Visual Studio, which is a beautiful program. Many thanks to Microsoft for their fine work. Looking forward to the upcoming 2005 road show in Olando.|||

Hi Steve,
I'm glad you were able to resolve the problem. I know starting from scratch can be time consuming, but as you pointed out, when going from beta software to production code, it's definitely worth starting with a clean machine (even on a test machine).

If possible, you might consider using something like Virtual PC for testing future beta applications.

Regards,
Gail

|||Your instructions were much more precise than the "read me" file. Could the read me be improved?

The trouble is too many variables. Incomplete instructions require guessing, blind allies, recovery, trying something else, etc. The advantage of a read me file is that it can be updated.

I spent probably 24 nonproductive hours with this and a similar Beta problem a couple weeks ago. Productive time, learning something new, is fine. Time going around in circles due to arbitrary idiosyncracies is different. For example, I don't mind the 8 hours I spent the other day debugging a javascript. Specific Beta version dependencies is arbitrary information.

Thanks again.|||I'll check into getting the readme modified.

Thanks,|||

Gaile,

I had the exact issue. Your help above sold my issue perfectly. All I did was install only the .NET Framework I needed and my BOL uninstalled just fine.

Thanks,

Scott

Cant turn off tool tips !

Hi,
I am using CR10 ASP.NET with CR10 merged module & crystal report viewer, if I create a report in the crystal IDE and use file/options/review/tool-tips this turns off/on tool tips ok but when the report is viewed in the web asp.net the tool tips are always there and what's more you can't suppress individual field tool tips with CHR(9), can anyone helpI found this thread awhile ago. Not sure if it will work with your versions (I use VB6 and CR 8.5).

http://www.dev-archive.com/forum/showthread.php?s=&threadid=297373|||I agree , I have done same thing and it works. Phil let us know if this helps.

Thanks
Dilemma|||Nah this don't work, this is just suggesting using CHR(9) which as I pointed out initially dosn't work in the web browser, I know it works in CR developer
any idea's?