|
jetset32 wrote:
the TOP 1 thing didnt work, and I have tried every way i know to get this to work!
OK, is there more to the SQL than we're seeing ? ( it ends in a + ).
jetset32 wrote:
the shopping cart will only contain the productID once, so am still a little clueless on this one!
Have you made sure this is the case ? It seems the problem is elsewhere, or the TOP 1 thing would have worked, but that's still the only place you have a subquery that you assume will return one item, and apparently it's not. Have you run the SQL in query analyser, a bit at a time ( like do the select for the product ID first, then if that works, add the rest, etc ) to see what you get ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi,
The sub query will return more than one product id , this is because the customer has bought more than one item.
the query works if only one item is purchased but if more than one item is bought then mysql returns the error more than 1 row is returned, that is correct.
I want it to update all the product id's with the quantity that the customer has bought.
Can this be done in MySQL, I dont know.
Thanks in advance
Stuart
|
|
|
|
|
OK, then you're going about it completely wrong
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
Why 'WHERE ProductID = ProductID' ? That's the same as WHERE 1=1.
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID = s.ProductID
AND s.CartID = '" + Convert.ToString(cartID) + "'
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Cheers, your right, I knew this site was a god send.
Thanks for all your help with this
Stuart
|
|
|
|
|
Is it possible to get the record which is updated when an update trigger executed? Thank you.
<italic>Work hard, Work effectively.
|
|
|
|
|
Hi everybody!
I 'm going to generate a report which I need the summery of some times for example I 've three recorde with these values :
ID Desc Time
2 Something 03:15:45
6 Something else 12:48:50
12 Another Thing 20:15:36
in my report I need something like this:
Sum of time : 36:20:11
any body can help me to do this. may be a stored procedure ,may be a way?????
Masoud_TB
|
|
|
|
|
Hi,
I am using the GetSchemaTable() method of the C# .NET OleDbDataReader class to read the schema of an Access table. The following columns always have the same wrong values:
AllowDBNull = True
IsUnique = False
IsKey = False
IsAutoIncrement = False
Why do these schema columns show the opposite values of what they should be?
Any help appreciated,
Royce
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
