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

Searching All Columns Without Using Column Name

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

Searching In Columns Dynamically Without Dynamic SQL

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

Searching For Encrypted Fields In Data Columns

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

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

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

SQLCE Syntax For Multiple Word Columns

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

Is Pattern Matching Possible In Derived Columns Expression Syntax?

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

XML Source - No External Columns Avail-- Syntax Error In XML/XSD?

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

Combined Result...

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

Can These Tables Be Combined?

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

Combined 2cols Into 1

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

Combined 2 Data And Separate Them

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

Combined Sum Of Query From Two Tables?

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

Passing Variables From URL And DDL Combined

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

Need Help With SQL Statement (combined SELECT And WHERE At Same Time)

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

Using A Convert And Trim Statement Combined

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

Combined Orders Grand Total?

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

SELECT Statement To Display Combined Fields

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

Multiple Rows Combined Based On A Column

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

Mirroring :: Combined With Cluster And Asynchronous Mirror

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

Retrieve Data Form A Country Combined With 3 To 4 Cities

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

Errors With Combined Use Of Transactional And Merge Replication - SQL2005

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

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

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

Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character

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

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

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

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

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

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

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

Incorrect Syntax When There Appears To Be No Syntax Errors.

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

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

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

Converting Rrom Access Syntax To Sql Syntax

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

Searching For ._

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

Need Some Help In Searching

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

Searching With LIKE

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







Copyrights 2005-15 www.BigResource.com, All rights reserved