Database

29-Apr-04 22:54

Does anyone know how to insert commas in database? like if i want to insert:

Hi,How are you? I am fine, ThanQ.

??

Colin Angus Mackay29-Apr-04 23:56

29-Apr-04 23:56

This should work:

INSERT myTable(myColumn) 
VALUES('Hi,How are you? I am fine, ThanQ')

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

Anonymous30-Apr-04 7:52

30-Apr-04 7:52

Thats wrong syntax.

What about multicolumns?

Insert into Table1(col1,col2,col3) values('Hi, how are you?','I'm fine thanQ',I guess, this SQL will fail, for sure');

See, so many quotes and commas in the SQL statement.
Do I need to parse and eleminate all those quotes and commas, do u have any other trick?

Colin Angus Mackay30-Apr-04 9:47

30-Apr-04 9:47

Anonymous wrote:
Thats wrong syntax

Sorry, I missed the INTO Blush | :O

Anonymous wrote:
What about multicolumns?

They are no different.

INSERT INTO Table1(col1, col2, col3) 
values('0,23','2,49','4,99')

Anonymous wrote:
See, so many quotes and commas in the SQL statement

Ah! Now... This is different - You never mentioned anything about quotes before.

Anonymous wrote:
Do I need to parse and eleminate all those quotes and commas, do u have any other trick?

No tricks, just the SQL Server Books online that come with SQL Server 2000.

SET QUOTED_IDENTIFIER OFF
GO
Insert into Table1(col1,col2,col3) 
values("Hi, how are you?","I'm fine thank you","I guess this SQL won't fail!");
GO
SET QUOTED_IDENTIFIER ON

I actually tested this on a real live SQL Server so I wouldn't mess up the syntax again by just writing it in directly to the web site

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

Anonymous30-Apr-04 14:00

30-Apr-04 14:00

Thanks! but i am using MS Access, and trying to insert using ADO.NET(C#)

Colin Angus Mackay wrote:
SET QUOTED_IDENTIFIER OFFGOInsert into Table1(col1,col2,col3) values("Hi, how are you?","I'm fine thank you","I guess this SQL won't fail!");GOSET QUOTED_IDENTIFIER ON

Anonymous30-Apr-04 14:01

30-Apr-04 14:01

Thanks! but i am using MS Access, and trying to insert using ADO.NET(C#)

Colin Angus Mackay wrote:
SET QUOTED_IDENTIFIER OFFGOInsert into Table1(col1,col2,col3) values("Hi, how are you?","I'm fine thank you","I guess this SQL won't fail!");GOSET QUOTED_IDENTIFIER ON

The above syntax is SQL server specific!!

Colin Angus Mackay30-Apr-04 14:04

30-Apr-04 14:04

Anonymous wrote:
Thanks! but i am using MS Access.... The above syntax is SQL server specific!!

Why didn't you say that?!?

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

Hesham Amin30-Apr-04 22:14

Hesham Amin

30-Apr-04 22:14

Hi the best way to avoid problems realated to invalid characters is to insert using Insert Commands. and pass the string values as parameters..
this way it will work fine even if the string contains * ' _ ? etc.

Jeff Varszegi1-May-04 9:29

Jeff Varszegi

1-May-04 9:29

The advice given by other folks here is sound. I just wanted to mention a couple of things.

1) I'm no Access expert, but valid SQL escaping syntax for quotes in strings works this way:

INSERT INTO blah (data) VALUES ('My daddy''s goat just ran up a tree')

Just double them.

2) I think that you're confused about commas. If they appear inside strings, they're not evaluated as list delimiters by the parsing engine at all. You can have zillions of commas inside your strings with no impact whatsoever.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

SQL Named Instance

vikramlinux29-Apr-04 18:02

vikramlinux

29-Apr-04 18:02

Hi Friends,
Is there any way to install office 2000 developer on named instance?

VIkramS

Re: SQL Named Instance

Colin Angus Mackay30-Apr-04 9:51

Name and Data Type of all columns

30-Apr-04 9:51

vikrams wrote:
Is there any way to install office 2000 developer on named instance?

Do you mean SQL Server named instance?

If so, it asks you during setup. I think one of the questions is about a default install/instance and you say no then you get a text box to type the name of the instance.

It has been a while since I've installed it, so I'm a bit fuzzy.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

DotNet_Newbie29-Apr-04 11:08

DotNet_Newbie

29-Apr-04 11:08

Hello All,
I have a SQL 200 DB in place, but now I want to run a script or SP that will give me as an output the name and data type of all the columns in one of the tables. Any Ideas?

Thanks in Advance.

Re: Name and Data Type of all columns

Jeff Varszegi29-Apr-04 11:32

Jeff Varszegi

29-Apr-04 11:32

SELECT
   COLUMN_NAME,
   DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'whimsy'
ORDER BY ORDINAL_POSITION

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Re: Name and Data Type of all columns

DotNet_Newbie29-Apr-04 11:56

DotNet_Newbie

29-Apr-04 11:56

Thank you..

Re: Name and Data Type of all columns

Jeff Varszegi29-Apr-04 12:05

Jeff Varszegi

29-Apr-04 12:05

You're welcome! I assume you have access to Query Analyzer, yes? You could spend a fun couple of hours just poking around in

1) the master.dbo tables, where lots of useful information is stored;

2) INFORMATION_SCHEMA views, which Microsoft recommends whenever possible;

3) the other databases like msdb, where job and replication info is stored

etc. etc. Also look up system functions and sp_ stored procedures in books online. After all that, you'll have the beginnings of the beginnings of a good working knowledge of SQL Server. And get your boss to spring for the Microsoft Press books on SQL Server; they're really good.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Auto Translate=False problem

saglam29-Apr-04 5:38

saglam

29-Apr-04 5:38

For some reasons I have to use Auto Translate=False in my connection string.
My connection string is like;
"data source=2003-SERVER;Initial Catalog=db_name;uid=db_user;pwd=user_password;max pool size=50;Auto Translate=False"

This works fine when I use system.data.oledb. But not working with system.data.sqlclient. It gives me

System.ArgumentException: Keyword not supported: 'auto translate'

This shows me that I cannot use Auto Translate with system.data.sqlclient, but when I try to add sqlconnection from Visual Studio 2003, ToolBox->Data->SqlConnection and then build connection string from properties window <new connection...>, I can see Auto Translate option in Data Link Properties' All tab. Ok I set it to false and turn back to code behind to see how it uses auto translate=false, I saw nothing about it. ConnectionString is in there but contains nothing about Auto Translate=False.

Any ideas about how to add Auto Translate=False to my connection string, and use it with system.data.sqlclient?

Thanks in advance.

Re: Auto Translate=False problem

Mike Dimmick4-May-04 23:04

Mike Dimmick

4-May-04 23:04

System.Data.SqlClient implements the Tabular Data Stream (TDS) protocol for communicating with SQL Server directly (well, it uses the underlying dbnetlib.dll so that any protocol can be used, but it performs all the formatting itself). You shouldn't necessarily expect all parameters to be supported.

The Auto Translate option, by my understanding, applies to character data supplied to OLE DB in byte-oriented character format (loosely referred to as 'ANSI' by a lot of MS documentation). In order to prevent misinterpretation by the remote server, it converts from the thread's character set to Unicode (UTF-16) then back from UTF-16 to the server's configured byte-oriented character set. If you turn the option off, it simply passes the bytes directly to the server with no translation; if the client and server use different encodings, this can lead to misinterpreted data.

However, in .NET you only provide character data to the managed provider as a System.String. This type is natively Unicode UTF-16; hence the Auto Translate option, if it were available, would have no effect.

If you're trying to store and retrieve binary data, use a binary-type column (binary, varbinary or image) and the appropriate type for the SqlParameter (SqlDbType.Binary, SqlDbType.VarBinary, SqlDbType.Image).

If you're reading byte-oriented character data from a file, ensure that you're interpreting it correctly by setting the Encoding in your StreamReader constructor, or use the appropriate encoding when converting a byte array to a string.

If you post the exact problem you're experiencing, it might be possible to work out a solution.

Stability. What an interesting concept. -- Chris Maunder

Re: Auto Translate=False problem

saglam10-May-04 8:38

saglam

10-May-04 8:38

My problem is; I have a sql server which has collation: Latin1_General_CI_AS, and a database with same collation (actually, I am not the designer of that db, and I cant change any settings). In a table I have a varchar(60) column. When I insert text data which contains Turkish characters, Turkish characters are not stored normal. So when I retrieve those data to my asp.net application, Turkish characters are not displayed correctly, which is expected. All my language settings in web.config file is set to Turkish, but this still couldnt solve my problem. But when I use data.oledb to connect sql server and set auto translate=false, everything works fine. But when I use data.sqlclient, Turkish characters are not displayed correctly. And I cant use auto translate=false property with sqlclient.

Any ideas?

Thanks in advanced

Re: Auto Translate=False problem

Mike Dimmick10-May-04 9:49

Mike Dimmick

10-May-04 9:49

Ah, I see what you're doing: setting Auto Translate=False simply does a byte-for-byte copy into the field - you're basically pretending to SQL Server that you're providing the correct encoding.

The database column is using code page 1252[^] (Windows Western), but your data is encoded with 1254[^].

This really isn't a good idea. Searches and sorts are unlikely to match your cultural expectations if the server has the wrong collation.

I'm afraid I can't offer a short-term solution: I don't know enough about it. The long-term solution is to get the collation changed for the field.

I think this is probably a job for Microsoft tech support.

Stability. What an interesting concept. -- Chris Maunder

SQL

Boycech29-Apr-04 4:56

Boycech

29-Apr-04 4:56

Hi, what is the best way to write SQL code when creating ASP Web enabled systems that access a Sequel Server Database? I'm use to Access so when I have a complicated query, I've been creating a carbon copy DB in Access, setting up the relationships, and then creating queries so I can use the SQL it generates in ASP. I'm not up to speed yet with writing SQL when you have to include inner and outer joins etc. What are my options other than what I'm doing now. Probably just study up on SQL? Thanks

Boycech

Mike Ellison30-Apr-04 5:44

Mike Ellison

30-Apr-04 5:44

Hi Boycech. Using Access the way you're describing isn't a bad way to start getting to know SQL - just be aware that Access' flavor of SQL isn't exactly ANSI-compliant. Access is also notorious for generating sql statements that are far more verbose than they need be - downright confusing at times.

If you're really just getting started with SQL, take a look at the W3Schools Sql Tutorial[^]. You might find it useful.

Boycech30-Apr-04 5:46

Boycech

30-Apr-04 5:46

Thanks I will.

Bill Dean4-May-04 5:04

Bill Dean

4-May-04 5:04

Howdy,

A few random bits of advice: 1) You are dead-right: study up on SQL. 2) Make use of stored procedures (aka: sprocs). I don't know if there is an Access equivalent, but in sql-server a sproc is like a function call: you pass it parameters when you call it, it does some voodoo (run sql code) on the database and returns a record set (if you need it to). The big advantage to sprocs is that your ASP code doesn't rely on how you implemented things in the database. Think of them as a layer of insulation between the GUI and the data. So 6 months from now when you need to change something on the database, all you need to do is adjust the code in the sprocs to assure that they all still return the same data. Otherwise a design change on your database might break A LOT of your GUI.

Bill

Boycech4-May-04 5:18

Boycech

4-May-04 5:18

Thanks a lot Bill!