can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

can view be dynamically filtered like an MSAccess query?

In MSAccess you can dynamically filter a query by adding square brackets [ ]
to the criteria section and a prompt inside the brackets [enter filter code]
.
Then you can open the query directly and be prompted for a filter. I tried
this in the view without any success.
I was approached today by an End user who uses an Access ADP to interface
with our sql server. The user needs to pull/look at data from a large
table, and currently there is no mechanism for filtering the respective tabl
e
in the Access ADP. Since several users are using the same ADP, I don't want
to modify it. I was thinking of creating a view that could be opened from
the ADP. But rather than having the user call me everytime he needs to
change the criteria, is there a way to make the view dynamic?
Thanks,
RichRich,
You can use an inline table-valued function. You can not pass parameters to
a view.
Inline User-Defined Functions
http://msdn.microsoft.com/library/d...>
_08_73lf.asp
AMB
"Rich" wrote:

> In MSAccess you can dynamically filter a query by adding square brackets [
]
> to the criteria section and a prompt inside the brackets [enter filter cod
e].
> Then you can open the query directly and be prompted for a filter. I tri
ed
> this in the view without any success.
> I was approached today by an End user who uses an Access ADP to interface
> with our sql server. The user needs to pull/look at data from a large
> table, and currently there is no mechanism for filtering the respective ta
ble
> in the Access ADP. Since several users are using the same ADP, I don't wa
nt
> to modify it. I was thinking of creating a view that could be opened from
> the ADP. But rather than having the user call me everytime he needs to
> change the criteria, is there a way to make the view dynamic?
> Thanks,
> Rich|||Thanks - very . I will give this a shot. The only thing is that - afte
r
reading the article - it looks like the user needs to write a sql statement.
If the user is using an Access ADP - where does he write the statement to us
e
the inline function? In the article it looked like the user had QA on his
desktop.
"Alejandro Mesa" wrote:
> Rich,
> You can use an inline table-valued function. You can not pass parameters t
o
> a view.
> Inline User-Defined Functions
> http://msdn.microsoft.com/library/d...
es_08_73lf.asp
>
> AMB
> "Rich" wrote:
>|||I figured it out. The function will show up in the user interface. The use
r
can invoke the function the same way as a query in MSAccess and will be
prompted for input. Very . Thanks again for the suggestion.
"Rich" wrote:
> Thanks - very . I will give this a shot. The only thing is that - af
ter
> reading the article - it looks like the user needs to write a sql statemen
t.
> If the user is using an Access ADP - where does he write the statement to
use
> the inline function? In the article it looked like the user had QA on his
> desktop.
> "Alejandro Mesa" wrote:
>

Can someone help with this T-SQL Real quick?

Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:

(@.StudioINT =NULL,@.Br1INT =NULL,@.Br2INT =NULL,@.Br3INT =NULL,@.Br4INT =NULL,@.OverBr4INT =NULL,@.CondoINT =NULL,@.ListingTypevarchar(10) =NULL,@.WindowAirINT =NULL,@.CentralACINT =NULL,@.BalconyDeckPatioINT =NULL,@.UseOfYardINT =NULL,@.DishwasherINT =NULL,@.WasherDryerINT =NULL,@.FireplaceINT =NULL,@.EIKINT =NULL,@.HardwoodFloorsINT =NULL,@.BroadbandNetINT =NULL,@.TVINT =NULL,@.ThermostatINT =NULL,@.LandlordNotPresentINT =NULL,@.SmokingINT =NULL,@.NoPetsAllowedINT =NULL,@.CatINT =NULL,@.MoreCatsINT =NULL,@.SmallDogINT =NULL,@.LargeDogsINT =NULL,@.DoorpersonINT =NULL,@.IngroundPoolINT =NULL,@.AboveGroundPoolINT =NULL,@.ElevatorINT =NULL,@.UseOfGarageINT =NULL,@.LaundryFacilitiesINT =NULL,@.HealthCenterINT =NULL,@.StorageAreasINT =NULL,@.WheelchairAccessINT =NULL,@.BusinessCentersINT =NULL,@.RentChargeMinINT =NULL,@.RentChargeMaxINT =NULL,@.DebugBIT = 1)AS SET NOCOUNT ONDECLARE @.SQLVARCHAR(8000)SET @.SQL ='SELECTr.REListingID,r.REListingDate,r.Username,r.ZipCode,r.ListingType,r.StudioFlag,r.BRFlag1,r.BRFlag2,r.BRFlag3,r.BRFlag4,r.OverBRFlag4,r.CondoFlag,a.WindowAir,a.CentralAir,a.BalconyDeckPatio,a.UseOfYard,a.Dishwasher,a.WasherDryer,a.Fireplace,a.EIK,a.HardwoodFloors,a.BroadbandNet,a.TV,a.Thermostat,a.LandlordNotPresent,a.Smoking,a.NoPetsAllowed,a.Cat,a.MoreCats,a.SmallDog,a.LargeDogs,a.Doorperson,a.IngroundPool,a.AboveGroundPool,a.Elevator,a.UseOfGarage,a.LaundryFacilities,a.HealthCenter,a.StorageAreas,a.WheelchairAccess,a.BusinessCenters,a.RentCharge,a.RentFrequencyFROMdb_REListings as rINNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingIDWHERE1 = 1'IF @.StudioISNOT NULLSET @.SQL = @.SQL +' AND r.StudioFlag = ' +CONVERT(VARCHAR(20), @.Studio)IF @.Br1ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag1 = ' +CONVERT(VARCHAR(20), @.Br1)IF @.Br2ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag2 = ' +CONVERT(VARCHAR(20), @.Br2)IF @.Br3ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag3 = ' +CONVERT(VARCHAR(20), @.Br3)IF @.Br4ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag4 = ' +CONVERT(VARCHAR(20), @.Br4)IF @.OverBr4ISNOT NULLSET @.SQL = @.SQL +' AND r.OverBRFlag4 = ' +CONVERT(VARCHAR(20), @.OverBr4)IF @.CondoISNOT NULLSET @.SQL = @.SQL +' AND r.CondoFlag = ' +CONVERT(VARCHAR(20), @.Condo)IF @.ListingTypeISNOT NULLSET @.SQL = @.SQL +' AND r.ListingType = ' +CONVERT(char, @.ListingType)IF @.WindowAirISNOT NULLSET @.SQL = @.SQL +' AND a.WindowAir = ' +CONVERT(VARCHAR(20), @.WindowAir)IF @.CentralACISNOT NULLSET @.SQL = @.SQL +' AND a.CentralAir = ' +CONVERT(VARCHAR(20), @.CentralAC)IF @.BalconyDeckPatioISNOT NULLSET @.SQL = @.SQL +' AND a.BalconyDeckPatio = ' +CONVERT(VARCHAR(20), @.BalconyDeckPatio)IF @.UseOfYardISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfYard = ' +CONVERT(VARCHAR(20), @.UseOfYard)IF @.DishwasherISNOT NULLSET @.SQL = @.SQL +' AND a.Dishwasher = ' +CONVERT(VARCHAR(20), @.Dishwasher)IF @.WasherDryerISNOT NULLSET @.SQL = @.SQL +' AND a.WasherDryer = ' +CONVERT(VARCHAR(20), @.WasherDryer)IF @.FireplaceISNOT NULLSET @.SQL = @.SQL +' AND a.Fireplace = ' +CONVERT(VARCHAR(20), @.Fireplace)IF @.EIKISNOT NULLSET @.SQL = @.SQL +' AND a.EIK = ' +CONVERT(VARCHAR(20), @.EIK)IF @.HardwoodFloorsISNOT NULLSET @.SQL = @.SQL +' AND a.HardwoodFloors = ' +CONVERT(VARCHAR(20), @.HardwoodFloors)IF @.BroadBandNetISNOT NULLSET @.SQL = @.SQL +' AND a.BroadbandNet = ' +CONVERT(VARCHAR(20), @.BroadbandNet)IF @.TVISNOT NULLSET @.SQL = @.SQL +' AND a.TV = ' +CONVERT(VARCHAR(20), @.TV)IF @.ThermostatISNOT NULLSET @.SQL = @.SQL +' AND a.Thermostat = ' +CONVERT(VARCHAR(20), @.Thermostat)IF @.LandlordNotPresentISNOT NULLSET @.SQL = @.SQL +' AND a.LandLordNotPresent = ' +CONVERT(VARCHAR(20), @.LandLordNotPresent)IF @.SmokingISNOT NULLSET @.SQL = @.SQL +' AND a.Smoking = ' +CONVERT(VARCHAR(20), @.Smoking)IF @.NoPetsAllowedISNOT NULLSET @.SQL = @.SQL +' AND a.NoPetsAllowed = ' +CONVERT(VARCHAR(20), @.NoPetsAllowed)IF @.CatISNOT NULLSET @.SQL = @.SQL +' AND a.Cat = ' +CONVERT(VARCHAR(20), @.Cat)IF @.MoreCatsISNOT NULLSET @.SQL = @.SQL +' AND a.MoreCats = ' +CONVERT(VARCHAR(20), @.MoreCats)IF @.SmallDogISNOT NULLSET @.SQL = @.SQL +' AND a.SmallDog = ' +CONVERT(VARCHAR(20), @.SmallDog)IF @.LargeDogsISNOT NULLSET @.SQL = @.SQL +' AND a.LargeDogs = ' +CONVERT(VARCHAR(20), @.LargeDogs)IF @.DoorpersonISNOT NULLSET @.SQL = @.SQL +' AND a.Doorperson = ' +CONVERT(VARCHAR(20), @.Doorperson)IF @.IngroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.IngroundPool = ' +CONVERT(VARCHAR(20), @.IngroundPool)IF @.AboveGroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.AboveGroundPool = ' +CONVERT(VARCHAR(20), @.AboveGroundPool)IF @.ElevatorISNOT NULLSET @.SQL = @.SQL +' AND a.Elevator = ' +CONVERT(VARCHAR(20), @.Elevator)IF @.UseOfGarageISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfGarage = ' +CONVERT(VARCHAR(20), @.UseOfGarage)IF @.LaundryFacilitiesISNOT NULLSET @.SQL = @.SQL +' AND a.LaundryFacilities = ' +CONVERT(VARCHAR(20), @.LaundryFacilities)IF @.HealthCenterISNOT NULLSET @.SQL = @.SQL +' AND a.Health Center = ' +CONVERT(VARCHAR(20), @.HealthCenter)IF @.StorageAreasISNOT NULLSET @.SQL = @.SQL +' AND a.StorageAreas = ' +CONVERT(VARCHAR(20), @.StorageAreas)IF @.WheelchairAccessISNOT NULLSET @.SQL = @.SQL +' AND a.WheelchairAccess = ' +CONVERT(VARCHAR(20), @.WheelchairAccess)IF @.BusinessCentersISNOT NULLSET @.SQL = @.SQL +' AND a.BusinessCenters = ' +CONVERT(VARCHAR(20), @.BusinessCenters)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge BETWEEN ' +CONVERT(VARCHAR(20), @.RentChargeMin) +' AND ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNULLSET @.SQL = @.SQL +' AND a.RentCharge >= ' +CONVERT(VARCHAR(20), @.RentChargeMin)IF @.RentChargeMAXISNULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge <= ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.Debug = 1PRINT @.SQLEXEC (@.SQL)

Try this:

@.StudioINT =NULL,
@.Br1INT =NULL,
@.Br2INT =NULL,
@.Br3INT =NULL,
@.Br4INT =NULL,
@.OverBr4INT =NULL,
@.CondoINT =NULL,
@.ListingTypevarchar(10) =NULL,
@.WindowAirINT =NULL,
@.CentralACINT =NULL,
@.BalconyDeckPatioINT =NULL,
@.UseOfYardINT =NULL,
@.DishwasherINT =NULL,
@.WasherDryerINT =NULL,
@.FireplaceINT =NULL,
@.EIKINT =NULL,
@.HardwoodFloorsINT =NULL,
@.BroadbandNetINT =NULL,
@.TVINT =NULL,
@.ThermostatINT =NULL,
@.LandlordNotPresentINT =NULL,
@.SmokingINT =NULL,
@.NoPetsAllowedINT =NULL,
@.CatINT =NULL,
@.MoreCatsINT =NULL,
@.SmallDogINT =NULL,
@.LargeDogsINT =NULL,
@.DoorpersonINT =NULL,
@.IngroundPoolINT =NULL,
@.AboveGroundPoolINT =NULL,
@.ElevatorINT =NULL,
@.UseOfGarageINT =NULL,
@.LaundryFacilitiesINT =NULL,
@.HealthCenterINT =NULL,
@.StorageAreasINT =NULL,
@.WheelchairAccessINT =NULL,
@.BusinessCentersINT =NULL,
@.RentChargeMinINT =NULL,
@.RentChargeMaxINT =NULL,
@.DebugBIT = 1
)
AS

SET NOCOUNT ON

SELECT

r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROM db_REListings as r
INNER JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE (@.StudioIS NULL OR r.StudioFlag =@.Studio)
AND (@.Br1IS NULL OR r.BRFlag1 = @.Br1)
...
AND (@.ListingType IS NULL OR r.ListingType =@.ListingType)
...
etc...

|||

but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?

|||

Nevermind... the variable needed to have single quotes around it..

IF @.ListingType IS NOT NULL
SET @.SQL = @.SQL + ' AND r.ListingType = ''' + CONVERT(VARCHAR(20), @.ListingType) + ''''

|||

Mnemonic:

but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?

If ListingType equals null then

(@.ListingType IS NULL OR r.ListingType =@.ListingType) is also true! This is just another way of handling optional parameters.

So yes, the missing quotes are causing your error, but the solution that I wrote will also work (did you try it?) and I believe it is the better solution. Another advantage of using parameters is that you don't have to worry about wheter or not to use quotes...

|||

Oh... reading the logic you're definitly right...

I went my way with the query, but i might switch it over to yours... because the select statement is inside of @.sql (i think) none of my schema is showing any columns on my gridview...

Any idea on why that would do that? I would really rather not re-write this query (for the 3rd time)