Insert multiple values through Trigger - Database Discussion Boards

You need to restore a back up of the database to a new database with a different name and copy the data from there.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

I can connect to it and get the column names, etc.
But it doesn't retrieve any records and I don't know why!
I have tried a few different ways, but nothing seems to work.

What am I doing wrong??

Here's my code:

// string query = @"SELECT * FROM CONNTEST\FLOW_1";

string DBConnection = @"Provider=WinCCOLEDBProvider.1;DataSource=Laptop\WinCC;Catalog=CC_Connecti_06_07_29_15_50_04R;";
string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:10:00.000','0000-00-00'";
//string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'";
// TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'
//string SqlStr = "TAG:R,1,'0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'";

//Open Recordset via Connection object.

//string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:01:00','0000-00-00 00:00:00";
// 1: Make Connection to Database
ADODB.ConnectionClass Conn = new ADODB.ConnectionClass();
Conn.ConnectionString = DBConnection;
Conn.CursorLocation = CursorLocationEnum.adUseServer;
Conn.Open(DBConnection, "", "", 0);

// Conn = Server.CreateObject("ADODB.Connection");

// 2: Use the command text query
ADODB.CommandClass OCom = new ADODB.CommandClass ();
OCom.CommandType = ADODB.CommandTypeEnum.adCmdText;
OCom.ActiveConnection = Conn;
OCom.CommandText = SqlStr;

// 3: Create the RecordSet and fill with the data
//'ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
//rs.Open(SqlStr,DBConnection,ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
//'rs.Open(OCom, Type.Missing, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, -1);

object dummy = Type.Missing;
ADODB.Recordset rs = OCom.Execute(out dummy, ref dummy, 0);

object GetName;

GetName = rs.RecordCount;
////////GetName = rs.Fields[1].Value;

What you need is three tables. tblRecipes, tblIngredients and tblRecipeIngredients.

tblRecipes should be keyed on RecipeID
tblIngredients should be keyed on IngredientID
tblRecipeIngredients should have a dual key of RecipeID and IngredientID
Then you can look up recipe by ingredient or ingredient by recipe.
Simple.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Hello,

Thanks for your comments.

I think your advice will do the job but have problems creating the relation between two tables. I get the error "can not create a child list for field RelationName". I get this when it is trying to bind the relation to a GridBox.

Any idea what went wrong?

Do you think it might be that i am using the names column of the recipes and the ingredients tables as my key?

george

Hello, could somebody tell me how to configure Zone Alarm so that it would allow remote connections to SQLServer 2005 express?

R
Johan

I want to create a stored procedure that select certain fields from one databases, and somemore from another these databases are on 2 different servers but run the same sql server 2000. if anyone can help point me in the right direction i would much appreciate it.

There is no sql server 2003. It is 2000 or 2005. Check the Books on line for setting up a linked server. You can add a linked server from enterprise manager and then access the linked server via t-sql.

select * from server.database.owner.table

Make sure the MSDTC (distributed transaction coordinator) service is running on your servers.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Hi frends
I have written a trigger in MS-SQL 2000 runing successfully to insert single row.....but if i need to insert multiple rows from a select query in this trigger ......I am Confused ? Confused | :confused:

Purpose of Trigger
whenever a column will be updated in a table the data from two tables will be inserted into two tables where one table has multiple rows to insert.

I am trying to use While loop but confused that what should be the condition there to break tha loop......pls help if someone knows. Confused | :confused:

My trigger is

IF (COLUMNS_UPDATED() ) > 49
BEGIN
Declare @ServerDate datetime;
Declare @TranID numeric(18,0);
Declare @OppID numeric(18,0);
Declare @ProductID int;
Declare @Qty int;
Declare @Cost numeric(18,2);
Declare @Price numeric(18,2);
Declare @Discount numeric(18,2);
Declare @TaxAmt numeric(18,2);
Declare @UserID numeric(18,0);
Declare @ContactID numeric(18,0)
Declare @CountProduct int

SELECT @ServerDate=getdate()
SELECT @OppID=Opp.OppId, @ContactID=Opp.ContactID, @UserID=Opp.UserID FROM Opportunity Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3

if (Convert(varchar(10),@OppID) != '')
BEGIN
INSERT INTO [Transaction] (OppID,TranDate, ContactID,UserID) values (@OppId, @ServerDate, @ContactID, @UserID)

SELECT @TranID=TranID from [Transaction] where TranDate=@ServerDate
SELECT @ProductID=Opp.ProductID, @Qty=Opp.Qty, @Cost=Opp.Cost, @Price=Opp.Price, @Discount=Opp.Discount, @TaxAmt=Opp.TaxAmt FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3
--Count row
SELECT @CountProduct=count(Opp.ProductID) FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3

--WHILE (-----------)
--BEGIN
INSERT INTO [TranProduct] (TranID,ProductID,Qty,Cost,Price,Discount,TaxAmt) values(@TranID, @ProductID, @Qty, @Cost, @Price, @Discount, @TaxAmt)

--END
END
END

Dinesh Sharma

I would suggest a cursor but using a cursor in a trigger is bad practice as it will be slow. Is there no way to do this update from your application inside of commitment control? If a trigger is your only option then use a cursor to do the loop. You don't need a row count variable either just loop through the results of your select statement. Check Here
for more info.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Thanks ToddHileHoffer
I done this and now its working properly...

Thanks a lot

Dinesh Sharma

Dinesh Sharma

Hi all,

Well first of all, I would like to know if it is possible to put sql scripting in a batch file for execution?

I can connect to the MySql Server with a batch file but can't execute anymore commands (MySql command like reading from a file). I don't know how to do this, so If anyone could point me to a site or help me .... well just thanx in advance
D'Oh! | :doh:

Regards

Programm3r

Hi all,

Well first of all, I would like to know if it is possible to put sql scripting in a batch file for execution?

I can connect to the MySql Server with a batch file but can't execute anymore commands (MySql command like reading from a file). I don't know how to do this, so If anyone could point me to a site or help me .... well just thanx in advance

Regards

Programm3r

You can use the osql command to execute the sql commands from the batch fils.

thanks,
SeEa

Hi everyone!

I have an Access table made up of various "ingredients". From this table by selecting various "ingredients" i want to create another table (or just create new entries in another table) of "recipe" names and store this table in the same or another Access file.

I am connecting to the ingredients table and filling a dataset with it, through a DataAdapter, then i'm displaying the contents in a DataGrid in which i've added a CheckBox column. I'm planning to use this checkbox column to select the ingredients for the various recipes.

I know i can link such tables by using a column in the ingredients table which will specify a recipe name for each ingredient. However my problem is that some of the recipes will share the same ingredients so i can't use such a column to create the relationship.

Any ideas on the way to tackle this problem?

Thanks in advance.

Confused | :confused:

george

I get this message when running an application from a remote computer with standard connection.

SELECT permission denied on object 'custGroups', database 'ValkyriaDB', schema 'dbo'

I have SQL 2005 express database and MS SQL server managament studio express.
I done this so far:

In the security - logins - 'USER' - properties i have mapped my username to the correct db (default db) and in user mappings for that db i have granted all database roles to my username. The default scheme is dbo.

For the valkyriaDB schemes - dbo i have granted (Grant and With Grant) all permissions.

I simply dont understand why i get this error message... Must be something i dont understand.

Please advice

PS. It is much easier with old MSDN, or any opensource DB, dont understand why it has to be so hard with MS.

ITs not hard with MS it just enforces strong security.

Check you can access the object through a query using the 'USER' and debug from there. You might want to specifiy the db in your connection string as well as speficing the instance name mymachine\SQLEXPRESS

Look where you want to go not where you don't want to crash.

Bikers Bible

Thx, it helped, but now i got a new problem. Now it suddenly says:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

But still i can do connection.Open() from remote, i just can execute any statements. Where can i check if the server is set to receive remote connections?

BTW, my connection String:
="Server=MACHINENAME\SQLEXPRESS;Database=ValkyriaDB;UserID=XXXX;Password=XXX;Trusted_Connection=False;"

Thx for previous anserw, im new to SQL2005 or any MS database...

-- modified at 5:06 Tuesday 1st August, 2006

You are not using integrated windows security. Make sure that the SQL LogIn is a user in the db_datareader role in the database and you should be good to go.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

How to return first 10 rows in a table using the select command in SQL?

SELECT TOP [rownumber] [col1][, col2]...
FROM [table]

e.g. SELECT TOP 10 name FROM tblNames

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson

"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin

Edbert
Sydney, Australia

you can use order by to select on a defined order of a column

<br />
select Top 10 * from table<br />
order by created_date <br />

Look where you want to go not where you don't want to crash.

Bikers Bible

SELECT * FROM table where ROWNUM < 10 ORACLE
SELECT TOP 10 * FROM table SQL Server
SELECT * FROM table LIMIT 10 MySql Also
SELECT * FROM table LIMIT 10 OFFSET 50

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

i am trying to write a script. i need to copy the contents of my clipboard into a text file. then i need to save the text file as a htm file extention with a different name then i need to close both of the files. basically what i am doing is saving web page code in a filemaker pro data base. and i want to take the contents of a field that contains my code. and save itr as a htm file. is there any scripting language that will allow me to do this.

thanks in advance for any responses

Try APS.NET, Windows, C# or VB

Look where you want to go not where you don't want to crash.

Bikers Bible

weelze wrote:
is this the right forum

Not really

weelze wrote:
basically what i am doing is saving web page code in a filemaker pro data base. and i want to take the contents of a field that contains my code. and save itr as a htm file.

Sure, shouldn't be terible difficult with a scripting language such as VBS (Visual basic Scripting), the MSDN has a bunch of examples.
Visual Basic Scripting[^]

I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley: