Apostrophes Replaced By Question Marks Problem... Somebody Must Have A Fix
Dec 7, 2007
Hi I have an asp.net 2.0, web app with a sql 2000 db. I am having a problem when users cut and past content from Word and Excel and past it into a textbox apostrophes are replaced as question marks when saved to db. I have set the following line in my web.config which did not resolve the problem<globalization requestEncoding="iso-8859-1" responseEncoding="iso-8859-1" fileEncoding="iso-8859-1" culture="en-GB" uiCulture="en-GB"/> I have also read many posts, but have not found a resolutionhttp://www.codingforums.com/showthread.php?t=124609I cant do a find and replace of the character before insertion into DB, as this would involve visiting over 90 pages and changing every insert statement. Is there a way I a can force my sql server to only allow characters in a certain format, eg iso-8859-1 to resolve this problem Please help somebody must have a resolutionMany thanks in advance
In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:
Weather - clear(1), cloudy(2), rain(4) or snow(8).
If the operator selected cloudy and rain, the value of Weather would be 6.
I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.
In Access, it was easy - I plugged a decode procedure into the OnFormat event...
cnum = obj![WEATHR] If cnum - 8 >= 0 Then obj![SNOW] = "X" cnum = cnum - 8 End If If cnum - 4 >= 0 Then obj![RAIN] = "X" cnum = cnum - 4 End If If cnum - 2 >= 0 Then obj![CLOUDY] = "X" cnum = cnum - 2 End If If cnum - 1 >= 0 Then obj![CLEAR] = "X" End If
Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.
Hello, could someone help with this query in a stored proc.? SET @SQL = 'SET ''' + @avgwgt + ''' = ' '(SELECT AVG(AverageWeight) FROM CageFishHistory where CageID IN (' + @cagearray + ') and ItemDate =''' + CONVERT(varchar(23),@startdate) + ''')' EXEC @SQL I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @avgwgt line it works fine, but otherwise I'm getting this error: "Incorrect syntax near '(SELECT AVG(AverageWeight) FROM CageFishHistory where CageID IN ('." It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.
How do I handle and apostrophes and other punctuation in stored procedure Sql statements: SELECT L_ID, L_NameFROM tblHVACContractorsWHERE (L_Name = 'Mare's Heating & Cooling Services')
I am using a SQLDatasource to populate a dataview as illustrated below. I run into a problem when I search for a "LastName" that includes an (') Apostrophe ( e.g. O'Reilly) . Searchin for the whole name there is no problem, but when I search for simply O, or O' I get errors. I am not sure ...when to address names with an apostrophe... 1) On insertion to the database (using a "Replace(LastName, "'", "''") 2) or after they have been entered and they are to be searched for. If scenario 1, can anyone provide the best way to do this...If scenario 2, how would that be worked into the SQL Datasource code below.... I have tried several variations with the times SQL Datasource to no avail.... I would appreciate any help ! Thanks ! <asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:ClinicTest2ConnectionString1 %>" SelectCommand="SELECT [PatientID], [Accession], [FirstName], [LastName], [Address1], [City], [strddlPatientState], [ZIP], [DOB] FROM [ClinicalPatient] WHERE [LastName] LIKE @LastName ORDER BY [LastName],[FirstName]ASC">
I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John
After creating an IN clause from a bunch of character strings created by aWord macro, Query Analyzer complains about a syntax error. The macro takes acolumn full of character strings and wraps apostrophes (single-quotes)around each string and adds a comma to the end of each line, ready to pasteinto a Query Analyzer session. The problem is that Query Analyzer doesn'trecognize the MS-Word apostrophes. It has nothing to do with the standardapostrophe/quote problems that some people face when dealing with embeddedquotes.Can anyone suggest how to produce single-quote characters in MS-Word thatQuery Analyzer understands?Thank you.
I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily. this section shows the problem, i am loading xml from a file and trying to insert this into the sql database XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close(); pls help
I'd like some ideas on how to help out my VB staff when it comes to the tick ( ' ) mark being used in front end applications. When users enter data into the front end, and they include tick marks (eg: Sam's Auto Store), this causes trouble in how the app uses that data in a WHERE clause and other places. Does anyone have any slick ideas or experience in handling this. We have experimented with search & replace, double tick marks ( '' ), etc...
When we insert text into field in a table, SQL SERVER apparentlyreplaces apostrophes with question marks -- is there a way to not havethis occur? We don't have this happen with the mySQL databases thatwe also support.Much help appreciated.
I work with both Oracle and SQL Server databases (most of the time Oracle) and I was wondering if there is a function that allows ampersands and apostrophes to be inserted into the database. Oracle has a function called SET DEFINE OFF and that allows for ampersands and apostrophes to be inserted without giving me an error. Is there something similar in SQL Server? I'm migrating some data and quite few rows have ampersands and apostrophes.
I have an update statement that accepts a user's text input. problem is that the string terminates the sql update statement if there is an apostrophe in it. so, headline='i like my brother's car" willl terminate after "brother". "s car" is read as sql.
how is this resolved?
Code:
Dim MyCmd2 As New Data.SqlClient.SqlCommand("SET ANSI_WARNINGS OFF " & _ "UPDATE EditProfile " & _ "SET headline='"+ Me.tb_headline.Value+"', about_me='"+ Me.ta_aboutme.Value+"', edit_date='"+System.DateTime.Now.ToString+"' WHERE email_address='"+Context.User.Identity.Name.ToString+"' " & _ "SET ANSI_WARNINGS ON ", MyConn)
declare @test as varchar(32) declare @test2 as varchar(32)
set @test='today''s problem' set @test2='my <string> '
select @test as '@attribute' for xml path ('myrow') select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website. The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.
I am working on a project to import information from one database table to another. This info includes names, addresses, etc. I am running into a challenge whenever one of the names, such as O'Brien, causes problems in the sql statement. Is there a way for me to tell it to ignore the ' in O'Brien?
Hi all,I have an old ASP .NET 1.1 application that I haven't had time to rebuild with .NET 2.0. No changes have been made to the application or the SQL Server the application uses. I have a web form where users can type multiple lines of text, and it is entered into an SQL database (SQL 2000 Enterprise) into a column with a datatype of "text". Recently (it seems out of nowhere), If my users enter a carriage return into the webform, it becomes a question mark in the sql database. It's really a bit funny, but also annoying, lol. Does anyone have any idea why this might be happening? Thanks so much!
Please can anyone help me - I am trying to establish whether there are any reserved symbols in SQL passwords
eg % £ " $ ( / < * etc
The SQL BOL has a list of reserved words but I cannot find a list of reserved symbols.
We have a problem where by for example if the main part of the password is "test" and my user wants to add a punctuation mark into this we get "You are attempting to use an invalid character message"
Specifically
"test$" is accepted "$test" is not accepted None of "te+st" "+test" "test+" are accepted.
Has anyone else come across this and found a list of the logic that produces these results so I can point my users in the right direction?
Hi, My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer. Here is my problem query: SELECT * FROM OPENROWSET('MSDASQL', 'DSN=SHADOW', 'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'
I am getting this error: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '2007'.
I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file with the same name already exists, that file isn't replaced. I would like the opposite to happen. I'm using the following code:
--Executable
set svr=http://w3sdwsqld1/reportserver set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" set dest_fld="Associados" set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld% PAUSE
--rss Code
' ' Script Variables ' ' Variables that are passed on the command line with the -v switch: ' ' (a) parentFolder - corresponds to the folder that the script creates and uses ' to contain your published reports
' (b) reportP - corresponds to the report to publish
Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"
Dim definition As [Byte]() = Nothing Dim warnings As Warning() = Nothing Dim parentPath As String = ROOT + "/"+ parentFolder Dim filePath As String = path Dim report As String = reportP
'Create the parent folder Try rs.CreateFolder(parentFolder, ROOT,Nothing) Console.WriteLine("Parent folder {0} created successfully", parentFolder) Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create shared data source 'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")
'Publish the sample reports PublishReport(report)
End Sub
Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String) 'Define the data source definition. Dim definition As New DataSourceDefinition() definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated definition.ConnectString = connectionString definition.Enabled = True definition.EnabledSpecified = True definition.Extension = extension definition.ImpersonateUser = False definition.ImpersonateUserSpecified = True 'Use the default prompt string. definition.Prompt = Nothing definition.WindowsCredentials = False
Catch e As Exception Console.WriteLine(e.Message) End Try
End Sub
Public Sub PublishReport(ByVal reportName As String) Try Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx") Console.WriteLine(reportName)
definition = New [Byte](stream.Length) {} stream.Read(definition, 0, CInt(stream.Length)) stream.Close()
Catch e As IOException Console.WriteLine(e.Message) End Try
Catch e As Exception Console.WriteLine(e.Message) Console.WriteLine("Failed to publish report") End Try End Sub --------------------------------------------------------------------------------------------------------------------
I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.
S.No. Marks 1 ------ 5 2 ------ 5 3 ------ 6 1st Average Value here (16/3) 4 ------ 5 5 ------ 6 6 ------ 7 2nd Average Value here (18/3) 7 ------ 7 8 ------ 7 9 ------ 8 3rd Average Value here (22/3) 10 ----- 8 11 ----- 9 12 ----- 8 4th Average Value here (26/3)
So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
Is it possible to remove the tick marks that I have circled in red?The chart has data Week by week over a year, and the T1, T2 etc are equivalent to Quarter1, Quarter 2 etcI've got a 3 level grouping, with the top level being Year, then Quarter, then Week. The label for the "Week" grouping is set to blank, and that's why you don't see it here.Removing the Tick marks would make the chart legend more readable in my opinion.
Grade_letter lowbound_marks uperbound_marks A 85 100 B 60 84 C 0 59
Suppose some another query returns marks.Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hardcoded and calculates the grade based on the values given in the table only.
