Can VB ADO Command pass Null value to SP

Dear all,
When calling SP from VB client,It seems like can't pass null as
parameter value, Did anybody happen same issue?
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
See my reply to your other message and download the ADO stored
procedure add-in to generate the client code.
--Mary
On Wed, 29 Sep 2004 13:01:52 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Dear all,
>When calling SP from VB client,It seems like can't pass null as
>parameter value, Did anybody happen same issue?
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

Can SQL2000 & SQL2005 co-exist on the one server?

i realise you can have both SQL2000 & SQL2005 Client software co-existing on one (developement) machine.

but i cant seem to find a definitive answer on whether i can install both complete databases on a server?

we are currently building a new development server and would like to install both version on it if possible?

Cheers,
Craig

You can install both. You'd have to have an instance name, however.

ie. servername\sql2005

I think it's less confusing if you put both servers on separate instances..

instead of

sql2000: servername
sql2005: servername\sql2005

use

sql2000: servername\sql2000
sql2005: servername\sql2005

this way you know what environment you're pointing to..

Can SQL 2000 restore database and user login on another server?

Can SQL 2000 restore user login and database to another
domain and computer name? If it can't, how to export user
login to another computer?
You can migrate logins from one server to another server by generating a
script using a routine called sp_help_revlogin. Here is an article I wrote
about it:
http://databasejournal.com/features/...le.php/2228611
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:481301c49fe2$d7215e10$a401280a@.phx.gbl...
> Can SQL 2000 restore user login and database to another
> domain and computer name? If it can't, how to export user
> login to another computer?

Can Someone PLEASE help! (PLEASE)

I can not figure out why I am getting this error. I have gone over everything and cant find any discrepancies.

I have confirmed that eveything matches up with the Database/tabel

Error:

Parameterized Query '(@.WorkSheetID nvarchar(14),@.Origin nvarchar(5),@.DealerName nvarc' expects parameter @.Address, which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Parameterized Query '(@.WorkSheetID nvarchar(14),@.Origin nvarchar(5),@.DealerName nvarc' expects parameter @.Address, which was not supplied.

Source Error:

Line 401: .Add(New SqlParameter("@.DOB", sDOB))Line 402: End WithLine 403: myCommand.ExecuteNonQuery()Line 404: myConnection.Close()Line 405: 'Lets Check to see if there is a Co Applicant

My Code:

Protected

Sub bSubmitNC_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles bSubmitNC.ClickDim myConnectionAs SqlConnectionDim myCommandAs SqlCommandDim connStringAsString ="Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True"Dim CPhoneNum, CWorkNum, CCellNum, sWorkSheetNum, sSaleOriginAsStringDim sDealerShipName, sBkupSlsRep, sDateCreated, sFirstName, sMiddleName, slastname, sBuyAddress, sSSN, sDOBAsStringDim scity, sState, sZip, sEmailAddres, sBuyersCounty, sSalesTaxAsStringDim SlsManLoginNAsStringDim myPrincipalAs IPrincipal =Me.User'Customer Information

sWorkSheetNum = lblCustWksnum.Text

sSaleOrigin = lblSaleOrigin.Text

sDealerShipName = lblDealerShipName.Text

sDateCreated = lblDateCreated.Text

sBkupSlsRep = lblBkupSlsRep.Text

sFirstName = lblcFirstName.Text

sMiddleName = lblcMiddleName.Text

slastname = lblcLastName.Text

sBuyAddress = rBuyAddress

scity = rcity

sState = rState

sZip = rZip

sEmailAddres = lblEmailAddres.Text

sBuyersCounty = lblBuyersCounty.Text

sSalesTax = lblSalesTax.Text

sSSN = lblSSN.Text

sDOB = lblDOB.Text

'Lets get the Current Logged on User

SlsManLoginN = lblSlsRep.Text

myConnection =

New SqlConnection

myConnection.ConnectionString = connString

myConnection.Open()

CPhoneNum = lblHnum.Text

CWorkNum = lblWnum.Text

CCellNum = lblCnum.Text

Const sqlAsString ="Insert into CustomerTable (WorkSheetID, Origin, DealerName, DateCreated, SalesRep, " _

+

"BkupSalesRep, FirstName, MiddleName, LastName, Address, City, State, Zip, HomePhone, WorkPhone, " _

+

"CellPhone, Email, County, SalesTax, SS, DOB) Values(@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

+

"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, @.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

myCommand =

New SqlCommand(sql, myConnection)With myCommand.Parameters

.Add(

New SqlParameter("@.WorkSheetID", sWorkSheetNum))

.Add(

New SqlParameter("@.Origin", sSaleOrigin))

.Add(

New SqlParameter("@.DealerName", sDealerShipName))

.Add(

New SqlParameter("@.DateCreated", sDateCreated))

.Add(

New SqlParameter("@.SalesRep", SlsManLoginN))

.Add(

New SqlParameter("@.BkupSalesRep", sBkupSlsRep))

.Add(

New SqlParameter("@.FirstName", sFirstName))

.Add(

New SqlParameter("@.MiddleName", sMiddleName))

.Add(

New SqlParameter("@.LastName", slastname))

.Add(

New SqlParameter("@.Address", sBuyAddress))

.Add(

New SqlParameter("@.City", scity))

.Add(

New SqlParameter("@.State", sState))

.Add(

New SqlParameter("@.Zip", sZip))

.Add(

New SqlParameter("@.HomePhone", CPhoneNum))

.Add(

New SqlParameter("@.WorkPhone", CWorkNum))

.Add(

New SqlParameter("@.CellPhone", CCellNum))

.Add(

New SqlParameter("@.Email", sEmailAddres))

.Add(

New SqlParameter("@.County", sBuyersCounty))

.Add(

New SqlParameter("@.SalesTax", sSalesTax))

.Add(

New SqlParameter("@.SS", sSSN))

.Add(

New SqlParameter("@.DOB", sDOB))EndWith

myCommand.ExecuteNonQuery()

myConnection.Close()

EndIfEndIfEndSub

Make sure you clear the sqlParameters before you ever add something to it.

After looking at that code i'm pretty confused on which way you are trying to do this. Are you using sql stored procedures or regular sql statements?

Can you please post your sql stored procedure if your using one. I can help you further but I need to know what one your going to plan on using.
|||

I am not using any stored procedures... Under Server Exploror/Data Connections I just have the CustomerInfo.mdf./Tables I dont have anything under Stored procedures

Whats got me stumped is I had this working and was successfully populating data into my database.

And for somereason unknown to me, Its not working now

I did tested the Data Connecttion by right clicking on the CustomerInfo.mdf and choose the "test Connection" and it came back successful.

I dont have any other code. This is a simple Webform with Textboxes and labels. User just files in all the informationa and clicks the Submit button.

Any help would beso greatly appricated.

T

|||

I did some research online and ended up makingn some changes... I am not getting any errors now but no data is being populated??

Here is what I changed to

Dim

myCommandAs SqlCommandDim myConnectionAs SqlConnectionDim insertCmdAsString

myConnection =

New SqlConnection("Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True")

insertCmd =

"Insert into CustomerTable Values (@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

&

"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, " _

&

"@.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

myCommand =

New SqlCommand(insertCmd, myConnection)

myCommand.Parameters.Add(

New SqlParameter("@.WorkSheetID", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.WorkSheetID").Value = lblCustWksnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.Origin", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Origin").Value = lblSaleOrigin.Text

myCommand.Parameters.Add(

New SqlParameter("@.DealerName", Data.SqlDbType.Char))

myCommand.Parameters(

"@.DealerName").Value = lblDealerShipName.Text

myCommand.Parameters.Add(

New SqlParameter("@.DateCreated", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.DateCreated").Value = lblDateCreated.Text

myCommand.Parameters.Add(

New SqlParameter("@.SalesRep", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SalesRep").Value = lblSlsRep.Text

myCommand.Parameters.Add(

New SqlParameter("@.BkupSalesRep", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.BkupSalesRep").Value = lblBkupSlsRep.Text

myCommand.Parameters.Add(

New SqlParameter("@.FirstName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.FirstName").Value = lblcFirstName.Text

myCommand.Parameters.Add(

New SqlParameter("@.MiddleName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.MiddleName").Value = lblcMiddleName.Text

myCommand.Parameters.Add(

New SqlParameter("@.LastName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.LastName").Value = lblcLastName.Text

myCommand.Parameters.Add(

New SqlParameter("@.Address", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Address").Value = rBuyAddress

myCommand.Parameters.Add(

New SqlParameter("@.City", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.City").Value = rcity

myCommand.Parameters.Add(

New SqlParameter("@.State", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.State").Value = rState

myCommand.Parameters.Add(

New SqlParameter("@.Zip", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Zip").Value = rZip

myCommand.Parameters.Add(

New SqlParameter("@.HomePhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.HomePhone").Value = lblHnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.WorkPhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.WorkPhone").Value = lblWnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.CellPhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.CellPhone").Value = lblCnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.Email", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Email").Value = lblEmailAddres.Text

myCommand.Parameters.Add(

New SqlParameter("@.County", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.County").Value = lblBuyersCounty.Text

myCommand.Parameters.Add(

New SqlParameter("@.SalesTax", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SalesTax").Value = lblSalesTax.Text

myCommand.Parameters.Add(

New SqlParameter("@.SS", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SS").Value = lblSSN.Text

myCommand.Parameters.Add(

New SqlParameter("@.DOB", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.DOB").Value = lblDOB.Text

myCommand.Connection.Open()

Try

myCommand.ExecuteNonQuery()

ShowMessageBox(

Me,"Record Added " & insertCmd)Catch exAs SqlExceptionIf ex.Number = 2627Then

ShowMessageBox(

Me,"ERROR: A record already exists with " _

&

"the same primary key")Else

ShowMessageBox(

Me,"ERROR: Could not add record")EndIfEndTry

myCommand.Connection.Close()

|||

I would suggest to use the function .AddWithValue and not Add and set the value separately. I think I had the same problem that you have. I have it working with AddWithValue.

Rumoldus

|||

(1) Try specifying the column names too in the INSERT statement as in :

"Insert into CustomerTable(WorkSheetId, Origin,..... ) Values (@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

&"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, " _

&"@.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

(2) You can simplify your code a little bit by declaring and setting the value for the parameter in 1 line.

myCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int)).Value = 1

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)

can someone help me construct a SQL command....

My head hurts...it might be an easy one for many people, but cant seem to figure a way to do it.
Ok, I have a table with a field called Ad_Price. This field is a nvarchar one, theres many different value in it, all of them corresponding to a price.
Now, what I want to do is only get the row that are not written in a good price format (like123$ is wrong, but 123.00 is good, but only value following this exemple)

For exemple, if I have all these value
145.87
6785.34
654$
45 to negociate
1bvcaa
0.01
876.556
the value I want to have are:
654$
45 to negociate
1bvcaa
876.556
because they dont follow the format I want
so im not sure if my explication was clear enough..all in all what I want to complete is the WHERE part of my SQL instruction...what should I put in:
SELECT *
FROM Ads
WHERE ???
thansk for taking the time to read this
Hello !!

You can try this:
SELECT Ad_PriceFROM Ads
WHERE Ad_PriceNOT LIKE'%[.][0-9][0-9]'
|||argh, its so simple...I hate myself when I ask something and the answer is so obvious:(...with a little tweak fto satisfy my need, this SQL instruction will be perfect to solve my problem. Thanks a lot for your quick help

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
Eddie
It could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>
|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get
>
|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements?
Ed
|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> > I have a problem in the SQL 2000 Server. In one my database, I found
that I
> > can retrieve the records from every table in the database (by "select"),
but
> > when I wanted to update or insert the record in each table, It failed
and
> > return error "timeout expired". What is the problem? What should I do to
get
> > rid of this problem?
> >
> > Thanks.
> >
> > Eddie
> >
> >
>|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time bec
ause lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx
.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that
I
> can retrieve the records from every table in the database (by "select"), b
ut
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to g
et
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
>|||To check if you are suffering from blocks run a start a Trace in SQL Profile
r before you try and run one of the qeries that times out, make sure that yo
u select all of the locks events, you add these to the trace in the Trace pr
operties events tab.
I addition check that the queries you are running actually reference the ind
exes you have created - are the inserts or updates particulary complicated s
tatements?
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can see it, cant put it to sql,...

I have an odd thing to do in sql, it's got to count how many times it finds a gap between dates in the database, this is a 2003 one, not the shiny cool 2005 one :(
Imagine a table with two fields, FromDate ToDate with in that order these values:
01/01/01 - 02/01/01
02/15/01 - 03/01/01
02/20/01 - 04/01/01

What I would like to do is see whether there are no overlaps in a give date range. So say I would want to see whether there are gaps between 01/01/01 and 06/01/01 it would return a count of two because between 02/01/01 and 02/15/01 there's a 2 week gap and there's a 2 months gap between 04/01/01 and 06/01/01.

So I can see it but have no clue how to do it in plain sql,... can someone help? Also,.. without doing it in code, do you think it's possible to return the start and endate of the gaps instead?

Cheers!why did you post this in both the oracle and sql server forums? the solutions will be completely different|||well, it has to run on both.
Why did you ask me the same question on both forums, the answer is the same.