Syntax? - Searching Combined Columns
Aug 31, 2005
Dear Group
The scaenario is as follows:
FirstName and LastName are separate columns in the contact table and I
want to be able to search e.g. for the FirstName and part of the
LastName at the same time e.g. 'John A' should return 'John Adams'.
Would be grateful if you can give me some hint as I don't seem to get
it work.
SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +
Lastname AS CName) LIKE 'John A%'
Thanks very much for your help and efforts!
Martin
View 2 Replies
ADVERTISEMENT
Dec 30, 2003
Hi,
I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
View 4 Replies
View Related
May 8, 2007
Hi everyone
Is it possible to search dynamically in table columns without using dynamic SQL (i.e. EXEC or sp_executesql)?
I wanna exec FREETEXT(<column_name>, @searchPhrase) by giving columns' names as paramater to stored proc. Is it possible to avoid using dynamic SQL?
Thanks in advance
View 8 Replies
View Related
Jul 20, 2005
I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
View 10 Replies
View Related
Feb 12, 2007
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
View 12 Replies
View Related
Feb 2, 2005
What is the syntax when creating a SQL statement and using a column that has a multiple word name?
I know that in query analyzer it should be delimited? by double quotation marks like such:
SELECT Item, "Item Description" FROM ComponentList WHERE Item = 'CPU'
The above works great in Query Analyzer on the device and in the device emulator.
but I'm attempting to build a SQL string in code...VB.Net using CF and PocketPC SDK, SQLCE on the PocketPC device....
Here's the snippet that does not work:
sql = "SELECT Item, 'Item Description' FROM ComponentList WHERE Item = '" & selectedItem & "' "
Try
cnITSComponents.Open()
cmdItems = New SqlCeCommand(sql, cnITSComponents)
drItems = cmdItems.ExecuteReader()
fieldCount = drItems.FieldCount
While drItems.Read()
.....More code here not copied
I'm trying to fill a listview control and when I use the above syntax one of the columns is filled with the text Item Description instead of pulling the actual Item Description from the table...So what is the correct way to construct the SQL string?
Thanks
Greg
View 5 Replies
View Related
Feb 8, 2008
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
View 9 Replies
View Related
Jan 28, 2008
Hi All,
Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.
XML File:
Code Snippet
<?xml version="1.0" encoding="utf-16"?>
<AccountResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AccountNumber>S12345678</AccountNumber>
<RecordNumber>AAA1122</RecordNumber>
<ProcessedDate>Monday, January 28, 2008 11:07 AM</ProcessedDate>
<Status>0</Status>
<StatusMessage>Complete</StatusMessage>
</AccountResponse>
XSD File:
Code Snippet
<?xml version="1.0"?>
<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">
<xs:element name="AccountResponse">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="AccountNumber" type="xs:string" />
<xs:element minOccurs="0" name="RecordNumber" type="xs:string" />
<xs:element minOccurs="0" name="ProcessedDate" type="xs:string" />
<xs:element minOccurs="0" name="Status" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="StatusMessage" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xsd:schema>
Thanks!
View 1 Replies
View Related
Jun 1, 2006
hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.
View 3 Replies
View Related
Mar 5, 2004
I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.
This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.
This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.
Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.
Orders:
Contains the core order information pertaining to all transactions
CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
)
Transactions:
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.
CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
(
[OrderID],
[SequenceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
)
View 1 Replies
View Related
Mar 24, 2008
create view vwchannel
as
select distinct s2.soptype,
s2.sopnumbe , --internet orders/information center orders
s2.custnmbr , --amazon.com orders
s3,itemnmbr, sum(s3.quantity) from salestab s2
left outer join (select distinct soptype,sopnumbe,itemnmbr,quantity from salesdisttab) s3
on s2.soptype = s3.soptype and s2.sopnumbe = s3.sopnumbe
where (s2.CUSTNMBR LIKE 'amazon%')
AND ((s2.SOPNUMBE LIKE 'net%') OR (s2.SOPNUMBE LIKE 'inv%'))
AND s2.soptype = 3
group by s3.itemnmbr,s2.sopnumbe,s2.custnmbr,s2.soptype
go
i m getting 70 rows in output which is correct but..
i have to combine sopnumbe and custnmbr into one column name as channel
how can i do that?
i tried like:
case when (s2.SOPNUMBE LIKE 'net%' OR s2.SOPNUMBE LIKE 'inv%') then s2.sopnumbe
when s2.CUSTNMBR LIKE 'amazon%' then s2.custnmbr
end as channel
but i m getting 0rows affected in ouput.. instead of 70rows...
can anyone help me?
thanks.
View 3 Replies
View Related
May 26, 2005
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View 1 Replies
View Related
Apr 9, 2012
I'm running a query to show the transfer fees spent by a club with data from two tables :
Code:
SELECT SUM([Transfer Fee]) From [Summer_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Union All
SELECT SUM([Transfer Fee]) From [Winter_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Shows the sum from each table :
Code:
545.01
110.98
What do I need to add to the query to show the total from both tables?
View 5 Replies
View Related
Sep 10, 2007
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.
We have a report in which there are 2 parameters (Drop Downs).
Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine.
Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)
Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
View 4 Replies
View Related
Oct 29, 2007
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category".
Sample:
Human Resources
All documents related to new hire process.
New Hire Request forms, New Hire forms, Termination Forms
Total Files: 25
Programs & Utilities
Windows utilities and other tools to keep you safe on the net.
Antivirus, Spyware Removal Tools, Other programs
Total Files: 37
My SQL2005 DB has 2 tables.
1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription)
2) Files. (CategoryID, ParentCAtegoryID, FileTitle)
My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents.
My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only.
Sample:
Human Resources. CategoryID=4, ParentCategoryID=NULL
New Hire Request forms. CategoryID=13, Parent CategoryID=4
New Hire Forms. CategoryID=17, Parent CategoryID=4
Termination Forms. CategoryID=22, Parent CategoryID=4
Programs & Utilities. CategoryID=6, ParentCategoryID=NULL
Antivirus. CategoryID=8, Parent CategoryID=6
SpyWare Removal Tools. CategoryID=26, Parent CategoryID=6
Other Programs. CategoryID=31, Parent CategoryID=6
When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories.
Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else.
I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
View 2 Replies
View Related
Aug 4, 2000
I need t do some date converts on some varchar fields which
might have trailing spaces.
I tried this below but still
select df_ppd, df_xray_date from patient_
where convert(datetime,rtrim(df_ppd),101) > '06/20/2000'
received a conversion error message
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any ideas?
View 1 Replies
View Related
Apr 20, 2007
Hi,
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
Anyone help!?
thanks
View 3 Replies
View Related
Oct 17, 2004
Dear All
I am trying to populate an OledbDatareader for binding to a ASP datagrid.
For this I use select statement to display combined fields in a datagrid cell.
Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
But the problem is if any of the three field is null the combined field 'Address' returns as Null.
Please help me to overcome this problem.
Regards
kalanad ( beginner)
View 12 Replies
View Related
Sep 6, 2011
I have a table with two columns refid and name and it has the following values
1 tom
1 jim
2 bob
1 bob
I need a resultset that would have the following values
1 tom, jim, bob
2 bob
I have tried couple of things one being:
DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist
But I am looking for a resultset with a unique refid and all the names comma separated for that refid.
View 2 Replies
View Related
Jun 1, 2015
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node
b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
View 11 Replies
View Related
Feb 12, 2015
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
View 1 Replies
View Related
Oct 13, 2006
I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
View 10 Replies
View Related
Sep 1, 2014
Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS
[Code] .....
View 2 Replies
View Related
Oct 24, 2007
Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS
View 2 Replies
View Related
Sep 17, 2015
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
View 2 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
Apr 24, 2008
I am trying to find all the email addresses with a " ._" I use '%._%' but it returns all records. What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton
SELECT DISTINCT fname, lname, inet
FROM ocadbo.notes
where inet like '%._%'
View 6 Replies
View Related
May 8, 2004
Dear ASP.NET
How can I find records that contain a STRING from some (more than one) other fields ?
for example, I have:
Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"
Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)
How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?
Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)
Thanks in advance, Yovav.
View 3 Replies
View Related
Feb 13, 2006
Let say a user wants to search for the name Joe Soap
I have two column's in my table, firstname and lastname
So if I do:
Code:
SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'
it returns nothing! So do I have to split the string Joe Soap or something ?
View 1 Replies
View Related