Database

DATE COMPARISON In JET SQL QUERY

27-Sep-07 18:48

The appropritate place for your post is the jobs board[^]

Jerry0288 wrote:
Include city/state of location

City: Glasgow
State of location: Needs a bit of a clean.

Upcoming FREE developer events:
* Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Nadia Monalisa27-Sep-07 12:08

Nadia Monalisa

27-Sep-07 12:08

Hi.,

I want to get a record from MS Access where a datetime field is equal to a C# DateTime type's ToShortDateString(), for example,

DateTime myTime = DateTime.Now;
string query = "SELECT * FROM myTable WHERE [date] = '" + myTime.ToShortDateString() + "'";

Now, is it correct syntax or do I need any Convert function in JET SQL ?

Is it correct to compare with C# DateTime's ToShortDateString() formatted value with JET SQL Date type field ?

I want to compare only the Date part, no Time stamp. I want to get all records for the same date I query. But if it compares also with the time part, then In fact I will not get any record in return because it is not useful to get a records for a particular second.

Would you please help me.

Thanks.

Calling a web service from SQL [modified]

DerekFL27-Sep-07 7:24

DerekFL

27-Sep-07 7:24

Need a method to call a web service from SQL passing the web service billing info and receiving back the results of the charges in xml. Its a asp page with a CustomerID and Amount parameter and returns xml for success or fail

Example

http://CreditCardSvr/processes/chargecard.aspx?customer_id=1022&amount=5.75

Response

How to set ExitCode for SQL Job

alex.almeida27-Sep-07 6:04

alex.almeida

27-Sep-07 6:04

Hello Everybody

I developed a console app that imports CSVs files to DataBase.

Well, I need that app runs each 2 min, then I used SQL Server Agent to create a job to do that.

But when my app raise a exception, the job can't recognize this error and enter in continuous process (looping). So the job can't execute next schedule.

When app runs successful, the ExitCode == 0.

See code below

try
{
  .... code
  Environment.ExitCode = 0; 
} 
catch(Exception e)
{
  Environment.ExitCode = -1; 
  process.Kill();
}
finally
{
  process.Close();
}

Re: How to set ExitCode for SQL Job

DerekFL27-Sep-07 8:41

DerekFL

27-Sep-07 8:41

Have you tried out SSIS. It's pretty simple to use and you can send rows from your csv file that cause insert errors to an error file. See http://www.mridey.com/blog/Lists/Posts/Post.aspx?List=321eab0d%2De4d2%2D4523%2D914e%2D128a6688109f&ID=7

database select query probem

Nishad8527-Sep-07 4:49

Nishad85

27-Sep-07 4:49

Hi I have 3 tables in my database. Students , SessionTable and Campus.
Table campus have two fields
CampusId and CampusName

and SessionTable have two fields
SessionId and Sessioname

and the tble student have12 fields
they are
studentId,Name,DOB,Grade,Phone,City, campus1,Session1, campus2,Session2, campus3,Session3

I want tao take all the rows of following fields from tables

STUDENTNAME,STUDENTID,DOB,GRADE,PHONE,CITY,Campus.CAMPUSNAME AS CAMPUS1,SessionTable.SESSIONNAME AS SESSION1,CAMPUS.CAMPUSNAME AS CAMPUS2,SessionTable.SESSIONNAME AS SESSION2,CAMPUS.CAMPUSNAME AS CAMPUS3,SessionTable.SESSIONNAME AS SESSION3

I wrote a query like this . But does not takes all the rows from Database

SELECT Students.StudentID, CampusList_1.Name As Campus1, CampusList_2.Name As Campus2 , CampusList_3.Name As Campus3, Students.Session1,Students.Session2,Students.Session3,Students.StudentName as FirstName,Students.StudentLastName as LastName, Students.DateOfBirth, Students.Street,Students.AppartmentNo,Students.City, Students.State, Students.ZipCode, Students.Phone, Students.ParentFirstName, Students.ParentLastName, Students.SSN, Students.SchoolYear, Students.Semester, iif(Students.Sibling,'Yes','No')as Sibling,iif(Students.Accepted,'Yes','No')as Accepted,iif(Students.Waitlisted,'Yes','No')As Waitlisted ,Students.ApplicationDate,Students.CurrentSchool,Students.AltenatePhone From ((( Students LEFT JOIN CampusList AS CampusList_1 ON Students.Campus1 = CampusList_1.CampusID) LEFT JOIN CampusList AS CampusList_2 ON Students.Campus2 = CampusList_2.CampusID) LEFT JOIN CampusList AS CampusList_3 ON Students.Campus3 = CampusList_3.CampusID )LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session1 LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session2 LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session3

Pls help me on this issue..........

Re: database select query probem

andyharman27-Sep-07 7:00

andyharman

27-Sep-07 7:00

Try:

SELECT Students.StudentID, CampusList_1.Name As Campus1,
   CampusList_2.Name As Campus2, CampusList_3.Name As Campus3,
   Students.Session1, Students.Session2, Students.Session3,
   Students.StudentName as FirstName,
   Students.StudentLastName as LastName,
   Students.DateOfBirth, Students.Street, Students.AppartmentNo,
   Students.City, Students.State, Students.ZipCode, Students.Phone,
   Students.ParentFirstName, Students.ParentLastName, Students.SSN,
   Students.SchoolYear, Students.Semester,
   iif(Students.Sibling, 'Yes', 'No') as Sibling,
   iif(Students.Accepted, 'Yes', 'No') as Accepted,
   iif(Students.Waitlisted, 'Yes', 'No') As Waitlisted,
   Students.ApplicationDate, Students.CurrentSchool,
   Students.AltenatePhone
FROM Students
LEFT JOIN CampusList AS CampusList_1
   ON Students.Campus1 = CampusList_1.CampusID
LEFT JOIN CampusList AS CampusList_2
   ON Students.Campus2 = CampusList_2.CampusID
LEFT JOIN CampusList AS CampusList_3
   ON Students.Campus3 = CampusList_3.CampusID
LEFT JOIN SessionTable AS Session_1
   ON Session_1.SessionId = Students.Session1
LEFT JOIN SessionTable AS Session_2
   ON Session_2.SessionId = Students.Session2
LEFT JOIN SessionTable AS Session_3
   ON Session_3.SessionId = Students.Session3
ORDER BY Students.StudentID

How to connect two database in an single application

Exelioindia27-Sep-07 3:13

Exelioindia

27-Sep-07 3:13

Hi all,

I am developing an windows application using .net. In that i am gettting data's from two database, how to write connection string to these databases commenly? also how to get data using an single query from those two database.

For Eg:

database1:
select id,name from table1;

database2
select id,name from table2;

Both the tables have same fields but diff table and diff database.

Plz any one suggest me how to write as single query.

Thanks in advance

Know is Drop, Unknown is Ocean

Re: How to connect two database in an single application

Pete O'Hanlon27-Sep-07 3:35

Re: How to connect two database in an single application

27-Sep-07 3:35

Example:

SELECT field1, field2 FROM mydatabase.dbo.table inner join myotherdatabase.dbo.table2 on field3 = field4

.The parts before the .dbo refer to the two databases.

Deja View - the feeling that you've seen this post before.

Exelioindia27-Sep-07 4:23

Exelioindia

27-Sep-07 4:23

Hi,

Thankyou very much for your kind suggestion.

Know is Drop, Unknown is Ocean

Re: How to connect two database in an single application

Pete O'Hanlon27-Sep-07 4:29

Re: How to connect two database in an single application

27-Sep-07 4:29

No problem. Big Grin | :-D

Deja View - the feeling that you've seen this post before.

Shyam.r27-Sep-07 3:39

Shyam.r

27-Sep-07 3:39

u can use union,since both table has same structure

Re: How to connect two database in an single application

Pete O'Hanlon27-Sep-07 4:08

Re: How to connect two database in an single application

27-Sep-07 4:08

Shyam.r wrote:
u can use union,since both table has same structure

Eh? What on earth are you blathering on about? Union? He wanted to know how to link together two tables in different databases.

Deja View - the feeling that you've seen this post before.

PIEBALDconsult27-Sep-07 7:36

PIEBALDconsult

27-Sep-07 7:36

Well, no, he wanted to write one connection string to two databases. He wasn't clear on what he wanted to do with it. Or even what the two database systems are. I would have asked for more information, but as he's satisfied with the answers so far, I won't bother. Smile | :)

Re: How to connect two database in an single application

Pete O'Hanlon27-Sep-07 22:01

Re: How to connect two database in an single application

27-Sep-07 22:01

Actually, he was clear - he stated that he also wanted to get the data with a single query.

Deja View - the feeling that you've seen this post before.

Colin Angus Mackay27-Sep-07 4:37

Re: How to connect two database in an single application

27-Sep-07 4:37

Exelioindia wrote:
how to write connection string to these databases commenly?

If both databases exist on the same SQL Server then just use the connection string to either one. NOTE: The login will have to be associated to a user on each database with sufficient right to perform the actions you want to perform.

Exelioindia wrote:
also how to get data using an single query from those two database.

When you reference a table you can use a fully, or partially qualified name.

The full naming convention is:
[server].[database].[schema].[table]

You can ingore the server part.

Upcoming FREE developer events:
* Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Gamzun29-Sep-07 18:37

Gamzun

29-Sep-07 18:37

select * from [Database1].[dbo].[table1],[Database2].[dbo].[table2]

in this way u can get ur data from two databases

now about the connection i didn't try doing this but im quite sure that if you connect to any one of the database (in ur case [Database1] or [Database2] ) u'll probably get the result.
Try it
happy experimenting! Smile | :)

Sql Injection in SqlServer2005

subbu.sk27-Sep-07 2:08

subbu.sk

27-Sep-07 2:08

Hai all,
can any one tell me what is SqlInjection in sql Server2005.
i Don't know any thing about it.why should we go for SqlInjection and
in which situations.
and what r the authentications done there.

Thanks
Subbu.

Colin Angus Mackay27-Sep-07 2:23

27-Sep-07 2:23

subbu.sk wrote:
can any one tell me what is SqlInjection in sql Server2005

It isn't specific to SQL Server 2005. It affects all databases.

subbu.sk wrote:
why should we go for SqlInjection and
in which situations.

I wouldn't advise you go for it at all. It is security hole, not something to aspire to.

Please read: SQL Injection Attacks and some Tips on How to Prevent Them[^]

Upcoming FREE developer events:
* Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Christian Graus27-Sep-07 2:24

Christian Graus

27-Sep-07 2:24

LOL - like I could be faster than you on a SQL injection question...

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Colin Angus Mackay27-Sep-07 2:28

27-Sep-07 2:28

Christian Graus wrote:
like I could be faster than you on a SQL injection question...

I have to change my signature to "I beat Christian '39904 posts and counting' Graus to answering a question on the Code Project forums" Big Grin | :-D

I beat Christian '39904 posts and counting' Graus to answering a question on the Code Project forums
My website

Pete O'Hanlon27-Sep-07 2:30

27-Sep-07 2:30

Christian Graus wrote:
like I could be faster than you on a SQL injection question...

He's like a greasy whippet on steroids when it comes to SQL Injection posts.

Deja View - the feeling that you've seen this post before.