Update sql :: correct concantenation and delimiter help
jason
24 database access fields considering they will be a mix of text and numeric
fields - I find the quotation marks quite confusing. I know there are some
who feel I should write the update in jet sql engine inside access but I
would like to get this up and running in asp first in terms of readability
etc. Is this syntax correct - should I stuff each line into a variable eg
UpdateSQl or just use the underscore/ampersand.
I appreciate some help on getting the syntax right:
UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" & NewPrice & ",
& _ '//numeric
ChangeDate =" & ChangeDate & " & _ '//Date
Name ='" & Name & "'"," &_
Original_Price=" & Original_Price
WHERE PriceChangeID =" & PriceChangeI
dlbjr
This can be emulated with an access query.
This will allow you to completely isolate the asp from the data container.
dlbjr
Unambit from meager knowledge of inane others,
engender uncharted sagacity.
jason
"dlbjr" <dl...@dontknow.doyou> wrote in message
news:u36FprOc...@tk2msftngp13.phx.gbl...
Bob Barrows
> What is the best way to write/concenantenate a massive update
> statement of 24 database access fields considering they will be a mix
> of text and numeric fields - I find the quotation marks quite
> confusing.
So do most newcomers. That is why I consider it to be a crime to teach
newcomers to use dynamic sql. This has got to be the biggest stumbling block
for new programmers. They should be taught the correct way (saved
queries/stored procedures) right from the start.
> I know there are some who feel I should write the update
> in jet sql engine inside access but I would like to get this up and
> running in asp first in terms of readability etc.
You really consider this mess of quotes, ampersands and line continuation
characters to be readable??
Why do you insist on doing this the hard way?
Oh,
well. Here are the rules:
To decide whether or not to delimit the data, look at the datatype of the
FIELD - NOT THE DATA.
1. If it's a numeric field, you must supply it with numeric data, which
means you MUST NOT delimit the data by putting quotes around it.
2. If it's a character/text field, then you must supply string data by
delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:
Update tbl set textfield = 'O''Malley'
In Access, you can use double quotes for your delimiters, so this will work
as well:
Update tbl set textfield = "O'Malley"
Note: you don't have to escape the apostrophe in O'Malley when you use
double quotes as the delimiter. However, you will need to escape the double
quotes when assigning this statement to a variable:
sSQL = "Update tbl set textfield = ""O'Malley"""
So most people will use the single quotes and escape the apostrophe:
sName = "O'Malley"
sSQL = "Update tbl set textfield = '" & Replace(sName,"'","''") & "'"
response.write sSQL
3. If it's a date/Time field, then the delimiters depend on the type of
database. Since you are using Access, then you must delimit the data with
hash marks (#). Additionally, you must supply the data in US format
(m/d/yyyy) or in ISO (yyyy-mm-dd), with the latter being the more
recommended.
4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using
And then, when you think you have it right and it still does not work,
response.write it to see the result of your concatenation. If you've done it
correctly, you will have a statement that you can copy and paste from the
browser window into the SQL View of an Access Query Builder and run without
modification (unless you need to replace the wildcards with the Jet
wildcards).
This all seems rather difficult, doesn't it? Add to this the fact that a
dynamic sql query will not perform as well as a saved query/stored
procedure, and you have two strikes against it. Add the lack of security due
to leaving yourself open to a SQL Injection attack and you have three
strikes. Why did you say you wanted to do it this way ...?
Let me show you how easy this can be using a saved parameter query. let's go
back to your statement and parameterize it* :
UPDATE tblListingspriceChanges SET NewPrice = [P1],
ChangeDate = [P2], [Name]=[P3], Original_Price=[P4]
WHERE PriceChangeID = [P5]
Do you notice ANY delimiters in the above sql statement? :-)
Test this statement in the Access Query Builder by running it: you will be
prompted to supply values for each of the parameters. Supply some values and
make sure it works as intended. When you've finished debugging it, save it
as qUpdPriceChange. Notice that you've created and debugged your query in
the environment where debugging and testing of queries should be done: in
the database environment.
Now to run it in ASP:
'create and open a connection object, cn, populate and
'validate your data variables, then:
cn.qUpdPriceChange NewPrice, ChangeDate, Name, _
Original_Price, PriceChangeI
How could this be more readable? :-)
HTH,
Bob Barrows
* I just noticed you have a field called "Name". "Name" is a reserved word
and should be avoided for user-defined object names. See
http://www.aspfaq.com/show.asp?id=2080 for the list of words that should be
avoided. If you can't rename the field for some reason, you will need to
bracket it [] in order to use it in queries called from asp.
Tom B
written, easy to follow and should be required reading for all newbies.
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:e6lLVvOc...@TK2MSFTNGP12.phx.gbl...
Bob Barrows
> Nice job Bob, I've printed this out and stuck it on my desk. It's
> well written, easy to follow and should be required reading for all
> newbies.
>
jason
insert statements for the future. I have been using them for my views but
not for the action queries......
Here goes...
Thanks
Jason
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:e6lLVvOc...@TK2MSFTNGP12.phx.gbl...
jason
errors when testing internally eg: Cannot update Owner_ID; field not
updateable.
PARAMETERS ListingsID long,
vName text (255), vCode text(255), Owner_ID long, Model_ID long, Size_ID
long, vYear text(255), Original_Price text(255), Company_ID long, Status_ID
long, Condition_ID long,
Yacht_Type_ID long, Charter_Status_ID long, ListingBrokerID long,
ListingBrokerID long, Location_Status_ID long, Location_Status_ID long,
Marina text(255), City text(255),
State_ID long, Country_ID long, Description_Title text(255), Description
text(255), ListItem1 text(255), ListItem2 text(255), ListItem3 text(255),
LinkTo text(255);
UPDATE tblListings
SET
[Name]=[vName], [Code] =[vCode], Owner_ID=[Owner_ID],
Model_ID=[Model_ID], [Size_ID]=Size_ID, [Year]=[vYear],
Original_Price =[Original_Price],
Company_ID = [Company_ID],
Status_ID = [Status_ID],
Condition_ID = [Condition_ID],
Yacht_Type_ID = [Yacht_Type_ID],
Charter_Status_ID = [Charter_Status_ID],
ListingBrokerID = [ListingBrokerID],
Location_Status_ID = [Location_Status_ID],
Marina = [Marina],
City = [City],
State_ID = [State_ID],
Country_ID = [Country_ID],
Description_Title = [Description_Title],
Description = [Description],
ListItem1 = [ListItem1],
ListItem2 = [ListItem2],
ListItem3 = [ListItem3],
LinkTo =[LinkTo]
WHERE
ListingsID=[ListingsID]
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:u6n7EWVc...@TK2MSFTNGP11.phx.gbl...
Bob Barrows
it.
The names you use for your parameters must not be the same as the names of
your fields (that is why I used the [PX] nomenclature in my example. Put a
"p" prefix on all the parameter names that match a field name in your table.
jason wrote:
> Hey Bob - I am running the UPDATE in Access as suggested but get wierd
> errors when testing internally eg: Cannot update Owner_ID; field not
> updateable.
>
> PARAMETERS ListingsID long,
> vName text (255), vCode text(255), pOwner_ID long, pModel_ID long,
> pSize_ID long, vYear text(255), pOriginal_Price text(255), pCompany_ID
> long, pStatus_ID long, pCondition_ID long,
> pYacht_Type_ID long, pCharter_Status_ID long, pListingBrokerID long,
> pListingBrokerID long, pLocation_Status_ID long, pLocation_Status_ID
> long, pMarina text(255), pCity text(255),
> pState_ID long, pCountry_ID long, pDescription_Title text(255),
> pDescription text(255), pListItem1 text(255), pListItem2 text(255),
> pListItem3 text(255), pLinkTo text(255);
>
> UPDATE tblListings
> SET
> [Name]=[vName], [Code] =[vCode], Owner_ID=[pOwner_ID],
> Model_ID=[pModel_ID], [Size_ID]=[pSize_ID], [Year]=[vYear],
> Original_Price =[pOriginal_Price],
> Company_ID = [pCompany_ID],
> Status_ID = [pStatus_ID],
> Condition_ID = [pCondition_ID],
> Yacht_Type_ID = [pYacht_Type_ID],
> Charter_Status_ID = [pCharter_Status_ID],
> ListingBrokerID = [pListingBrokerID],
> Location_Status_ID = [pLocation_Status_ID],
> Marina = [pMarina],
> City = [pCity],
> State_ID = [pState_ID],
> Country_ID = [pCountry_ID],
> Description_Title = [pDescription_Title],
> Description = [pDescription],
> ListItem1 = [pListItem1],
> ListItem2 = [pListItem2],
> ListItem3 = [pListItem3],
> LinkTo =[pLinkTo]
> WHERE
> ListingsID=[pListingsID]
jason
Just struggline with the asp part as it seems the "cnn" connection object
wants to call a method rather than the query:
set cnn = Server.CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/Listings.mdb") '//This one is for Access
2000/2002
cnn.Open strCon
cnn.qry_Update_tblListings ListingsID, Name etc
....This is how I have it at the moment but it does not look right?
_ jason
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:uS#SdbWcD...@tk2msftngp13.phx.gbl...
Bob Barrows
> Hi Bob - that works - awesome, awesome.
>
> Just struggline with the asp part as it seems the "cnn" connection
> object wants to call a method rather than the query:
Are you talking about the Intellisense function in Visual Interdev? Just
ignore it.
>
>
> set cnn = Server.CreateObject("ADODB.Connection")
> strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> Server.MapPath("../database/Listings.mdb") '//This one is for Access
> 2000/2002
>
> cnn.Open strCon
> cnn.qry_Update_tblListings ListingsID, Name etc
>
> ....This is how I have it at the moment but it does not look right?
That looks all right. Just remember to supply the data values in the same
order in which they are "declared" in the PARAMETERS statement in your
query. So Name should come first, followed by Code, etc.
jason
jason
ADODB.Connection (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
/catamaranco/listings/add_edit_execute_Listing.asp, line 187
For this exectued stored query in Access:
cnn.qry_Update_tblListings listingsID, Name, Code, Owner_ID, Model_ID,
Size_ID, varYear, Original_Price, Company_ID, Status_ID, Condition_ID,
Yacht_Type_ID, Charter_Status_ID, ListingBrokerID, ListingBrokerID,
Location_Status_ID, Location_Status_ID, Marina, City, State_ID, Country_ID,
Description_Title, Description, ListItem1, ListItem2, ListItem3, LinkTo
It works in Access query window (see below). I put [ ] around "Name" and
"Year". I copied the parameters out of the window and placed them above to
make sure I had the correct order and amount of fields. I also tried
placing brackets () around the parameter string in asp but this also
generated an error.....
I guess this is the point you wish you could debug in asp right? :)
PARAMETERS pListingsID Long, pName Text ( 255 ), pCode Text ( 255 ),
pOwner_ID Long, pModel_ID Long, pSize_ID Long, pYear Text ( 255 ),
pOriginal_Price Text ( 255 ), pCompany_ID Long, pStatus_ID Long,
pCondition_ID Long, pYacht_Type_ID Long, pCharter_Status_ID Long,
pListingBrokerID Long, pListingBrokerID Long, pLocation_Status_ID Long,
pLocation_Status_ID Long, pMarina Text ( 255 ), pCity Text ( 255 ),
pState_ID Long, pCountry_ID Long, pDescription_Title Text ( 255 ),
pDescription Text ( 255 ), pListItem1 Text ( 255 ), pListItem2 Text ( 255 ),
pListItem3 Text ( 255 ), pLinkTo Text ( 255 );
UPDATE tblListings SET Name = [pName], Code = [pCode], Owner_ID =
[pOwner_ID], Model_ID = [pModel_ID], Size_ID = [pSize_ID], [Year] = [pYear],
Original_Price = [pOriginal_Price], Company_ID = [pCompany_ID], Status_ID =
[pStatus_ID], Condition_ID = [pCondition_ID], Yacht_Type_ID =
[pYacht_Type_ID], Charter_Status_ID = [pCharter_Status_ID], ListingBrokerID
= [pListingBrokerID], Location_Status_ID = [pLocation_Status_ID], Marina =
[pMarina], City = [pCity], State_ID = [pState_ID], Country_ID =
[pCountry_ID], Description_Title = [pDescription_Title], Description =
[pDescription], ListItem1 = [pListItem1], ListItem2 = [pListItem2],
ListItem3 = [pListItem3], LinkTo = [pLinkTo]
WHERE ListingsID=[pListingsID];
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:#i4qwlXc...@tk2msftngp13.phx.gbl...
Bob Barrows
they appear in the PARAMETERS statement. The parameters should appear in the
same order in which they are used in your SQL statement. When you test it in
Access, which parameter does it prompt you for first? pName, right? So
that's the first parameter you should supply when calling the query from
asp.The last parameter value you should supply is pListingsID.
Sorry,
Bob Barrows
jason
ListingID to the end in in the parameters collection in both asp and the
acess window.
I did find two duplicates but removed these. i have confirmed I have the
correct number of fields (25) and that that they are matched in
sequence.....
Any more clues?:
ASP
cnn.qry_Update_tblListings Name, Code, Owner_ID, Model_ID, Size_ID, varYear,
Original_Price, Company_ID, Status_ID, Condition_ID, Yacht_Type_ID,
Charter_Status_ID, ListingBrokerID, Location_Status_ID, Marina, City,
State_ID, Country_ID, Description_Title, Description, ListItem1, ListItem2,
ListItem3, LinkTo, listingsID
ACCESS (numbers just for sequence):
PARAMETERS
pName Text ( 255 ), 2
pCode Text ( 255 ), 3
pOwner_ID Long, 4
pModel_ID Long, 5
pSize_ID Long, 6
pYear Text ( 255 ), 7
pOriginal_Price Text ( 255 ), 8
pCompany_ID Long, 9
pStatus_ID Long, 10
pCondition_ID Long, 11
pYacht_Type_ID Long, 12
pCharter_Status_ID Long, 13
pListingBrokerID Long, 14
pLocation_Status_ID Long, 15
pMarina Text ( 255 ), 16
pCity Text ( 255 ), 17
pState_ID Long, 18
pCountry_ID Long, 19
pDescription_Title Text ( 255 ), 20
pDescription Text ( 255 ), 21
pListItem1 Text ( 255 ), 22
pListItem2 Text ( 255 ), 23
pListItem3 Text ( 255 ), 24
pLinkTo Text ( 255 )25
pListingsID Long; 1
UPDATE tblListings SET
Name = [pName], 1
Code = [pCode], 2
Owner_ID = [pOwner_ID], 3
Model_ID = [pModel_ID], 4
Size_ID = [pSize_ID], 5
[Year] = [pYear], 6
Original_Price = [pOriginal_Price], 7
Company_ID = [pCompany_ID], 8
Status_ID = [pStatus_ID], 9
Condition_ID = [pCondition_ID], 10
Yacht_Type_ID = [pYacht_Type_ID], 11
Charter_Status_ID = [pCharter_Status_ID], 12
ListingBrokerID = [pListingBrokerID], 13
Location_Status_ID = [pLocation_Status_ID], 14
Marina = [pMarina], 15
City = [pCity], 16
State_ID = [pState_ID], 17
Country_ID = [pCountry_ID], 18
Description_Title = [pDescription_Title], 19
Description = [pDescription], 20
ListItem1 = [pListItem1], 21
ListItem2 = [pListItem2], 22
ListItem3 = [pListItem3], 23
LinkTo = [pLinkTo] 24
WHERE ListingsID=[pListingsID];25
ADODB.Connection (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
/catamaranco/listings/add_edit_execute_Listing.asp, line 187
a.. Browser Type
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:#$RTndYcD...@TK2MSFTNGP12.phx.gbl...
Bob Barrows
What happens if you use literal values instead of the variables, like this:
cnn.qry_Update_tblListings "me","code",1,1,1, _
"1998","$1500",1,0,1, _
1,1,1, _
1,"Marina","city",33,1, _
"DescriptionTitle","Description","ListItem1","ListItem2","ListItem3", _
"LinkTo", 1
While typing out the above, I noticed something: line breaks. Is your call
to ccn.qry_Update_tbleListings all on one line? It should be. If you want to
break it up, use line continuation characters, like this:
cnn.qry_Update_tblListings Name, Code, Owner_ID, Model_ID, Size_ID, varYear,
_
Original_Price, Company_ID, Status_ID, Condition_ID, Yacht_Type_ID, _
Charter_Status_ID, ListingBrokerID, Location_Status_ID, Marina, City, _
State_ID, Country_ID, Description_Title, Description, ListItem1, ListItem2,
_
ListItem3, LinkTo, listingsID
Bob Barrows
jason
Also, the fields are validate from a populated form. I have doublechecked
the variables are populated as I have been response.writeing the entire
time:
Audit Type: EDIT
User: Gaida Cabral
Name: Jason`s Fleece
Code: 4323
Owner ID: 2
Model ID: 25
Size: 34
Year: 2004
Company ID: 2
Status ID2
Condition ID: 2
Yacht Type ID: 2
Charter Status ID: 2
Broker ID: 48
Location Status ID: 5
Marina: Harbor Island
City: Fort Lauderdale
State_ID: 6
Country_ID: 158
Description Title: The Greek favourite
Description: A wonderfully put together boat"
a.. ListItem1: good finishing
a.. ListItem2: great extras
a.. ListItem3: awesome speed
LinkTo: Cheap
Insertion Date: 9/2/2003
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:eLEofDZc...@TK2MSFTNGP10.phx.gbl...
Bob Barrows
parameters. I don't see a value for listingsID.
Bob
jason
insert script so I forgot the EDIT section did not have the form variable -
thank you very much Bob!
- Jason
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:eMochbZ...@TK2MSFTNGP12.phx.gbl...
Bob Barrows
jason
code. I also like the way I can cycle through the parameters within Access
to test the query.
Future problem: Moving my stored queries to stored procs in sql server (+/-
6 months) along with asp code. That scares me!
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:uXeLNsZc...@TK2MSFTNGP10.phx.gbl...
Bob Barrows
> By the way - I am convert - it cleans up my asp page tremendously -
> reduces code. I also like the way I can cycle through the parameters
> within Access to test the query.
>
> Future problem: Moving my stored queries to stored procs in sql
> server (+/- 6 months) along with asp code. That scares me!
It shouldn't. You won't have to change your asp code, except to use a
different connection string.You'll be able to concentrate on the syntax
differences between jetSQL and T-SQL.
This stored procedure will look something like this:
Create Procedure qry_Update_tblListings (
@Name varchar(255),
...
@ListingsID int) AS
UPDATE tblListings SET
[Name] = @Name,
...
WHERE ListingsID= @ListingsID
Bob