|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
simple insert of stringsspecial characters that aren't being accepted as the string to insert. I'm pretty new to the SQL syntax and string limitation, can someone please review and tell me ho I need to handle the special characters in my strings? -- AND if you ave a suggestion to better accomplish what I'm tryin to do, I am open. Thanks, Mark -------------------- BEGIN DECLARE numREC num; INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, UserP, DatePosted, Status, Category, Version) VALUES (‘CB (HIDTA) – This is a test record and description for the test record.’, ‘GrantManagement’, ‘stu1_fm’, @@date, ‘’, NULL, ‘S’, ‘ReprogSubmitHIDTA’, 0); SELECT @@identity AS numREC; UPDATE jrnGrantManagement SET Version = numREC WHERE IDJ = numREC; INSERT INTO jrnDTLGrantManagement (IDJ, RowNum, Debit, Credit, Remark, Initiative, RR, AR, Account) VALUES (numREC, 1, 50, NULL, ‘This is item number 1 for this reprogramming.’, ‘CB.1.IntelSupportCtr’, ‘1FY05CB’, ‘’); COMMIT; END; The only things i can spot are :-
DECLARE numREC int and @@date should by GETDATE() -- Show quoteHTH. Ryan "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message news:F7C4BBD2-9094-466D-B63F-9D31391DCB40@microsoft.com... > I'm trying to validate to run the following statments. I have several > special characters that aren't being accepted as the string to insert. > I'm > pretty new to the SQL syntax and string limitation, can someone please > review > and tell me ho I need to handle the special characters in my strings? -- > AND > if you ave a suggestion to better accomplish what I'm tryin to do, I am > open. > > Thanks, > Mark > -------------------- > > BEGIN > > DECLARE > > numREC num; > > INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, > UserP, DatePosted, Status, Category, Version) > VALUES ('CB (HIDTA) - This is a test record and description for the test > record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', > 'ReprogSubmitHIDTA', 0); > > SELECT @@identity AS numREC; > > UPDATE jrnGrantManagement SET Version = numREC WHERE IDJ = numREC; > > INSERT INTO jrnDTLGrantManagement (IDJ, RowNum, Debit, Credit, Remark, > Initiative, RR, AR, Account) > VALUES (numREC, 1, 50, NULL, 'This is item number 1 for this > reprogramming.', 'CB.1.IntelSupportCtr', '1FY05CB', ''); > > COMMIT; > > END; > You need to use single quotes (') instead of the ' and ' characters as
character literal enclosures. If you have single quotes within your data, you can escape by using 2 consecutive single quotes within the string. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message news:F7C4BBD2-9094-466D-B63F-9D31391DCB40@microsoft.com... > I'm trying to validate to run the following statments. I have several > special characters that aren't being accepted as the string to insert. > I'm > pretty new to the SQL syntax and string limitation, can someone please > review > and tell me ho I need to handle the special characters in my strings? -- > AND > if you ave a suggestion to better accomplish what I'm tryin to do, I am > open. > > Thanks, > Mark > -------------------- > > BEGIN > > DECLARE > > numREC num; > > INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, > UserP, DatePosted, Status, Category, Version) > VALUES ('CB (HIDTA) - This is a test record and description for the test > record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', > 'ReprogSubmitHIDTA', 0); > > SELECT @@identity AS numREC; > > UPDATE jrnGrantManagement SET Version = numREC WHERE IDJ = numREC; > > INSERT INTO jrnDTLGrantManagement (IDJ, RowNum, Debit, Credit, Remark, > Initiative, RR, AR, Account) > VALUES (numREC, 1, 50, NULL, 'This is item number 1 for this > reprogramming.', 'CB.1.IntelSupportCtr', '1FY05CB', ''); > > COMMIT; > > END; > > You need to use single quotes (') instead of the ' and ' characters BTW, I was referring the the extended ASCII characters CHAR(145) and CHAR(146). It looks like regular quotes (CHAR(39)) were included in my response. -- Hope this helps. Dan Guzman SQL Server MVP how do i access those characters???
Show quote "Dan Guzman" wrote: > > You need to use single quotes (') instead of the ' and ' characters > > BTW, I was referring the the extended ASCII characters CHAR(145) and > CHAR(146). It looks like regular quotes (CHAR(39)) were included in my > response. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > > > The single quote is immediately to the left of the enter key. Note that
some text editors may automatically translate a quote into an extended ASCII character. The statement below is valid: INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, UserP, DatePosted, Status, Category, Version) VALUES ('CB (HIDTA) - This is a test record and description for the test record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', 'ReprogSubmitHIDTA', 0); I didn't notice this earlier but the rest of your script doesn't look like Transact-SQL. Are you using Microsoft SQL Server? -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message news:0B03AC01-B9A2-451A-B17D-AD97837E6102@microsoft.com... > how do i access those characters??? > > "Dan Guzman" wrote: > >> > You need to use single quotes (') instead of the ' and ' characters >> >> BTW, I was referring the the extended ASCII characters CHAR(145) and >> CHAR(146). It looks like regular quotes (CHAR(39)) were included in my >> response. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> >> >> Awesome. I've since discovered what you're talking about. My quotes got all
messy when copying and pasting sample code from the internet. Really, I'll I'm attempting is to write a header record in a main table and corresponding detail records in a detail table. I don't know my primary Key ID until the insert which is why I'm using the @@identity. Taking that, assigning it to a variable them using it in th subsequent INSERT statement int othe details table. Do you have a cleaner solution? Thanks for all your assistance, Mark Show quote "Dan Guzman" wrote: > The single quote is immediately to the left of the enter key. Note that > some text editors may automatically translate a quote into an extended ASCII > character. The statement below is valid: > > INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, > UserP, DatePosted, Status, Category, Version) > VALUES ('CB (HIDTA) - This is a test record and description for the test > record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', > 'ReprogSubmitHIDTA', 0); > > I didn't notice this earlier but the rest of your script doesn't look like > Transact-SQL. Are you using Microsoft SQL Server? > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message > news:0B03AC01-B9A2-451A-B17D-AD97837E6102@microsoft.com... > > how do i access those characters??? > > > > "Dan Guzman" wrote: > > > >> > You need to use single quotes (') instead of the ' and ' characters > >> > >> BTW, I was referring the the extended ASCII characters CHAR(145) and > >> CHAR(146). It looks like regular quotes (CHAR(39)) were included in my > >> response. > >> > >> -- > >> Hope this helps. > >> > >> Dan Guzman > >> SQL Server MVP > >> > >> > >> > >> > > > > Really, I'll I'm attempting is to write a header record in a main table Below is an example script that accomplishes this task. The comments show > and > corresponding detail records in a detail table. I don't know my primary > Key > ID until the insert which is why I'm using the @@identity. Taking that, > assigning it to a variable them using it in th subsequent INSERT statement > int othe details table. the major changes I made from your original script. Make sure the you use Transact-SQL script examples written specifically for Microsoft SQL Server. It looks to me line the script example you got from the internet was written for a different DBMS product. --added BEGIN TRAN since implicit transactions are not on by default; BEGIN TRAN --added @ to variable name and specified a valid data type DECLARE @numREC int; INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, UserP, DatePosted, Status, Category, Version) VALUES ('CB (HIDTA) - This is a test record and description for the test record.', 'GrantManagement', 'stu1_fm', --changed @@date to GETDATE() GETDATE(), '', NULL, 'S', 'ReprogSubmitHIDTA', 0); --changed variable name --changed @@identity to SCOPE_IDENTITY() (SQL 2000 and above) SELECT @numREC = SCOPE_IDENTITY(); --superfluous UPDATE removed INSERT INTO jrnDTLGrantManagement (IDJ, RowNum, Debit, Credit, Remark, Initiative, RR, AR, Account) VALUES (@numREC, 1, 50, NULL, 'This is item number 1 for this reprogramming.', 'CB.1.IntelSupportCtr', '1FY05CB', '', --added additional values so that number of values matched column count ''); COMMIT; -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message news:B380F5FB-D5D6-4FF6-8236-200DF9963381@microsoft.com... > Awesome. I've since discovered what you're talking about. My quotes got > all > messy when copying and pasting sample code from the internet. > > Really, I'll I'm attempting is to write a header record in a main table > and > corresponding detail records in a detail table. I don't know my primary > Key > ID until the insert which is why I'm using the @@identity. Taking that, > assigning it to a variable them using it in th subsequent INSERT statement > int othe details table. > > Do you have a cleaner solution? > > Thanks for all your assistance, > Mark > > "Dan Guzman" wrote: > >> The single quote is immediately to the left of the enter key. Note that >> some text editors may automatically translate a quote into an extended >> ASCII >> character. The statement below is valid: >> >> INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, >> UserP, DatePosted, Status, Category, Version) >> VALUES ('CB (HIDTA) - This is a test record and description for the test >> record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', >> 'ReprogSubmitHIDTA', 0); >> >> I didn't notice this earlier but the rest of your script doesn't look >> like >> Transact-SQL. Are you using Microsoft SQL Server? >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message >> news:0B03AC01-B9A2-451A-B17D-AD97837E6102@microsoft.com... >> > how do i access those characters??? >> > >> > "Dan Guzman" wrote: >> > >> >> > You need to use single quotes (') instead of the ' and ' characters >> >> >> >> BTW, I was referring the the extended ASCII characters CHAR(145) and >> >> CHAR(146). It looks like regular quotes (CHAR(39)) were included in >> >> my >> >> response. >> >> >> >> -- >> >> Hope this helps. >> >> >> >> Dan Guzman >> >> SQL Server MVP >> >> >> >> >> >> >> >> >> >> >> Dan,
I need one other favor. I am trying to delete the detail records from the detail table before deleting the header record from the pk table. I have put together the following SQL. The SELECT statement gets the 2 records I need from the detail table, but I'm not sure how to pass the ID's to the DELETE part of the statement to get id of the details records. I have the overall statement as a SELECT for now, until I work it all out. I will then change the SELECT to a DELETE. I have included both versions for your review. I would certainly appreciate your assistance. I can give you my phone number if you'd like to speak in person more about my effort. Gratefully, Mark CODE SAMPLE: ------------------- SELECT * FROM jrnDTLGrantManagement AS Tdtl WHERE EXISTS -->this part gets the 2 records I'm looking for but the above returns 8000 + record -->ignoring the criteri below. Am I using the EXISTS incorrectly -->TSum.IDJ is the PK below -->Tdtl.IDJ is the FK (SELECT * FROM jrnGrantManagement AS Tsum INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID WHERE TInit.ParentH1 = 'CanadianBorder' AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06') when the above works, I plan to convert it to a DELETE statement as follows: DELETE jrnDTLGrantManagement FROM jrnDTLGrantManagement AS Tdtl WHERE EXISTS (SELECT * FROM jrnGrantManagement AS Tsum INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID WHERE TInit.ParentH1 = 'CanadianBorder' AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06') ====================== Show quote "Dan Guzman" wrote: > > Really, I'll I'm attempting is to write a header record in a main table > > and > > corresponding detail records in a detail table. I don't know my primary > > Key > > ID until the insert which is why I'm using the @@identity. Taking that, > > assigning it to a variable them using it in th subsequent INSERT statement > > int othe details table. > > Below is an example script that accomplishes this task. The comments show > the major changes I made from your original script. > > Make sure the you use Transact-SQL script examples written specifically for > Microsoft SQL Server. It looks to me line the script example you got from > the internet was written for a different DBMS product. > > > --added BEGIN TRAN since implicit transactions are not on by default; > BEGIN TRAN > > --added @ to variable name and specified a valid data type > DECLARE @numREC int; > > INSERT INTO jrnGrantManagement > (Description, > App, > UserC, > DateModified, > UserP, > DatePosted, > Status, > Category, > Version) > VALUES ('CB (HIDTA) - This is a test record and description for the test > record.', > 'GrantManagement', > 'stu1_fm', > --changed @@date to GETDATE() > GETDATE(), > '', > NULL, > 'S', > 'ReprogSubmitHIDTA', > 0); > > --changed variable name > --changed @@identity to SCOPE_IDENTITY() (SQL 2000 and above) > SELECT @numREC = SCOPE_IDENTITY(); > > --superfluous UPDATE removed > > INSERT INTO jrnDTLGrantManagement > (IDJ, > RowNum, > Debit, > Credit, > Remark, > Initiative, > RR, > AR, > Account) > VALUES (@numREC, > 1, > 50, > NULL, > 'This is item number 1 for this reprogramming.', > 'CB.1.IntelSupportCtr', > '1FY05CB', > '', > --added additional values so that number of values matched column count > ''); > > COMMIT; > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message > news:B380F5FB-D5D6-4FF6-8236-200DF9963381@microsoft.com... > > Awesome. I've since discovered what you're talking about. My quotes got > > all > > messy when copying and pasting sample code from the internet. > > > > Really, I'll I'm attempting is to write a header record in a main table > > and > > corresponding detail records in a detail table. I don't know my primary > > Key > > ID until the insert which is why I'm using the @@identity. Taking that, > > assigning it to a variable them using it in th subsequent INSERT statement > > int othe details table. > > > > Do you have a cleaner solution? > > > > Thanks for all your assistance, > > Mark > > > > "Dan Guzman" wrote: > > > >> The single quote is immediately to the left of the enter key. Note that > >> some text editors may automatically translate a quote into an extended > >> ASCII > >> character. The statement below is valid: > >> > >> INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, > >> UserP, DatePosted, Status, Category, Version) > >> VALUES ('CB (HIDTA) - This is a test record and description for the test > >> record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', > >> 'ReprogSubmitHIDTA', 0); > >> > >> I didn't notice this earlier but the rest of your script doesn't look > >> like > >> Transact-SQL. Are you using Microsoft SQL Server? > >> > >> -- > >> Hope this helps. > >> > >> Dan Guzman > >> SQL Server MVP > >> > >> "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message > >> news:0B03AC01-B9A2-451A-B17D-AD97837E6102@microsoft.com... > >> > how do i access those characters??? > >> > > >> > "Dan Guzman" wrote: > >> > > >> >> > You need to use single quotes (') instead of the ' and ' characters > >> >> > >> >> BTW, I was referring the the extended ASCII characters CHAR(145) and > >> >> CHAR(146). It looks like regular quotes (CHAR(39)) were included in > >> >> my > >> >> response. > >> >> > >> >> -- > >> >> Hope this helps. > >> >> > >> >> Dan Guzman > >> >> SQL Server MVP > >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > > On Wed, 11 Jan 2006 07:15:02 -0800, Mark in Miami wrote:
>Dan, Hi Mark,> >I need one other favor. I am trying to delete the detail records from the >detail table before deleting the header record from the pk table. I have put >together the following SQL. The SELECT statement gets the 2 records I need >from the detail table, but I'm not sure how to pass the ID's to the DELETE >part of the statement to get id of the details records. I have the overall >statement as a SELECT for now, until I work it all out. I will then change >the SELECT to a DELETE. I have included both versions for your review. I >would certainly appreciate your assistance. I can give you my phone number >if you'd like to speak in person more about my effort. I hope you don't mind me chiming in :-) Show quote >CODE SAMPLE: I assume that the comment means that the subquery by itself returns the>------------------- >SELECT * >FROM jrnDTLGrantManagement AS Tdtl >WHERE EXISTS >-->this part gets the 2 records I'm looking for but the above returns 8000 + >record >-->ignoring the criteri below. Am I using the EXISTS incorrectly >-->TSum.IDJ is the PK below >-->Tdtl.IDJ is the FK >(SELECT * FROM jrnGrantManagement AS Tsum >INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj >INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID >WHERE TInit.ParentH1 = 'CanadianBorder' >AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06') rows you want to delete (joined to data from the two other tables). It makes sense that the outer query returns 8000+ rows - it should return all rows in Tdtl! The subquery is not correlated to the outer query. It will evaluate to the same two rows for each row in the outer query, and hence the EXISTS will be true for each row in the outer query. Since you didn't post CREATE TABLE and INSERT statements that would allow others to recreate your problem, I'm going to do some guesswork here. First, see if the query below will also return the rows you need to delete: SELECT * -- DELETE FROM jrnDTLGrantManagement WHERE EXISTS (SELECT * FROM jrnGrantManagement AS Tsum WHERE TSum.Idj = jrnDTLGrantManagement.Idj AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06' -- see below ) AND EXISTS (SELECT * FROM mbrInitiative AS TInit WHERE TInit.ID = jrnDTLGrantManagement.Initiative AND TInit.ParentH1 = 'CanadianBorder' ) If this gives you the correct results, then comment the first and uncomment the second line to delete those rows. * The date comparison is not done with the most efficient technique. If you have an index on TSum.DateModified, you'll probably have a much better performance if you change this to AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06' -- see below AND TSum.DateModified >= '20060111' AND TSum.DateModified < '20060112' Note that I used the unambiguous yyyymmdd format. And note that I used the DateModified column on it's own, not in an expression. Instead of removing the time part, then comparing, I am searching for all date/time combinations on ar after Jan 11, midnight, but before Jan 12, midnight. The results is the same, but SQL Server can now use an index seek to satisfy the filter. -- Hugo Kornelis, SQL Server MVP Dan,
I'm not sure I follow. The string argument in question, I believe is: 'CB (HIDTA) - This is a test record and description for the test record.' I am using a single quote to identify the string. thanks. Show quote "Dan Guzman" wrote: > You need to use single quotes (') instead of the ' and ' characters as > character literal enclosures. If you have single quotes within your data, > you can escape by using 2 consecutive single quotes within the string. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Mark in Miami" <MarkinMi***@discussions.microsoft.com> wrote in message > news:F7C4BBD2-9094-466D-B63F-9D31391DCB40@microsoft.com... > > I'm trying to validate to run the following statments. I have several > > special characters that aren't being accepted as the string to insert. > > I'm > > pretty new to the SQL syntax and string limitation, can someone please > > review > > and tell me ho I need to handle the special characters in my strings? -- > > AND > > if you ave a suggestion to better accomplish what I'm tryin to do, I am > > open. > > > > Thanks, > > Mark > > -------------------- > > > > BEGIN > > > > DECLARE > > > > numREC num; > > > > INSERT INTO jrnGrantManagement (Description, App, UserC, DateModified, > > UserP, DatePosted, Status, Category, Version) > > VALUES ('CB (HIDTA) - This is a test record and description for the test > > record.', 'GrantManagement', 'stu1_fm', @@date, '', NULL, 'S', > > 'ReprogSubmitHIDTA', 0); > > > > SELECT @@identity AS numREC; > > > > UPDATE jrnGrantManagement SET Version = numREC WHERE IDJ = numREC; > > > > INSERT INTO jrnDTLGrantManagement (IDJ, RowNum, Debit, Credit, Remark, > > Initiative, RR, AR, Account) > > VALUES (numREC, 1, 50, NULL, 'This is item number 1 for this > > reprogramming.', 'CB.1.IntelSupportCtr', '1FY05CB', ''); > > > > COMMIT; > > > > END; > > > > >  |
|||||||||||||||||||||||
Other interesting topics