|
Hi all:
I'm pretty new at db programming, but over the past year have a come to a decent understanding of connections, data adapters, and datasets. Here's something though that I just can't think through.
Here's what I'd like to do.
1. My music collection and audio file information are in a MSDE database with several tables containing workid (PK), artist information, style, filename (location of the sound file), etc.
2. I created XML file to use as a playlist, something like
<playlist>
<workid>2</workid>
<workid>15</workid>
<workid>4</workid>
<workid>47</workid>
</playlist>
3. I saved the file something like saturdaynightmusic.xml
4. Using the Build Schema tool in VS Studio, I created a schema that joins the Music table with the XML playlist with workid as the key.
5. Now I have a .xsd file which contains the relationship, an .xml file to control the order of play, and a database table with fields pointing to the audio files, album and artist info.
However, I can't for the life of me, figure out how to make them work together.
Conceptually, I've got it - Read each element in the XML file, put them into a list (such as a list box), select first song in the list, it populates the fields with the artist info, title, then plays, the next song is selected, played .... until the end.
Here's how I think it could be done...
1. The XML playlist workid is stored as a variable.
2. The variable is added to a select command which is then run against a view or stored procedure
3. The database returns the information which is bound to textboxes, lists, and the filename variable to play the audio file.
4. Loop through the above steps until the end of the list
Am I on the right track???
Thanks for any help in clarifying this for me.
Norm
-- modified at 17:58 Tuesday 15th November, 2005
|
|
|
|
|
Hi,
I have two objects, both point to each other in OO.
e.g.
Class Father
{
private int id; //id of father
private Son son;
}
Class Son
{
private int id; //id of Son
private Father father;
}
so I need to have two tables in database as follow,
Table Father :
(id, SonId)
Table Son :
(id, FatherId)
The problem is, should I eliminate the one of the reference in one of the table, e.g.
Table Father :
(id)
Table Son :
(id, FatherId)
so I just have the fatherID in the son table.
But if I remove the sonID from the father table,
I need to join the two tables when I retrieve fathers
from the father table.
So what are the pros and cons of the two schema design ?
Thanks
|
|
|
|
|
Analyze a bit...
Without going into much trouble.. the relationship between father and son tables is as follows: A Father can have 1 or more sons, but a Son can only have a father at any one time. If you follow this, then only having idFather in the son table it's more than enough to make the searches you need.
Anything else you need to add?
daniero
|
|
|
|
|
daniero wrote: Father can have 1 or more sons
i would say so, but looking at his class definitions, a father have only one child 
ppp001 wrote :
Class Father {
private int id;
private Son son; <-- see what i mean
}
Class Son {
private int id;
private Father father;
}
maybe a modeling mistake, but he wrote it !
TOXCCT >>> GEII power
[toxcct][VisualCalc]
-- modified at 11:29 Wednesday 16th November, 2005
|
|
|
|
|
i would like to know how that i can do something like this:
this is my query: select a.col2, a.col3, b.col2 from tableA a
INNER JOIN tableB
On a.col1 = b.col1
now if i use a fill from a data adapter i get a dataset with one table with my results.
what i want is a dataset: with 2 tables one with the nfo from table a, and one with the info from table b
how can i do this? or isn't this possible?
|
|
|
|
|
Yes, query tablea, and fill a 'tableA' in a dataset, and then query tableb, and fill a 'tableB' in the dataset, you can have a preset dataset with the relationship or generate it in code.
daniero
|
|
|
|
|
how do you mean a preset dataset with relationship?
the thing i want to avoid is: query table a and fill it in the dataset, than query table b and fill the same dataset, and then put the relationships on it.
I want to do this with one query to the db, is this possible? to execute your query, so your adapter will fill your dataset with different tables and relationships.
|
|
|
|
|
Unless someone else has a better Idea, that's the way I would do it.
By preset I mean an xsd file representing a dataset (I think it's called typed dataset).
I don't think you can have 2 tables out of one query, unless you make the query as you did, and manually adding the rows in the datatables... but that's even worse :P.
daniero
-- modified at 18:21 Tuesday 15th November, 2005
|
|
|
|
|
Hi.
I'm running SQL Server 2000.
I have a little problem, and I do not know how to solve it. I have a News tables, a Category table, and a Publisher table. When inserting a new news article, the user can select the news category and the news publisher from drop downlists. A category is required, but not a publisher. So if no publisher is selected, then I make this foreign key in the News table NULL.
My problem comes in when I want to retrieve a news article that has a publisher ID of NULL. The reason for this is because it goes and looks for an ID of NULL in the publishers table and finds none. But what I actually want is for it to bring back the complete news article, and make put and empty string in publisher name, and 0 in publisher ID, or something like this. My SQL statement in my stored procedure looks like this:
SELECT
PKNewsID,
FKNewsCategoryID,
LinkTitle,
NewsTitle,
NewsBodyCopy,
NewsDatePublished,
Publish,
FKNewsPublisherID,
NewsPublisher,
NewsCategory
FROM News
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
INNER JOIN NewsCategory ON News.FKNewsCategoryID = NewsCategory.PKNewsCategoryID
WHERE PKNewsID = @NewsID;
Please can someone help me with this.
Regards,
ma se 
|
|
|
|
|
Then an INNER JOIN isn't going to work as it requires that all rows exist.
Change this line:
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID to
LEFT OUTER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
You will get nulls in the columns that came from NewsPublisher when there is no join.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
how do i connect to a database and pick elements from it at random to fill and array..
first i need to know how to connect to it in the first place and be able to read from it.. please your quick help is needed..
thanks in advance
Nab
|
|
|
|
|
This thing is very strange.I have been using the following connection string all these days
data source=server;initial catalog=NIA;password=tpassword;persist security info=True;user id=sa
But now all of my .net application is not getting connected to sqlserver
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
NewNia.WebForm2.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewNia\WebForm2.aspx.vb:40
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Though i can acces my server through query analyzer.Please help
|
|
|
|
|
since your datasource is "server" I assume it's a different machine?
if that's the case, have you recently installed SP2 or SP1 (in Server 2003)?
They change the security model and as a result you won't be able to connect to sql server over a network connection unless you tweak some settings. Search for SP2 in Microsoft and then SQL Server and you will find a work around.
If it's not SP 2, then there is some problem with your connection, or SQL server is not running, or the database NIA is not started, there are a number of reasons why this can happen.
|
|
|
|
|
Hello All,
Whenever I try to execute a Query in Analyzer, I get the below error.
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
I went Microsoft support site where they have mentioned that the above issue is fixed in service pack 4. Even after downloading the service pack 4, I receive the same error. I have been held up since a week because of this issue.
Please someone get me out of this situation.
For your information, I am using SQL Server 2000 with SP4.
Thanks a lot in advance
Looney
|
|
|
|
|
i want to know the differences betwween ado and ado.net
|
|
|
|
|
i think (but i'm not sure) that ADO.NET is designed for .net languages such as C#, VB.NET, MC++, and so...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
hi guys..
can anyone tell me what might be the error at the following code ?
public void getCounterValue(string countername,string tablename,string email)
{
SqlConnection conn = null;
SqlDataReader reader = null;
int result = 0;
try
{
conn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis");
conn.Open();
SqlCommand cmd = new SqlCommand("select @ctr from @table where @mail = @mail", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@ctr";
param.Value = countername;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@table";
param2.Value = tablename;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@mail";
param3.Value = email;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);
reader = cmd.ExecuteReader();
while(reader.Read())
{
result = Convert.ToInt32(reader[0]);
}
}
catch(Exception x)
{
MessageBox.Show(x.ToString());
}
finally
{
reader.Close();
conn.Close();
}
}
it throws the exception @table doesn`t have a value,
but it has the value tablename from the method construction..
any thougts ?
thx !
good coding !
|
|
|
|
|
You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
How can you pass the table name to a stored procedure?
|
|
|
|
|
TheJudeDude wrote: How can you pass the table name to a stored procedure?
Previously Colin Angus Mackay wrote: You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Gotcha...use command text w/ defined function with the name passed as a parameter.
Could you answer ( I sure you can)..will you answer this??
I am storing a variable to smalldatetime in a table. The parameter is defined as:
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString();
When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Thanx in advance!
|
|
|
|
|
TheJudeDude wrote: When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Because it must. Actually the order of the elements (month, day, hours, minutes, etc.) are not important, that is just the user interface. Internally it is stored the same way regardless of the culture.
The type is designed for storing a date with a time to a resolution of one minute. Therefore the date part must be filled with something. If I remember correctly it will be the 1st January 1900.
There is no specific time only type in SQL Server, so you will need to work around the addition of the date.
Finally, you may find that posting a new thread when you have a new question will be seen by more people and you will get a quicker response if I'm not around. Posting on a thread that started last November will only be seen by the person whose post you responded to (assuming they have email notification turned on). Hopefully you'll find that helpful in getting a good response sooner.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanx for the info! If you get this, I have another delima 
I am storing a decimal value in the table that is defined as a decimal, but it is only storing the integer part. In the debugger I would have something like 8.234534, but it stores 8.The definition for the column is decimal 5 allow nulls pricision 4.
The code to store the value is:
cmdSetHours.CommandText = "UPDATE " + strName + " SET Hours = " + decTotalHours +
" WHERE EmpID = " + m_intEmployeeID;
Thanx again!
|
|
|
|
|
I have a table which has 1 Cr records .Now i have a query which has joins on 3 tables includung the one that has the large number of records.The select fields remain the same but the fields in where condition changes as per user selection.I have created a view when there is no where condition and this works fast.But now what do i do when there is a where condition.This query takes 9 min.I have already applied indexes
|
|
|
|
|
Hello,
CREATE PROCEDURE dbo.spFindProduct
@searchText varchar(50)
AS
SELECT * FROM Product
WHERE productName LIKE '@searchText%'
GO
When the user enters a letter in the textbox the stored procedure will find all the words that begin with that letter. When they type a second letter the stored procedure will find all the words that begin with those 2 letters etc. All rows that are matched are displayed in datagrid. This is the same as one of those applications that find the closest word for what you are looking for.
My problem is that the above stored procedure does not return anything, even if the right letter has been entered.
The parameter is being passed correctly: WHERE productName = @searchText
That works and fine if l am looking for the exact word.
WHERE productName = 'F%' that works ok and finds all the rows where product name begins with a F.
But the above stored procedure does not work. Can anyone tell me how to correc this.
Many thanks in advance,
Steve
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
