Database

Pete O'Hanlon11-Dec-06 23:52

Pete O'Hanlon

11-Dec-06 23:52

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Colin Angus Mackay11-Dec-06 23:54

Colin Angus Mackay

11-Dec-06 23:54

therasa12345vijay wrote:
how to reply to the particular message

By pressing the [reply] button on the message you want to reply to.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

therasa12345vijay11-Dec-06 23:55

therasa12345vijay

11-Dec-06 23:55

sorry........ now only i got the reply link.

Help needed....Please tell me how can i find all table information from access in to ASP.NET

ed^nrgc12-Dec-06 8:54

ed^nrgc

12-Dec-06 8:54

Don't know WTF | :WTF:

NileshUpadhyay11-Dec-06 22:37

NileshUpadhyay

11-Dec-06 22:37

Hi,

I want to Fetch all user table's name and information (this mean i want to describe all tables information) from my MS Access DataBase using ASP.net 2.0 and C#...

I tried with msysobjects but it shows error retrieving problem form database in ASP.NET 2.0

Is there Any Idea or any Other Way please give me your views on it

Thanks in advance...
Nilesh Upadhyay

Re: Help needed....Please tell me how can i find all table information from access in to ASP.NET

Pete O'Hanlon11-Dec-06 23:21

Pete O'Hanlon

11-Dec-06 23:21

Nilesh, you can try the following:

Use this to get all of the tables in a particular database.

private DataTable GetSchema()
{
	DataTable schema = null;

	using (OleDbConnection dbConn = new OleDbConnection(_connectionString))
	{
		dbConn.Open();
		schema = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
			new object[]{null, null, null, "TABLE"});
		dbConn.Close();
		// The table name is in the schema field "TABLE_NAME";
	}
	return schema;
	
}

To retrieve the column information for a particular table, call this function with the name of the table:

private DataView GetSchema(string table)
{
	DataTable schema = null;

	using (OleDbConnection dbConn = new OleDbConnection(_connectionString))
	{
		dbConn.Open();
		schema = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
			new object[]{null, null, table});
		dbConn.Close();
	}
	return schema.DefaultView;
}

MySQl -> SELECT ALL BUT TheseField

Tomy140211-Dec-06 21:24

11-Dec-06 21:24

I wonder if anyone knows MySQL syntax for display All Field except certain Fields.

example :
I have a table with 100 Fields. And I want to display All Fields except Field number 25, 50, 75 and 100.

How do I do that?

Thanks for any answer.

Enjoy It!

Colin Angus Mackay11-Dec-06 21:40

Colin Angus Mackay

11-Dec-06 21:40

Tomy1402 wrote:
I wonder if anyone knows MySQL syntax for display All Field except certain Fields.

example :
I have a table with 100 Fields. And I want to display All Fields except Field number 25, 50, 75 and 100.

How do I do that?

SELECT [column1], [column2], [column3], [column4], [column5], [column6], [column7], [column8], [column9], [column10], [column11], [column12], [column13], [column14], [column15], [column16], [column17], [column18], [column19], [column20], [column21], [column22], [column23], [column24], [column26], [column27], [column28], [column29], [column30], [column31], [column32], [column33], [column34], [column35], [column36], [column37], [column38], [column39], [column40], [column41], [column42], [column43], [column44], [column45], [column46], [column47], [column48], [column49], [column51], [column52], [column53], [column54], [column55], [column56], [column57], [column58], [column59], [column60], [column61], [column62], [column63], [column64], [column65], [column66], [column67], [column68], [column69], [column70], [column71], [column72], [column73], [column74], [column76], [column77], [column78], [column79], [column80], [column81], [column82], [column83], [column84], [column85], [column86], [column87], [column88], [column89], [column90], [column91], [column92], [column93], [column94], [column95], [column96], [column97], [column98], [column99]
FROM MyTable

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Tomy140211-Dec-06 21:44

11-Dec-06 21:44

Well, I was expecting something simpler.
maybe like this:

SELECT * EXCEPT `Field25`, `Field50`, `Field75`, `Field100` FROM MyTable;

Enjoy It!

Pete O'Hanlon11-Dec-06 23:24

Pete O'Hanlon

11-Dec-06 23:24

Select * is generally considered to be bad practice. You should explicitly name your columns.

There is no EXCEPT construct in SQL, because you either want to retrieve everything or you want to retrieve certain fields, in which case you specify the names of the fields.

therasa12345vijay11-Dec-06 23:54

therasa12345vijay

11-Dec-06 23:54

ya. thats correct. there is no EXCEPT keyword in the MySql. So we type all the necessary fields we want...

Tomy140212-Dec-06 14:43

How to use stored procedureof SQL Server in vc++

12-Dec-06 14:43

well, I guess I have to write 95 column names whenever I query my table.
Thx.

Enjoy It!

With_problem11-Dec-06 20:13

With_problem

11-Dec-06 20:13

i am trying to use stored procedure to store data in my dtabse getting from remote server....but i dont know from where to start...
i have done ole db connection to database with the help of ATL consumer wizard....now how to proced further...can anybody help me.... D'Oh! | :doh:

Sql Server 2000 Connection Problem

kirankanchinadam11-Dec-06 20:00

kirankanchinadam

11-Dec-06 20:00

hello
I am using Sql server 2000 and .Net 2005 i am suffering from the connection problem ,error are.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have Already Enabled Tcp/IP And Named Pipes in Network utility of sql server.
Please Help

kiran

Re: Sql Server 2000 Connection Problem

Paul Conrad25-Dec-06 18:25

Paul Conrad

25-Dec-06 18:25

kirankanchinadam wrote:
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Have you fixed this problem? If not, look in the Surface Configuration tool...

If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa

how to connect to database

Rmesh11-Dec-06 17:41

Rmesh

11-Dec-06 17:41

i want to connect vb.net to sql server 2000 how to connect

Re: how to connect to database

Paul Conrad11-Dec-06 19:31

Paul Conrad

11-Dec-06 19:31

Rmesh wrote:
i want to connect vb.net to sql server 2000 how to connect

Use a connection string, here is a useful site: http://www.connectionstrings.com[^]

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon

Re: how to connect to database

karam chandrabose12-Dec-06 0:48

karam chandrabose

12-Dec-06 0:48

create a SQLConnection object
assign a connection string to the connection object's connection string property
say ConnectionObject.Open();
now u r connected
use this connection object in your command object to run commands in the database

Karam C Bose

MySQL -> InnoDB error code 1005

Tomy140211-Dec-06 17:09

Re: MySQL -> InnoDB error code 1005

11-Dec-06 17:09

Hi, I have 3 tables, here is the .sql file, just copy and paste starts from the line :

-- STARTS FROM HERE --

DROP TABLE IF EXISTS `gudang_list`;
CREATE TABLE `gudang_list` (
`Gudang ID` varchar(15) character set latin1 NOT NULL default '0',
`Description` varchar(50) character set latin1 NOT NULL default '',
`Address` text character set latin1 NOT NULL,
`Phone 1` varchar(15) character set latin1 NOT NULL default '',
`Manager` varchar(10) character set latin1 NOT NULL default '',
`Maximum Hold` int(5) unsigned NOT NULL default '0',
`Current Hold` int(5) unsigned NOT NULL default '0',
`Created Date` date NOT NULL,
PRIMARY KEY (`Gudang ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `item_general`;
CREATE TABLE `item_general` (
`Item ID` varchar(15) character set latin1 NOT NULL,
`Description` varchar(50) character set latin1 NOT NULL,
`Item Class` varchar(15) character set latin1 NOT NULL default 'Stock Item',
`Active` enum('Yes','No') character set latin1 NOT NULL default 'Yes',
`Subject to Commission` enum('Yes','No') character set latin1 NOT NULL default 'No',
`Description for Sales` text character set latin1,
`Description for Purchases` text character set latin1,
`Price level 1` double(7,2) default NULL,
`Price level 2` double(7,2) default NULL,
`Price level 3` double(7,2) default NULL,
`Price level 4` double(7,2) default NULL,
`Price level 5` double(7,2) default NULL,
`Price level 6` double(7,2) default NULL,
`Price level 7` double(7,2) default NULL,
`Price level 8` double(7,2) default NULL,
`Price level 9` double(7,2) default NULL,
`Price level 10` double(7,2) default NULL,
`Item Tax Type` tinyint(3) unsigned NOT NULL default '1',
`Last Unit Cost` double(7,2) default NULL,
`Cost method` enum('FIFO','LIFO','Average') character set latin1 NOT NULL default 'FIFO',
`GL Sales Account` varchar(15) character set latin1 NOT NULL default '40000',
`GL Inventory Account` varchar(15) character set latin1 NOT NULL default '12000',
`GL Cost of Sales Account` varchar(15) character set latin1 NOT NULL default '50000',
`UPC/SKU` text character set latin1,
`Item Type` varchar(15) character set latin1 default NULL,
`Unit/Measure` varchar(15) character set latin1 default NULL,
`Weight` double(5,2) default NULL,
`Qty on Hand` smallint(5) unsigned default NULL,
`Qty Available` smallint(5) unsigned default NULL,
`Qty on SO's` smallint(5) unsigned default NULL,
`Qty on PO's` smallint(5) unsigned default NULL,
`Qty on Service` smallint(5) unsigned default NULL,
`Minimum Stock` smallint(5) unsigned default NULL,
`Reorder Stock` smallint(5) unsigned default NULL,
`Preferred Vendor ID` varchar(15) character set latin1 default NULL,
`Buyer ID` varchar(15) character set latin1 default NULL,
PRIMARY KEY (`Item ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `item_location`;
CREATE TABLE `item_location` (
`Item ID` varchar(15) collate latin1_general_ci NOT NULL default '',
`Gudang ID` varchar(15) collate latin1_general_ci NOT NULL default '',
`Quantity` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`Item ID`,`Gudang ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- ENDS HERE --

I Use MySQL Administrator

When I tried to make a foreign key in table ITEM_LOCATION.
Field "Item ID" will be connected with "Item ID" in table ITEM_GENERAL, with RESTRICT on DELETE and CASCADE on UPDATE.

The problem is, I kept getting this message :

MySQL Error Number 1005
Can't create table 'myDbName\#sql-610-122.frm' (errno 150)

I have another Database with tables like above, but I got no problems when creating foreign key. It's only in the above tables the problem is on.

I just hope it's not a bug...
Any Helps? Thanks.

Enjoy It!

Rob Graham11-Dec-06 17:42

Rob Graham

11-Dec-06 17:42

Tomy1402 wrote:
`Item ID` varchar(15) collate latin1_general_ci NOT NULL default '',

Is the underlined part correct? defaults to an empty string?

Re: MySQL -> InnoDB error code 1005

Tomy140211-Dec-06 17:49

Re: MySQL -> InnoDB error code 1005

11-Dec-06 17:49

Well, I made the tables more simpler:

-- ------------

CREATE DATABASE qwe;
USE qwe;

DROP TABLE IF EXISTS `gudang_list`;
CREATE TABLE `gudang_list` (
`Gudang ID` varchar(15) character set latin1 NOT NULL default 'GA-001',
`Description` varchar(50) character set latin1 NOT NULL default 'Gudang Atas',
PRIMARY KEY (`Gudang ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `item_general`;
CREATE TABLE `item_general` (
`Item ID` varchar(15) character set latin1 NOT NULL default 'KOM-001',
`Description` varchar(50) character set latin1 NOT NULL default 'KOMPUTER',
PRIMARY KEY (`Item ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `item_location`;
CREATE TABLE `item_location` (
`Item ID` varchar(15) collate latin1_general_ci NOT NULL default '',
`Gudang ID` varchar(15) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`Item ID`,`Gudang ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- -------------------------

This produces the same error... and I suspect the CHARSET and COLLATE play something about the error..
Just import it using MySQL Administrator.

Tomy140211-Dec-06 18:05