|
You could consider current your SQL staterment as a View, then you create another SQL staterment that joint with above view on Email field. Hope that could work
-- modified at 5:36 Monday 20th March, 2006
|
|
|
|
|
Hi,
Im not sure what you mean. Could you give me an example?
Thanks!
|
|
|
|
|
SELECT a.*, b.* FROM Contacts AS a, (SELECT Email, COUNT(Email) AS NumOccurrences FROM Contacts GROUP BY Email HAVING NumOccurrences > 1) AS b WHERE a.Email=b.Email
Also, I dont really understand what you mean. So hope that work
|
|
|
|
|
nguyenvhn,
I tried the code you gave me and got the following error:
Microsoft OLE DB Provider for SQL Server error '80040e14' <br />
<br />
Invalid column name 'NumOccurrences'. <br />
Ok let me explain....
I have a table which holds members contact / address book. I need to determine if they have duplicate entries for the same email in their contacts. So for example:
Dave, Smith, Dave@mywebsite.com
and
D, Smith, Dave@mywebsite.com
would be considered duplicate entries because they both have the same email address. Because each member has often several thousand contacts, their maybe 4 or 5 copies of a contact.
So i need to be able to display all these duplicate contacts on screen, and grouped together so that for example all the
Dave@mywebsite.com 's are all grouped to gether on screen and all other duplicates are on screen eg:
D, Smith, Dave@mywebsite.com
Dave, Smith, Dave@mywebsite.com
Dve, Smith, Dave@mywebsite.com
Paul, Wills, Paul@wills.com
P, W, Paul@wills.com
etc etc.
Hope this makes it clearer?
So what would be the code i need for this?
Thanks for the help so far!!!!!
|
|
|
|
|
I have created a table (named Contacts) with 3 columns and folowing data:
Email FirstName LastName
-------------------- -------------------- --------------------
Dave@mywebsite.com D Smith
Dave@mywebsite.com Dave Smith
Dave@mywebsite.com Dve Smith
Paul@wills.com Paul Wills
Paul@wills.com P W
vu.nguyen@erasvn.com V N
(6 row(s) affected)
Here is my SQL statement:
SELECT a.*, b.NumEmail FROM Contacts AS a, (SELECT Email, COUNT(Email) AS NumEmail FROM Contacts GROUP BY Email HAVING COUNT(Email)>1) AS b WHERE a.Email=b.Email ORDER By a.Email
And below is the result:
Email FirstName LastName NumEmail
-------------------- -------------------- -------------------- -----------
Dave@mywebsite.com D Smith 3
Dave@mywebsite.com Dave Smith 3
Dave@mywebsite.com Dve Smith 3
Paul@wills.com Paul Wills 2
Paul@wills.com P W 2
(5 row(s) affected)
Is that your needed?
|
|
|
|
|
Hi nguyenvhn,
Thats excellent mate! Thanks so much for the help.
However i realise now that i forgot to mention that i need to perform the above statement in relation to specific users contacts.
In other words i need to show the results only working from a specific users own address book. So how do i make it search by the AccountHolderID ?
I tried this:
SELECT a.*, b.NumEmail FROM Contacts AS a, (SELECT Email, COUNT(Email) AS NumEmail FROM Contacts GROUP BY Email HAVING COUNT(Email)>1) AS b WHERE a.Email=b.Email AND a.AccountHolderID = '2 & accountID & "' ORDER By a.Email
But the data it returned did not match up prorperly. For example it would show a contact just once but say it had 6 matches. So it appears that it was probably doing the email match count on ALL records in the db but then only showing those registered to the current user????
HOpe you understand what i mean!!!
So i obviously need to place:
a.AccountHolderID =
somewhere else in the statement? I dont know...so if you could help me out id be even more greatful than i am now!
Thanks again!!!!
-- modified at 6:36 Wednesday 22nd March, 2006
I think i got it:
SELECT a.*, b.NumEmail FROM Contacts AS a, (SELECT Email, COUNT(Email) AS NumEmail FROM Contacts WHERE AccountHolderID = " & makeSQL(session("SAT_USERID")) & " GROUP BY Email HAVING COUNT(Email)>1) AS b WHERE a.Email=b.Email AND AccountHolderID = " & makeSQL(session("SAT_USERID")) & " ORDER By a.Email
This seems to work as i expected. If their is a more efficient way, then let me know. If not, thanks for your help!!!!
|
|
|
|
|
Hello Members,
I am facing problem in updating the dataset (Database). I have tried number of things(all may b wrong). Can u please help me. This is the code
Dim bm As BindingManagerBase<br />
Dim currentrow As Integer<br />
Dim ds As New DataSet()<br />
Dim bval As Boolean<br />
Dim dt As DataTable<br />
Dim dr As DataRow<br />
Dim cn As SqlConnection = New SqlClient.SqlConnection("Data Source = ; Database = Pubs;uid=sa")<br />
Dim da As SqlDataAdapter = New SqlClient.SqlDataAdapter("Select * from publishers", cn)<br />
Dim flag As Integer<br />
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
<br />
da.Fill(ds, "publishers")<br />
bm = Me.BindingContext(ds.Tables("Publishers"))<br />
bm.Position = 0<br />
Loaddataincontrols()<br />
Setbuttons(True)<br />
LockTbox()<br />
End Sub<br />
<br />
Public Sub Loaddataincontrols()<br />
TextBox1.Text = ds.Tables("Publishers").Rows(bm.Position)("Pub_id").ToString<br />
TextBox2.Text = ds.Tables("Publishers").Rows(bm.Position)("Pub_name").ToString<br />
TextBox3.Text = ds.Tables("Publishers").Rows(bm.Position)("city").ToString<br />
TextBox4.Text = ds.Tables("publishers").Rows(bm.Position)("State").ToString<br />
TextBox5.Text = ds.Tables("Publishers").Rows(bm.Position)("Country").ToString<br />
End Sub<br />
<br />
Private Sub CmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdAdd.Click<br />
'dr = (ds.Tables("Publishers").NewRow)<br />
Setbuttons(False)<br />
UnLockTbox()<br />
ClearControls()<br />
flag = 1<br />
'LoaddatainDataset()<br />
'ds.Tables("Publishers").Rows.Add(dr)<br />
End Sub<br />
Public Sub UnLockTbox()<br />
TextBox1.ReadOnly = False<br />
TextBox2.ReadOnly = False<br />
TextBox3.ReadOnly = False<br />
TextBox4.ReadOnly = False<br />
TextBox5.ReadOnly = False<br />
End Sub<br />
Public Sub ClearControls()<br />
TextBox1.Text = ""<br />
TextBox2.Text = ""<br />
TextBox3.Text = ""<br />
TextBox4.Text = ""<br />
TextBox5.Text = ""<br />
End Sub<br />
Public Sub LoaddatainDataset()<br />
dr("Pub_id") = TextBox1.Text<br />
dr("Pub_name") = TextBox2.Text<br />
dr("City") = TextBox3.Text<br />
dr("State") = TextBox4.Text<br />
dr("Country") = TextBox5.Text<br />
End Sub<br />
<br />
Private Sub CmdModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdModify.Click<br />
UnLockTbox()<br />
flag = 3<br />
Setbuttons(False)<br />
' dr = ds.Tables("Publishers").Rows(bm.Position)<br />
' dr.BeginEdit()<br />
' LoaddatainDataset()<br />
' dr.EndEdit()<br />
'Setbuttons(False)<br />
'da.Update(ds, "Publishers")<br />
'da.Fill(ds)<br />
'bm.Position = 0<br />
End Sub<br />
<br />
Private Sub cmdRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemove.Click<br />
' dr = ds.Tables("Publishers").Rows(bm.Position)<br />
' ds.Tables("Publishers").Rows.Remove(dr)<br />
dt = ds.Tables("Publishers")<br />
dr = dt.Rows.Find(TextBox1.Text)<br />
flag = 2<br />
End Sub<br />
<br />
Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click<br />
Dim da As SqlDataAdapter = New SqlClient.SqlDataAdapter("Select * from publishers", cn)<br />
If flag = 1 Then<br />
dt = ds.Tables("Publishers")<br />
dr = dt.NewRow()<br />
LoaddatainDataset()<br />
dt.Rows.Add(dr)<br />
ElseIf flag = 2 Then<br />
dr.Delete()<br />
ElseIf flag = 3 Then<br />
dt = ds.Tables("Publishers")<br />
dr = dt.Rows.Find(TextBox1.Text)<br />
dr.BeginEdit()<br />
LoaddatainDataset()<br />
dr.EndEdit()<br />
End If<br />
flag = 0<br />
CreateCommandAndUpdate("Data Source = ; Database = Pubs;uid=sa", "Select * from Publishers")<br />
da.Fill(ds)<br />
<br />
<br />
' LoaddatainDataset()<br />
' da.Update(ds, "Publishers")<br />
' da.Fill(ds)<br />
bm.Position = 0<br />
Loaddataincontrols()<br />
Setbuttons(True)<br />
End Sub<br />
<br />
Public Function CreateCommandAndUpdate( _<br />
ByVal connectionString As String, _<br />
ByVal queryString As String) As DataSet<br />
da.SelectCommand = New SqlCommand(queryString, cn)<br />
<br />
Dim commandBuilder As SqlCommandBuilder = _<br />
New SqlCommandBuilder(da)<br />
<br />
<br />
<br />
' Code to modify the data in the DataSet here. <br />
<br />
' Without the OleDbCommandBuilder this line would fail.<br />
da.Update(ds)<br />
da.Fill(ds)<br />
'End Using<br />
CreateCommandAndUpdate = ds<br />
End Function
It gave me these errors
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.
An unhandled exception of type 'System.Data.MissingPrimaryKeyException' occurred in system.data.dll
Additional information: Table doesn't have a primary key.
Please Help....
Regards,
Amit
|
|
|
|
|
Hello Everybody,
I was searching net on data concurrency, when I came across this Article, where the user says we can use a webservice and create a component which can send a message to the user informing him that the message he is trying to edit is already in use by other user and i personally belive tht after the editing is completed by user1, user2 should again get a message stating that the user1 has completed editing record, you can now edit the record.... is it possible.. if yes then how???? can someone give me the code for it.
http://weblogs.asp.net/fbouma/archive/2003/05/24/7499.aspx[^]
I am pasting the last para of the article for your kind reference
So, every time you write code to pop up a window to notify the user he can't save his work because another user has already modified the rows he tries to overwrite, ask yourself this: "wouldn't it be better to notify the user before he started working on that rows?".
Thanking you and waiting for some kind replies.
Amit
|
|
|
|
|
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Hi to all, 
please help me i am finding commands to Backup and restore sql database which i will select , from my .Net project directly.
anant
-- modified at 3:03 Sunday 19th March, 2006
|
|
|
|
|
Hi, the only SQL Command I know is this one:
The SELECT INTO Statement
The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.
Syntax
SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source
Check the w3school website for complete documentation:
http://www.w3schools.com/sql/sql_select_into.asp
With Kind Regards
|
|
|
|
|
backup database database_name to disk='path'
Beemen Beshara
-- modified at 10:50 Sunday 19th March, 2006
|
|
|
|
|
hi Beemen,
Thanks for helping me, But you didn't give the restore command.
I tried the Backup command and it worked, COOL man  .
My the faculties never teach me these kinds commands.
THANKs
anant
|
|
|
|
|
Hi Amit,
You should you DataSet for First,Back,Next,Last.
How to use?
1. Use DataAdapter to Fill DataSet and then it will be easy. But make a Separate Function like RecordJumper(Num as internet) as Integer.
where num is to Move record Up/Down.
2. Like wise for Last ---------- RecordJumper(0),
for back ---------- RecordJumper(i-1)
for next ---------- RecordJumper(i+1)
for last ---------- RecordJumper(record.RecordCount -1)
And use DataAdapter with DataSet to Add,Remove,Modify,Update.
Note: Use select commands for Add,Remove,Modify,Update.
I think this will help you.
anant
|
|
|
|
|
Hi,
Is there a command to only get row 450-500 out of a 1000 row result?
nixter
|
|
|
|
|
i think there is no direct way for that. But you can use subtract query. And if you get any direct way then also tell me.
|
|
|
|
|
Excuse my lack of knowlege here, I am primarily a MySql user. Importing data in MySQL is a piece of cake in through mysql.exe using the following syntax:
<br />
LOAD DATA LOCAL INFILE 'filename.csv'<br />
INTO TABLE myTable<br />
FIELDS TERMINATED BY ','<br />
LINES TERMINATED BY '\n';<br />
But I now have to switch over to SQL Server. Is there an equivalent SQL Conversational interface like mysql.exe through which I can enter SQL commands?
If not, what is the recommended method for importing files?
Mike Luster
CTI/IVR/Telephony SME
|
|
|
|
|
There are several interfaces to enter SQL commands for SQL Server: Query Analyzer (a GUI application), isql.exe and osql.exe, plus sqlcmd.exe in SQL Server 2005.
For performing a bulk file import, you would normally use either bcp.exe (stands for Bulk CoPy) or the BULK INSERT statement.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
You need the MS SQL server enterprise manager there you will find a tool just to import and export data for just about anything.
Hear,See,Learn,Understand,Practice <hslup>
Many can be good,but only ONE can be the best keep practicing
|
|
|
|
|
Thanks guys, I used Enterprise Manager and it was pretty simple for importing 37K rows of CSV data. Getting data into your database is easy BUT... when I create a Table Definition importing to that seems to need some extra work. I get the impression I'll have to write a Stored Procedure in TSQL to convert the data into the desired custom Table Format. Recommendations welcome.
Mike Luster
CTI/IVR/Telephony SME
|
|
|
|
|
I want to display a simple Crystal Report (already, I think this is an oxymoron) in a Windows Form using a dataset (I'd prefer to do all the database work in my code, rather than letting CR do it).
All of my C# code is using Oracle.DataAccess.Client, but when I tried to create a new DataSet, VS said I had to use the Microsoft Oracle object. I did, created a DataSet with one table, EMPLOYEE. I then created my report that lists four items from the table -- no grouping, no counting, just a list.
Then, I created a Windows Form that gathers the required parameters for my query, and using those parameters, build the SQL. I use the SQL to fill the EMPLOYEE table in the DataSet created earlier. If I look, the table has 700+ rows after filling.
Then, I create a report object, set the datasource to the filled dataset, and set the Crystal Reports Viewer's ReportSource to the report. Seems straightforward, no errors. However, when I try to display the new form (that contains the crystalReportViewer), I am presented with a logon dialog box, asking for server, database, username, password. Even if I supply all of these items, it fails to logon. The form containing the viewer is shown, but there is no report.
I tried filling the dataset with the MS Oracle connection/command/dataadapter, but it made no difference. I also tried setting the username/password for each table in the report after the report object was created, but no luck either.
I read http://support.businessobjects.com/forums/message.asp?fid=251&sk=5&ps=25&pn=1&mid=152400#m152400, and a couple of other articles as well (http://www.codeproject.com/aspnet/crystal_report.asp#xx852522xx, http://aspalliance.com/265, and http://www.dotnetjunkies.com/Article/790775A0-C493-46D8-ABE0-40CA588D33D3.dcik).
Any thoughts on how to make this work, and/or a better place to ask?
DatasetReportPositiveEmployees ds = new DatasetReportPositiveEmployees();
string sSQL = "SELECT DISTINCT empID, empFName, empLName, empAcctNum from Employee " +
"LEFT INNER JOIN TestResult ON empID = testEmployeeID " +
"WHERE (testResult IN (SELECT resultID FROM Result WHERE resultTreatAsPositive=1))";
System.Data.OracleClient.OracleConnection localConn = new System.Data.OracleClient.OracleConnection(settings.ConnectionString);
localConn.Open();
System.Data.OracleClient.OracleCommand localCmd = new System.Data.OracleClient.OracleCommand();
localCmd.Connection = localConn;
localCmd.CommandText = sSQL;
localCmd.CommandType = CommandType.Text;
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter();
adapter.SelectCommand = localCmd;
adapter.Fill(ds, "EMPLOYEE");
CrystalReportPositiveEmployees report = new CrystalReportPositiveEmployees();
Tried with/without this section
CrystalDecisions.Shared.TableLogOnInfo login = new CrystalDecisions.Shared.TableLogOnInfo();
foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in report.Database.Tables)
{
login = tbl.LogOnInfo;
login.ConnectionInfo.ServerName = "myComputer";
login.ConnectionInfo.UserID = "myUser";
login.ConnectionInfo.Password = "myPass";
tbl.ApplyLogOnInfo(login);
}
report.SetDataSource(ds);
formReportViewer dlg = new formReportViewer();
dlg.crystalReportViewer1.ReportSource = report;
dlg.ShowDialog();
Thanks,
Glenn
|
|
|
|
|
Hi to all
I have MSSQL database with stored BMP binary image data.
I need to convert BLOB field to JPG on fly. My web server is based
on windows and probably I need ASP script to do that. I am new in ASP and ADO. Can anybody navigate me or recommend the source?
Thanks for all ideas.
|
|
|
|
|
There is a new version of Programming Microsoft ADO.NET 2.0 Core Reference" by David Sceppa coming out in July 2006 that looks like it will be a smart book to buy.
In the mean-time, what is the best one-stop shopping/comprehensive online source for ADO.NET 2.0 information?
|
|
|
|
|
ADO.NET[^] on MSDN.
Google[^] results
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi
I want to access a .csv file on a network drive.
I am using the following code:
string filename = "csvsample.csv";<br />
<br />
string cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y:/csv/;Extended Properties='text;HDR=Yes;FMT=Delimited'";<br />
<br />
OleDbConnection objOleDBConn = new OleDbConnection(cs);<br />
objOleDBConn.Open();<br />
sqlSelect = "select * from ["+ filename+"]"; <br />
OleDbDataAdapter objOleDBDa = new OleDbDataAdapter(sqlSelect,objOleDBConn);
However, I am getting "'Y:/csv/' is not a valid path. " error.
Y is a network drive AND the file does exist on Y:/csv/ folder.
Can anyone help me, please.
Thanks
|
|
|
|
|
Maybe it should be Y:\csv\ not Y:/csv/
Jonathan
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
