|
Can someone tell me whether or not the below SQL will do what I want it too...
I am trying to pull a listing of categories with the number of articles under each in a single query instead of using two queries and a loop. Does the below SQL seem coorect for doing this? Is this evenb possible?
Also it's important to note, i'm not interested in returning anything from the article list table, just the details from the categorey are required, one from each categorey NOT one for each categorey for each article returned as well... 
$sql = '<code>SELECT</code> cat_list.pkid,
cat_list.parentid,
cat_list.name,
cat_list.img_url,
cat_list.content,
<code>COUNT</code>(class_articles.pkid) <code>AS </code>article_cnt
<code>FROM </code>class_articles, cat_list
<code>WHERE </code>class_articles.parentid=cat_list.pkid
<code>AND</code> cat_list.pkid='.$parentid;
Regards,
Alex
It's frustrating being a genius and living the life of a moron!!!
|
|
|
|
|
First, you don't need that join because it is redundand. You could quite easily put WHERE class_articles.parentid=...
Second, COUNT is an aggregator and requires that the other columns be placed in a GROUP BY clause at the end of the statement so that it knows what to count. i.e. GROUP BY pkid, parentid, name, img_url, content
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat (@@language = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-and rebuild and after that re populate the catalog
and it still drop me an ERROR !!! how can i resolve this prob ???
tnx tnx .
|
|
|
|
|
removing ignored words with full-text catalog
|
|
|
|
|
hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat ('@@language' = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-and rebuild and after that re populate the catalog
and it still drop me an ERROR !!! how can i resolve this prob ???
tnx tnx .
|
|
|
|
|
Hi all
I have an empty database in access. I want to create the tables using SQL statements as that easier for me.
How can I do that with MS Access? remember tha db is empty
Thanks
|
|
|
|
|
You know, this is really easy to find in Microsoft Access' online help.
Type "How do I create table in SQL" in to the answer wizard (That's the annoying paper clip thing)
From the selection select "Create an SQL-specific query" (it was the first choice for me). From the page that is shown, expand the section marked "Create a data-definition query to create, delete, or alter tables, or to create indexes in a database". That should answer your question.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
My project is in initial phase.i m doing project on SPATIAL MESSAGING. The system is for rescue purposes mainly.Each user will b equiped with a PDA ( which v ppl havnt arranged yet) n can send msg to a main server via GPRS.Users will b connected throu website(our design is like this...u can suggest somthing else)ideally this system is to b designed wid PDA users, but no PDA here...cant afford...so we ourselves are just attaching GPS n GPRS modules with LCD-developed a microcontroller based Hardware.)That device(v’ll develop) shud only b able to send n receive some text msges.Some database will store msgs with time n coordinates.Do we need full os for this?Shuld we embeded linux or ne embedded window therer....wt to use...how....umm...a website(wap site) is to designed n a database...with dynamic entries....wt i hav studied is web services 'll be used...website is connected to a database in SQL. Every time a user gets connected to website...is entered automatically in database...database will check status of user after some time n if user no longer exists...remove entry automatically....As far as i hav studied i 'll need vb.net n web services for website n sql n ado.net for database....m i right?
Will u plz guide me k how it wid b done...n wat shuld i do n study n in wat order
v doing work in a group n i m assigned the job of server, databse, webservices....i m almost new to all this n now a days studying on these.Ur advice n help ll b welcomed by me n i wud b much thankful to u...n if possible 4 u, kindly refer some other person if available n if possible who can help me out n is willing to do so
Regards
|
|
|
|
|
virgo* wrote:
me n i wud b much thankful to u...n if possible 4 u,
Man, this crap is annoying to read. how old are you, 8 ?
virgo* wrote:
....As far as i hav studied i 'll need vb.net n web services for website
No. You can use vb.NET or C# ( it sounds like VB.NET is more your style tho ) to write a website, which requires ASP.NET. It sounds like you don't necessarily want a website ( something you can connect to via a browser ), and for the PDA connection, you will need a webservice ( again, C# or VB.NET, and so long as we're naming technologies, XML ).
virgo* wrote:
sql n ado.net for database....
First, you need a database, probably SQL Server 2000. Then you will use SQL to write the database, and ADO.NET isjust a library in VB.NET or C#, nothing special you need to source. If you look at data access examples in either language, you'll be looking at ADO.NET.
virgo* wrote:
Some database will store msgs with time n coordinates.Do we need full os for this?
Yes, no database will run without an OS. And if you use Linux, expect hell getting .NET to connect to the DB. At least, I would have thought so.
virgo* wrote:
i m assigned the job of server, databse, webservices....
What experience do you have in these areas ? Or is the whole thing bound to go belly up ecause you guys have an idea, and no experience at all ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
|
Sorry, I cannot understand your post, all the abbreviations were giving me a headache.
Look at what you wrote:
b ==> be
v ==> ? I have no idea what this means
ppl ==> people
n ==> and
msg ==> message
thou ==> through
u ==> you
wid ==> with (also as abbreviation for "would")
v'll ==> we'll
shud/shuld ==> should
msges/msgs ==> messages (why the two different spellings?)
ne ==> any?
therer ==? ???
wt/wat ==> what
i ==> I (is it really that hard to hit the shift key?)
hav ==> have
m ==> am
plz ==> please
Ur ==> your
ll ==> will
4 ==> for
¡Por Dios!
Your post is unstructured - which is possibly a reflection on your thinking. If you are to get anywhere you need to communicate effectively. I've read your post through twice now and I don't know what your problem is, all I know is that you have a problem and you are stressed by it.
I'll try and answer the recognisable stuff.
virgo* wrote:
Every time a user gets connected to website...is entered automatically in database...database will check status of user after some time n if user no longer exists...remove entry automatically....
Do you mean that each time they connect to the website there is some sort of audit trail created? Or that they just get authenticated onto the website based on the credentials supplied being verified against details in the database?
If it is the former then you can create a simly INSERT query to be run when they access a page. This can be triggered off in, say, the Page Load event handler of an ASPX page.
If it is the latter then you can google site:msdn.microsoft.com Forms Authentication SQL Server and you should find a solution that you can use in the first few hits.
virgo* wrote:
As far as i hav studied i 'll need vb.net n web services for website n sql n ado.net for database....m i right?
Yes. VB.NET as the language, using ASP.NET to use web services, SQL Server for the backend database and ADO.NET to access it in your application (that's the web service you'll be writing)
virgo* wrote:
Will u plz guide me k how it wid b done...n wat shuld i do n study n in wat order
There are many articles on CodeProject and MSDN that will help you - Those are the two main sources of free information that I use on a daily basis. You should learn how to use Google effectively and ensuring that your terminology is correct will help. That is why I have tried to straighten out the terms above as precision helps to find things faster.
I hope this guides you in the right direction.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
v ==> ? I have no idea what this means
I assumed 'very', but who knows ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
i have an query
There are four tables::
table_1 :: date,acct_ref,prod_ref,short_qty,long_qty
table_2:: acct_num,date,cus,Qty
table_3:: acct_ref,acct_num,acct_name
table_4:: prod_ref,cus
use table_3 to join between acct_num and acct_ref & table_4 to join cusip and prod_hub
table_1 has (-ve)qty in short_qty & (+ve)qty in long_qty whereas qty column in table2 has both (-ve) & (+ve) qty.
There can be either a (-ve)qty or (+)qty
Requirement:: To identify where the qty is different from table1 & table2 & for which all accounts
please suggest the sql query to do so
Reghards
Varun
varun handa
|
|
|
|
|
This reads like a homework question - and a badly written one at that.
So, I hope you'll understand that I'm not going to give you a solution, but I will do my best to nudge you along in the right direction.
varun80 wrote:
use table_3 to join between acct_num and acct_ref
So, table_3 is linked to table_1 through the acct_ref column
table_3 is also linked to table_2 through the acct_num column
varun80 wrote:
table_4 to join cusip and prod_hub
cusip and prod_hub don't exist so I'm guessing this is copied wrong.
I'm assuming that cusip == cus and prod_hub == prod_ref
table_4 is linked to table_1 through the prod_ref column
table_4 is also linked to table 2 through the cus column
varun80 wrote:
To identify where the qty is different from table1 & table2 & for which all accounts
table_1 and table_2 are related via table_3 - the joins you made earlier should show you how match the rows in table_1 with the corresponding row in table_2
Because table_1 has two qty columns (long_qty and short_qty) they will need to be added together to produce a value that is comparable to the Qty column in table_2
varun80 wrote:
please suggest the sql query to do so
As this looks suspciously like homework I'll decline that request - you won't learn anything if I do all the work for you. However, as I have said, I am quite happy to nudge you along in the right direction.
If you get stuck, post your workings to show where you got.
Good luck.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi All,
I have developed an Module using SQLDMO in ASP.NET that takes the backup of the database and puts the .bak file on the Database server itself.
Now when I install the application on a fresh machine wiht Windows 2000 Server Operating System, which does not contain the SQL Server Client installed on it, the application does not give the result.
But if I install the SQL Server Client on the machine then the application would run properly.
Can any one help me out of this situation, and tell me what needs to be done......
Its Urgent..........
Thanks in Advance 
Prashant N.Rana
|
|
|
|
|
SQLDMO is a pig to re-distribute. You should find some info on the original SQL server CD or from the MS website. There are 3 ways to get an SQLDMO application to work on a client.
1: install SQL server on the PC
2: install SQL server Client tools on the PC
3: install all the SQLDMO dlls to the correct locations on the client PC.(see below)
These are the files that need to be installed and where they need to be installed to:
sqlunirl.dll to (WinSysPath)
sqldmo.rll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033
sqlsvc.rll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033
sqlresld.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
w95scm.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
sqlsvc.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
sqldmo.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
This is probably why most people will use ado / ado.net and use the system stored proceedures which can do pretty much everything SQLDMO cna do anyway.
I think thats it.
Jon
|
|
|
|
|
Hi Jon,
Thanks a lot for the quick response.
Sorry for the late response from my side, but I was in the process of implementing the suggessions given by you.
Well I was able to execute the application even with the third option.
I know that this was a very bad option to select and I should have used stored procedures. Well I will keep this in mind the next time I develop such kind of an applicaiton.
Thanks a lot once again for the quick response.
Thanks & Regards,
Prashant N.Rana
|
|
|
|
|
I am kind of stuck on stupid, and need some help. The following code works only once and then I get and error because of multi '@Param' use. I coded it this way because of the need to do some conversions as you can see. Can someone tell me how to code this in the correct way? I am new at this kind of code.
Norm
try
{
sqlInsertCommand1.CommandText = "INSERT INTO services([service-code], [service-description] ,[large-animal-cost], [medium-animal-cost], [small-animal-cost])" +
" values(@Param1,@Param2,@Param3, @Param4,@Param5) SELECT @@IDENTITY";
SqlParameter param = sqlInsertCommand1.Parameters.Add("@Param1",SqlDbType.Int);
param.Value = txtServiceCode.Text;
param = sqlInsertCommand1.Parameters.Add("@Param2",SqlDbType.VarChar);
param.Value = txtServiceDesc.Text;
param = sqlInsertCommand1.Parameters.Add("@Param3",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbLargeAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param4",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbMediumAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param5",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbSmallAnimalCost.Text);
sqlInsertCommand1.Connection = sqlConnection1;
if (sqlConnection1.State != ConnectionState.Open)
sqlConnection1.Open();
sqlInsertCommand1.ExecuteNonQuery();
nbohana
|
|
|
|
|
First, it looks like you have used the designer to write your query, in which case the query object is stored as a member variable in the class. This means that each time you run the command it keeps on appending the parameters to what was there before.
I would write this so that each time it creates a new instance of the Command object it starts afresh and you don't need to worry about what state it was left in the last time you used it.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have a database in which there are companies, sites, and accounts servicing the sites. Most of the time there is one account per site but it may be the case that one account covers many sites. There is a sites table and an accounts table. Is there a way to join these guys without the use of an intermediate table holding id's from each table?
Sorry if this is obvious but I am used to financial modeling not database development.
Mark
|
|
|
|
|
mjackson11 wrote:
Is there a way to join these guys without the use of an intermediate table holding id's from each table?
No, you must use an intermediate table if you want to keep the data model normalised. Even in database systems that permit Many-to-Many joins directly it is implemented with an intermediate table under the covers.
If you are willing to denormalise the datamodel you could implement it without the intermediate table if there were a limited number of possibles joins, say an Account can cover a maximum of 3 sites. You have three site foreign key references on Account that are nullable (just in case there are not exactly 3 sites) and vice versa. This will, however, become much more difficult to maintain than the intermediate table solution.
Is there any particular reason why you don't want to use an intermediate table?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
>Is there any particular reason why you don't want to use an intermediate table?
I'm lazy  . No reason really, it will just take a couple days to add the table and make sure the data is converted, then audited.
We have sites/account combinations that would require 15 links so I will put in a sites-account table.
Thx for the info. I found your blog article very helpful.
|
|
|
|
|
We are developing a VB.NET database application, using windows forms and Microsoft Access.
The application requires dynamic reports, where the users change data on the forms, if a report is being shown it will automatically update.
To do this we are using the .NET crystal report viewer, which loads a crystal report that we set it's data source at runtime using a dataset. If the user changes data on the data collection form, we update the dataset and refresh the reports data source.
The problem we are getting it's with the memory overhead. When we first show the form containing the crystal viewer control, an additional 20Mb of memory is allocated. Every time the report is then refreshed with the updated dataset, 1-2Mb is added to the memory each time. If we close the report viewer form, none of the memory is ever recovered. If the report is displayed again, the memory keeps increasing with every refresh.
Is there a way to release the memory used by the crystal viewer, or is this a know issue. If it is an issue does anyone have any better alternative to crystal reports?
Any advise would be appreciated.
Jason
|
|
|
|
|
I am getting the same problem, any suggestions would be very much appreciated.
|
|
|
|
|
We had a similar problem a while ago with crystal 8.5. From the research we did we found the following (to the best of my understanding). It seems when you open the report it loads all the data into an array in memory. You initially get a very large increase in the resources used (about a meg for the viewer object and other objects if you use charts etc and the rest for the data for the first report, This in your case is about 18 meg). When you close the form the viewer object is destroyed but the array is not(you should see a small drop in allocated memory). The next time you run a report it again creates the viewer object adding a few more meg. The data is loaded into the already existing array. If the data is more than before the array will grow. If it is less it all fits into the existing array and NO changes are made to the size. The array is destroyed when you close your application.
As our application was running on a terminal server we found out about the problem very quickly (clients server died if several people ran reports). With crystal the problem is made all the worst by the fact it will load ALL the data into the array then work on any critera you have set, so it may load 3 linked tables in their entirety yet only display 2% of the records if that all that fits the selection criterea.
Rather than upgrade and risk running into the same issues we just switched to Active Reports by DataDynamics which are XML layout based and have a tiny memory footprint.
So, sorry to say but i think if you want to use crystal you are stuck with this.
Jon
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
