Re: SQL variable in MS Access - Database Discussion Boards

Access of Office 2007 as a DB in .NET

Tzumer Edo3-Jun-07 4:27

3-Jun-07 4:27

Hi guys,

I have a website that works with an Access DB,
now, I've moved it to a computer that has "only"
office 2007, and when the site tries to open a connection
to the DB, I get :
'Could not find installable ISAM.' message

Anyone ?

Regards,
Tzumer Edo.

Re: Access of Office 2007 as a DB in .NET

originSH3-Jun-07 23:30

originSH

3-Jun-07 23:30

It could be that you connection string is wrong or the MDAC installation o nthat machine is b0rked.

Theres plenty of hits to help you debug on google:

http://www.google.co.uk/search?hl=en&q=Could+not+find+installable+ISAM.&meta=[^]

Also you cna check your connection string is correct here:

http://www.connectionstrings.com/[^]

Specificly:

http://www.connectionstrings.com/?carrier=access2007[^]

Adding a new column is DBNULL

steve_rm2-Jun-07 4:06

steve_rm

2-Jun-07 4:06

Hello,

VS 2005

I am developing the database application. This is a live database and is being used by the customer.

I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.

There are already over a 1000 rows in this table. When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:
"The value for column 'DateStarted' in table 'IncidentTask' is DBNull."

The method for inserting is:
Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted

What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:
UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)

Many thanks for any suggestions,

Steve

Re: Adding a new column is DBNULL

Rob Graham2-Jun-07 5:53

Rob Graham

2-Jun-07 5:53

Or you could specify a default value for the date and total hours in the alter table script that adds the new columns...the DateStarted should probably not allow nulls in the first place.

Alter table [IncidentTask] add Datestart Datetime DEFAULT 20050101 NOT NULL, TotalHours INT DEFAULT 0 NOT NULL

Re: mysql vs sqlserver

Rob Graham2-Jun-07 4:55

Rob Graham

2-Jun-07 4:55

Please read "how to get an answer to your question" before posting questions you should have answered for yourself.
Google is your friend: http://www.google.com/search?q=mysql+vs+sql+server+2005[^]

SQL variable in MS Access

Blue_Boy2-Jun-07 2:25

Blue_Boy

2-Jun-07 2:25

Can I declare variables in MS Access same as we can do in T-SQL.

Example:

declare @i  as Integer<br />
set @i=1;<br />
<br />
select tablename.ColName where Col.ID=@i

I Love SQL

Re: SQL variable in MS Access

Krish - KP2-Jun-07 2:30

Krish - KP

2-Jun-07 2:30

VBA is used in MS Access so this usage is not possible.

Regards
KP

Re: SQL variable in MS Access

Blue_Boy2-Jun-07 2:50

Blue_Boy

2-Jun-07 2:50

Thanks , I will try to find another solution without using t-sql in ms access.

Regards

I Love SQL

Re: SQL variable in MS Access

Dave Kreskowiak2-Jun-07 9:28

Dave Kreskowiak

2-Jun-07 9:28

Access doesn't support true stored procedures like this. Anything you do like this has to be implemented in your own application code.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: SQL variable in MS Access

Ilya Verbitskiy5-Jun-07 4:49

Ilya Verbitskiy

5-Jun-07 4:49

Access doesn't support T-SQL, not only stored procedures.

Select statement......,

HemMagesh1-Jun-07 20:11

HemMagesh

1-Jun-07 20:11

SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblTimeCardmain.carddate,
dbo.tblTimeCardmain.reportingtime, dbo.tblTimeCardmain.leavingtime, dbo.tblsalarydetails.Whrs,
dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid FROM dbo.tblEmployeeMaster INNER JOIN
dbo.tblTimeCardmain ON dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid AND
dbo.tblEmployeeMaster.projectId = dbo.tblTimeCardmain.projectid INNER JOIN
dbo.tblsalarydetails ON dbo.tblTimeCardmain.empid = dbo.tblsalarydetails.EmpId INNER JOIN
dbo.tblMonthHourDetails ON dbo.tblTimeCardmain.empid = dbo.tblMonthHourDetails.EmpId AND
dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate AND
dbo.tblTimeCardmain.projectid = dbo.tblMonthHourDetails.ProjectId
WHERE (dbo.tblTimeCardmain.carddate = CONVERT(DATETIME, '2007-06-04 00:00:00', 101)) AND
(dbo.tblTimeCardmain.app1 = 1) AND (dbo.tblTimeCardmain.projectid = 100)

Hi i amn't well in sqlserver so hlp me...,
See the above query in that i am checkng "dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"...,
if both dates are equal then those dates cant display how to do that?
same time it want to check other conditions also...,
Plz hlp me...,

Mag

Re: Select statement......,

Krish - KP2-Jun-07 2:28

Krish - KP

2-Jun-07 2:28

the table structure is not fully understood.
however, from remove join on date ...
"dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"
and use it in where with not equal
"dbo.tblTimeCardmain.carddate <> dbo.tblMonthHourDetails.SDate"

check whether is solves

Regards
KP

Stored procedure with a variable number of parameters

Vadim Tabakman1-Jun-07 18:32

Vadim Tabakman

1-Jun-07 18:32

Hi all,

I need to know if this is possible, and if it is, whats would a stored proc like that look like.

What I'm looking for is a query that the stored proc will run, which will like the following :

SELECT * FROM myTable <br />
WHERE<br />
(<br />
	SELECT COUNT(*) FROM myOtherTable<br />
	WHERE (myOTID = @param1 OR<br />
		  myOTID = @param2 OR<br />
		  myOTID = @param3 OR [could me more ORs]) AND (myOtherTable.myID= myTable.myID)<br />
) = 3

Any help would be much appreciated...

Jubjub

"If you're too careful, your whole life can become a f---in' grind." - Mike McD (Rounders)

Re: Stored procedure with a variable number of parameters

Arun.Immanuel2-Jun-07 7:56

Arun.Immanuel

2-Jun-07 7:56

Yes, it is possible.

There are 3 ways to do this as mentioned here :

Click Here[^]

Regards,
Arun Kumar.A

Re: Stored procedure with a variable number of parameters [modified]

Vadim Tabakman2-Jun-07 13:27

Vadim Tabakman

2-Jun-07 13:27

~~Thanx arul, that helps a lot.~~

That helps more than a lot. I tweaked the code that Mark J. Miller wrote in that article to suit my needs and it works a treat. I appreciate the help Arul.

Looks like I have malfunctions with my web surfing abilities Smile | :)

.

-- modified at 20:38 Saturday 2nd June, 2007

"If you're too careful, your whole life can become a f---in' grind." - Mike McD (Rounders)

Using Strongly typed Datasets and a Data Access Layer

Drathmar1-Jun-07 7:29

Drathmar

1-Jun-07 7:29

I've built some strongly typed data sets, they work pretty good, but they didn't do everything I wanted them to do.

So I make a class Library and called it my DAL(Data access layer) in it, I had the data adapters, and would do some processing and then make some calls to the db through the functions set in the strongly typed datasets.

The whole system seems to be working pretty good, but now I have someone else working with me on the page stuff. At first he created his own instance of the data adapters and bypassed my DAL.

Does anyone know a good way of preventing the DAL from being bypassed? I don't know if there's a way to change the scope of a strongly typed data set so that only one class could access it.

Kris

Re: Using Strongly typed Datasets and a Data Access Layer

Colin Angus Mackay1-Jun-07 9:59

Colin Angus Mackay

1-Jun-07 9:59

Drathmar wrote:
Does anyone know a good way of preventing the DAL from being bypassed?

It is called discipline.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Using Strongly typed Datasets and a Data Access Layer

Paul Conrad1-Jun-07 10:14

Paul Conrad

1-Jun-07 10:14

Colin Angus Mackay wrote:
Drathmar wrote:
Does anyone know a good way of preventing the DAL from being bypassed?

It is called discipline.

Sometimes the short answers are the best Big Grin | :-D

Can't search through Date

Gamzun1-Jun-07 5:52

Gamzun

1-Jun-07 5:52

im using this query

select date from table where date='5/7/2007'

it returns '0' rows, but when I run this follwoing query it returns 2 rows

select date from table where date='5/7/2007 1:30:00 PM'

i don't want to search with time how can i do that?

i even try this

select date from table where date='5/7/2007%'

but it gives error as it cant convert smalldatetime to string any solution.

Re: Can't search through Date

leckey1-Jun-07 6:14

leckey

1-Jun-07 6:14

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html[^]

I've used this reference for formating in the past. Hope it helps.

__________________
Bob is my homeboy.

Re: Can't search through Date

Mike Dimmick1-Jun-07 8:03

Mike Dimmick

1-Jun-07 8:03

Everyone seems to be doing this today!

Firstly - use ISO 8601 format. This format - yyyyMMdd - is not subject to misinterpretation by different locale settings.

Secondly, dates have a time part. If you want any time on 7 May 2007, use:

WHERE date >= '20070507' AND date < '20070508'

If you don't specify a time part for a date literal, the time part is set to 00:00:00 (i.e. 12am).

Stability. What an interesting concept. -- Chris Maunder

Re: Can't search through Date

Gamzun1-Jun-07 21:47

Gamzun

1-Jun-07 21:47

Thanks Mike,
I'll be much oblige if you can tell me how to set that format? n where should set this ISO format either in OS or MSSQL Management Studio?
Thanks Again

Re: Can't search through Date

Rob Graham2-Jun-07 4:50

Rob Graham

2-Jun-07 4:50

gamzun wrote:
n where should set this ISO format either in OS or MSSQL Management Studio?

YOU must employ the format in YOUR application / queries. This is not an OS or Server task. It is up to the application to format user entered dates properly before submitting them to the server in queries....

Re: Can't search through Date

P_pk3-Jun-07 19:39

P_pk

3-Jun-07 19:39

Use convert statement to the format required and use style 105 to cut date...
Remember to convert date to the table format while selecting... '%' Sign can be used only with a string and if ur date format in the table is datetime then that cant be done

PPK

scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

steve_rm1-Jun-07 3:03

steve_rm

1-Jun-07 3:03

Hello

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted.
23/05/2007 00:00:00

I tried the following

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.