text , ntext datatypes ( PLEASE HELP ) - Database Discussion Boards

CandyMe5-Oct-06 19:41

5-Oct-06 19:41

I figured it some 2 hours after my last reply.
I decided to create a temp table where I'd do my select top
then just count the number of rows in that temp table
with the whereclause, ofcourse

CREATE TABLE #RowCount(SubscriberId INT)

EXEC ('INSERT #RowCount' + ' SELECT TOP ' + @Top + ' (SubscriberId) FROM ' + @TableName)

--get total pages
EXEC ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM #RowCount')

--get total number of rows
EXEC ('SELECT COUNT(*) FROM #RowCount')

so if i selected top 10000 and only 3000 passed the condition
totalrowcount is 3000; otherwise, if i selected top 2500 and 3000 passed the condition, totalrowcount is 2500

thanks! Suspicious | :suss:

Gerri

Re: select with TOP and COUNT

templarx5-Oct-06 22:08

templarx

5-Oct-06 22:08

Or you can try something like this:

SELECT
sub.surname,count(*) as count
FROM
(select top 5 surname from myTable) sub
GROUP BY sub.surname
--[WHERE surname = 'CRUZ']

OUTPUT will bev [without the where clause]
CRUZ 3
TOM 1
JACK 1

if you want to see all 5 rows [basically repeating ]

SELECT sub3.surname,sub2.count
FROM
(select count(*) as count from
(select top 5 surname from myTable where surname = 'CRUZ') sub) sub2
CROSS JOIN
(select top 5 column_name from myTable where surname = 'CRUZ' ) sub3

Re: select with TOP and COUNT

CandyMe7-Oct-06 2:17

CandyMe

7-Oct-06 2:17

thanks so much. well appreciated. Smile | :)

Gerri

datetime

lakshmi_sri4-Oct-06 15:01

lakshmi_sri

4-Oct-06 15:01

how can i get the time and date in the datecreated field in a data table while inserting a record?

Re: datetime

Edbert P4-Oct-06 16:39

Edbert P

4-Oct-06 16:39

You can return it as an OUT parameter if you are using a stored procedure, or do a select query on the record after you've inserted it. AFAIK there's no easier way to get this.

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson

"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin

Edbert
Sydney, Australia

Re: datetime

Eric Dahlvang5-Oct-06 5:11

Eric Dahlvang

5-Oct-06 5:11

When you insert into the table, use getdate() as the value for the DateCreated field:

insert into mytesttable (MyDateField) values (getdate())

Or, to make a default value of the current datetime, in design view in the Enterprise Manager type (getdate()) in the Formula field for the table.

Or, when you create the table in the Query Analyzer, do it this way:

CREATE TABLE [MyTestTable] (
    [IDColumn] [int] IDENTITY (1, 1) NOT NULL ,
    [DateCreated] AS (getdate())
) ON [PRIMARY]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Query Builder to make stored procedures?

eggsovereasy4-Oct-06 10:43

eggsovereasy

4-Oct-06 10:43

Ok, I know how to start the query designer in VS2005 and I can make a query and run it and see the data, thats all good.

I can also create new stored procedures by using the server explorer to go the SP folder, right click, add new. I then type in my stored procedure and everything works fine.

However, is there a way to create stored procedures with the query builder, other than using typed datasets?

text , ntext datatypes ( PLEASE HELP )

abdelhameed814-Oct-06 9:22

abdelhameed81

4-Oct-06 9:22

hi all
every time i add a column to my table with the type text or ntext all what is entered in the database is only the first 16 characters. it happened also with all my colleagues
plz help me with this problem

abdelhameed81

Re: text , ntext datatypes ( PLEASE HELP )

Mark Salsbery4-Oct-06 10:44

Mark Salsbery

4-Oct-06 10:44

How is the column defined?
How are you inserting text into the column?

Mark

Re: text , ntext datatypes ( PLEASE HELP )

abdelhameed814-Oct-06 21:51

abdelhameed81

4-Oct-06 21:51

the column is created in the design view of the SQL enterprise manager and the data is inserted to it from an ASP.Net application

abdelhameed81

Re: text , ntext datatypes ( PLEASE HELP )

Mark Salsbery5-Oct-06 6:26

Mark Salsbery

5-Oct-06 6:26

What is the max length of the text for the column?

I meant can you post code snippet showing how you do the insert?

Database is SQL Server?

Mark

Re: text , ntext datatypes ( PLEASE HELP )

Jerry Hammond5-Oct-06 4:20

Jerry Hammond

5-Oct-06 4:20

FWIW text is a datatype that will be deprecated in future versions of SQL Server. Keep that in mind as you develop your tables. varchar or nvarchar are the best choice in my nosey opinion.

"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky

Delete Row Count

Skanless4-Oct-06 9:21

Skanless

4-Oct-06 9:21

Hello,

I have a couple questions and would really appreciate your responses.

Question:

1. If you are new to a database, is there a way that you can quickly find out relationships between the tables?

2. I need to retrieve information on all the tables in a database to find out which tables have a rowcount of more than 50,000 records then delete some record which thus reducing the table to 50,000 rows so as to make space. Is there a way to do that? and if so, what is the syntax to obtain this?

3. How can I cascade constraints when deleting records from a specific table?

I am using SQL server 2000 to accomplish this task.

Thanks

Greg

Coding makes the world go round!!!

Re: Delete Row Count

Edbert P4-Oct-06 16:45

Edbert P

4-Oct-06 16:45

Skanless wrote:
1. If you are new to a database, is there a way that you can quickly find out relationships between the tables?

There are diagrams that you can create to see the relationship between the tables.

Skanless wrote:
2. I need to retrieve information on all the tables in a database to find out which tables have a rowcount of more than 50,000 records then delete some record which thus reducing the table to 50,000 rows so as to make space. Is there a way to do that? and if so, what is the syntax to obtain this?

You can use the sysobjects table that can return the tables existing in your database, then query each table to check how many records it has and delete the ones over 50,000. I can't remember the correct syntax, I'm sure a bit of googling will do.

Skanless wrote:
3. How can I cascade constraints when deleting records from a specific table?

You need to specify the necessary constraints to the tables before deleting the record, and it will automatically trigger the deletion of child records.

Column Length

Guinness4Strength4-Oct-06 7:50

Guinness4Strength

4-Oct-06 7:50

I'm using ADO.NET and I'm attempting to get the column length for VARCHAR columns using the MaxLength property of the DataTable class, but it always returns -1, any secrets to finding this info from a returned MS SQL Server Query ?

Re: Column Length

Mark Salsbery4-Oct-06 10:56

Mark Salsbery

4-Oct-06 10:56

According to the docs

"If the column has no maximum length, the value is –1 (default)."

If you set MaxLength on a column do you still get -1 back?

Mark

Re: Column Length

Guinness4Strength4-Oct-06 11:00

Guinness4Strength

4-Oct-06 11:00

Yes, the length limit can be seen in the "Design Table" dialog of the SQL Server Enterprise Manager for the table in question. Its a VARCHAR(50) column, but after I query this tabkle the DataTable's DataColumn has a MaxLength value of -1

Here is the code i use to Query the Table if that helps...

DataTable DT = new DataTable();
if(m_DB is SqlConnection)
{
	m_DA = new SqlDataAdapter(Query,(SqlConnection)m_DB);
	m_CB = new SqlCommandBuilder((SqlDataAdapter)m_DA);
	((SqlCommandBuilder)m_CB).QuotePrefix="[";
	((SqlCommandBuilder)m_CB).QuoteSuffix="]";
	((SqlDataAdapter)m_DA).Fill(DT);
}

Re: Column Length

Mark Salsbery4-Oct-06 11:49

Mark Salsbery

4-Oct-06 11:49

Ok, sorry.

I use ODBC to get schema info in my database code, specifically SQLTables() and SQLColumns().

Take a look at GetSchemaTable (in DataReader). I'm pretty sure that retreives the same info
as the ODBC functions.

I think it's tough to use a specific SQL query because the way schema is stored varies by server,
version, etc.

Mark

Ms access and Scope identity

Vipin.d4-Oct-06 2:12

Vipin.d

4-Oct-06 2:12

hai frndz,
i am using MS office 2003.
In MSAccess Database is there any way to retrive autoincremt value just after insertion to the table like
select @@identity or select Scope_identity in MSSQL 2000.

in select @@identity some times it return 0 value.

help me plzzzzzzz

All I ever wanted is what others have....

CrazySanker

Re: Ms access and Scope identity

Alaric_4-Oct-06 19:45

Alaric_

4-Oct-06 19:45

Well....are you talking about an 'Autonumber' field? If you are, then if your Autonumber field were something like 'ProductID' then you could say something like

SELECT Max(ProductID) FROM Product

Problem in Implementing DataAccess Mechanism

Jay_se4-Oct-06 0:45

Jay_se

4-Oct-06 0:45

Hi All,

This is my first post in this message board. I am working in ASP.NET 2.0 project using C#. Smile | :)

I am implementing DataAccess mechanism using 3 tier architecture.

UI:In my aspx page, i am using ObjectDataSource, in which the SELECT method is a method from my BLL(Business Logic Layer).

BLL:In this class, it accesses a method from my DAL(Data Access Layer), whose return type is DataTable.

DAL:It uses a User Stored Procedure (USP) to retrieve the DataTable, and it has some OUTPUT variables also.

The whole logic is works fine. The OUTPUT variable's values are captured in BLL class (during run time).

My Problem : I wish to access the OUTPUT variables from the ObjectDataSource's Selected event handler.How to do this?

Previously, i am directly access my DAL from my ObjectDataSource, that time the stmt e.OutputParameters["output_var"] gives me the result.

Thanks.

Regards,

Jay

Re: Problem in Implementing DataAccess Mechanism

Jay_se5-Oct-06 19:04

Jay_se

5-Oct-06 19:04

The problem is that i didn't passed the arguments in my method in BLL as Reference Variable.

Now i am used the Reference Variable using ref keyword in C#. Now i got the result value in my ObjectDataSource's Selected event handler using e.OutputParameters["output_var"].

Thanks. Laugh | :laugh:

Regards,

Jay

Urgent :: SQL Server Connection Problem

harshActsw3-Oct-06 11:41

harshActsw

3-Oct-06 11:41

When trying to get a connection to a SQL server thro ASP.net application, if the SQL server is not running , what error usually it will throw, and how can i trace that error.

thanks,

Harsha

Re: Urgent :: SQL Server Connection Problem

mr_lasseter3-Oct-06 13:45

mr_lasseter

3-Oct-06 13:45

You will get the error "Sql Server does not exist or access is denied."

Mike Lasseter

Design Issue: Multiple definitions of ADO.NET abstraction classes [modified]

Alaric_3-Oct-06 10:00

Alaric_

3-Oct-06 10:00

Hi. I've had an issue come up in a program that I am working on with multiple projects in the same solution. The way I handle database connectivity is to encapsulate my database connection and my dataset in two separate class files, clsDatabase and clsQueryResults. clsDatabase evaluates a given connectionString and dataProvider to determine whether the app is using an OLEDB, SQLServer, MySQL, or Oracle database and uses the appropriate connections (i.e. OleDbConnection for Access, SQLConnection for SQLServer)

...Anyway, each of the projects that are being put together into this program were developed discretely, so each of them needed to be able to communicate with the database. Thus, clsDatabase and clsQueryResults are located in each of the programs. This causes a problem in my application because it means that I have to maintain 2 separate instances of clsDatabase (both of which connect to the same db file) which is very undesirable. What I would like to do is pull clsDatabase and clsQueryResults out of the individual project files and place them in their own library. However, for security reasons, the db methods located in clsDatabase have been given access type "internal." I would like to keep this library exposed to my projects while not disclosing it to the UI or Application layer. Does anyone know of any way to accomplish this?

For clarity:
This is how my logic looks:

UI->Application->|Project1->|Project1ClassFile->|clsDatabase->|ADO.NET
                 |
                 |Project2->|Project2ClassFile->|clsDatabase->|ADO.NET

This is how I want it to look:

UI->Application->|Project1->|Project1ClassFile->|
                 |                              |->clsDatabase->|ADO.NET
                 |Project2->|Project2ClassFile->|

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