insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers

INSERT special characters into SQL Database (Protect my Tic)

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.Was it a quote that didn't go through?|||

Quote:

Originally Posted by Arielle

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.

hi Arielle,

if you are using sql server2000 for backend activities, it accepts all the special characters while inserting a record,except for ' single quotes. You would have noticed that it throws errors, when we try to insert something like this:

eg:
insert into <tablename> values(val1,val2,'the student's of csc dept have scored the highest')

unclosed quotation mark before the character string...
incorrect syntax near...

if you want to insert your message that contains apostophe character from your asp page to sql server table, then you will have to use the Replace() function in your asp page.

Replace(string,find,replacewith)
where,
string: the string to be searched
find: the part of the string that will be replaced
replacewith: the replacement substring

eg: dim txt,crreason
txt=request("txtCrReason")
crreason=Replace(txt,"'","''")
...and send the value stored in the variable 'crreason' to your insert query.

try this and let me know if it has solved your problem. Gud Luck!

cheers,
jai