|
cool I'll try it. Just gotta write my XP and find out how to return a value. I'm going to assume that, that is the same as SP's too?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
|
Hi everybody, I'm building my very first database application.
I have an Access database and I work with ADO with C# + Jet 4.0. I have 3 tables in my database.
For now, my dataAdapter is constructed like this, to work with only one table:
myDataAdapter = new OleDbDataAdapter("Select * From Table1", myOleDbConnection);
But now, I want my DataAdapter to work with my 3 tables... what should be my connection string?
"Select * From Table1, Table2, Table3" ??? Probably not...
Thanks!!!
|
|
|
|
|
Your connection string won't change. A connection string is per database, not per table.
In order to return info from more than one table, you will need to use a JOIN.
Here's a SQL Query I am using for a product of mine - also MS Access:
SELECT Staff.FirstName, Staff.LastName, ClassSchedule.ClassName, ClassSchedule.Duration, Staff.ID
FROM (StaffClassSignIns INNER JOIN Staff ON StaffClassSignIns.StaffID = Staff.ID) INNER JOIN ClassSchedule ON StaffClassSignIns.ClassScheduleID = ClassSchedule.ID
WHERE (((StaffClassSignIns.ClassScheduleID)=[@ClassSchedule]));
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus
|
|
|
|
|
Can any one tell me how to configure FTP to save sanpshot.
I want to store snapshot files to be stored at the FTP site(which is m/c oth
er than the distributor/publisher)
Plz tell me detailed procedure of how to do this.
Thanks n Regards,
Ruchir
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
FTP has very little capability built into it. You won't be able to execute anything other than a file transfer or directory listing using it. You'll need to write a proc on the server to save a snapshot out of the database to a file. Then you'll need to write a set of code that does a put to the FTP site.
If you are looking to code part of this in .net you can look into the FTP library from edt
http://www.enterprisedt.com/downloads/csftp/csftp.html[^]
Hope that at least points you in the right direction.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
I've got a problem. When adding an index to a table using either the Index manager or index tuning wizard the application hangs and no index is ever created. The table I'm trying to add the index too is in high demand so I figured it was some kind of "leave us alone - we're busy" kinda thing, but still, it's frustrating.
Anyone have experience or suggestions about this?
cheers,
Chris Maunder
|
|
|
|
|
I think you'll have to fall back on Query Analyzer and CREATE INDEX.
Alternatively you could ALTER DATABASE cp SET SINGLE_USER WITH ROLLBACK AFTER 60 but you will obviously get an outage!
The index build process has to inspect every row in the table on which it's defined, so it's going to take a while anyway. If you're trying to build a clustered index it will have to restructure the entire table, so be prepared for this to take a very long time.
If your tempdb is on a separate spindle, you may get better performance by using the SORT_IN_TEMPDB option. Without this option it sorts in the actual pages to be used for the index.
I admit that most of this information came from my copy of Inside SQL Server 2000!
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike Dimmick wrote:
I think you'll have to fall back on Query Analyzer and CREATE INDEX
Tried that one. It's a small (8000 row) table, non-clustered. Hmmm.
cheers,
Chris Maunder
|
|
|
|
|
We've had some periodic problems at one of our customers' sites where apparently simple queries will appear to block forever, and we've not worked out why. Our solution to that is just to run sp_updatestats. I'm not sure why it works but it seems to.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hmm - worth a shot. Thanks Mike.
cheers,
Chris Maunder
|
|
|
|
|
For a Windows form using VB.NET and an Access database of community events.
I have a DataAdaptor and DataSet that create a datatable with a row for each event. I also have a DataAdaptor and DataSet that create a datatable for sponsoring organizations.
I would like to have a combo box to choose the organization (bound to the organization table) and then populate the event combo box with a list of events sponsored by that organization. The user would then choose the event and the rest of the form controls would fill with the information for that event.
How would I create a subset of events based on the sponsoring organization? Do I create another DataAdaptor and DataSet through code to create an event table based on the organization chosen?
The books I have describe how to create DataAdaptor and DataSets through code, but once created, is there a way to destroy them and create others based on choosing another sponsoring organization?
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
