|
Hi everyone.
pls help,I want to display data from DB to the grid
but my data looks like this: Code Name Time
1 Me 2/15/2007 10:18:00 AM
and I want the grid to look like this: Code Name Time
1 Me 10:18
how do I display/formatt only that portion of time on my grid.
|
|
|
|
|
You usually don't try to format the data coming from your database in your code, instead you set a display format on the column in the grid itself.
|
|
|
|
|
hi
i'm developing a wizard that creates triggers and i need a couple of Create Trigger examples to map the query for different triggers(of type INSTEAD OF).
if u have any tiggers urself pleaze do post here
thanks fo![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) r ur time an effort.
|
|
|
|
|
sorry that i can't help but did u search the web?
I Wish the Life Had CTRL-Z
|
|
|
|
|
|
I have a table in SQL Express 2005 that I would like to delete the first row after a specified number of rows have been added. The table contains four columns, one of the column contains the ID number starting at 1 and is autoincremented when a new row is added. Before a new row is added, I want to check if there are already 60 rows and delete the first row before adding the new row. I want new rows to be added, but want to keep the number of rows at 60 by deleting the oldest rows. I would really appreciate it if someone can provide with an example of this.
|
|
|
|
|
may u can use triggers like this:
CREATE TRIGGER myTrigger<br />
ON myTable<br />
FOR INSERT <br />
AS<br />
<br />
BEGIN<br />
DECLARE @count int;<br />
SELECT @count = COUNT(*) FROM myTable<br />
IF(@count>60)<br />
BEGIN<br />
DECLARE @first int;<br />
SELECT @first = MIN(ID) FROM myTable<br />
DELETE FROM myTable WHERE ID=@first<br />
END<br />
END<br />
and if you want to have your ID column Identity and only from 1-60 use IDENTITY_INSERT
check msdn and web for more
I Wish the Life Had CTRL-Z
|
|
|
|
|
hi
i'm trying to use the alter table query to add the primary key constraint to a column that already exists in the table(The table has been created without specifying any primary key).
how can i do this??
i tried doing it but the server gives a message that primary key constraint cannot be added in the Alter table queryas it can be dropped through this query.
plz do explain the meaning of this message and also tell the query through which this task can be accomplished.
thanks for ur time
Saira
|
|
|
|
|
The query that works fine for me is (works on SQL Server):
ALTER TABLE MyTable<br />
ADD CONSTRAINT PK_KeyName primary key clustered (FieldName).
You can get an error message if there a re null values or duplicates
in the field you want to declare as primary key.
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
|
Your connection string looks ok, assuming you don't have instances on your sql server box. If you do have an instance you need to put that in your Data Source. I am wondering if it is some of your other code that is accessing your tables that is causing your errors.
Ben
|
|
|
|
|
hi
i am developing a web based application usin c#. i wanted to perform some searching functionality in the website.
i have laid down some fields such as Name, Age.
now i want the user can perfrom a search on any of these parameters. eg. if he just wants to perform the search on name, he may or if he wants on bothe the params, he is able.
how shud i do.
in the sql i tried with AND. but for AND. you wud need to specify both the params..
Kunal
|
|
|
|
|
Hi,
In using queies i can help u.
eg: select * from tablename where (''='aaa' or Name='aaa') and (0=16 or Age=16)
check it out with this query
Regards,
LEE
|
|
|
|
|
hi
thankx for replying.. but the querying is asking for both the parameters.
Kunal
|
|
|
|
|
Hello!
I have tested this code in SQL Query Analyser:
--/////////////////////////////////////////////
CREATE TABLE TESTDATE(
T_ID INT NOT NULL PRIMARY KEY,
TDATE DATETIME
)
-- ///////////////////////////////////////////////////
INSERT INTO TESTDATE(T_ID,TDATE) VALUES(1,'12/07/2003')
INSERT INTO TESTDATE(T_ID,TDATE) VALUES(2,'13/07/2003')
First row is inserted correctly (with date '12/07/2003') but while inserting
second row with date '13/07/2003' it gives following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated
My date is of formate DD/MM/YYYY but SQL Server is reading it as MM/DD/YY.
and not accepting 13 as a valid month.....
How can i resolve this problem?
colgateas900197
|
|
|
|
|
You should write SQL to be culture independent. It is the reponsibility of the Presentation layer to deal with rendering and parsing culture dependent information. You should use the ISO format when writing SQL Scripts e.g. 2007-02-14
|
|
|
|
|
You are right!
But i think i have to discuss my problem in detail!
It was a test code... Actually Database is created in ACCESS and date is stored as text in previous database. While generating reports using access database i use 'Weekday' function to get day i.e SUNDAY for 12/07/2003.
Now, I have fetched data from ACCESS to SQL server and again i put date as string using vnarchar(10) datatype. When i use
datename(dw,DateFieldName) to get name of day on specific date i get an 'Arithmatic Overflow Error'
But when i created this Test code(with datefield with datatype datetime) i have posted it was also creating problem!
I am sorry! i have written too much... but the only thing i want to do is
"Date is stored in SQL table as string with date formate DD/MM/YYYY (13/07/2003) and i want to get the name of day i.e SUNDAY"
Thank you for your response! and again i hope you will help me..
colgateas900197
|
|
|
|
|
You can use "SET DATEFORMAT dmy". However using a date format of "20070214" or "14 feb 2007" means that you don't have to muck-around with the dateformat setting.
Andy
|
|
|
|
|
Thanks!!
It worked.
colgateas900197
|
|
|
|
|
Hi,
At the top of the stored procedure, change the dateformat as given below. Hope this helps 
SYNTAX:
SET DATEFORMAT dmy
GO
For Example:-
SET DATEFORMAT dmy
GO
DECLARE @datevar datetime
SET @datevar = '13/07/2003'
SELECT @datevar
GO
- Harini
|
|
|
|
|
Please see this database diagram. I need your suggestion on the database I have created.
http://www.geocities.com/amit_logicon/diagram.jpg
Thank you
|
|
|
|
|
Very pretty.
I'm not sure what you want us to comment on? The fact that you have odd field names? (Why have VCode when Code will do just as well)? The fact that you have address information in multiple tables, suggesting that this should be in its own table?
Without more information about what you want, there's not a lot that we can do.
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.
|
|
|
|
|
VCode to differentiate in queries that it is Code of Vendor. I need review on its design. I have kept address of different entities in different table, i.e., Address of Vendors, Branches in respective tables.
I was asking for your review because I am having really great difficulties in creating queries.
|
|
|
|
|
amit_logicon wrote: VCode to differentiate in queries that it is Code of Vendor
Yup. Not being an idiot and able to read database diagrams, I figured that out. No, my comment was that you were prefixing fields with an identifier to show the table they were in. It's something I don't like stylistically.
amit_logicon wrote: I have kept address of different entities in different table, i.e., Address of Vendors, Branches in respective tables
See the opening to my comment above. Consider normalising the structure a bit. Remember, if you have a vendor and branch address that are the same, you have duplicated the data. Having a separate address table would help with this.
amit_logicon wrote: I was asking for your review because I am having really great difficulties in creating queries.
What difficulties? I don't see how my looking at the structure will pass the ability on to you to ascertain what Inner Joins, Left Joins, Right Joins, etc to do. Only you know what your queries are going to do, so the ball is firmly in your court.
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.
|
|
|
|
|
Hi,
The scenario is like this, -
Company has several branches and Vendors are associated with branches separately. That means each vendor is attached to one branch only. Now company sales products through Vendors. Products which are being sold have different price on different week days. Vendors when take products from branch either pay cash or submit coupons. These coupons have some price and vendors can submit different types of coupons in different quantity. So suppose they take product of value 200, he can either pay full cash or submit coupons of worh 50 and cash 150. This paid amount can be less that total purchase sometime.
There are different queries like take one Branch Summary for any day -
In this we have to summaries sales and Payment for all branch like this -
------| Products |---------|--Received-|Total|--Day--|-Previous--|--Net--|
Branch| A | B | C| NetValue|Cash|Coupon|Value|Balance|Outstanding|Balance|
Here in Product Main Column we have to just sum the quantity of product sold that day,
Net value is sum of Price*Qnty for that branch,
Total Value is Cash + Coupon value (Coupon Value = Qnty * Price),
Day Balance = NetValue - Total Value
Previous Outstanding = Sum of all previous sale - Sum of all previous Cash Return - Sum of all previous Coupon Price * Qnty + sum of all opening balances of vendors of that branch
NetBalance = Day balance + Previous out standing
I have done this currently using many functions in VB but i want to do this using SQL only.
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
(
