Database

Function returning Table (SQL)

5-Jun-05 11:18

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 Smile | :)

Christian Graus - Microsoft MVP - C++

LIUCKAS31-May-05 0:28

LIUCKAS

31-May-05 0:28

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.

Executing a query

Pothirajan C30-May-05 19:15

Pothirajan C

30-May-05 19:15

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

Re: Executing a query

MasudM30-May-05 21:17

MasudM

30-May-05 21:17

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();

Re: Executing a query

Colin Angus Mackay30-May-05 22:18

Colin Angus Mackay

30-May-05 22:18

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

Update query

jetset3230-May-05 1:19

30-May-05 1:19

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

Christian Graus30-May-05 12:16

30-May-05 12:16

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++

Anonymous2-Jun-05 3:37

Anonymous

2-Jun-05 3:37

thanks,
havnt had time to try this yet, but will let u know the outcome.

jetset324-Jun-05 10:49

4-Jun-05 10:49

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

Christian Graus5-Jun-05 11:17

5-Jun-05 11:17

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++

jetset3217-Jun-05 22:03

17-Jun-05 22:03

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

Christian Graus20-Jun-05 13:30

20-Jun-05 13:30

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++

jetset3221-Jun-05 20:16