SQL Server 200 View - Creating A Percentage Field With The '%' Char
Normally I don't get hung up on stuff like this, but for some reason, I cannot get the field to CAST/CONVERT correctly. Here's the part in question:
CASE WHEN pcs_Survey.Response2 < 3 THEN 1 ELSE 0 END +
CASE WHEN pcs_Survey.Response3 < 3 THEN 1 ELSE 0 END +
CASE WHEN pcs_Survey.Response4 < 3 THEN 1 ELSE 0 END +
CASE WHEN pcs_Survey.Response5 < 3 THEN 1 ELSE 0 END +
CASE WHEN pcs_Survey.Response6 < 3 THEN 1 ELSE 0 END)/5 *100 AS Overall_100,
This yields a percentage score, but what I cannot figure out is how to add the '%' character to the end. For example, the above code may return 84, but I want it to return as 84%. I've tried numerous CAST/CONVERT's, but I still haven't found the golden combination. For the time being, I've just created yet another field to hold the '%' by itself and then I'm putting the two fields together in the application.
Can somebody please help?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
ORDER BY (percentage Field) : SQL SERVER 2000
I'm trying to work with the ORDER BY clause, but it doesn't want to work with me. I have a percentage field coming from a view. I want to sort the results by that field. The problem that I'm having is that it's seeing 100% as a lower number than, say 40% (because 1 is less than 4). So, here's an example of how a standard data set may end up being sorted:
We only work in increments of 20% (-0-20-40-60-80-100-) so I don't need to bother with decimals and what not. However, I can't seem to get it to sort correctly. We have a workour in place using the other scaling formula, but to be 100% correct, I am trying to see if any experts have some suggestions to share.
Linking Access Tables To SQL Server 2000 With A Field Larger Than 255 Char.
I'm having a problem when I link tables in Access 97 (my client's version, not my choice!) to my SQL Server 2000 database. Several of the fields in some of the tables are either ntext(16) or varchar(3000) and when I link a table via ODBC it creates a text(255). To make matters worse, it doesn't even take the first 255 characters, if the data is longer than 255 char, it tosses out the first 255 and just takes the last few. Any ideas on how to convince Access to use Memo instead of Text?
Error While Creating A View In SQL Server 7.0
In SQLServer 7.0, I'm trying to create the view for a table which does indeed exist. When I go to add the table to the diagram area, when creating the view, an error comes up that says "CoInitialize has not been called." Any idea what the error is referring to? I've been looking around a few minutes and can't seem to detect what the error is all about.
Thanks so much!
Data Report: Percentage Field Problem
I would like to have a percentage field in the details section of my data report. My problem is I have no idea how to do this.
The percentage is calculated with rptTxtFieldNumber / rptTxtTotalFieldNumber . The rptTxtTotalFieldNumber is an
aggregate (SUM of rptTxtFieldNumber) field in the Group_Footer section of the report. This will then give me a percentage for each record.
Blank Char At The End Of String In Db Field
I've this problem:
While Not rs.EOF
rs.Fields("Device") = "Error"
The fields "Device" is a text field of 20 char in Access table.
In the above loop I think to write the string "Error" but If I try to read
the fields I found that:
How can I write only the 5 char of the string "Error" instead 5 char + 15 blanks?
Thanks to everybody,
Find Duplicate Of First 10 Char Of Field
I am using VB6, DAO on an Access database. I need to find duplicate records in the [Address] field. The query below, created by Access, will work to some extent, but it requires an exact match of the records. I would like to compare just the first 10 characters of the [Address] field.
I have tried several attempts using LEFT([Address],10) in the query but I have not been successful. Any ideas on what I can try next?
SELECT * FROM Customer
WHERE Address In (SELECT Address FROM Customer As Tmp GROUP BY Address HAVING Count(*)>1 )
ORDER BY Address;
Mysql - Trailing Spaces In Char Field
I have a textbox bound to a field that is CHAR(255). When I retrieve the recordset (or refresh it), the field correctly displays the string specified.
However, if I ammend the field and update it, (record displays correctly after I update it), then move to a different record - then back to this record, the field is displayed padded with trailing spaces. Refreshing the recordset will make it display correctly.
I had the understanding that char fields eliminate the padding blanks when it's read. So I really don't understand why this is happening - the textbox itself has no code applied to it that would do this, so it's just the ADO recordset.
Any ideas on resolving this - I dont want to incorporate a refresh of the recordset, if a user retrieves 1 record the query will be quick, but if they retrieve (for instance) the entire recordset then there is a pointless overhead.
Convert MSSQL Datetime Field To Char
How can I convert a MSSQL datetime field value to a normal date en time?
Can anyone help me with this?
Example MSSQL field:
numeric(18,0) = 1202417100000
How to convert the above numeric to a normal date and time.. in vb6??
Creating Slash Char On Textbox
hi i just want a slash "/" on my textbox .
for entering patient blood pressure...
the left position accepts maximum of 3 chars before going to the right position
120/80 normal blood pressure
80/100 if the patient is low blood
ill b saving this on the database..
Creating A Char-array Of A String
I need to put a string "HELLO" into a char array so i can access it with
mycrars(0) = H etc...
Is there a method for doing this in VB? I tried split, but it doesn't work without a delimiter. I have written a function myself in vb that does this, but now I am curious if such a method exists in vb? The way I solved it was by using Mid() and a loop. It looks kinda cheap :=)
btw, if I only need a variable as a single chatacter, how can I declare it? the char type doesn't exist in vb...
Henrik - Learning vb...
Char Before Carriage Return With SQL Server
(Sorry for my english)
I developp actually an application with Oracle database but we must migrate it to SQL Server.
The application is using RDO and DAO 3.51 to access the data.
So i have a problem : many tables have a field of type text (Memo in Ms Access) witch contain the types of data :
This is a test est123456
End of test
But when we migrate the data from Oracle or Access to SQL Server, SQL Server replace the carriage return by . (in others terms, he delete the carriage return char when the line is terminated by carriage return). So the data become :
317This is a test est123456End of test
With this problem, the application do not function properly.
So i want to migrate the data to SQL Server with no replacement of this char. I not want to change the code of the application and so the data must migrate like they are presented in the source database. (Actually, the only solution is to replace the carriage return by espace carriage return
Do you have a solution to this problem ???
How To Insert Chinese Char To SQL Server 7.0?
I am currently using SQL Server 7.0.
FYI, I have already set the data type of the field that will hold double-byte Chinese characters to be 'NVarchar'. When I use the "Import Data" feature, all the chinese characters can be imported into the fields, and displayed properly...
When I use VB to retrieve the data, the Chinese character can be retrieved and displayed properly. However, when I tried to do a normal data insertion back to the SQL Server DB, it is stored as "??????".
Why????? CAN SOMEONE HELP!!! Do I need to change any configuration in SQL Server to accept double-byte characters? Like "Set Lanaguage"??? If so, how to use it, there's no variable to set for Traditional Chinese.
Or, do I have to convert the data into DBSC before inserting to the DB?? If so, what commands can I use?
Appreciate you help greatly!!! Thanks.
2003 Server Char Problem
hi i have a problem about 2003 server.
i use vb6 with SQL 2000 in 2003 server.
i can connect to SQL server. after connection :
if i do following code
rs.open "SELECT det FROM tdeti ",cnn
there is not problem.
but when i want to get the data in some date i cant get data.the error is tdate is smalldatetime you can use char (something like that)
rs.open "SELECT det FROM tdeti WHERE tdate='" & dtpicker1.value & "'",cnn
i can get that data in query analyzer in 2003 server
.vb6 code works in XP,2000 server
but not in 2003 server.
what can i do? please help
Field Of View
I want to limit the viewable area of the player during certain situations. I've search for Field of View but can't seem to find anything to help me.
Here is a diagram showing what I'm after.
Here is a map
now let's say its night time. The * is what the player can see and P is the player
I saw something like this but it was for hex tiles. It did something with pi and radius. Would those help me with this? hehe I feel so stupid... guess I should have paid more attention in math class.
Splitting A Field Within A View
Hi can anyone help,
I have a view that brings back fields, one of which is an address field.
this is seperated in the field with line feed carrige returns.
within my code i can bring the view back and break down this field and store each string into a varible.
But i want to know if i can do this within an SQL view.
so that in the view i have fields Address (original long string), and Address1, Address2, Address3, Address4 all with there split strings.
Any ideas would be great.
Getting Field Names From A View In A Dabase
I need to programatically get field names from view in an oracle database. Is there an easy way to do that? I tried DESC but got 'invalid syntax' error when tried to get result set. I think field names come when you do a simple SELECT *...but the view is huge and I don't want to do that. Is there a way to limit result to only one row of resutls, therefore getting just the column names?
MSHflexgrid Specific Field View
i have following fileds with data in my SQL table
field1 field2 field3 field4
sr.no. | username | password | created on|
01 | admin |manish12 | 8/2/2008 |
02 | user1 | sawant1 | 3/2/2008 |
I have taken MSHflexgrid control where i am showing above details. But I wanted to show password field with someting like *****, it should not show actual password value in MSHflexgrid control. Any code for this
Can I Sort A List View By A Numeric Field
I have a list view which I populate at run time, including creating the column headers. I don't know exactly what the columns are going to be at design time.
I want the column to be sortable when a user clicks on the column header but a list view uses a text sort by default, so in a numeric column 99 will appear to be higher then 100. Normally I'd get around this by hiding a column with the numeric values left padded with zeros and then sort by that instead but, due to the dynamic nature of the columns, that's going to be a bit of a nightmare to manage.
Does anyone know of a way I can 'tell' the column it's a numeric so that it sorts in a numeric fashion, or maybe override the sort algorithm myself?
Password-field View (Handles, API, Mouse)
Hello, fellow Visual Basic programmer,
I've recently more or less finished the PortScan! It's done if you ask me, I'm letting a friend testing it, so maybe I have to fix some bugs or something. But that's irrelevant.
I'm wanting to make a program which reveals the text contained by a TextBox, protected with PasswordChars (The "******" when you type in a password).
My goal is making the mouse icon a key (I know how to do that, so that's not included in my question), and when clicking a TextBox (parented by any window, not just mine only) it should retreive it's string. What I'm going to do with that, is either showing it under the mouse icon or replace the TextBox's text.
The main thing I would like to know is how to retreive. I know I firstly have to determine the TextBox's handle which is clicked, and with that handle, using API to reveal, or something near.
Why? For educational purposes. In case of suspition of illegal use, I hope it will be enough, giving my word of honour. I'm just a 15-year old (Dutch) Visual Basic programmer who's goal is gaining programming knowledge.
I'm hoping on clear explainations and/or examples. I appreciate any help.
Thank you in advance,
Creating A View At Runtime
LEt me through this one out there for everyone. I am creating a database on the fly, with the following tble "Index_Page" I am doing this through an ADO connection. Now no problem with this at all. Here is where the problem comes from, I wont to create a view in that database.
Code:[Public Sub CreateDB()
On Error GoTo CreateDB_Err
Dim CatNewDB As ADOX.Catalog
Dim strTemp As String
' On Error Resume Next
'Create new database if one doesnt exist
100 Set CatNewDB = New ADOX.Catalog
105 CatNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "" & lvwStreet.SelectedItem & ConfigID & "S.mdb"
110 Set CatNewDB = Nothing
115 strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "" & lvwStreet.SelectedItem & ConfigID & "S.mdb"
120 cnIndex.Open strConnection
'Add tables to the database
125 cnIndex.Execute "Create Table Index_Page(Road text(50),FullStreet text(50), LoLeft INTEGER, HiLeft INTEGER, LoRight INTEGER, HiRight INTEGER, StreetType text(20), Page text(20), Pre_Dir text(10), Suf_dir text(10), Aka Text(50), T_Node text(50), F_Node text(50))"
126 cnIndex.Execute "Create View St_Sum as SELECT FullStreet', Page, IIf([LoLeft]<[LoRight],[LoLeft],[LoRight]) AS LRange, IIf([HiLeft]>[HiRight],[HiLeft],[HiRight]) AS HRange, Aka, T_Node, F_Node FROM Index_Page ORDER BY FullStreet, Page, IIf([LoLeft]<[LoRight],[LoLeft],[LoRight]), IIf([HiLeft]>[HiRight],[HiLeft],[HiRight])"
Select Case MsgBox("Error " & Err.Number & ": " & Err.Description & vbCrLf & "in MapBookProAdmin.frmMain.CreateDB at line " & Erl, vbAbortRetryIgnore, "MapBookProAdmin Error")
Screen.MousePointer = 0
Line 126 is my issue
Which is what i would use if I werent doing an ADO connection. Anyone have a suggestion on creating a view using an ado connection, by the way its an access database.
Creating A Folder/File View
I am trying to re-create filelistbox so that it is similar to that of XP. I was wondering if anyone has done this before and has some source lying about. Otherwise, some tips on getting started would be great.
I would like it to be able to read and display up to 128x128 icons, and unlike some others ive seen I dont want it to use the system shell icons as alot of them time people have selected custom icons for those folders.
If anyone would like to colaborate on the project, it will be a great future source for all =]
Problem In Creating The View In Msaccess
Please help me
I am trying to create a msaccess view from visual basic code, it was creating but not displaying in access queries.
I am giving the source what i was used.
Public gcon As New ADODB.Connection
Dim sql As String
gcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Databasedbabc.mdb;Jet OLEDBatabase Password=abc;Persist Security Info=False"
sql="Create view vwTrsummary as (select * from Transactions)"
gcon.Execute "Drop View vwTrSummary"
Thanx in advance.
SQL Server View Help
I need help with the FROM part of a SQL statement for a SQL Server 2000 view. I need to write a view that pulls records from a database table on a different database and a different server? The server is within my LAN. Thank you to all who can help.
Help With Creating A Folder View Type Window
I am trying to figure out how to develope a window, such as any windows folder view. IE Each Column is resizable, when resized the text in each column is displayed accordingly. Can some one point me in the direction please.
View Server Directories
Have anyone create a server and client applications where the
client application is able to view the directories of the
server ? Thanks in advance.
View Server Directories
Have anyone create a server and client applications where the
client application is able to view the directories of the
server ? Thanks in advance.
SQL Server 2k (copy View From 1 DB To Another)
I need to move a view from one DB on a SQL Server 2k box to another DB...(this is not through code) I just need to copy it in the SQL Server enterprise manager mmc, when I import data and select it, it imports it as a table into the new DB, I want it to copy over as a view
Access A View From Sql Server 7.0
I'm trying to access a View defined in a SQL Server database from VisualBasic, however I can retrieve the data from the tables to VB. Cansomeone give me a hint?
SQL Server View Is Not Visible
I created View in SQL Server 2000 database, opened a new report, in Options I selected Tables, Views and Stored Procedures. While I see Tables and stored procedures, Views are not there. What I'm doing wrong?
Missing Data When Creating Labels To Print But Not From View.
I am in the process of moving a Visual Basic 5.0 using Crystal Rpeorts from one server to another. When I test the label printing I get a label without City, State and Zip. I preview the print and print it from there and I get the label OK. I am really new to Visual basic and Crystal reports - inherited application to support. Please help.
Strings - Running Through Them Char By Char
How do you go about selecting each individual character from a string.
For example, I only want the value of the four right hand characters on a string I will not know the length of. It sounds like an easy thing to answer, but I just can't figure it out.
Thanks for all help, in advance.
Knows a little about a lot
Moving Char-by-char In A Given String
can anyone knows how to move char-by-char in a given string, this i figured so far
For i = 1 To Len(strTest)
inside this loop should be a statement to access the first char, anyone knows it??
Does Anyone Here Understand Char 10 And Char 13 Vb Chars?
in order to unlock a password for something? it is 9 lines and i am wondering if anyone knows how to figure out what it is.. since they don't show up in html or e-mails.. i also know that if you made all possiblities with char10 and char13 vb characters for a 9 letter pw it has to be one of them.
does anyone understand these or can help me?
Can I Read Line Char By Char
Can I take a line of input and read each character withou assigning whole line to a string? If so can you give an example?
DirListBox View Drives On Ftp Server
I am using a dart control to connect to a FTP server and i am trying to use a DirListBox to display the folders on the server. Any suggestion would be great!
Passing Parameters To A View (SQL SERVER)
Does anybody know how to pass a parameters to a view?
I want to create a view depending on a value i want to know two things:
1. How i do it on vb design time
2. If the view was created using sql how i do say to SQL that i want a parameters
Select Statement Using A View From SQL Server.
I am using Sql server and have an ADO connection to the database and when I
set a recordset to the records. Can this be done using a view? If so how
would the select statement look for that?
Below I have code for how I tried to connect to it.
Dim adoPrimaryRS As Recordset
Dim mbDataChanged As Boolean
Private Sub Form_Load()
Set adoPrimaryRS = New Recordset
Client_ID,Client_Name,Current_Build,Date_Of_Build,A ctive From
Active_Clients", conn, adOpenStatic, adLockOptimistic
Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Dim oCheck As CheckBox
'Bind the check boxes to the data provider
For Each oCheck In Me.chkFields
Set oCheck.DataSource = adoPrimaryRS
mbDataChanged = False
The bold statement is the query I used to try to connect to the records. The
call main is my connection string to the database. I hope someone can help
Thanks in advance.
View SQL Server Transaction Logs
Does anyone know of a cheap (preferably free) tool/utility that can be used to analyse a SQL Server transaction log? I've got someone here taking the ****, and I would like to show that they are so I can make them look stupid (for want of a better word!).
I've downloaded Lumigent Log Explorer, but the Demo will not read my log file and the cost is prohibitive.
Any pointers would be most appreciated.
Writing A View With Time In Sql 2000 Server
how do i write a "view" in sql
in which the view automaticlly run's once a week
and build a quesry which i will see it's result
once a week for all the week
and it wil be updates
only once a week
thnaks in advance
Update/Append To An SQL Server View Through MS Access
I have a SQL Server view that has three linked tables (one main table and two reference data lookup type tables) and have added "INSTEAD OF UPDATE" / "INSTEAD OF INSERT" triggers to it so that I can add/update the main table through the view.
This works well from T-SQL or through the enterprise manager application, but when I link to the view through Microsoft Access it is read only. Does anyone know how I can convince Microsoft Access that the view is, in fact, editable.
Very Very Slow View In SQL Server 2005 Express
I am experiencing a very query of a View on my SQL Server.
I have a table called 'Actions' that contains about 2000 records (So far)
I have a table called 'Parts' that can have many actions. It contains about 1600 records. (the database is still young so there are many parts that have ony one 'Action' record associated with them.
The 'Actions' are stored for Historical purposes. One may wish to view all Actions that occured for a part over a period of time.
However for day to day usage we only want to return the most recent 'Action'. To do this I have a view Called 'Current_Actions 'that displays only the most recent 'Action' per part_id. The Slow view in question Called 'Current_Parts uses 'Current Actions to join all 'Part' Records with the most recent 'Actions'.
Currently this results in about 1600 records. (of course.)
The problem is it takes about 6 seconds to generate the View when called from an application. "SELECT * FROM Current_Parts"
Any query I make that has to touch all records from Current parts now takes at least that long.
"SELECT * FROM Active_JobNos WHERE WT_ID IN
(SELECT WT_ID FROM Current_Parts
GROUP BY WT_ID,Act_Status HAVING Act_Status = 'NST')
ORDER BY SO_No ASC, WT_No ASC"
Takes 7 seconds.
This doesn't sound so bad until you look at the number of records.
I plan to have one heck of a lot more in the future. I can only envision that it will take longer and longer as time goes on.
Any Ideas on how to speed up the process?
Data Grid Bound To A SQl Server View
Most of you would have come across similar situations many times before and would have solved it in your own style and preference. let me explain the situation.
I have the
(1)ItemRefsLookup Table :
CREATE TABLE [dbo].[ItemRefsLookUp] (
[ItemRef] [varchar] (25) NOT NULL ,
[ItemDesc] [varchar] (50) NULL ,
[ItemType] [varchar] (10) NULL )
(2)ItemCostings Table :
CREATE TABLE [dbo].[ItemCostings] (
[ItemCostingsID] uniqueidentifier ROWGUIDCOL NOT NULL , [ItemRef] [varchar] (25) NOT NULL ,
[SubsidiaryCode] [varchar] (3) NOT NULL ,
[QuotedPrice] [decimal](18, 0) NULL ,
[ProdCost] [decimal](18, 0) NULL ,
[SellPrice] [decimal](18, 0) NULL ,
[Commission] [decimal](18, 0) NULL ,
[CurrencyCode] [varchar] (3) NOT NULL )
I have a foreign key declared as
ALTER TABLE [dbo].[ItemCostings]
ADD CONSTRAINT [FK_ItemCostings_ItemRefsLookUp] FOREIGN KEY ([ItemRef]) REFERENCES [dbo].[ItemRefsLookUp]
([ItemRef]) ON DELETE CASCADE ON UPDATE CASCADE ,
Now I need to design a data entry screen for these two tables using Visual Basic 6.0.
(1) I created a View as follows:
CREATE VIEW dbo.VIEWItemRef
SELECT dbo.ItemRefsLookUp.ItemRef AS ItemRef, dbo.ItemRefsLookUp.ItemDesc, dbo.ItemRefsLookUp.ItemType, dbo.ItemCostings.QuotedPrice, dbo.ItemCostings.SellPrice, dbo.ItemCostings.Commission, dbo.ItemCostings.ProdCost, dbo.ItemCostings.SubsidiaryCode,dbo.ItemCostings.CurrencyCode, dbo.ItemCostings.ItemCostingsID FROM dbo.ItemRefsLookUp INNER JOIN dbo.ItemCostings
dbo.ItemRefsLookUp.ItemRef = dbo.ItemCostings.ItemRef
(2)Created a VB form with a DataGrid control in it with a ADODC control. The View was used as the record source for the Grid and then the ADODC was used as the source for the grid.
(3)Brilliant it does display the data properly allows me delete any rows selected and also allows me to edit the rows whichever I selected to edit.
(4)But when i try to add a new row it comes up with errors asking me to check on the status of each OLEDB.
(5)I have read in this forum that to insert using Views we have to use an Instead Of Insert Trigger.
(6) I did create a trigger in the lines what some freinds suggested as follows:
CREATE TRIGGER UTrig_INS_ItemRef ON [dbo].[ViewItemRef]
INSTEAD OF INSERT
Declare @SubsCode As Varchar(5)
Declare @CurrCode As Varchar(5)
SET NOCOUNT ON
-- Check for duplicate ItemRef in ItemrefsLookup table . If no duplicate, do an insert.
IF (NOT EXISTS (SELECT IR.ItemRef
FROM ItemRefsLookup IR, inserted I
WHERE IR.ItemRef = I.ItemRef))
INSERT INTO ItemRefsLookup
SELECT ItemRef , ItemDesc, Itemtype
IF (NOT EXISTS (SELECT IC.ItemRef
FROM ItemCostings IC, inserted
WHERE IC.ItemRef = inserted.ItemRef))
INSERT INTO ItemCostings
SELECT ItemCostingsID, ItemRef ,
'100' As SubsidiaryCode,
QuotedPrice, ProdCost, SellPrice, Commission ,
'HKD' As CurrencyCode
(7)This trigger works fine when I directly try to do an insert using the Query Analyser. But it still wont work via the data grid.
I am really stuck and would appreciate if someone can help please. If you have different approach to design this data entry please do advice. Its only very recently I started using Stored procedures and triggers etc. So please ber with my ignorance.