|
Look into date function
SELECT * FROM TABLE WHERE EndDate < GETDATE()
SELECT * FROM TABLE WHERE DATEDIFF(d,Startdate,EndDate) > 10
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for code project team .it helps me alot...
|
|
|
|
|
hello
can any one give me a sample code of
creating a function or procedure in oracle
and then use it to return a datatable with tableadapter
thank you
|
|
|
|
|
What you have asked for is not a simple request, some of the requirements are.
Create an oracle database
create a table in the database
create a stored procedure to return some data from that table
create a C# project to serve as the client
create a connection to the oracle database
create a method of calling the stored procedure
create a process to display the data returned by the method
If you have a look through the articles there are many that do precicely that, most are going to SQL Server but that is only a connection change.
Do a search on northwind to see if any articles use the sample database from MS
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Everyone,
I am using MS Access database with VB.NET. My problem is very simple. It is as follows:
I have two tables "Class" ( with a field "Class") & "Section"( with a field "Section"). I can fill both this tables of MS Access database programmaticaly thorough VB.NET. There are two textboxes t1 & t2 on the Form to fill corresponding fields "Class" & "Section" of the tables.
Now what I want is to fill "Class" field and then "Section" field in such a way that for each entry of "Class" field, there will be entry in "Section" field.
e.g. Insert class 1 in textbox t1
Insert setion A in textbox t2
While save, section A must be saved under class 1. Similarly, if I
Insert class 1 in textbox t1
Insert section B in textbox t2
then, section B should come under class 1.
Similarly, if I insert class 2 and then section A & section B, then both this sections should get stored under class 2.
Overall, there should not be any conflict between class and section entries. i.e. each class entries has its corresponding section entries.
Hope My issue is clearly. Can someone help me how to do this?
Any example would be appreciated.
Regards
R.S.
|
|
|
|
|
I think you are referring to referential integrity and how to create and maintain a relationship between class and section.
In your tables you need to have key fields something like this
ClassID
Class
SectionID
ClassID
Section
ClassID and SectionID are auto increment field and are the primary keys to their tables. When you insert a record in the Class table you should get back the ClassID from the insert method (in SQL Server it is with @@Identity or @Scope_Identity).
You now need to use this ClassID when inserting the record into the Section table. Generally these type of linked inserts are wrapped in a transaction so if a Class insert fails then you do not insert a Section record with no related Class record.
I suggest you get a book on databases and start working through the examples, you will struggle if you try to use a forum for your primary learning tool.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks....Any sample example/link will be appriciated.
|
|
|
|
|
I'm no professional, so I am aware that I may be missing out on a better method but here is what I am trying to do.
int Market_Value;
if (this.MarketValue.Text == "") Market_Value = -1;
else Market_Value = Int32.Parse(this.MarketValue.Text);
TableAdapter.InsertQuery(Market_Value == -1 ? Market_Value : null)
TableAdapter.Update(DataSet);
*lots of code left out
This doesn't compile because of the line : Market_Value == -1 ? Market_Value : null, its telling me that I cannot set an int to null
Perhaps I could handle this in my InsertQuery? I'm super new to SQL so I cant see how... heres that function:
INSERT INTO [dbo].[THE DATABASE]
([Market Value])
VALUES (@Market_Value)
I guess I could make the conditional statement in there somehow... but how?   .. need to learn more SQL 
I just dont know, any insight into a better way would be greatly appreciated!
|
|
|
|
|
This is a c# issue and has nothing to do with Sql or the database. Your code will not compile because the Conditional Operator (?) is typed by the test values as an int and it cannot convert null to an int. Try changing Market_Value to a nullable int (int?).
|
|
|
|
|
duh.. yea I always seem to hit the wrong forum.. thanks for your help!!! =)
|
|
|
|
|
hello
i used to work with c# and sql server
but now i am working with oracle
now I want to return data table from procedure
i used to create sql proceduer "select * from tablename"
and then i use datatable with dataadapter to return the rows of table
but it does not work with oracle
what can i do???????????????!!!!!!!!!!!!!
|
|
|
|
|
What do you mean by 'it does not work'; do you get an error or no data or what?
|
|
|
|
|
the procedure did not created (syntax error)
sum frien told me to use function
but it did not work
|
|
|
|
|
|
|
You have to use Package insted of procedure.
Search in oracle or MSDN to get help about package.
here is an example of package (Collected from MSDN):
Drop table Emp;
drop table DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);
INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');
CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
HIREDATE DATE NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);
INSERT INTO Emp VALUES(123,'Bob','Sales',555,'28-JAN-79',35000,12,33);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,'12-MAY-83',42000,12,11);
INSERT INTO Emp VALUES(234,'Mary','Account',555,'14-AUG-82',33000,12,22);
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);
END curspkg_join;
/
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
Convert the following VB Code into C# to execute that package:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim x As Exception
Dim Ds As New DataSet()
Dim Oraclecon As New OracleConnection("Server=giit;Uid=scott;Pwd=tiger")
Oraclecon.Open()
Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "curspkg_join.open_join_cursor1"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("n_empno", OracleType.Number)).Value = TextBox1.Text
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
'MyDA is your dataadapter
Dim MyDA As New OracleDataAdapter(myCMD)
Try
MyDA.Fill(Ds)
Catch x
MessageBox.Show(x.Message.ToString)
End Try
DataGrid1.DataSource = Ds.Tables(0)
Ds.WriteXml("C:\packqry.xml")
Oraclecon.Close()
End Sub
|
|
|
|
|
thanks that is exactly what i want
|
|
|
|
|
I create some tables (the same ones mentioned below), but when i try to do select * from tablename (in a query window in SQL Manager Express), it tells me this:
Invalid object name 'tableName'.
I can see the table there, and I can modify it, but I can't run a query on it. I've even tried putting "dbo." at the beginning of the table name, but it still gives that error. Is it a database permissions thing? I haven't expressly set any permissions because I figured I didn't have to.
SOLUTION ==============
I guess I didn't have the database selected in the Object Explorer when I created the query window...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Saturday, March 20, 2010 9:49 AM
|
|
|
|
|
you can set all kinds of user permissions, such as create table, create record, read table, etc. I don't know the details, Management Studio should offer a way to see them all. Myself, I mostly use MySQL and MyPHPAdmin.
FWIW: one way to get into trouble is by using a reserved word as a table name or field name; error messages aren't always very informative here.
|
|
|
|
|
None of my table names are reserved words... :/
Do I *need* to set permissions? Like I said, I can add/modify tables and stored procs, but I can't run a simple query in a query window.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: Do I *need* to set permissions?
Guess so. In PHPMyAdmin, when you create a database, you have to specify what permissions you give everyone, based on username and hostname (e.g. you can restrict a user to localhost only, no remote); there are over 20 checkboxes detailing all privileges.
Are you sure you are logged into your SQL Manager with the same username/password your app is running? (Not that you have to, but that should yield the same privileges).
|
|
|
|
|
Solution - See original post in this thread
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I have a table that has multiple instances of a given item, and the date on which that record was inserted into the table.
How do I retrieve the newest copy of all unique items in the table?
I'm an almost complete newbie regarding SQL, so please don't be harsh.
SOLUTION ===========
select field1, max(field2)
from table
group by field1
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Hi John,
try something like:
SELECT TOP 1 selectedFields FROM tablename WHERE matchConditions ORDER BY createdField DESC
|
|
|
|
|
I'd love to try that, but I can't even run a simple query (see next question above this thread).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
