Re: SQL ToTitleCase Script - Database Discussion Boards

webhay12-Jan-05 10:11

12-Jan-05 10:11

Hello all,

I have 4 tables the first table contain IDs from the other 3 tables. I need to Select the names from the other tables by the IDs in the first table

1- tbl
1|2|4

2- tbl
1|manager

3-tbl
2|Desgined

4-tble
4|Computer

please help

Thanks
hay

Re: select

Colin Angus Mackay12-Jan-05 22:58

Colin Angus Mackay

12-Jan-05 22:58

SELECT table2.name, table3.name, table4.name
FROM table1
INNER JOIN table2 ON table1.id2=table2.id
INNER JOIN table3 ON table1.id3=table3.id 
INNER JOIN table4 ON table1.id4=table4.id

As you have not provided table and column names I have named the tables after their number as in your example. In table1 the columns id2, id3 and id4 represent foreign keys to table2, table3 and table4 respectively.

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Re: select

webhay12-Jan-05 23:40

webhay

12-Jan-05 23:40

Thanks Colin ,

It is working but it give me duplicate recorder is there is a way to avoid duplicate.

Thanks for your time.

hay

Re: select

Colin Angus Mackay12-Jan-05 23:48

Colin Angus Mackay

12-Jan-05 23:48

SELECT DISTINCT....

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

SQL ToTitleCase Script

Jason Weibel12-Jan-05 7:50

Jason Weibel

12-Jan-05 7:50

I need to update a field so the values change from all upper case to title case. Can anyone please point me to a sql script example of how to do this. Thanks,

Jason W.

Re: SQL ToTitleCase Script

Ritesh123413-Jan-05 3:53

Ritesh1234

13-Jan-05 3:53

Hi,

U can use...............

UPDATE Customer
SET
C_Name = LOWER(C_Name)

I hope this will help u..........:->

Regards,
Ritesh

Re: SQL ToTitleCase Script

Jason Weibel13-Jan-05 4:18

Jason Weibel

13-Jan-05 4:18

Thanks for the try, but that won't work. Lower gives the following results:

Select lower('TEST')<br />
---- <br />
test

The following would work if there was only one word:

Select upper(left('TEST',1)) + lower(substring('TEST', 2, len('TEST')))<br />
----- <br />
Test

Problem is when you have more then one word such as:

DECLARE @TEST as varchar(50)<br />
SET @TEST = 'TEST TEST TEST'<br />
Select upper(left(@TEST,1)) + lower(substring(@TEST, 2, len(@TEST)))<br />
--------------------------------------------------- <br />
Test test test

The results I want are
---------------------------------------------------
Test Test Test

Any idea on how to output this format?
Thanks again.

Jason W.

Re: SQL ToTitleCase Script

Ritesh123413-Jan-05 22:40

Ritesh1234

13-Jan-05 22:40

Hi,
In that case u have to write ur own logic...........
Here is my idea.............

1>Loop through ur string and separate our the substring till u get Space.
2>Then apply ur logic which u shown for this word and append it with @Result.
3>COntinue with ur loop till u get Next substring(word) use step 2 and append it to the @Result with Space
4>Continue till the length of Entire String......................

u might be having better logic...................:->

Regards,
Ritesh

Exporting DataGrid to Excel

janigorse11-Jan-05 20:49

janigorse

11-Jan-05 20:49

Hello!

Is it posible to export DataGrid (not DataSet!) to Excel? If it is, how?
I can't find any exapamle on net...

Re: Exporting DataGrid to Excel

janigorse12-Jan-05 4:06

janigorse

12-Jan-05 4:06

Well I mean I can't find any example for Win DataGrid. So is it posible to export
data from Win DataGrid to excel?

Query to get table & field name

reenacutie11-Jan-05 19:41

reenacutie

11-Jan-05 19:41

Hi, Is there any way of getting table & field name from an SQL database
Need to extract field& table name from VB

Re: Query to get table & field name

Ritesh123411-Jan-05 21:36

Ritesh1234

11-Jan-05 21:36

Hi,

U can get the Name of all the Tables in ur current DB using

SELECT name,xtype FROM sysobjects WHERE xtype = 'U' AND name 'dtproperties'

And u can get the list of all the Columns with there Table name using

SELECT so.name TableName,sc.name ColumnName FROM syscolumns sc
INNER JOIN sysobjects so on sc.id = so.id
WHERE so.xtype = 'U' AND so.name <> 'dtproperties'

Hope this will help u.....................:->

Regards,
Ritesh

Re: Query to get table & field name

turbochimp12-Jan-05 7:52

turbochimp

12-Jan-05 7:52

You can also get a wealth of information using the OleDbConnection's GetOleDbSchemaTable method.

The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

Unisntall sql server 2000 service pack 3

Mekong River11-Jan-05 4:19

Mekong River

11-Jan-05 4:19

Hi, my computer have sql server and install service pack 3. I also have vb.net. Now I want to uninstall sql server service pack 3 and install sql server service pack 3a. Because I want to install sql server reporting services. But I don't is it possible to uninstall sql server 2000 service pack 3. Does any one know is it possible or not??? If it is possible, how could I do that?

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: Unisntall sql server 2000 service pack 3

Colin Angus Mackay11-Jan-05 5:30

Colin Angus Mackay

11-Jan-05 5:30

Can't you just install SP3a on top of SP3?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Re: Unisntall sql server 2000 service pack 3

Mekong River13-Jan-05 5:45

Mekong River

13-Jan-05 5:45

I can install sp3a over sp3 without any indication. But when I try to install SQL server reporting service it still let me know that I don't have sp3a. I don't know how to solve that problem... WTF | :WTF:

I don't know how to fix it...

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: Unisntall sql server 2000 service pack 3

Mike Dimmick11-Jan-05 6:23

Mike Dimmick

11-Jan-05 6:23

http://www.microsoft.com/sql/downloads/2000/sp3.asp[^]:

"If you have applied SP3, you do not need to apply SP3a. SP3a is only for SQL Server users who have not applied any versions of SP3. However, you should use SP3a rather than SP3 moving forward."

Specific differences: SP3a can be applied to trial versions of SQL Server, it includes MDAC 2.71a (security and installation fixes to MDAC 2.71), and it disables UDP port 1434, the service attacked by Slammer, when networking is disabled.

If you've not already done so, you should apply MS03-031[^] to bring your server up to version 8.00.818, then apply the hotfix in KB 826161[^] to your client tools so you can modify the properties of a SQL Server login without having to set the login's password.

Stability. What an interesting concept. -- Chris Maunder

validation problm

Member 165962410-Jan-05 23:12

Member 1659624

10-Jan-05 23:12

hi all,
i have created one validation form,loginname and pass have to check fromthe new user table that i had already created.i was trying to write code but could not complete. can u all please help/guide me to write the code.

Re: validation problm

Colin Angus Mackay10-Jan-05 23:24

Colin Angus Mackay

10-Jan-05 23:24

pankajPandey wrote:
i was trying to write code but could not complete

Well, what code do you have? And, what is it that you are stuck on?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Syncing different DataAdapters

Jim_Stanley10-Jan-05 11:44

Jim_Stanley

10-Jan-05 11:44

Oh help...

I'm experienced in programming but new to .NET, and am having a problem updating bound data that feeds the drop-down items in a combo box. I'm using a data adapter to feed an INSERT statement to a table using the connection directly (I don't use a DataTable). I use ExecuteNonQuery to update the table and it works fine. However, as the bound control works from another data adapter, the new item doesn't show in the drop-down. I created a small project and found that if I called Adapter.Fill() after executing the insert, it would show up. However, that adapter was hooked up to a dataset. Do I have to completely rejigger my code, or is there some way to force a refresh of the data? I tried a CurrencyManager but got nowhere.

Thanks for any help.

Jim Stanley
Media Services, Inc.

big problem

WDI10-Jan-05 11:33

WDI

10-Jan-05 11:33

hi
this doesn't work
what should i do????

public void GetData( )
{

SqlConnection sqlConnection;
SqlCommand sqlCommand;

sqlConnection = new SqlConnection ( ConfigurationSettings.AppSettings["DataBaseConnection"] );
sqlConnection.Open();

sqlCommand = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand.CommandType = CommandType.StoredProcedure;

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

while ( sqlDataReader.Read() )
{

do somethings...

SqlCommand sqlCommand_2 ;

sqlCommand_2 = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand_2.CommandType = CommandType.StoredProcedure;

SqlDataReader sqlDataReader_2 = sqlCommand_2.ExecuteReader();

while ( sqlDataReader_2.Read() )
{
do somethings...
}
sqlDataReader_2.Close();
}

sqlDataReader.Close();

sqlConnection.Close();
}

a very big problem

WDI10-Jan-05 11:29

WDI

10-Jan-05 11:29

hi
i have a problem.
this code doesn't work why? and what should i do plz????? Mad | :mad:

public void GetData( )
{

SqlConnection sqlConnection;
SqlCommand sqlCommand;

sqlConnection = new SqlConnection ( ConfigurationSettings.AppSettings["DataBaseConnection"] );
sqlConnection.Open();

sqlCommand = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand.CommandType = CommandType.StoredProcedure;

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

while ( sqlDataReader.Read() )
{

do somethings...

SqlCommand sqlCommand_2 ;

sqlCommand_2 = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand_2.CommandType = CommandType.StoredProcedure;

SqlDataReader sqlDataReader_2 = sqlCommand_2.ExecuteReader();

while ( sqlDataReader_2.Read() )
{
do somethings...
}
sqlDataReader_2.Close();
}

sqlDataReader.Close();

sqlConnection.Close();
}

Re: a very big problem

Colin Angus Mackay10-Jan-05 13:16

Colin Angus Mackay

10-Jan-05 13:16

You cannot have two data readers executing on the same connection at the same time. You need to create a second connection for the second command.

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Excelt and ADO .NET !!! made me cry now!!!

fadee10-Jan-05 5:32

fadee

10-Jan-05 5:32

Hi
Its getting pathetic and I dunno why !!

I am trying to read/insert/update an excel sheet using ADO .NET. I am using
following connection string to connect to the data source.

Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\inetpub\wwwroot\App\files\file.xls;Extended Properties='Excel
8.0;HDR=YES;MAXSCANROWS =1'

The development machine contains Microsoft Office and everything upto date
and the application is working fine.

On server I am getting following exception since last One Month!!

---------------------------------------------------------------------
Could not find installable ISAM.Microsoft JET Database Engine at
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at
System.Data.OleDb.OleDbConnection.InitializeProvider() at
System.Data.OleDb.OleDbConnection.Open() at
MyPledge.ViewClientPaymentData.LoadData() in
C:\Inetpub\wwwroot\MyApp\ViewClient.aspx.vb:line 58
---------------------------------------------------------------------

I have asked the server admin to do following ...

- Give Rights to Read/Write to ASP NET process on directories containing
excel files
- Update Jet Drivers from following URI,
http://www.microsoft.com/downloads/details.aspx?FamilyID=1E268B39-533E-48B0-B8D7-1781BEFDE1F8&displaylang=en

But I am still getting this message...

CAN anyone, PLEASE PLEASE help me??? I am one of the big .NET lovers but
this thing is REALLY REALLY REALLY making me start to hate it .. :S :S :S...

eff_kay

-------------------
Therez No Place like ... 127.0.0.1

Re: Excelt and ADO .NET !!! made me cry now!!!

Rob Graham10-Jan-05 13:53

Rob Graham

10-Jan-05 13:53

First and foremost, make sure the version of ADO is at least as high as your development box. The Jet drivers may not help if the ADO layer is not in synch.
find it here.[^]

second, I think the Extended Properties section should be in double quotes rather than single:

Extended Properties="Excel 8.0;HDR=YES;MAXSCANROWS =1";

You will need to escape the quotes or use an @ style literal string.

HDR=YES is the default, so you could ommit it. MAXSCANROWS =1 only considers the 1st row when determining column data types. I think the default is 8, so this part could also be ommitted, performance hit is negligible, and you appear to beleive the data types will be consistent. I have seen both of these be problematic with different versions of ADO.

The ISAM error usually seems to mean that the current version of ADO/jet did not like the connection string, so experimenting with the Extended properties to try the minimal specification (Just "EXCEL 8.0") should be worthwhile.

Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.