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...
View 1 Replies
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?
Thanks,
Brian
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.
View 2 Replies View RelatedI am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)
A sample time series would look like this:
11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT
The result of the SQL query should be:
Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45
This is the SQL statement I came up with but obviously doesn't work:
SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)
I would appreciate any suggestions.
Thank you!
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Assume you have a table called Tick with 2 columns
(
tickId bigint IDENTITY(1,1)
, price int -- usually money data type, making it int for simplicity
)
I am tasked with creating bars that are 10 units long.
Now the catch is I'm not looking for the tickId where price is >= t1(price) + 10 where t1(price) is the price for the first row where tickId = 1. (it could also be where price <= t1(price) - 10)
Here is sample data:
1, 25
2, 26
3, 23
4, 26
5, 27
6, 30
7, 34
8, 32
9, 30
10, 33
What I am looking for are rows 3 (23) and 7 (34)
Currently I have:
Code:
DECLARE @tickDiff int
SET @tickDiff = 10
DECLARE @r1TickId bigint
[Code].....
This seems to work but it is taking multiple minutes to run for about 50k rows of data (which I created off of the 24 million row table I have just looking at data from today). So it takes ~5 minutes to create the first bar which is not acceptible.
If my logic above seems acceptable are there any indexes you could recommend. Database engine tuning advisor didn't find any.
I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.
Any suggestions.
Thanks
Richard
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 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.
View 7 Replies View RelatedHi 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
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!
View 3 Replies View RelatedPlease 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?
Cheers
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'}.
Please help me.
Hi There,
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?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'
,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '
How Can I Ignore Accsents and diacritical marks in WHERE statement?
i use sql statment for searching and i wont to search in text filds where some accent and diacritical marks have been enterd.
like: i want to search for 'e' or 'o' ... letters in this text: "éüöä"
if i used: "Select * from table where fild like 'e' " the result will be 0 items.
Also, same proplem in Arabic accents for eg: if i want to search for 'موت' in this text 'مَوتÙ?' , using select where statement. what can i do?
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.
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
Grade Table is:
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.