|
thank you very much Colin
Christophe
|
|
|
|
|
I am trying to access a database from a client and am getting access errors. I have it working on the server. The server is setup to trust the assembly via a strong key.
I don't want to setup each client to trust the assebly so I am trying to use SqlClientPermission.Assert but it does not seem to be working.
Any ideas? Thanks in advance.
Here is the code
SqlClientPermission sqlClientPerm = new SqlClientPermission(PermissionState.Unrestricted);
sqlClientPerm.Assert();
string strSql;
string myConnectString = "Persist Security Info=False;Integrated Security=true;database=myDataBase;server=xx.xxx.xxx.xx;Connect Timeout=10";
strSql = "UPDATE tblText SET Text = '";
strSql += tbText.Text;
strSql += "' WHERE TextID = 1";
// Set the query
SqlCommand sqlCmd = new SqlCommand(strSql);
// Get the connection
sqlCmd.Connection = new SqlConnection(myConnectString);
sqlCmd.CommandType = CommandType.Text;
// Open the database
if ( sqlCmd.Connection.State == ConnectionState.Closed )
{
sqlCmd.Connection.Open();
}
// Do the query
sqlCmd.ExecuteNonQuery();
SqlClientPermission.RevertAssert();
|
|
|
|
|
Hi,
I am working on VB.NET Windows form application. I am using Data Binding feature of .NET. I have one problem as described below:
There are two table call “Overheads” and “OverheadDistribution”.
OverheadID is Primary Key for Overheads and FK for Overheads.
There is a Master-Detail Win Form where OverheadDistribution is shown in Datagrid as part of Detail part for selected Master (Overhead).
When I fetch data from Data source in DataSet. I make a DataRelation using OverheadId and assign this DataRelation to Datagrid as DataSource.
There are ten numbers of records in OverheadDistribution and for OverheadID=1, there are two records (OverheadDistributionID = 3,5). So, there are two rows in Datagrid.
When I want to delete a row from Datagrid, I select a row in Datagrid, get Row index using CurrentRowIndex, let assume I select row no 2 means actually fifth row in datatable (“OverheadDistribution”).
So, How can I find corresponding row of Datagrid in Datatable? Means, by clicking on Datagrid row, how can I find respective row in DataTable?
Regards,
|
|
|
|
|
Hi there! Over the past few years I've worked on a number of websites. I've implemented a Search with varying conditions many times, but now it's different. So far the Search used stored procedures and dynamic SQL:
DECLARE @select_fields NVARCHAR(1024)
DECLARE @where_clause NVARCHAR(4000)
DECLARE @join_clause NVARCHAR(2048)
DECLARE @query NVARCHAR(4000)
SET @select_fields = '...'
SET @join_clause = ''
SET @where_clause = ' WHERE 1 = 1'
...
IF @in_field1 IS NOT NULL
SET @where_clause = @where_clause + ' AND [table1].[field1] = @in_field1'
IF @in_field2 IS NOT NULL
SET @where_clause = @where_clause + ' AND [table2].[field2] = @in_field2'
...
SET @query = 'SELECT' + @select_fields + ' FROM [table1] AS [t1]' + @join_clause + @where_clause
DECLARE @params_def NVARCHAR(1024)
SET @params_def = '@in_field1 TYPE, @in_field2 TYPE, ...'
EXEC sp_executesql @query, @params_def,
@in_field1 TYPE,
@in_field2,
... But now I'm facing an application that is requiring an advanced search with about 35-40 conditions, some of them arrays. I can't use the code above, because the @query variable is only 4000 characters long. As you can imagine, 35-40 conditions won't fit in it . What is the best way to implement this Search using stored procedures? I don't want to build the SQL code in the program code and pass it to the stored procedure. Thank you. Stanimir.
|
|
|
|
|
What if you passed the conditions in as an XML document, used that to build a table and then did your query against the values in the table ( which would have one row ) ? I believe you can pass in a text field greater than 4000 chars ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi Christian!
Thank you for your reply. The solution is great, but can you give me an example where the search is performed against UNIQUEIDENTIFIERs, INTs, BITs, NVARCHARs (using LIKE ? How should I build the table from an XML document? How should I match the real table with the temporary one?
Thank you.
Stanimir.
|
|
|
|
|
LIKE only works for strings.
This link is one of many I found on OpenXML, which is built into SQL Server 2000, and lets you build a table from an XML document.
http://www.sql-server-performance.com/jb_openxml.asp[^]
Note - if you use OpenXML, you need to have a recent version of IE installed, to get the required XML DOM support. Fun and games.
Stanimir Angeloff wrote:
How should I match the real table with the temporary one?
Just join to it, or match against it's properties. Use select TOP 1 to remind SQL Server that you're selecting a single value ( there is only one row to start with ).
Hopefully that's enough to get you started, let me know if you're stuck. OpenXML is IMO the best way to pass a variable number of parameters to SQL Server, for example if you need to delete an unknown number of rows, or in a situation like this.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi Christian!
Thank you for your quick reply. The information is very useful. It's just what I was looking for. I've solved my problem using only one NTEXT parameter (where the XML document is passed).
Thank you.
Stanimir.
|
|
|
|
|
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
|
|
|
|