Database

Type mismatch error for date field (MS ACCESS)

21-Oct-07 13:21

DotNetWWW wrote:
the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security?

Adding the rights to the views increases your attack surface. The attack surface is the amount of your system that is potentially open to abuse.

You also say that you have tables, and corresponding views. If the view is defined as SELECT * FROM CorrespondingTable then I don't see any advantage in that.

My personal opinion is that the best solution in most cases is to allow access only to the stored procedures that are required. That way SQL Server has the ability to verify the data before modifying the database, it can also veto and request for information. Whereas access to tables and views gives much wider scope for an application to abuse the database.

DotNetWWW wrote:
Not that I access SQL Server Database from a .Net App.

I wouldn't think the type of application would make much difference.

Upcoming FREE developer events:
* Glasgow: SQL Server Managed Objects AND Reporting Services ...

My website

Trav120-Oct-07 3:00

Trav1

20-Oct-07 3:00

Hi,

I am using VB in MS ACCESS and is not able to update a date field in the database, I am taking the value from a textbox in the form.

This is the query I have been using...

s = "Update Table1 set DOB = """ + Text2.Value + """ where name = """ + Text0.Value + """ "
DoCmd.RunSQL (s)

It is showing the type mismatch error.

Can somebody please help me with this.

Thanks

Re: Type mismatch error for date field (MS ACCESS)

Blue_Boy21-Oct-07 9:27

Blue_Boy

21-Oct-07 9:27

try to use formating if the field is DateTime type

example:
Update Table1 set format('dd.mm.yyyy',DOB) = '" + Text2.Value+"' where [Name]='"+Text0.Value+"'"

I Love SQL

Ambiguous column name 'Primary_ID'. whats wrong??

Vimalsoft(Pty) Ltd18-Oct-07 22:59

Vimalsoft(Pty) Ltd

18-Oct-07 22:59

Hi All the Following Statement in SQL gives me an Error, the Primary_ID is the Primary key for both tables

Ambiguous column name 'Primary_ID'.

<br />
select E1.Primary_ID, E2.Primary_ID,<br />
   IsNull(E1.Lis_key, E2.Lis_key) AS Liskey,  <br />
 IsNull(E1.Attrib_code, E2.Attrib_code) AS Attributecode<br />
from Property_Mass E1<br />
full outer join Property_Home_Match E2<br />
on E1.Lis_key = E2.Lis_key<br />
 and E1.Attrib_code = E2.Attrib_code<br />
where (E1.Primary_ID is null or Primary_ID is null)<br />

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: Ambiguous column name 'Primary_ID'. whats wrong??

GuyThiebaut18-Oct-07 23:05

GuyThiebaut

18-Oct-07 23:05

This is because Primary_ID is contained in Property_Home_Match and Property_Mass.

You need to prefix Primary_ID with E1 or E2 to make it clear which Primary_ID you are referring to.

You always pass failure on the way to success.

Re: Ambiguous column name 'Primary_ID'. whats wrong??

Colin Angus Mackay18-Oct-07 23:05

Re: Ambiguous column name 'Primary_ID'. whats wrong??

18-Oct-07 23:05

In your WHERE clause you reference Primary_ID twice but don't say which table it comes from the second time. Since it exists in more than one table it is ambiguous.

Upcoming FREE developer events:
* Glasgow: SQL Server Managed Objects AND Reporting Services ...

My website

Vimalsoft(Pty) Ltd18-Oct-07 23:10

Vimalsoft(Pty) Ltd

18-Oct-07 23:10

Ohh

Sorry Guys, my mistake, it will never happen again.

Am Sorry

Thanks

Re: Ambiguous column name 'Primary_ID'. whats wrong??

Colin Angus Mackay18-Oct-07 23:21

18-Oct-07 23:21

Don't worry about it. About 10-15 years ago I once sat for two hours trying to figure out why my C++ application wouldn't compile. I'd missed a semi-colon but the compiler had thrown up 100 completely unrelated errors.

Upcoming FREE developer events:
* Glasgow: SQL Server Managed Objects AND Reporting Services ...

My website

@ and @@ parameters

Sonia Gupta18-Oct-07 22:43

18-Oct-07 22:43

what is the basic difference between @a and @@a type parameter?

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Joe18-Oct-07 23:42

Joe

18-Oct-07 23:42

You may like to make a quick reference from here[^]

Sonia Gupta18-Oct-07 23:52

18-Oct-07 23:52

thanQ

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Pete O'Hanlon21-Oct-07 10:06

Pete O'Hanlon

21-Oct-07 10:06

Sonia - will you please buy some books? And will you start off by learning to use google? This information is readily available with a simple search.

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

squattyarun21-Oct-07 23:01

squattyarun

21-Oct-07 23:01

@ parameter are defined by user they are local variables in Sql. but @@ variables are global and defined by system only....

cursors in functions

Sonia Gupta18-Oct-07 22:40

18-Oct-07 22:40

CREATE FUNCTION [dbo].[waste] ()
RETURNS table

AS
declare @a int
declare @b int
BEGIN
declare cur cursor for select a1,a2 from a
open cur
fetch next from a into @a,@b
while @@FETCH_STATUS =0
BEGIN
SELECT @a as a1 , @b as a2
fetch next from a into @a , @b
END
close cur
deallocate cur
return
END
where is the bug lying?

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Krish - KP18-Oct-07 23:24

Krish - KP

18-Oct-07 23:24

1. remove BEGINafter the Decleration of A & B
2. and include before before A & B

Regards
KP

Sonia Gupta18-Oct-07 23:35

18-Oct-07 23:35

CREATE FUNCTION [dbo].[waste] ()
RETURNS table
AS
declare @a int
declare @b int

declare cur cursor for select a1,a2 from a
open cur
fetch next from a into @a,@b
while @@FETCH_STATUS =0
BEGIN
SELECT @a as a1 , @b as a2
fetch next from a into @a , @b
END
close cur
deallocate cur
return
GO

Now it is saying
incorect syntax near the keyword declare.
return statement statement i scalar valued function must include an argument

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Rocky#19-Oct-07 0:55

Rocky#

19-Oct-07 0:55

youre declaring that the fn will return a table and look at the last line

Sonia Gupta wrote:
return
GO

whats happening here??

Rocky

You can't climb up a ladder with your hands in your pockets.

neeraj_indianic19-Oct-07 19:25

neeraj_indianic

19-Oct-07 19:25

You have not specified that which type of function you want to create means scalar valued function or table valued function.

I suppose that you want to create table valued function. For that your code is wrong in the starting line.
Look at the below code. Hope it will guide you.

CREATE FUNCTION [dbo].[waste]()
RETURNS @student1 TABLE
(
student_rec int,
St_name nchar(10)
)
AS
Begin
declare @a int
declare @b nchar(10)
declare cur cursor for select Id,name from student
open cur
fetch next from cur into @a,@b
while @@FETCH_STATUS =0
BEGIN
INSERT @student1 values(@a,@b)
--SELECT @a as a1 , @b as a2
fetch next from cur into @a , @b
END
close cur
deallocate cur
RETURN
end

Neeraj Gupta
IndiaNIC Infotech Ltd.

CORBA , MSP

hellogany18-Oct-07 17:36

hellogany

18-Oct-07 17:36

hi all,
i m ganesh new to code project....

i jus want to know all information about CORBA and MSP for database connection. Is anyone to help me out? n the details needed asap...thnks in advance...

GANESH

Re: CORBA , MSP

Colin Angus Mackay18-Oct-07 22:37