|
Glad to help. I know that OpenXML rocked my world when I found out about it. It makes a lot of stuff that seems hard suddenly very simple.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
OpenXML is integrated in sql 2000 or not ? If yes why do i need new ie version?
sorry for asking i`m new in this
|
|
|
|
|
OpenXML is integrated, but the XML DOM implimentation it uses is not. Believe me, I was pretty amazed myself when the one part of my app that uses OpenXML started crashing on W2000
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi, i have a question for you.
I use a function that applies some filter on a view and then return all its fields (column).
FUNCTION FilterDataView(....) RETURNS TABLE (table declaration).
The problem is that the number of columns of the view are not fixed yet (its all under construction) so i need to change the declaration of the table returned by the function everytime the view changes (at least untill the structure of the view is fixed).
Even if this is not a big problem, i'm wondering if there's a way to dinamically define the structure of the return table accessing the definition property of the view (by sysobjects and syscolumns).
thks.
|
|
|
|
|
I have the following query statement in RichTextBox control
While executing i am reading the text from the Richtextbox and passing it to
SqlDataAdapter object. But it is telling me that incorrect syntax near 'GO'
statement
MyQuery:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_createAddEditscript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_createAddEditscript]
GO
MyCode:
try
{
SqlConnection con = new SqlConnection(conStr);
SqlDataAdapter da = new SqlDataAdapter(this.richTextBox1.Text.ToString(), con);
DataSet ds = new DataSet();
da.Fill(ds);
//this.dataGrid1.DataSource = ds.Tables[0];
}
catch (SqlException e1)
{
MessageBox.Show(e1.Message);
}
Please help me
Thanks in Advance,
Pothirajan C
|
|
|
|
|
Hi
In SqlDataAdapter da = new SqlDataAdapter(this.richTextBox1.Text.ToString(), con);
The first parameter in the Select Command and DA. Use this command to fill the dataset. As you are written you drop a procedure and does not select anything !
To run such command against Sql Server use SQlCommand class.Like this :
SqlCommand cmd = new SqlCommand("<sqlcommand>",<sqlconnection>);
cmd.ExecuteNonQuery();
|
|
|
|
|
The reason is that "GO" is not part of SQL. It is a command for the Query Analyser to tell it to run the next set of statements in a different batch.
Also, as your query is not returning a result set you should not be trying to Fill a DataSet . You should create a SqlCommand object then ExecuteNonQuery() on it. For example:
SqlCommand cmd = new SqlCommand("if exists (select * from dbo.sysobjects where id ="+
"object_id(N'[dbo].[sp_createAddEditscript]') and OBJECTPROPERTY(id, N'IsProcedure')"+
" = 1) drop procedure [dbo].[sp_createAddEditscript]", myConnection);
myConnection.Open();
cmd.ExecuteNonQuery();
myConnection.Close();
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi all check this out, it returns this error
ERROR [HYT00] Subquery returns more than 1 row
the update query is as follows
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 is this and how do I get round it???
Thanks in advance
Jetset
|
|
|
|
|
jetset32 wrote:
SELECT ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
This is returning more than one row. You need to make sure each cart can only contain each product once, or change your SQL, or do this:
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID =(SELECT TOP 1 ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
The TOP 1 thing is a hack, there is obviously a problem in the data in your database.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
thanks,
havnt had time to try this yet, but will let u know the outcome.
|
|
|
|
|
the TOP 1 thing didnt work, and I have tried every way i know to get this to work! the shopping cart will only contain the productID once, so am still a little clueless on this one!
Any one have any ideas?
Cheers
|
|
|
|
|
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
|
|
|
|