|
Hi database people.
So how can I remove duplicate rows from a table? So far I have been using:
select distinct * into NewTable from OldTable
and then removing the original table and renaming the new table to the original name.
Anyone know a better way? Thanks..
Regards,
Rob Philpott.
|
|
|
|
|
check this
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|
|
|
|
|
Nice one. Thanks!
Regards,
Rob Philpott.
|
|
|
|
|
|
Search comments[^]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Good point. I must learn how to do this...
Regards,
Rob Philpott.
|
|
|
|
|
Hi EveryOne,
I have created 1 table in my DB named F-orgs containg only 1 field F_orgs. And it’s the primary key. Its Data Values are
F_orgs
F01
F02
F03
F04
and other table Item_Details having F_Orgs as the foreign key.
and One Item can be used by multiple F_Orgs.
Now the prob is that:
My table Item Details is not accepting multiple values under F__orgs field. It is only taking 1 value like
Item_No F_orgs
1414-3635 F01
1414-3636 F02
I want this
Item_No F_orgs
1414-3637 F01, F02
1414-3638 F02, F03
And it is but obvious while inserting it is checkn values with F_Orgs Table (Relationship Bw F_Orgs and Item_Details Table)
Rite. So is my logic going wrong somewhere.
Can anyone have any idea, where i m going wrong.
Plz tell.
Regards
|
|
|
|
|
I think your insert should be trying to get records like this:
1414-3637 F01
1414-3637 F02
1414-3638 F02
1414-3638 F03
When you put comma separated values into a field, the DB checks that the string as a whole matches a single entry in the F_Orgs table.
|
|
|
|
|
Hi there,
2 of my table fields is an int (winery ID), and the other is ntext (winery description). The size of the int is 4, and that of ntext is 16.
When passing the parameters in a stored procedure, I thought that using a size 4 or 16 would pass the whole number, or text, but surely this is not the case. When specifying a size of 16, it cuts off on the 16th character. So what I am going to do is use objWinery.Description.Length. But how do I then pass the size/length of the int?
I have a function that createds my parameter like:
AddParameterToSQLCmd(objCmd, "@Description", SqlDbType.NText, 16, ParameterDirection.Input, objWinery.Description);
I hope this makes sense.
Regards,
ma se
|
|
|
|
|
The length of the int will always be 4 (byte size in the DB).
The ntext data is stored separately from the rest of the table data, and does not take a length parameter. The length of 16 you see in the DB refers to the byte size of the reference the db saves to the external data.
Hope that made sense...
|
|
|
|
|
Yes it does, so int will always except as parameter?
|
|
|
|
|
|
Hello, i want to know some knowledge on connecting a database within vb.net! Please can someone help me!!
Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.
|
|
|
|
|
You might like to try using a search engine for tutorials on ADO.NET
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Step 1: Turn on computer
Step 2: Open Visual Studio
Step 3: Follow MSDN links
Pictures of the menu available at the drive-thru
|
|
|
|
|
Can any one make it syntactically correct 2 use inside c#
"SELECT Word,Category,Number,Gender,Person,Degree,ObjectType,Tense,Aspect,Mood,Comp1,Comp2,Comp3,Theta1,Theta2,Theta3 FROM HindiLex,Attributes,SubCategoryFrames,ThematicFrames where HindiLex.Word = N'"+word+"'" and HindiLex.AttrNo = "Attributes.AttrNo%" and HindiLex.LexSCFNo = SubCategoryFrames.LexSCFNo and HindiLex.ThemSCFNo = ThematicFrames.ThemSCFNo";
|
|
|
|
|
You can't write a query in C#, all you can do is pass the query from C# to your database. What database are you using ? You should read some of the articles on this site, I'm sure there's one for connecting to whatever DB you use.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Yes U R Right,
I wanna pass Query fm C#.
so for passing the query string syntax changes inside c#, is nt it...
i want that string syntax.
i am using SQL sERVER AS DB.
If possible pls modify to make it syntactically correct, thank U
|
|
|
|
|
cshivaprasad wrote: so for passing the query string syntax changes inside c#, is nt it...
Nope - C# is just calling SQL Server, so the syntax has to be what SQL Server expects. If you put a @ at the front of your string, as in
string s = @"this is the string";
then the only special character is to use "" for ". Otherwise, it's verbatim.
cshivaprasad wrote: If possible pls modify to make it syntactically correct, thank U
What is the reason for htis question ? Have you connected to the DB and had an error about syntax ? Where are you putting this SQL ? Are you creatings Connection objects and so on and connecting to SQL Server ? Have you tried your SQL inside SQL Server to make sure it's good there ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
This code is susceptible to SQL Injection Attacks. You inject a variable called word into the query string. You should use parameters instead. See SQL Injection Attacks and Tips on How to Prevent Them[^] it will give you information on how to call SQL from C#
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I want to create some database objects (tables, stored procedures etc) from a TSQL script. The script is stored in an ASCII txt file, and was generated using the MS SQL Manager's "Generate SQL script" option.
This is my problem : the script file contains formatting (line breaks, tabs etc), TSQL comments and so forth. If I simply load the contents of the script file into a string variable, and execute this from my C# code the way I would any ad-hoc SQL command, the ASCII-text formatting breaks the SQL. For example,
"SELECT *
FROM MyTable;"
now becomes
"SELECT *\r\nFROM MyTable;"
This is not valid SQL, and an exception is thrown. If I parse out all the ASCII formatting (and TSQL comments etc), I can execute a very large TSQL script file as an adhoc SQL command with no errors. This is great, except that my stored procedures now lose all their nice formatting and comments, making them difficult to read.
So ... is there some way to execute an ASCII-formatted TSQL script from C# code without having to strip all the ASCII formatting out?
Thanks in advance!
|
|
|
|
|
spazzman wrote: This is not valid SQL, and an exception is thrown.
I doubt it is that because I have done that and it works perfectly. The only thing you need to parse out of the script are the GO statements as these are instructions to Query Analyzer and not SQL. The GO is a batch delimiter - In other words execute the bits between the GO statements as separate SQL commands from the .NET application.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Eek. I can't believe I missed that  Thanks for pointing it out to me!
|
|
|
|
|
Hi,
My query looks like this
select distinct lh.id, od.storename
FROM LPNHeader lh
LEFT OUTER join LPNDetail ld on lh.Id = ld.LPNHdrId
LEFT OUTER JOIN OrderDetail od on ld.OrderDtlId = od.Id
INNER JOIN OrderShipmentLPN OSL ON LH.id = OSL.LPNHdrId
INNER JOIN ShipmentHeader sh on sh.id = osl.shipmentHdrId
WHERE sh.Id = 25
and the results are
346 SN1
347 SN1
347 SN2
348 SN2
348 SN3
349 NULL
I want to finetune this query to return distinct lh.id's, like given below
346 SN1
347 SN1
348 SN2
349 NULL
Tried distinct, group etc. I think that I am missing something, anybody any ideas. Thanks
|
|
|
|
|
Use a subquery to return the first storename for each id:
select distinct LPNHeader.id,<br />
Storename = (<br />
SELECT <br />
TOP 1 oh.storename <br />
FROM<br />
LPNDetail ld<br />
LEFT OUTER JOIN OrderDetail od on ld.OrderDtlId = od.Id<br />
INNER JOIN OrderShipmentLPN OSL ON LH.id = OSL.LPNHdrId<br />
INNER JOIN ShipmentHeader sh on sh.id = osl.shipmentHdrId<br />
WHERE <br />
LPNHeader.Id = ld.LPNHdrId<br />
AND sh.Id = 25<br />
)<br />
FROM<br />
LPNHeader
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
