Grant insert, but only allow entering vals in some fields

I have a table with 3 fields:
val1
val2
val3
Is there a way using a role to allow users to create a new record in
the table, but only allowing them to populate the val1 field during the
insert? They should not be allowed to put data in fields val2 and val3
during the insert and they should only be allowed to modify the val1
field.
Thanks!
Chris(cbtechlists@.gmail.com) writes:
> I have a table with 3 fields:
> val1
> val2
> val3
> Is there a way using a role to allow users to create a new record in
> the table, but only allowing them to populate the val1 field during the
> insert? They should not be allowed to put data in fields val2 and val3
> during the insert and they should only be allowed to modify the val1
> field.
You could a create view that exposes the permitted column and let the
users insert into that view rather than directly to the table. Or you
could expose all columns in the table, and have an INSTEAD OF trigger
ignores the non-permitted columns.
... or you could use stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Grabbing first record rather than the record I am trying to find.

I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.

[CODE] public void UpdateMaleHistLbl()
{
SqlConnection conn = new SqlConnection("Server=localhost\\SqlExpress;Database=MyFamTree;" + "Integrated Security=True");
SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @.usrnmeLbl ", conn);
comm.Parameters.AddWithValue("@.usrnmeLbl", usrnmeLbl.Text);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
string usr = reader["username"].ToString();
usr = usr.TrimEnd();
string pss = reader["password"].ToString();
pss = pss.TrimEnd();
if (usrnmeLbl.Text == usr)
{
if (hiddenpassLbl.Text == pss)
{
maleHistLbl.Text = reader["GG_Grandfather"] + " > ";
maleHistLbl.Text += reader["G_Grandfather"] + " > ";
maleHistLbl.Text += reader["Grandfather"] + " > ";
maleHistLbl.Text += reader["Father"] + " > ";
maleHistLbl.Text += reader["Son"] + " > ";
maleHistLbl.Text += reader["Grandson"] + " > ";
maleHistLbl.Text += reader["G_Grandson"] + " > ";
maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />";
}
}
break; //exit out of the loop since user found
}
reader.Close();
conn.Close();
}
}[/CODE]

Thanks in advanceIt was the break statement taking me out too early.sql

Go to the last record

How can I go to the last record of database? I've been having problems after creating a new record, and trying to display it, but I don't know what primary ID it's given, so I dont know how to go the last record. Thanks for the help.

Quote:

Originally Posted by LuiePL

How can I go to the last record of database? I've been having problems after creating a new record, and trying to display it, but I don't know what primary ID it's given, so I dont know how to go the last record. Thanks for the help.

if you want to find the last record in a table (with PK = xx) you can to try with:

select * from tabv where xx = (select max(xx) from tab)|||Many thanks!!!!|||Hi,
select top 1 * from tablename order by column name desc

global temporary tables

When does a global temp table get dropped? i have a stored proc that creates
a global temp table and inserts a record into it.say for eg. i run the store
d
proc first, the temp table gets created.if another user runs the stored proc
now,the temp table is already there,hence the stored proc just uses the same
table and inserts a record into it.now if i end my session,will the temp
table still be available to the other user?
Actually what iam experiencing is, the temp table gets dropped if i end my
session even though another person is still using it.
Thanks in advance.Hi,

>From the BOL:
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for
the life of a single Transact-SQL statement. This means that a global
temporary table is dropped at the completion of the last Transact-SQL
statement that was actively referencing the table when the creating
session ended.
HTH
Barry|||> Actually what iam experiencing is, the temp table gets dropped if i end my
> session even though another person is still using it.
Yes, that is correct. The life of a global temp table is the same as the
life of the session that started it. Which is one reason why they shouldn't
be used for sharing between concurrent users. If this is your intention,
use a real table!
A

Giving Table name as a Variable

Hi guys,

I want to insert record to the table, the table name is a variable. I was tried like this,

declare @.Table_Name as varchar(255)

declare @.MM as varchar(2)

declare @.YY as varchar(4)

set @.MM = month(getdate())

set @.YY = year(getdate())

set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY

INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)

If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message

'Must declare the sclar variable @.Table_Name'

How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.

Arun.

You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec

Code Snippet

Create Table #TestDynSQL

(

Id int,

Name varchar(100),

DOB Datetime

);

Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime

Declare @.SQL as Nvarchar(4000)

Declare @.ParamDecl as Nvarchar(4000)

Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'

Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'

Set @.P_id=1

Set @.P_Name='Mani'

Set @.P_Dob='1979-07-26'

--Highly recommanded to use the sp_executesql

Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob

--or

--Beware of SQL injection while using the EXEC(STATEMENT)

Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'

Exec (@.SQL)

Select * from #TestDynSQL

|||

Mani gave you a good solution using dynamic SQL.

However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

giving more than one condition in selection formula

hi ,
how to use more than one condion in selection formula.for example
i wish to select the record between two date ranges. i am begnner to crystal repots and also how to use images in crystal report.any body have an idea about this pleaz forward ur answer to meYou will have to create two parameters in your select statement. Since you can not use multiplevalues you will have to do something like the following:

Parameter 1: DateFrom
Parameter 2: DateTo

Your select statment will look something like this:

Select ....
From...
Where table.date >= DateFrom AND table.date <= DateTo|||You can also use Selection Forumla

CR.SelectionFormula="Date between {firstDate} and {SecondDate}"