|
I'm not sure I follow this entirely but several points stand out to me.
First, you are updating one field for two selections by your example so only the last one sticks. Do you need an insert to support new records? Is your structure designed correctly to support the data you are trying to save?
Hope this helps,
Ware
Programmers are tools to convert caffiene to code.
|
|
|
|
|
Can you please help, in fixing this if i detail the exact thing
Listbox1 listbox2
file1 file4
file2 file7
file3
file5
file6
this is what user see.
listbox2 contains files already assigned to him
now he added file1, file2 from listbox1 to listbox2, so that listbox2 has
file4
file7
file1
file2
then he clciks update.
Again when he logs in, he can see only file2, where i want to display the 4 files he selected
hope someone can help me
-- modified at 11:39 Wednesday 7th February, 2007
|
|
|
|
|
I am going to use the following data as a basis for the before and after examples:
Table: Contact [Contact ID] [Name]
Row : 1 1 user
Table: Account [Account ID] [Name]
Row : 1 1 file1
Row : 2 2 file2
Row : 3 3 file3
Row : 4 4 file4
Row : 5 5 file5
Row : 6 6 file6
Row : 7 7 file7
I am assuming the data before looks something like this:
Table: Contact Account [Contact ID] [Account ID]
Row : 1 1 4
Row : 2 1 7
I am assuming the data after looks something like this:
Table: Contact Account [Contact ID] [Account ID]
Row : 1 1 2
Row : 2 1 2
You will need to keep a copy of the original data loaded into listbox2 for comparison purposes. You need to compare the original contents to the current contents when the user clicks update.
For any items removed you will need to call a procedure that is coded like this:
CREATE PROCEDURE usp_ContactAccount_Delete
( @contactID int
, @accountID int
)
AS
DELETE FROM [Contact Account ]
WHERE [Contact ID] = @contactID
AND [Account ID] = @accountID
For any items added you will need to call a procedure that is coded like this:
CREATE PROCEDURE usp_ContactAccount_Insert
( @contactID int
, @accountID int
)
AS
INSERT INTO [Contact Account ]
( [Contact ID]
, [Account ID]
)
VALUES
( @contactID
, @accountID
)
Ware
Programmers are tools to convert caffiene to code.
|
|
|
|
|
Hi!
Here's a meaner. I have a database that looks like this:
Names Table
Surnames Table
Car Names Table
Master Table
The first 3 are linked to the master. What happens is that all the tables has an ID (Primary key). After entering the details a stored procedure takes the ID's o the tables and add them in the Master Table.
Question: Is it possible to Import data from Excel into my tables and still get the same result. How can I utilize a Stored Procedure using DTS?
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
DTS allows you to import data from Excel spreadsheets. I would import the data into a database table, then use an "Execute SQL Task" (the tin can with red arrows) to do something along the lines of:
declare @col1 varchar(10), @col2 date, @col3 int
declare c1 cursor for
select * from MyExcelNames
open c1
while (@@fetch_status = 0) begin
fetch c1 into @col1, @col2, @col3
if (@@fetch_status = 0) begin
exec MyNamesProcedure @col1, @col2, @col3
end
end
close c1This will run your stored procedure once for each row imported from Excel.
Hope this helps.
Andy
|
|
|
|
|
Hi everyone.
I am stucked,busy doing an access system for monitering the arrival,lunch and depature time of employees.I need a sql statement that checks if the timein and timeout column are null in the table.
pls help.
kind regards
Minkinin.
|
|
|
|
|
SELECT * FROM SomeTable WHERE SomeColumn IS NULL
|
|
|
|
|
let me clarify my question.
I am busy with security access system for monitoring the arrival,lunch and depature time of employee,so when a user click an access card the system should check if that user has just arrived meaning that theres a record for arrival in the database,if not it should create it,
so now I want a sql statement to check if theres null value in the table.
my column names are:
Date, Name, IN, OUT so if a user click an access card in the morning the record will be like this: DATE Name TimeIn OUT
07/02/07 Jomo 7:45 Null
then when the user click the card during lunch, system willhave to check if the In and Out columns are null and insert that time on the null column,
so when the user comes back from lunch it has to create a new record
then the table will look like: Date Name Timein TimeOut
07/02/07 Jomo 7:45 12:00
o7/02/07 Jomo 1:00 Null
so I need a sql statement which can always check if the time field is null so that it can insert time .
|
|
|
|
|
You could always do it like this in a stored procedure:
IF EXISTS(SELECT * FROM Test
WHERE [User] = @User AND [Out] IS NULL)
BEGIN
UPDATE Test
SET [Out] = @TimeValue
WHERE [User] = @User
AND [Out] IS NULL
END
ELSE
BEGIN
INSERT INTO Test ([Date], [User], [In])
VALUES (GETDATE(), @User, @TimeValue)
END
Swapping in your parameters obviously.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you very much,its working now.
keep on the good srpirit.
|
|
|
|
|
try this
if ur field is x in table xyz which u have to check its null or blank
select x from xyz where isnull(x,'')<>''
this query check both null or blank in ur x colum give only filled records
Rakesh Jha
|
|
|
|
|
Hi
I m trying to count minimun no of friends that customer has set up in the table customer_fiends.
select min(count(friendname))
from customer_friends
where friendname<>'Me' group by userid
The following error in orruring!
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SHAHZAD ASLAM
|
|
|
|
|
what are the columns in yur table?
Gautham
|
|
|
|
|
userid,friendname,mobileno
the userid is the reference in Customers Table i want to select minimum no of friends that customer has set up.
SHAHZAD ASLAM
|
|
|
|
|
if i have 7 friends what would be the minimum of friends list, no clue what are your true intentions are????
Gautham
|
|
|
|
|
if userid 1 hve 7 friends and userid 2 hve 5 and userid 3 hve 2 friends then it should display userid 3 have 2 friends that is the minimum no of friends of that customer.
SHAHZAD ASLAM
|
|
|
|
|
OK, I did say that I didn't think it would work still. I answered in a hurry. Colin is right, you can't run min on count, count is always a number. To get the lowqest value, you want to do something like
select top 1 count(friends) as friendCount, userId from tbl group by userId order by friendCount asc
something like that, it's almost certainly not right 100%. But, you get the userId and the count of friends, you order it so the first record is the lowest, then you grab that one record.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Thanks its working fine.
SHAHZAD ASLAM
|
|
|
|
|
you can only group by things you select
select min(count(friendname)), userid
from customer_friends
where friendname<>'Me' group by userid
may work. I'm not sure this will work overall, I don't have SQL Server here to test it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
it Still not working the same error occuring.
|
|
|
|
|
Shahzad.Aslam wrote: The following error in orruring!
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
This:
select min(count(friendname)) is the source of the error.
You have nested two aggregate functions inside each other. COUNT() has a single result, so the MIN() of a COUNT() is always the same as the result of COUNT().. Essentially, there is nothing to aggregate.
I really cannot see what your intent was with this statement, so I cannot help you correct the problem. All I can suggest is to remove the MIN() and just leave the COUNT().
If you can explain your intentions then it might be easier to help.
|
|
|
|
|
hi
I think its not possible to find the minimum or maximum value for an aggregate function like 'count' because that will return a single value only..like if there are 12 members whose name <> 'ME', then the output will be 12.
From this 12, how can you find the minimum value? Thats why the error has occured.
|
|
|
|
|
Hai friends,
In my project i want to get the data from 2 databases.? i don't have any idea about this i tried but still i didn't get any answer...please help me to find a solution...i am waiting for your kind reply....
Thanks in advance...
Alex.
|
|
|
|
|
select........... dbo.tablename, 2nddatabsename.dbo.tablename.......
Gautham
|
|
|
|
|
Hello,
I have a typed dataset which I want to display in an ultragrid (infragistics) and display the parent and the children.
I can display this ok when I want to display all of the parents and children. But the problem is when user will need to select one parent (by customerID number) from a list and display that parent with its children in the grid.
I have created the dataset schema dsAssembliedEquipment.xsd and my 2 tables assembly and AssembiledEquipment. And also created the relationship between them.
I have a stored procedure (getDataByCustomerID) that will find the customerID of the assembly (parent) table.
This is my code for setting the grid.
<br />
'Table Adapter to get the customer ID record<br />
Me.TA_Assembly1.GetDataCustomerID(customerID)<br />
'Fill the assembly (parent) table and assembliedEquipment table(child)<br />
Me.TA_Assembly1.Fill(Me.DsAssemblyEquipment1.Assembly) Me.TA_AssembliedEquipment1.Fill(Me.DsAssemblyEquipment1.AssembliedEquipment)<br />
Me.grdCustomersAssembly.DataMember = "Assembly"<br />
Me.grdCustomersAssembly.DataSource = Me.DsAssemblyEquipment1 <br />
Code for the stored procedure getDataCustomerID:
<br />
ALTER PROCEDURE [dbo].[Assembly_SelByCustomerID]<br />
(<br />
@CustomerID int<br />
)<br />
AS<br />
SET NOCOUNT ON;<br />
SELECT AssemblyID, CustomerID, AssemblyName<br />
FROM dbo.[Assembly]<br />
WHERE CustomerID = @CustomerID <br />
I have Assembly (parent) Table fields AssemblyID / customerID / AssemblyName
I have child table called AssemblyEquipment (child) fields SerialNo / make / Model / AssemblyID (Foreign Key)
Many thanks for any help with this problem,
Steve
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
