i'm using delphi 7 and have a query in which i'm trying to find namesthat have an apostrophe in them, i.e. "o'mally". my problem is thatwhen i write my select statement i can't get the quotes right. i getall types of errors no matter what i try. i get "missing right quote","invalid token" etc. i've tried using QuotedStr and nothing works.here is my current attempt in which i get an "invalid use of keyword.token: n'%'"sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +' MiddleInitial, Indep, Book_number, Page_number,' +' CloseRec, TermBegins, TermEnds, DatePickedUp,FailedToQualify,' +' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order bylastname';
I'm trying to write an SP which takes a string parameter like '1,3,6,9' and then uses it in an IN clause of a SELECT statement to return a recordset, along the lines of:
SELECT custname FROM mytable WHERE theid IN @myparameter
...any ideas how to get this kind of thing to work. In the example, theid is a numeric value. What it needs to do, I guess, is do a string replacement of the parameter value, but the SP naturally enough attempts to compare the numeric theid value with the string @myparameter value and fails. It works OK if there's only one value in the string, because the server auto-converts the paremeter into a numeric, but if there are 2 or more values in the string then the server obviously can't do the conversion. How can I make this work?
Hi Friends, I have stored names with ' s (Apostrophes) and without 's in the database(sqlserver).Example:O'Relly I tried to get the values with select statement like Dim str As String = Request.QueryString("Title") Dim query As String = "SELECT * FROM Test where title like '" + str + "'" Problem is getting the all the values from database except O'Relly just getting O other word Relly no..... What should do I and what code do I need ..?
I tried with the below code also Dim x As Long For x = 1 To Len(str) If Mid$(str, x, 1) = "'" Then str = str & "''" Else str = str & Mid(title1, x, 1) End If Next
I need help with a simple query. We have 86 entries with the City of O'Fallon in our db. How do I do this with the apostrophe in O'Fallon? Below is just to give an idea of what I want. Thanks. SELECT * FROM Organization WHERE City=O'Fallon
Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table.
Ok, I still have some uncertainty as to just exactly how this whole apostrophe thing works with databases. I understand that it is a reserved character and so when a sql query runs into one of these creatures it looks at it as something other than a normal character.
I am working primarily in vb/asp/sql server with a little bit of access. I am familiar with the instrinsic 'Replace' function and I use it but I still have occassional problems.
I would like any information I can get on just exactly why/how this thing works and how to work-around the apostrophe when writing to, reading from, and validating data from sql server/access/any databases.
I am having weird trouble with READING data from my Access database where the field has an apostrophe - but only when I display the field in a textbox.
For example, the field "Don't do this" becomes "Don" when my SQL query outputs the result:
I am trying to have a query with the option for items to be null. (So users don't need to fill in the other fields if they choose not too) SELECT Tickets.TicketID, Tickets.UserID, Tickets.SystemID, Tickets.Title, Tickets.Description, Tickets.Software, Tickets.Date, Systems.OS, OS.OS AS OstitleFROM Tickets INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID INNER JOIN OS ON Systems.OS = OS.osIDWHERE (Tickets.Title LIKE '%' + @title + '%') AND (Tickets.Software LIKE '%' + @software + '%') AND (Tickets.Description LIKE '%' + @descrip + '%') AND (Systems.OS = @osid) OR (@osid IS NULL)This works when i give the LIKE values % as a parameter. So they can choose to search by title + software but not description or description and nothing else etc etc etc. The problem is, the osid. If I give it a value it works but if i try to do null, *. or % it always displays every item in the databse ignoring any of the previous like statements. Anyone have an idea?
I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?
Hello. I have set up an SQL Endpoint using NTLM authentication. When I try to retrieve the WSDL file I am prompted for credentials. I put in username/password (this user has Connect access to the Endpoint) but its not accepted. The dialog reappears. The SQL error log shows an HTTP authentication failed error. I've triple checked that the username/password is correct.
Now here's the strange part: the same endpoint (exact same code for setting up endpoint, permissions, user, etc.) works just fine on another box (username/password is accepted and WSDL is shown). This leads me to believe that the problem isn't with SQL or the endpoint, but someplace else. I really don't know how to debug this one.
When I run the Query I get... Message="Parameterized Query '(@ID int,@EmpID int,@Photo nvarchar(260),@DoB smalldatetime,@Med' expects parameter @EmpID, which was not supplied."
I also get this when the parameter can be a string. I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)
OK so I need to use a dynamic SQL statement in a SqlDataSource object because I need to pass in the column name. I'm having trouble accounting for the apostophes I have to interpret literals within the statement. This is connecting to an Oracle database. This is what I originally tried... <asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:My_Connection_String.ProviderName %>" SelectCommand="SELECT m.YIE, :selecteditem, m.ENDTIME FROM MyMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE 'YC%' AND m.GROUPID NOT LIKE 'YCX%' AND m.ENDTIME >= SYSDATE-14)"> <SelectParameters> <asp:ControlParameter Name="selecteditem" ControlID="itemlabel" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> And the second column returned as :selecteditem for the column name and the value of itemlabel.text (what I wanted to be the column that was queried) as the value in each of the rows of that column. So I tried dynamic SQL to put the value of itemlabel.txt as the column to be queried, but I'm not sure how to get the query to read the literals. How can I accomplish this? <asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:MY_Connection_String.ProviderName %>" SelectCommand="EXEC('SELECT m.YIE, '+selectedbin+', m.ENDTIME FROM MMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE '+paramlike+' AND m.GROUPID NOT LIKE '+paramnotlike+' AND m.ENDTIME >= SYSDATE-14)')"> <SelectParameters> <asp:ControlParameter Name="selectedbin" ControlID="binlabel" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramlike" ControlID="Label1" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramnotlike" ControlID="Label2" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> This errors to "illegal variable name" Can someone help me out please? Thanks a lot. -steve
Hi all,I am not sure this question will belong to a sql group or .net.but the problem I have is, my data entry forms crashes when some enter a apostrophe character in one of the field. that field is of type varchar in sql server 2000.for ex, Ryan O'neill will crash the application. I have a drop down list of all my crew names in my form.what should I need to do to allow user to add apostrophe and not to have application crash?Please help it is very urgent.Thank You,
The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).
The problem: I am building my query as a string and then using the EXEC() function to execute it. For example: SET @strSQL = 'SELECT col1, col2 FROM mytable' EXEC(@strSQL)
Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.
I have been unable to obtain the solution and I am wondering if it is even possible?
Hi, i'm having problems executing the followingSET @SQLAH = 'SELECT sub_id WHERE 'SET @SQLAH = @SQLAH + 'VENUE_TYPE = Hotel'EXEC(@SQLAH)Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:..SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' '..But this escapes the string, how would i escape an apostrophe in a string?I thought maybe:SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' 'But no joy :confused: Thanks
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14 Unclosed quotation mark after the character string ''.
Select Name From Table Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
Getting an error when i execute the query. select * from trio where ad_str1 like '%''
Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string '%' '. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '%' '.
In my datedabase I have some customers with the next name: 't Woud.
The problem is the apostrophe in my query for my drill through.
This is my query:
SELECT DISTINCT c.alias AS Client, p.Alias AS Periode, u.alias as Product, v.alias as Verschil, CAST(s.indicatie AS decimal(6, 2)) AS Indicatie, CAST(s.realisatie AS decimal(6, 2)) AS Realisatie, CAST(s.real_indic AS decimal(6, 2)) AS [Realisatie -/- Indicatie], CAST(s.indicatie_fin AS decimal(6,0)) AS [Indicatie Fin], CAST(s.realisatie_fin AS decimal(6,0)) AS [Realisatie Fin], CAST(s.real_indic_fin AS decimal(6,0)) AS [Realisatie -/- Indicatie Fin] FROM [BI_TZR].dbo.fact_clientsignaal s INNER JOIN [BI_TZR].dbo.dim_verschil v ON s.verschil = v.verschil INNER JOIN [BI_TZR].dbo.Dim_Periodewk p ON s.periode = p.Periode INNER JOIN [BI_TZR].dbo.dim_client_uren c ON s.client_id = c.id INNER JOIN [BI_TZR].dbo.dim_product u ON s.product = u.product WHERE (LEFT(p.Alias, 1) = 'p') AND (c.alias like '%794735%') and s.kplts <> '1583' ORDER BY s.product, s.periode
I try this: SELECT DISTINCT replace ( c.alias, 'd', '') AS Client, this works, for the caracter D. But know I want it for the caracter apostrophe.
I am inserting a value from vb.net to sql server 7. i created a table in the sqlserver with primary key. In VB.net form i didnt check the values of the check box. when i try to save the empty or null the sql server should throw the error. but its accepting the null value. how is it possible? can anyone help me? FYI: table name: JobMast Fields: jobcode int primary key jobtitle varchar(10)
Hello I am newbies with SQL 2005 I want install Windows sharepoint services 3 in french on my SBS 2003 R2 SP2 also in frecnh langage and at the end of the install, a message say to me : Bin caracter are not accepted in your configuration SQL Langage is not correct (I have French_CS_AS)
Where is possible to change these parameters ? I try to find but never find Thank You in advance for your help
++ Michel If you have the answer in French it's better for me but not neccessary !
