Database

Re: How to convert null dates to String?

1-May-04 15:59

Your question is nebulous. In the case of a null value, what do you want to happen? Store a null value, or some default value? If the former, pass NULL. If the latter, pass the default (or set up the table column to have a default value and don't pass anything at all).

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

-Dr_X-8-May-04 15:57

-Dr_X-

8-May-04 15:57

I use the following functions in conjunction with a datatable to retrieve and set the date values with the front end.

Friend Function DateToDb(ByVal val As Object) As Object
   'Validates a datevalue before saving to db
   If IsDate(val) Then
     Return val
   Else
     Return DBNull.Value
   End If

 End Function

 Friend Function DateFromDb(ByVal val As Object, Optional ByVal DateFormat As String = "MM/dd/yyyy") As Object
   'Validate the date from the database. Return "" if null
   If TypeOf val Is Date Then
     Return Format(val, DateFormat)
   End If
   Return C_ES

 End Function

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Preserve formatting and storing in Database

Anonymous1-May-04 8:48

1-May-04 8:48

Hello,

I have a problem in preserving the format of the data...

Consider this live example...

when i insert this message, the reply will appear as it is i type..

How to preserve that format?

querying XML database

ranadinesh1-May-04 0:47

ranadinesh

1-May-04 0:47

i have a XML file contaning data. this data contains a feild name paymentmode. on the basis of paymentmode i have to generate different reports. how i can filter all these records if i am using ADO.net based on a criteria on paymentmode. can i generate different XML files from the Main Xml File on the basis of paymentmode

Dinesh Rana

Connection Pool

Albert Pascual30-Apr-04 13:24

Albert Pascual

30-Apr-04 13:24

Question: What's the right order so the connection pool is reused?

<br />
     SqlConnection myConnection = new SqlConnection(m_sConnectionString);<br />
<br />
     SqlCommand myCommand = new SqlCommand(sQuery, myConnection);		<br />
<br />
     myConnection.Open();<br />
     myCommand.ExecuteNonQuery();<br />
     <br />
     myConnection.Close();                <br />
     myConnection.Dispose();<br />

<br />
     myConnection.Dispose();<br />
     myConnection.Close();              <br />
<br />

??? Anybody?

Al

Anonymous30-Apr-04 14:03

30-Apr-04 14:03

myConnection.Dispose();
myConnection.Close();

Dispose and then close

Jeff Varszegi1-May-04 9:34

1-May-04 9:34

With the SQL Server client, the connection pool is reused no matter what you do, or at least as long as you don't specifically disable it. Here's some information from MSDN on the subject.

I'm pretty sure you don't have to call Dispose(). Just call Close(); that's enough to return it to the pool.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Mike Dimmick4-May-04 5:45

Mike Dimmick

4-May-04 5:45

It's a bit odd that it has both. Close comes from IDbConnection, while Dispose is inherited from System.ComponentModel.Component.

A bit of digging with Reflector[^] shows that Dispose simply calls Close, if the connection is in the Open state, then sets the ConnectionString property to null.

My rule of thumb is that if you're planning to call Open on this object again, use Close, but if you're not going to use this object again, use Dispose.

Stability. What an interesting concept. -- Chris Maunder

Jeff Varszegi4-May-04 5:54

Trigger, extended SP, send message to a service

4-May-04 5:54

That's good information, but I don't think that using Dispose with the built-in connection pooling will do anything but incur a slight performance penalty for the extra work, although I haven't tested yet. It seems that the actual underlying connection is just mapped to IDbConnection instances, and associated upon Open() and disassociated upon Close. From the docs, it seems logical to assume that resetting ConnectionString, in correctly implemented code, would result in the IDbConnection being disassociated from its pool, but why bother if you're just throwing the object away?

One good thing that you bring up is that it's probably a good idea to hang onto your IDbConnection if you're going to need a connection again, even after Close(). This'll get you out of some creation and destruction overhead. Bright bright bright

-Jeff

here, bloggy bloggy

cmk30-Apr-04 8:38

cmk

30-Apr-04 8:38

I have a service that connects to SQL Server 2K via ODBC.
I need the service to know when certain tables have been modified.

Right now the plan is :
- create trigger(s) for the appropriate modifications
- have the trigger(s) call an extended stored proc with the name of the server, port and message
- the esp will connect to the specified service and send the message

Questions :
Is there a way to do this already that i've missed ?
If not, when does the dll with the esp get loaded/unloaded.
In particular, i don't want the esp to create a socket each time it's called, i'd like to have 3 esp's : an init, one called by the trigger, and a term. The init would open the socket, the term would close it, and the one called by the trigger would just send the message. Of course this won't work if the dll is loaded/unloaded on each call (doubt it works this way, just want to make sure).

...cmk

Save the whales - collect the whole set

Urgent Please !!! Stored Procedure to read data from all kinds of files

Wilbur J. Pereira30-Apr-04 0:38

Wilbur J. Pereira

30-Apr-04 0:38

Hey guys,
I need a stored procedure which could read data from all types of files and then print the data or store it in a table. i have tried bulk insert but when bulk insert reads the data and stores it in a table it creates many rows (.i.e rows = number of lines in the files). say if there are 20 lines then 20 rows and if 700 lines 700 rows. I want to store the data in one string and print it. Also the files i need to read are generated on runtime. If u want to take a look at the Sto. Proc. here it is. Please note files culd be of any size.
**********************************************************
1. Bulk insert
CREATE PROC sp_readTextFile @filename sysname
as
BEGIN
SET nocount ON
EXEC ('bulk INSERT WilTemp FROM "' + @filename + '"')
SELECT * FROM WilTemp
END
GO

***********************************************************
2. Using a filesytem object

CREATE PROCEDURE [dbo].[readFromFile] (@FileName varchar(255))
AS
DECLARE @FS int, @OLEResult int, @FileID int, @Text1 varchar(8000)

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--read a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Read Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadAll', @Text1 OUT
IF @OLEResult <> 0 PRINT 'ReadAll'

--insert into tfromfile values (@Text1)
Print @Text1

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
GO

Inserting commas in database

Anonymous29-Apr-04 22:54

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

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