|
DB: SQL Server 2005
I am trying to figure out how to check that a value exist before inserting/updating a record in a new table. Simplified Example below:
Product
Product_ID (int)
Product_Name (varchar(25))
Inventory
Inventory_ID (varchar(50))
Inventory_Product_ID (int)
Inventory_PO_Item_ID (int)
Inventory_Status (tinyint)
I need to make sure that the Inventory.Inventory_Product_ID exists in the Product.Product_ID table before the record in the Inventory table is inserted/updated. I could create a stored procedure to do this, but the problem still exist that a record could accidentally be manually added to the table without cheking first. (Plus, I would like to throw an error in the stored procedure if the Inventory_Product_ID does not exist in the Products table.) I read that you can not use Select statements in contraints or rules. Would this be an example of using a trigger. If so, I am not too familiar with triggers and would appreciate some guidance with this problem. Thank you in advance.
|
|
|
|
|
Why not use a foreign key on Inventory_Product_ID to Product_ID?
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 have a wireless network and sometimes there's a split-second hickup in the connection. The application disconnects and you have to relog in. Is there a way to overcome this with a SQLServer setting that allows the connection to stay up with a glitch in the connection.
|
|
|
|
|
Why would you maintain an open sql connection? That is terrible desgin. SQL Connections should be closed as soon they are no longer in use. You should be storing the connection string in a variable somewhere and creating / disposing connections each time you need to access data.
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 All,
I have a problem combining results of two queries. The queries are fired on the same table with the same select list, only the where condition changes. It would be better if i show what i actually want.
This is my table data for example.
Field1 Field2
AB 1
SD 2
SDFG 3
AS 4
QWE 5
BBG 6
I want an output as
Field1 Field2
AS 4
QWE 5
BBG 6
AB 1
SD 2
That means , if i give the value for Field2 = 3 in the query then the result will not contain record with Field2 = 3 and the result should display the record with Field2 > 3
first and the with Field2 < 3 both sorted in ascending order.
I am using the union operator for combining results, but the results are not as desired. Also i feel that the union clause is not efficient since it takes about 4-6 secs to fetch the records.
Is there any efficient solution for my problem. Please reply soon as i have already spent considerable time on this query and i am running short of time now.
Any help will be appreciable.
Thanks in Advance.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
Anant Y. Kulkarni
|
|
|
|
|
-- Try this
declare @field2 as int
set @field2 = 3
select field1, field2, case when
field2 > @field2 then 1
else 2 end as SortValue
from test where field2 <> @field2
order by SortValue, field2 asc
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 have a data structure that i am storing a set of data in, that is manipulated, before being applied to the database on calling the Save() function.
The database has a unique field, say 'Name', that is constrained as being unique.
This lead me to notice a potential problem that could affect how Datasets work, and i'd like to avoid it without having to turn off the database constraints. The problem is as follows:
Code
====
Firstly, this code is run, adding two records by name to the dataset, and then saving it to the database.
<br />
obj.Add("AAA")<br />
obj.Add("BBB")<br />
obj.Save()<br />
Database
========
After running this code, the database table will look as follows:
id name
1 AAA
2 BBB
Code
====
Then we run the following code, which loads the table, renames the two records in the dataset using a Tower of Babel kind of name swap, and saves it back to the database.
<br />
obj.Load()<br />
obj["AAA"].Name = "---"<br />
obj["BBB"].Name = "AAA"<br />
obj["---"].Name = "BBB"<br />
obj.Save()<br />
Now, at this point, when it tries to do the save, the application should fall over (Assuming i understand how datasets work)
In effect, we have swapped the two item names, "AAA" and "BBB", so we would like the table to read as follows:
Database
========
id name
1 BBB
2 AAA
However, as only two records exist in the dataset, both flagged as 'Updated', the following queries are generated:
"UPDATE SET name = 'BBB' WHERE (id = 1)"
"UPDATE SET name = 'AAA' WHERE (id = 2)"
At which point, on executing the first query, it would attempt to update the database table to the following state:
Database
========
id name
1 BBB
2 BBB
Whereupon, it would crash, as the unique constraint is broken.
The only possible solution would be if microsoft (in all their wisdom) thought of this, and implemented some kind of update stack, which may also work, as all three updates would be applied in order, and no exceptions would be generated. (But i don't think they do as the update commands are handled by the data adapter and the rows merely have an update type indicator)
Will it work, or will in not?
Cheers guys
Tris
-- modified at 4:54 Sunday 30th July, 2006
|
|
|
|
|
I just confirmed this problem - Still looking for a way around it tho.
<br />
private void btnSetup1_Click(object sender, EventArgs e)<br />
{<br />
DataSet1.EntityAliasRow dr;<br />
<br />
dr = (DataSet1.EntityAliasRow)dt.NewRow();<br />
dr.EntityID = 7;<br />
dr.EntityName = "AAA";<br />
dr.IsPrimaryName = false;<br />
dt.Rows.Add(dr);<br />
<br />
dr = (DataSet1.EntityAliasRow)dt.NewRow();<br />
dr.EntityID = 7;<br />
dr.EntityName = "BBB";<br />
dr.IsPrimaryName = false;<br />
dt.Rows.Add(dr);<br />
<br />
da.Update(dt);<br />
}<br />
<br />
private void btnSetup2_Click(object sender, EventArgs e)<br />
{<br />
DataSet1.EntityAliasRow dr1;<br />
DataSet1.EntityAliasRow dr2;<br />
<br />
da.Fill(dt, 7);<br />
<br />
dr1 = (DataSet1.EntityAliasRow)dt.Select("EntityName = 'AAA'")[0];<br />
dr2 = (DataSet1.EntityAliasRow)dt.Select("EntityName = 'BBB'")[0];<br />
<br />
dr1.EntityName = "---";<br />
dr2.EntityName = "AAA";<br />
dr1.EntityName = "BBB";<br />
<br />
da.Update(dt);<br />
}<br />
|
|
|
|
|
Hello All,
I 've 2 tables say AA and BB.
AA has 2 cols AA_old,AA_new & BB has i field BB_old.
Both AA_old and BB_old has 2 same rows.
I need to replace the values of BB_old with the corresponding values of AA_new.Its like :--
AA BB
AA_old AA_new BB_old
1 11 1
2 22 2
i want to replace BB_old with AA_new: Result will be like
BB_old
11
22
values may repeat
Can anyone help me generate a query Plz.
Thank you.
Regards,
jeeva
|
|
|
|
|
UPDATE BB SET BB_old = (SELECT AA_new FROM AA WHERE AA.AA_old = BB.BB_old)
It would be much easier to help you if you posted your actual table and field names. The above SQL is just weird - but it is clearly what you are trying to do.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks for the help.
I got the table updated but when i try to update some fields using UI i get an error :-
Key column information is insufficient or in correct.
Too many rowswere affected by update
i cannot update it.
my query is like:-
UPDATE sales1 SET sid = (SELECT sid_new FROM sales2 WHERE sales1.sid = sales2.sid_old)
Plz find me a solution for this.
Regards,
Jeeva
-- modified at 4:51 Saturday 29th July, 2006
|
|
|
|
|
Jeeva Mary Varghese wrote: Key column information is insufficient or in correct.
Too many rowswere affected by update
The Enterprise Manager will give you this error if you have more than one row that contains the same data in every column.
Solution: add a unique Identity column - and you will not get this error anymore (but, your UPDATE might error out when it attempts to create two rows with the same data in every column), or use the query analyzer to do your updates.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi, I am new to firebird database. I got "FirebirdSql.Data.Firebird.FbException" (arithmetic exception, numeric overflow, or string truncation
Implementation of text subtype 52 not located.) while trying to update my firebird database.
My code for updation is :-
Dim dbcon As IDbConnection = New FbConnection(connectionString)
dbcon.Open()
Dim dbcmd As IDbCommand= dbcon.CreateCommand
dbcmd.CommandText = sSQL
dbcmd.ExecuteNonQuery()
my query looks like this:- "UPDATE HISTORY_MASTER SET Status='RUNNING', WHERE Mkey='5e81f9859d223ea420aca993c647b839' AND Status='Start';" Please help me in that.
Thanks
Nicky
ssadsadadgawkjawoidehqwohoiweheiwqhrewoihriwolhrwiehriwehfuiwhfiluwhwifhiwhfiwhfiwehfiwhrwhrwhuiw
|
|
|
|
|
Hi All,
greetings.In my appilcation i need to take two different data that is EmployeeName from Employee Table and ClientName from Client Table.How i can write it in a single SQL Query.
Can any one one give me the query please.
Thanks in Advance.
Babu
|
|
|
|
|
ChennaiBabu wrote: In my appilcation i need to take two different data that is EmployeeName from Employee Table and ClientName from Client Table.How i can write it in a single SQL Query.
Try using JOIN.
You can find about Joins here[^]
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Or you can also use UNION operator to combine data from 2 select statements....
<< >>
|
|
|
|
|
|
Try to use the Response.write to write out the Statement
Then u can use SQL analysis to test it
or try this
=db.OpenRecordset("select * from emp where empno='" & Val(Text1(0).Text) & "' and ename='" & Val(Text1(1).Text) & "' and hidate='" & Text1(5).Text) &"'"
|
|
|
|
|
Hi,
In SQL Statement:
EX:
============
SELECT * FROM table_name WHERE field1 BETWEEN 1 AND 10
============
PLEASE CHECK YOUR STATEMENT AGAIN.
Hy Chanhan
|
|
|
|
|
hi,
pleas give space between "empno" and "and" statement
Ravikumar
[Coimbatore]
|
|
|
|
|
capamaro wrote: ("'select * from emp where empno='" & Val(Text1(0).Text) & "'[insert space] and ename='" & Val(Text1(1).Text) & "'[insert space]and hidate=[#]" & Text1(5).Text & "[#];")
You have an extra single quote in that query and are missing some spacing. When in doubt do a Debug.Print SQLQuery to see how it looks to the database engine.
You should really take a look at the article below, it’s not in the same language, but the content is still worth reading. Protecting the database is your number one goal no matter who the end users are. The way your queries are run they are prime for SQL injection attacks.
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
It is vulnerable to SQL injection.
(Also try wrapping the date in ##
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
|
|
|
|
|
Hi,
I want to join 2 xml documents to get the result into one table. Can any one gives the syntax.
Regards,
Kalyan
|
|
|
|
|
I want to update a cell in a datagrid, and then update my database with the cellvalue - Hopefully, using a contextmenu.
I am using a data/adapter.fill for the grid
Gord Warwick
|
|
|
|
|
Isn't updating the cell in grid update the database automatically?
|
|
|
|