|
If so; I think u have two tables, one has organisations list, others has events then assume two table is related to each other with OrganisationID.
1. Warn = use Just ONE DATASET, if not, it s painful.
============================================================================
Imports System.Data.SqlClient<br />
Public Class frmOrganisationsAndEvents<br />
Inherits System.Windows.Forms.Form<br />
<br />
' On Form There is two ComboBox named cmbOrganisations and cmbEvents<br />
<br />
Dim cnOE As New SqlConnection("")<br />
Dim dsOE As New DataSet<br />
Dim dRowOE() As DataRow<br />
Private Sub frmOrganisationsAndEvents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
Dim adpOE As New SqlDataAdapter("SELECT * FROM Organisations", cnOE)<br />
adpOE.Fill(dsOE, "Organisations")<br />
adpOE.SelectCommand.CommandText = "SELECT * FROM Events"<br />
adpOE.Fill(dsOE, "Events")<br />
For i As Short = 0 To dsOE.Tables("Organisations").Rows.Count - 1<br />
cmbOrganisations.Items.Add(dsOE.Tables("Organisations").Rows(i)("O_OrganisationName"))<br />
Next<br />
If Not cmbOrganisations.Items.Count = 0 Then<br />
cmbOrganisations.Text = "Select an Organisation"<br />
Else<br />
cmbOrganisations.Text = "No Organisation Installed"<br />
End If<br />
End Sub<br />
<br />
Private Sub cmbOrganisations_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbOrganisations.SelectedIndexChanged<br />
If Not cmbOrganisations.Items.Count = 0 And cmbOrganisations.SelectedIndex = -1 Then<br />
cmbEvents.Items.Clear()<br />
'Events SubSet <br />
drowOE = dsOE.Tables("Events").Select("E_OrganisationID=" & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationID"))<br />
If Not dRowOE.Length = 0 Then<br />
For i As Short = 0 To dRowOE.GetUpperBound(0)<br />
cmbEvents.Items.Add(dRowOE(i)("E_EventAlias"))<br />
Next<br />
cmbOrganisations.Text = "Select Event Sponsored by " & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationName")<br />
Else<br />
cmbOrganisations.Text = "No Event Installed Sponsored by " & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationName")<br />
End If<br />
End If<br />
End Sub<br />
<br />
Private Sub cmbEvents_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbEvents.SelectedIndexChanged<br />
If Not cmbEvents.SelectedIndex = -1 Then<br />
GetEventDetails(EventID:=dRowOE(cmbEvents.SelectedIndex)("E_EventID"))<br />
End If<br />
End Sub<br />
Protected Sub GetEventDetails(ByVal EventID As Integer)<br />
'Read Details From Database<br />
End Sub<br />
<br />
Private Sub cmbOrganisationsAndcmbEvents_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmbOrganisations.KeyPress, cmbEvents.KeyPress<br />
e.Handled = True<br />
End Sub<br />
End Class
|
|
|
|
|
Friends,
I have two tables A and B, the id is the primary key field of table A and is a foreign key of table B. There is one-to-many relation b/w the tables. It means that id can repeated in second table many times.
I am using inner join to join the tables on the basis on id. As such the result is that: for each id of table A, all matching id records of table B gets printed. So far, so simple and so good.
Now the problem is that in the result set, same id is repeated many times because there is one-to-many relationship b/w tables. What i want is that in result set each id should appear only once. How can i do so ???
Imtiaz
|
|
|
|
|
Imtiaz Murtaza wrote:
b/w
I'll assume this means "between". Please use only standard abbreviations - it helps make your post much more readable.
Imtiaz Murtaza wrote:
Now the problem is that in the result set, same id is repeated many times because there is one-to-many relationship b/w tables
That is correct. Performing an INNER JOIN is effectively creating a resultset in a denormalised form.
Imtiaz Murtaza wrote:
What i want is that in result set each id should appear only once. How can i do so ???
The question I have is: why? I cannot see how that is beneficial. Unless what you really want is the table on the many side aggregated into only one row.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
The question I have is: why? I cannot see how that is beneficial. Unless what you really want is the table on the many side aggregated into only one row.
I suspect that the OP's problem is that the mapping should be one-to-one, but that there's no UNIQUE constraint on one of the tables, and duplicate rows have been added.
You should always, IMO, apply constraints at every point to ensure that you have data integrity. It helps to prevent bugs. If you really have circular references, you might have an excuse for not using a constraint. Otherwise you always should. Performing partial initial updates is just lazy - you should work out on the client side what the complete relation is, then INSERT.
However, in the past I've had to do things like joining a master table onto a transaction log of changes and extract only the most recent change. Nasty. What I ended up doing was using a join to a group on the right-hand side of the join, something like:
SELECT a.id, a1, a2, a3, b1, b2, b3
FROM a
INNER JOIN
(
SELECT b.id, b.b1, b.b2, b.b3
FROM b
INNER JOIN
(
SELECT id, MAX(b3) AS maxb3
FROM b
GROUP BY id
) b2 ON b.id = b2.id AND b.b3 = b2.maxb3
) c
ON a.id = c.idMessy but it works. Except where you have identical values of b3.
The correct solution is to add a constraint using NOCHECK to stop further bad data being added, fix the bad data you already have, then alter the constraint to add WITH CHECK.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
|
Please First Write your SQL Sentence, i assume your problem is that u use * as asterix after SELECT command, write just field names u want to show.
|
|
|
|
|
Hi ,
Some times when i edit into SQL table a value , SQL server 2000
generate a message says
"Transaction Cannot start while in firehose mode"
can you till me what is firehose mode ??
and how can i stop that mode to make my changes free ?
thank you ..
|
|
|
|
|
|
Autonumber and Max+1 which is Best for long term
What are their advantages and disadvantages
please answer these question with solid reason
Tariq Mahmood
Software Engineer
|
|
|
|
|
if u need sequential ID serie , MAX+1 is the best. Because sometimes in rollbacked transactions, AutoNumber is generated but it is not used and ,in a new record, u may see number series like 1,2,3,4,6,7,8 (that means in 5th saving,something went wrong, and data wasnt saved to database but autonumber generated)
if u doesnt need sequential ID serie , AutoNumber is best. Because when u insert a new row to database,First u have to read Max number, then increase that number after checking that value is null either not.Sometimes to control this value is painful
|
|
|
|
|
If you're ever going to have more than one user acces your database, then you have to use AutoNumber. Max+1 could potentially give problems if two people (or processes) inserted into the datbase at the same time.
Database FAQ
|
|
|
|
|
 When I read the book "teach yourself SQL in 21 days", day 7, first example::
SELECT *
FROM TABLE1
WHERE TABLE1.SOMECOLUMN =
(SELECT SOMEOTHERCOLUMN
FROM TABLE2
WHERE SOMEOTHERCOLUMN = SOMEVALUE)
When I tried this subquery, it doesn't work in SQL-server 2000 at all ! After that I searched online, then found that I have to use "IN" instead of "=" to express the subquery.
I am confused ! Why the book writes like that? Is this an old SQL ? SQL is supposed to be universal, why I cannot use above example in SQL2000? I also found some other places which is not wworking in Sql-server 2000 environment.
Please help me on this silly question.
Thanks
Dennis
|
|
|
|
|
First Be Carefull,
u can use both of them but there is exceptions.
SELECT *
FROM TABLE1
WHERE TABLE1.SOMECOLUMN =
(SELECT SOMEOTHERCOLUMN
FROM TABLE2
WHERE SOMEOTHERCOLUMN = SOMEVALUE)
command is TRUE but "SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE" will be produce JUST ONE RESULT, NOT MORE. So to use any aggregation Function in this case is more true.
For Example;
USE NORTHWIND
select * from Orders where CustomerID =(select MAX(CustomerID) from Customers)
In this case, not just (=) equals sign, Also u cant use (=!,>,<,>=,<=,LIKE)
But some times we get more result from 2nd command.In this case u have to use IN keyword.
For Example, U need Orders information belongs to Customers in London.
if u execute "SELECT * FROM Customers WHERE City ='London'" u see 6 records. then write command for our first aim :
USE NORTHWIND
select * from Orders where CustomerID IN (select CustomerID from Customers WHERE City='London')
I hope u got what u need.
So u can get a result set that contains six different customers' Orders Informations.
|
|
|
|
|
in C#
I'm Beginner
Hi,
I created a function with Datareader object in DataAccessLayer side that I would like to pass to my Client Layer. What I can see that I cannot pass the DataReader itself because in a case of DataReader the connection must be open and close after. The goal is populated a combobox. How can I pass this to my clientLayer? Any idea for a beginner like me will be great.
Thanks,
Davy
|
|
|
|
|
Davy_Fraser wrote:
The goal is populated a combobox
If that`s your goal, why not just pass an array of data?
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi,
I wanted to be sure there is no specific way to do this.
As you said, passing array make sense.
Thanks for your help.
Davy
|
|
|
|
|
Hello to all who can help:
I am interested in importing an 83 million record file into ms sql 2000 using c#.net/ado.net and export it to a xml file.
Below is the query that I tried to use to compare two tables, CLIENT and MASTER, and remove rows in CLIENT of the Phone "column" that matches the MASTER table.
Then export CLIENT out again to a xml file.
Help!
----------------------------------------------------------
sqlConnection connclient = new SqlConnection("integrated security=SSPI;" +
"data source=(local);initial catalog=Client"); private void
butDelete_Click(object sender, System.EventArgs e) { try { sqlCommand sqlDelete
= new SqlCommand("DELETE FROM Client FROM master" + "WHERE master.Phone =
Client.Phone"); connclient.Open(); if (sqlDelete.ExecuteNonQuery() > 0) {
lblMsg.Text = "Record has been sucessfully deleted!"; RefreshList();
ClearText(); } else { lblMsg.Text = "Sorry, could not access database due to
the following error: " + ex.Message; } finally { connclient.Close(); } } }
Thank You very much...
|
|
|
|
|
Hi
I have a very very simple web application that does only one thing - tries to open a connection to SQL server.
On my PC it works fine. Connection is opened immediately!
But when I put that application on my host server I get this error "General network error. Check your network documentation."!!
The code is:
private void Page_Load(object sender, System.EventArgs e)
{
this.sqlConnection1.Close();
this.sqlConnection1.ConnectionString = "Data source=myServerIPAddress;Initial Catalog=dbName;User ID=user;Password=passwd";
try
{
this.sqlConnection1.Open();
}
catch(System.Data.SqlClient.SqlException ex)
{
this.Label1.Text = ex.Message;
}
finally
{
this.sqlConnection1.Close();
}
}
Doeas anyone know why is that so?
I tried tu turn pooling off, set bigger timeout but useless!
|
|
|
|
|
I've seen a similar thing when using Names Pipes if you don't have sufficient authentication to connect to the server. Are you using names pipes? If so, try changing to TCP/IP and see if the problem goes away. If it does, then its a security issue.
Database FAQ
|
|
|
|
|
The problem was that, that my host-server and sql server wad in one domain and they use different internal ip addresses, but I was given a different sql server address so I could reac it from outside the domain.
So I got the real ip and everything works fine!
Thanx
|
|
|
|
|
We have a VB6 application that uses an Access DB. One of our customers has reported that edits to the database are not there when they go back into the record. The structure is something like:
The function is to add reciepts to a customers booking.
Edit record (create reciept record)
Commit changes (ado commit to reciept recordset)
Call routine to update log in another database (log of changes to booking including receipts)
Print Crystal report with reciept details.
Close reciept form
refresh booking form with new balance.
Everything looks OK and the reciept prints off fine (data must be in tables correctly for Crystal report to pick it up). the problem is over the past week on 4 occasions the end of day reports which list cash taken are less than the actual cash taken (reciepts are missing from the DB). I have looked at one example and although i have the printed reciept there is no evidence of it in either the main DB or the log DB. The commit has been completly reversed out.
Any ideas?
Jon
|
|
|
|
|
The Jet database engine is not capable of recovering from bad writes. If data is missing, it's usually that some kind of hardware problem has occurred: power failure, disk errors, memory errors, etc.
I would check the file server holding the MDB file very thoroughly.
As I said, Jet is not capable of recovering from these errors. In the long term I would suggest moving to MSDE (SQL Server 2000 Desktop Engine) or, when released, SQL Server 2005 Express Edition which both have these capabilities.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
It is not just that the data is missing it is that it has been removed. It was in the tables (even if it was only for about 10 seconds) but must have dissapeared quite soon after that. Would the hardware problems cause that?
Yes,MSDE or SQL server would be better (especially given that some ofour new customers are quite large organisations) and i keep pushing that we look at migrating the code, but in this case it would be a big job. The app uses a mix of DAO and ADO and a lot of dynamic SQL, the boss just keeps saying 'maybe we'll look at it in the future', ie never.
Jon
|
|
|
|
|
I've added a dropdown list to a data grid and I've populated it with Dataset values.Its working fine.But its selected index is always 1. ie., the first list element is displayed in the dropdown by default. Can anyone pls tell me how to set the selected index of dropdown listdynamically while populating the data grid based on the value that is retrieved from the database?
Thanx a lot in advance, Sandhya
|
|
|
|
|
How to tell MS Access to allow zero lenght in the strings field using the CREATE TABLE command. Thanks in advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
