|
If it is something that you'll repeatedly use from various different bits of code, you could create a method for it and pass the id and the updated value. Then the method does the work and the various bits of code that need it only have to call the one method.
|
|
|
|
|
Hopefully it's not something I will be doing a lot (if ever again) - as has been said, its often best to do such work in SQL.
This sort of processing I normally do on the raw text files we receive but on this occasion the data was loaded into SQL Server before the anomalies were noticed.
|
|
|
|
|
after you read a record, then simply check for your required condition and accodrding ly give your update query. Everthing comes in while(dr.read)....
if(...).....update....
Gautham
|
|
|
|
|
|
Hi
I m tryring to configure report server the server is not initializing anyone can provide help regarding to configure report server or what is the appropriate forum for this.
Thanks
Shahzad Aslam
|
|
|
|
|
Hello C# coder,
Lets straight to the point;
I'm trying to get the schema of a table in Database (SQL Server, Access) using GetSchemaTable() method.
With same table structure (both in SQL, Access), I got different result of schema. For example, Field that has attribute Required=True in my table, the schema of SQL DB showed AllowDBNULL = FALSE but in Access showed AllowDBNULL = TRUE.
These are the snippets of my code
<br />
OleDbConnection conn1 = new OleDbConnection("Provider=SQLNCLI.1;Datasource=DEV06\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=JatiDB");<br />
<br />
conn1.Open();<br />
OleDbCommand command = new OleDbCommand("select * from parent",conn1);<br />
OleDbDataReader reader = command.ExecuteReader();<br />
DataTable schema = reader.GetSchemaTable();<br />
conn1.Close();<br />
In my program, I've changed the value of ConnectionString for accessing Ms. Access, and works.
So my question is;
1. Does GetSchemaTable() results different output for each database?
2. If so, is there another alternative to get the schema with same output?
Thanks, any suggestion is greatly appreciated.
TIA.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
There are several methods on the oledbconnection class (including GetOLEDBSchemaTable) that may be useful.
|
|
|
|
|
Is there any form generator or template to create search forms? I just want to display data from my sql server 2005 tables using drop down menu or search field options.
I'll greatly appreciate any help.
|
|
|
|
|
What is the SQL commands(script) to get a table size?
|
|
|
|
|
Try sp_spaceused 'MyTableName'.
|
|
|
|
|
Thanks that worked ;)
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
Hi everybody
I am trying to update some file names values into the table through listbox
here i have two listboxes, in listbox1, i have some filenames, which i can add or remove from listbox2
When the user logs in , he has some files in listbox2, which is already assigned to him.
If he tries to remove some from listbox2, or add some more from lisbtox1, then update, the filenames are not getting updated
Suppose the user chooses file1, file2, from listbox1, add to lisbox2 and then update, it is not getting updated.
i am inserting the listbox1.item.value along with the contactid.
Public Sub UpdateAccounts(ByVal contactid As Int32)
Dim userBll As UserCreationBLL = New UserCreationBLL()
Dim listitems As Int32 = lstAllAccounts.Items.Count
Dim lstItems As Int32 = lstAccountAccess.Items.Count
If (lstItems > 0) Then
Dim i As Int16
For i = 0 To lstItems - 1
userBll.UpdateAccounts(contactid, Convert.ToInt32(lstAccountAccess.Items(i).Value))
Next i
End If
End Sub
SP for updating is
ALTER PROCEDURE UpdateAccounts
@contactID int,
@accountID int
AS
UPDATE [Contact Account ]
SET [Account ID] = @accountID
WHERE [Contact ID] = @contactID
thanks
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
