How to Remove Dublicate from the Table - Database Discussion Boards

Re: enable paging in gridview programatically

DerekFL10-Sep-07 10:32

10-Sep-07 10:32

I have dealt with this problem before. Here is how I solved the problem. I used a proc that used the ROW_NUMBER() function. This way you can format the number of rows to show (page size) on your grid and show total pages as @totalRows/@PageSize

Example

CREATE PROC myPROC
(
@State char(2),
@PageSize tinyint,
@PageNumber smallint = 1,
@TotalRows smallint = 0 OUTPUT
)
AS
BEGIN
DECLARE @BeginRow int
DECLARE @EndRow int
SET @BeginRow = (@PageSize * (@PageNumber-1)) + 1
SET @EndRow = (@PageSize * (@PageNumber-1)) + @PageSize
SELECT RowId, FirstName, LastName
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY LastName, FirstName) AS RowId, FirstName, LastName
FROM
(
SELECT au_fname FirstName, au_lname LastName
FROM dbo.authors WHERE [state] = @State
) q1
) q2
WHERE RowId BETWEEN @BeginRow AND @EndRow
END

Dave Evans

Re: enable paging in gridview programatically

DerekFL12-Sep-07 5:00

DerekFL

12-Sep-07 5:00

I forgot to add SET @TotalRows = SELECT COUNT(*) FROM dbo.authors WHERE [state] = @State

Re: enable paging in gridview programatically

dinakatina12-Sep-07 13:51

dinakatina

12-Sep-07 13:51

thanks a lot dave

Drop

TAREQ F ABUZUHRI7-Sep-07 10:38

TAREQ F ABUZUHRI

7-Sep-07 10:38

i make drop to table ..
i want to retrive this table what i can do ??

Palestine

Re: Drop

Michael Potter7-Sep-07 11:34

Michael Potter

7-Sep-07 11:34

Restore from backup

Re: Drop

Rami Said Abd Alhalim8-Sep-07 0:22

Rami Said Abd Alhalim

8-Sep-07 0:22

Welcome Pelastine Rose | [Rose]

:

You can not retrieve table after drop

but if you have Backup from Database you can restore it

Rami Abdalhalim From Pelastine

Re: Drop

TAREQ F ABUZUHRI8-Sep-07 0:53

TAREQ F ABUZUHRI

8-Sep-07 0:53

thanks rami ,
i will be very happey to see in my msn tareq_abuzuhri@hotmail.com

Palestine

How to Remove Dublicate from the Table

Vimalsoft(Pty) Ltd7-Sep-07 5:49

Vimalsoft(Pty) Ltd

7-Sep-07 5:49

Evening Everyone

i have a Table Called "Property", this table a lot of Fields and a Primary key called "Property_ID" the identity column. when there are Dublicate in the table, i would not be able to Catch them by only "Property_ID". i have some 2 fields that are not supposed to be the same. Field "Fun_key" and "Lis_key", and that means there must never be
Func_key * 2 and Lis_key * 2, i tried this statement and it does not bring me Dublicates.

select Func_key, Lis_key
  from Property
group by  Func_key,Lis_key
having count(Lis_key) >1  and count(Func_key) >1
order  by Lis_key

how can i achieve this and sort them by lis_key

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com

Re: How to Remove Dublicate from the Table

Kschuler7-Sep-07 9:45

Kschuler

7-Sep-07 9:45

I'm not exactly sure what you are asking for. If you want a list of property_id's where the func_key and lis_key are the same, you would query something like this:

SELECT property_id, func_key, lis_key<br />
WHERE func_key=lis_key<br />
ORDER BY lis_key

If this is not what you want, then please provide us with examples of your data and specifically what you want to pull back.

Hope this helps.

Re: How to Remove Dublicate from the Table

Frank Kerrigan10-Sep-07 9:31

Frank Kerrigan

10-Sep-07 9:31

code>
SELECT DISTINT *
INTO #TEMP
FROM TABLE

SELECT * FROM #TEMP

TRUNCATE TABLE

INSERT TABLE
SELECT * FROM TEMP

Use this with care as it may cause you issue with indentities and triggers etc,,,,,

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: How to Remove Dublicate from the Table

Vimalsoft(Pty) Ltd10-Sep-07 20:41

Vimalsoft(Pty) Ltd

10-Sep-07 20:41

Thanks man it worked

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com

Oracle SQL Script

Programm3r7-Sep-07 4:36

Programm3r

7-Sep-07 4:36

Hi all,

Could someoine please provide me with an example of a Oracle SQL Script for creating more than one table.
Many Thanks in advance

Regards,

The only programmers that are better that C programmers are those who code in 1's and 0's Green Alien | [Alien]

Programm3r
My Blog: ^_^

Re: Oracle SQL Script

Kschuler7-Sep-07 9:47

Kschuler

7-Sep-07 9:47

CREATE TABLE MYTABLE1(<br />
  column1A DECIMAL(4,0) NOT NULL DEFAULT=0, <br />
  column1B TEXT(20) NOT NULL DEFAULT='', <br />
);<br />
CREATE TABLE MYTABLE2(<br />
  column2A DECIMAL(4,0) NOT NULL DEFAULT=0, <br />
  column2B TEXT(20) NOT NULL DEFAULT='', <br />
);

How can I give a No duplicates property?

Big Ralph7-Sep-07 1:09

Big Ralph

7-Sep-07 1:09

I don't want any duplicates in a field that is not a primary key how can I do that in SQL Server 2005? thanks

BiG RaLpH

Re: How can I give a No duplicates property?

Giorgi Dalakishvili7-Sep-07 1:22

Giorgi Dalakishvili

7-Sep-07 1:22

By defining unique constraint[^]

#region signature
my articles
#endregion

Re: How can I give a No duplicates property?

Chetan Patel7-Sep-07 3:10

Chetan Patel

7-Sep-07 3:10

Use Unique key constrainst

Best Regards,
Chetan Patel

Calling Stored SQL Functions from Visual Basic

Mark Cabbage6-Sep-07 23:10

Mark Cabbage

6-Sep-07 23:10

Hi Everyone,
Wasn't sure if this was a VB or SQL question and where to post so I am trying here.

I read it is better to use stored procedures and functions on the SQL server to centralise and re-use code, as well as allowing us to restrict rights to EXECUTE rather than full rights.

I wanted to retrieve (and maybe in another function SET), some data so I wrote the following basic function and stored it on the server. My problem is that no matter how I try, I can't seem to find a way to call the function from my VB.NET 2005 application. Please help

ALTER FUNCTION dbo.GetEmployeeFont
(
@EmployeeID int
)

RETURNS TABLE
AS
RETURN
(SELECT FontName, FontSize
FROM tblEmployees
WHERE (EmployeeID = @EmployeeID))

Re: Calling Stored SQL Functions from Visual Basic

pmarfleet6-Sep-07 23:14

pmarfleet

6-Sep-07 23:14

You can't call the function directly. You should write a stored procedure that acts as a wrapper for the function to call it from your VB application.

Paul

Re: Calling Stored SQL Functions from Visual Basic [modified]

manuo56-Sep-07 23:25

manuo5

6-Sep-07 23:25

You cannot use sqlserver function in vb.net .Create one sp in sqlserver
and call this fun with in sp .

-- modified at 5:30 Friday 7th September, 2007

Manu

Re: Calling Stored SQL Functions from Visual Basic

Paddy Boyd7-Sep-07 0:00

Paddy Boyd

7-Sep-07 0:00

I'm not sure what these people are talking about, but it's just the same as executing any other bit of sql:

SELECT FontName, FontSize FROM dbo.GetEmployeeFont(@EmployeeId)

Just run this with a sql command and return a reader (or whatever suits your code).

(Although, that said, it seems to be a more useful thing to have as a stored procedure. Functions are generally more useful when you will re-use them in other bits of sql.)

Re: Calling Stored SQL Functions from Visual Basic

pmarfleet7-Sep-07 0:22

pmarfleet

7-Sep-07 0:22

My statement is still correct. A function cannot be called directly. The call must be made from either a stored procedure or inline SQL statement.

Paul

Re: Calling Stored SQL Functions from Visual Basic

Paddy Boyd7-Sep-07 0:25

Paddy Boyd

7-Sep-07 0:25

Yes, but what benefit is gained from wrapping the function in a stored procedure? It's just another level of code to go wrong...

Re: Calling Stored SQL Functions from Visual Basic

pmarfleet7-Sep-07 0:36

pmarfleet

7-Sep-07 0:36

I never use inline SQL statements. They may be quicker to code, but the advantages of using stored procedures (maintainability, robustness, security) more than outweigh the extra code overhead.

Re: Calling Stored SQL Functions from Visual Basic

Paddy Boyd7-Sep-07 0:44

Paddy Boyd

7-Sep-07 0:44

I've been through this discussion a few times with different people and I think that it's more of a religious type decision than anything else. We may just have to agree to differ.

Re: Calling Stored SQL Functions from Visual Basic

Mark Cabbage8-Sep-07 14:45

Mark Cabbage

8-Sep-07 14:45

Thanks Paddy.

I need to return some values and use them so I think the function would be best, but there seems to be some debate here if it can be achieved.

Excuse my ignorance but my previous coding was for MSAcess so I"m trying to learn SQL server issues, and move from VBA to VB.NET.

I have put in the SELECT statement you sugessted but the compiler tells me "Select CASE must end with a matching END SELECT", and hovering over FROM in the statement gives me "name not declared" . Obviously I need more code to setup the SQL statement - could you kindly give me an example please.

Many thanks
Mark

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