Database Discussion Boards

munklefish14-Dec-05 11:54

14-Dec-05 11:54

If one user has two contacts records the same eg:

where the AccountHolderID, FirstName, and Email are the same for two or more records.

Re: Delete duplicate records

enjoycrack14-Dec-05 14:53

enjoycrack

14-Dec-05 14:53

just do what I suggest... what things do you worry about???
IMO, this can be done with sql script described above....
And remember to do back up first. Poke tongue | ;-P

Gud luke
<< >>

Re: Delete duplicate records

albCode15-Dec-05 6:00

albCode

15-Dec-05 6:00

here is ur solution

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

Re: Delete duplicate records

munklefish15-Dec-05 6:15

munklefish

15-Dec-05 6:15

Thanks!

Didnt think it would be so complicated!!!

Help please ... i don't know what is wrong!!

UltraMAX2K514-Dec-05 5:05

UltraMAX2K5

14-Dec-05 5:05

Hello, i'm novice with Visual C# and SQL Server, so can someone help me please ... i want search for a record in the database, and if it is found then i move the CurrencyManager position to its position but i got this error:

ConstraintException was unhandled:
Column 'cd_id' is constrained to be unique.  Value '9463216602' is already present.

in my database this field [cd_id] in both tables aren't the primary key, are just simple fields ... what's i'm doing wrong, can someone help me please, i'm using this code:

the general variables:

<br />
SqlConnection myConnection;<br />
SqlDataAdapter daCD, daTemas;<br />
DataSet myDataSet = new DataSet("MusicaTeca");<br />
DataViewManager myDVM;<br />
CurrencyManager cm;<br />

in the form_load:

<br />
private void frmProduto_Load(object sender, EventArgs e)<br />
{<br />
   myConnection = new SqlConnection(<br />
      "Server=(local)\\SQLEXPRESS;DataBase=musicateca;" +<br />
      "integrated Security=SSPI");<br />
<br />
   daCD = new SqlDataAdapter("SELECT * FROM tbl_cds", myConnection);<br />
   daCD.TableMappings.Add("Table", "tbl_scds");<br />
   daCD.Fill(myDataSet, "tbl_cds");<br />
<br />
   daTemas = new SqlDataAdapter("SELECT * FROM tbl_temas", myConnection);<br />
   daTemas.TableMappings.Add("Table", "tbl_temas");<br />
   daTemas.Fill(myDataSet, "tbl_temas");<br />
<br />
   myDVM = myDataSet.DefaultViewManager;<br />
<br />
   DataRelation relCD_Tema;<br />
   DataColumn parentCol, childCol;<br />
   parentCol = myDataSet.Tables["tbl_cds"].Columns["cd_id"];<br />
   childCol = myDataSet.Tables["tbl_temas"].Columns["cd_id"];<br />
   relCD_Tema = new DataRelation("RelCD_Temas", parentCol, childCol);<br />
   myDataSet.Relations.Add(relCD_Tema);<br />
<br />
   txtAlbum.DataBindings.Add("Text", myDVM, "tbl_cds.album");<br />
   txtAno.DataBindings.Add("Text", myDVM, "tbl_cds.ano");<br />
   txtArtista.DataBindings.Add("Text", myDVM, "tbl_cds.artista");<br />
   txtEditora.DataBindings.Add("Text", myDVM, "tbl_cds.editora");<br />
   txtGenero.DataBindings.Add("Text", myDVM, "tbl_cds.genero");<br />
   txtID.DataBindings.Add("Text", myDVM, "tbl_cds.cd_id");<br />
<br />
   dbGrid.DataSource = myDVM;<br />
   dbGrid.DataMember = "tbl_cds.RelCD_Temas";<br />
   dbGrid.Columns["reg_id"].Visible = false;<br />
   dbGrid.Columns["cd_id"].Visible = false;<br />
<br />
   cm = (CurrencyManager)this.BindingContext[myDVM, "tbl_cds"];<br />
}<br />

and the search button_click:

<br />
private void btnLocalizar_Click(object sender, EventArgs e)<br />
{<br />
    int intRow;<br />
<br />
    myDVM.DataViewSettings["tbl_cds"].Sort = "cd_id";<br />
    intRow = myDataSet.Tables["tbl_cds"].DefaultView.Find(txtID.Text);<br />
<br />
    if (intRow > 0)<br />
    {<br />
        cm.Position = intRow;   // The Error is in this line.<br />
        cm.Refresh();<br />
    }<br />
    else<br />
    {<br />
         MessageBox.Show("Registo não localizado!");<br />
    }<br />
}<br />

can someone help me please ... and forgive me my bad english.

Thanks
UltraMAX

Re: Help please ... i don't know what is wrong!!

Rob Graham14-Dec-05 5:22

Rob Graham

14-Dec-05 5:22

A field need not be a primary key to have a unique constraint. Sounds like cd_id has a unique constraint or index defined for it. Check the db definitions.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Re: Help please ... i don't know what is wrong!!

agauga14-Dec-05 5:24

agauga

14-Dec-05 5:24

i think you have set up the relation between the two tables in the dataset memory, hence your parent table which contains is taken as the primary key.
Please check out this. Smile | :)

Re: Help please ... i don't know what is wrong!!

UltraMAX2K514-Dec-05 8:09

UltraMAX2K5

14-Dec-05 8:09

Thank you guys for helping me ... jesus ... like i said i'm novice in .NET, i've used VB6 before, but it's completly different, and i've used with Ms ACCESS Blush | :O

... please, can you guys give me just a little example using the code that i'm using or just the code that is missing to resolve that, please!!!

SQL-SERVER 2000 storing documents

agauga14-Dec-05 5:02

agauga

14-Dec-05 5:02

I am using sql-server 2000 database. i am trying out document managment . can anybody tell me How do i store an MS-OFFICE document into a database table. i have created a table with the the column name as "document" datatype as varbinary.
Any help would be welcome Confused | :confused:

anil g

-- modified at 11:41 Wednesday 14th December, 2005

Re: SQL-SERVER 2000 storing documents

Colin Angus Mackay14-Dec-05 6:43

Colin Angus Mackay

14-Dec-05 6:43

varbinary is unlikely to be large enough (unless you are using SQL Server 2005) as its maximum size is just under 8K. You need to create the column as an image (the 2Gb limit should be sufficient for office files.)

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: SQL-SERVER 2000 storing documents

agauga14-Dec-05 19:08

agauga

14-Dec-05 19:08

thanks for the tip. Also can you guide me with the sql statements for storing
it as an image ?

Simple SQL delete statement

munklefish14-Dec-05 2:30

munklefish

14-Dec-05 2:30

Ok im sure this is simple but im new to SQL and dont want to mess things up so your assistance please.

Im using a MS SQL server, which holds client contact details. However in the email field some contacts do not contain properly formatted email adresses.

eg www.somewhere.com rather than someone@somwehere.com

So what sql statement would i run to delete these fields?

Is it as simple as:

Delete * From table WHERE email NOT LIKE %@%
(im looking for records without a '@' symbol)

Thanks!

Re: Simple SQL delete statement

albCode14-Dec-05 4:19

albCode

14-Dec-05 4:19

try this

delete from table where email not like %@%

--this query deletes all rows in which doesnt exists character @

Re: Simple SQL delete statement

munklefish14-Dec-05 4:20

munklefish

14-Dec-05 4:20

Yeah,

Thought so!

Thanks for confirming.

Re: Simple SQL delete statement

albCode14-Dec-05 4:25

albCode

14-Dec-05 4:25

u are welcome Wink | ;)

SQL Express and Learning Center Video

Brigg Thorp13-Dec-05 3:59

Brigg Thorp

13-Dec-05 3:59

OK all. I installed VS2005 Pro with SQL Express. No problems. I went to the Microsoft Learning Center[^] web site for the Visual Web developer and started watching the videos. However, when I try to follow the Lesson 8 video, I can't get the Advanced SQL Connection options to show themselves (they are all greyed out). So when I try to add a control that allows data editing to an ASPX page, I can't turn on the editing feature. Is this something in SQL Express I have to configure?

Regards,

Brigg Thorp
Senior Software Engineer
Timex Corporation

SQLEXPRESS

Illegal Operation13-Dec-05 2:47

Illegal Operation

13-Dec-05 2:47

Help!

I installed VWD 2005 with SQLEXPRESS 2005 and the management tools. Now when I try to create a new DB in VWD I get a error "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance."

Please help.

Illegal Operation

Making Computer Software Talk

Re: SQLEXPRESS

enjoycrack13-Dec-05 7:37

enjoycrack

13-Dec-05 7:37

Hi there,

This thread SQLExpress Issue might help u...

<< >>

SQL

dev_rfid13-Dec-05 1:09

dev_rfid

13-Dec-05 1:09

update command for interchanging to field values
ex.

f_name L_name
--------------------------
a b
c d

by mistake f_name went to l_name and vise varsa Smile | :)

Re: SQL

Ritesh123413-Dec-05 2:19

Ritesh1234

13-Dec-05 2:19

Hi,
Why don't u try with cursors......
e.g

DECLARE authors CURSOR FOR
SELECT au_id, f_name, l_name
FROM authors

OPEN authors

FETCH NEXT FROM authors
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN

Update author_master
SET f_name = @au_lname,
l_name = @au_fname
WHERE au_id = @au_id

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors
DEALLOCATE authors
GO

I hope this will help u....

Regards,
Ritesh

Re: SQL

toxcct13-Dec-05 23:27

toxcct

13-Dec-05 23:27

...and the question is ?

TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]

Re: SQL

miah alom14-Dec-05 4:24

miah alom

14-Dec-05 4:24

Simply run the update command. Below is a sample.

CREATE TABLE dbo.ctm
(
id numeric(18,0) IDENTITY,
col1 varchar(20) NULL,
col2 varchar(20) NULL
)
go

INSERT INTO dbo.ctm ( col1, col2 ) VALUES ( 'aa', 'bb' )
INSERT INTO dbo.ctm ( col1, col2 ) VALUES ( 'cc', 'dd' )

SELECT T.id, T.col1, T.col2 FROM dbo.ctm T
update ctm
set col1 = col2, col2 = col1
SELECT T.id, T.col1, T.col2 FROM dbo.ctm T

OleDbDataAdapter & Primary Keys

JohnBeton13-Dec-05 0:53

JohnBeton

13-Dec-05 0:53

Hi experts,

I have the following problem when saving data from an XML-file to a database using the OleDbDataAdapter:

I'm using a typed dataset, derived from the xml-schema.
I load the xml-file into the dataset and try to save the data to the database. (the dataAdapter has all the commands (select, insert, update, delete) properly configured.

When it's all new data, there's no problem, all data is saved correctly.

But it's also possible that the xml-file contains data that is already in the database (primary key already exists), so this data needs to be updated (instead of re-inserted)(this is what a dataAdapter takes care of, right ?)

I tried 2 approaches:
1) Only loading the xml-data into the dataset:
When i use the update-method of my dataAdapter and there is data that is already in the database (primary key already exists) i get an oleDbException.

2) First load the database-data in the dataset, and then load the xml-file in the dataset:
I get a ConstraintException while loading the xml-data because primary key already exists in the datatable of the dataset.

How can I fix this problem so that I get 1 table with updated old data and inserted new data ?
Any help is much appreciated... Thanks in advance...

John

Get Secure data from host

mtone12-Dec-05 10:12

mtone

12-Dec-05 10:12

I need to Get data from my Sql Server 2000 database from my web site host to my local dataserver. I need to do this in a way that the data is secure. I can use Enterprise manager to import data but this is not secure or is it?

Thanks

Re: Get Secure data from host

Farhan Noor Qureshi12-Dec-05 12:53

Farhan Noor Qureshi

12-Dec-05 12:53

You could use ASP.NET and encryption. See this.[^]

Farhan Noor Qureshi

^{if (this == this) thow this;}

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