|
The example you gave did not look like a stored procedure. It looked like a text SQL command. Stored procedures don't use wildcards (?) as parameter tokens.
If you would like to provide the stored procedure source, I will try to help you. I'd prefer to see what you've got versus rewriting something and missing some behavioral nuance.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Sorry, just got back in town; here is the stored procedure. Looks like you're right, it doesn't pull if I query both a First and Last name.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand
(
@Param2 varchar(35)
)
AS
SET NOCOUNT ON;
SELECT docIndex, docLName, docFName, docMName FROM dbo.tblStaffPhysicians
WHERE (docFName + ' ' + ISNULL (docMName, '') + ' ' + docLName LIKE '%' + @Param2 + '%')
ORDER BY docLName, docFName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- modified at 20:49 Sunday 18th September, 2005
|
|
|
|
|
Something like the following would work while preserving some benefit from any indices on the table columns and avoiding the double-space problem you'll encounter with the short version you're using now.
The procedure below parses the name provided as follows:
1. Everything from the start of the string to the position of the first space is considered the first name.
2. Everything from the position of the first space to the position of the second space is considered the middle name.
3. Everything from the position of the second space to the end of the string is considered the last name.
There are obvious issues with even the long version I'm attaching here. For instance, with names that include spaces (e.g. 'Jack Van Horn'). This name will cause problems with either version of the stored procedure; in the version you're using, if no middle name is stored in the database, you'll get the double-space issue, and in my version, 'Van' will get chosen as the middle name, and 'Horn' as the last which will lead to the same conclusion - possibly no returned records.
I don't know how critical this search mechanism is to your users, but I really would suggest looking at using a different solution for finding these names. One possibility would be to just do away with using the middle name in the search altogether, and having a new (calculated) column in your table representing First + ' ' + Last names, then just search on that, or do an edit distance algorithm or something.
Anyway, here's another way to do it, as promised:
<code>
CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand
(
@Param2 varchar(35)
)
AS
SET NOCOUNT ON;
-- Declare local variables
declare @firstName nvarchar(35)
declare @middleName nvarchar(35)
declare @lastName nvarchar(35)
declare @count int
declare @pos int
declare @oldPos int
declare @Param2 nvarchar(35)
-- Initialize local variables
set @oldPos = 0
set @count = 0
set @Param2 = 'a c'
-- Parse the name parameter
set @pos = charindex(' ', @Param2)
while (@pos > 0)
begin
set @count = @count + 1
if (@count <= 2)
begin
if (@count = 1)
-- First name
set @firstName = substring(@Param2, @oldPos, @pos - @oldPos)
else
-- Middle name
set @middleName = substring(@Param2, @oldPos, @pos - @oldPos)
end
else if (@count = 3)
begin
-- Last name
set @lastName = right(@Param2, len(@Param2) - @oldPos + 1)
break
end
-- Set a position marker and iterate
set @oldPos = @pos + 1
set @pos = charindex(' ', @Param2, @pos + 1)
-- If there are no further spaces, but the end of the string
-- has not been reached, place the remainder into the next
-- open slot
if (@pos = 0 and @count < 3)
begin
if (@count = 1)
set @middleName = right(@Param2, len(@Param2) - @oldPos + 1)
else
set @lastName = right(@Param2, len(@Param2) - @oldPos + 1)
set @count = @count + 1
end
end
-- Evaluate the count: if only 2 words were found,
-- assume they were a first and last name, adjut the
-- values accordingly
if (@count = 2)
begin
set @lastName = @middleName
set @middleName = null
end
-- Finally, select the matching names
SELECT
docIndex, docLName, docFName, docMName
FROM
dbo.tblStaffPhysicians
WHERE
docFName like '%' + @firstName + '%' AND
((docMName is null and @middleName is null) OR (docMName like '%' + isnull(@middleName, '') + '%')) AND
docLName like '%' + @lastName + '%'
ORDER BY docLName, docFName
</code>
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
The option CONCAT_NULL_YIELDS_NULL controls what happens when you concatenate a NULL with a string. As the name suggests, when you concatenate a NULL with a string and this option is set, the result is NULL.
This option is not set by default in the database, but is set by the ODBC driver and OLE DB provider. I'm guessing it's also set by the ADO.NET provider, although I can't find documentation on this.
So, when you get a NULL in the docMName column, the result of the concatenation is NULL, hence it doesn't match the query. The change you found on the other forum works because it changes a NULL to an empty string with the ISNULL function.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Yes, but I'm not out of the woods yet. When I search on a combined string it (John Smith) it fails to return any rows.
|
|
|
|
|
Ehi guys,
i've tried in the past to make some application in c# that use ADO.NET with Databasa Access.
It is a quite easy stuff, but i know i can use mySQL database too.
I Have no idea in matter, so i don't know if i need during the develope mySQL engine daemon running on my pc and if it is needed also for the final user.
Then i don't know how create a database in this way, connect it to my application and working on.
Finally i don't know how include it in my solution.
Big thanks to everyone can jelp me.
|
|
|
|
|
hi there,
Here are just some articles on CP site:
1. http://www.codeproject.com/dotnet/mysqlconnexion.asp
2. http://www.codeproject.com/cs/database/ConnectMySQL.asp#xx1105859xx
Hope that helps
<< >>
|
|
|
|
|
ok tnx for your indication but is there someone that can contribuite?
|
|
|
|
|
Why don't you use the MySQL Connector for .NET 1.0.5 from MySQL.com ?
|
|
|
|
|
becouse i don't know it and how use.
|
|
|
|
|
Hi,
here is the link: http://dev.mysql.com/downloads/connector/net/1.0.html
Downlaod and install the connector. After finishing the installation, you can use it like any other ADO.NET-Connector.
André
|
|
|
|
|
Hi
The situation i m facing is - i have several databases of lower level shops which needed to be consolidated on regional level, shops' DB will be ported on the regional HQ n on the basis of databases imported, reports will be generated.
Well, do u guys have any experiance of such situation or if you can suggest me some link which describes such situation in form of case study or whatever.
Thanks in advance
Utsav Verma
|
|
|
|
|
|
I have made windows application using .NET 2.0 BETA 2.0(VC#) with MSDE(Release A) as database tool. Now when I install my application on Windows 2000,XP,2003, it works well. But When I install on Windows 98, application is not connecting to database..
On command prompt, using OSQL, I can list database, table and I can connect to database. But When I try to connect throught application using connection string then it does not work.
Please help me sorting out this problem.
Thanks
1) Connection String is same in all O.S. version and it is SQL Auth.
2) I wrote following things in setup.ini of MSDE
[OPTIONS]
SAPWD="NILESH"
INSTANCENAME="NILESH"
SECURITYMODE=SQL
3) In Windows 98, I have used MDAC 2.8
Nilesh Gambhava,
Life is NP-Complete so don't use Algorithm but use Heuristics
|
|
|
|
|
Friends,
I've just installed MSDE in my machine for the first time. What i've seen is that it only installed Server and no client tools, unlike SQL server 2000.
Are there any client tools available for MSDE (like Enterprise Manager or query analyzer of SQL-server 2000). If not, then how can i manipulate the database of MSDE and create or view databases??
Ahsan
|
|
|
|
|
hi there,
have a look at on these:
1.http://www.visoracle.com/ada/bmg.html
2.http://www.itlocation.com/en/software/prd53603,,.htm
remember the google uncle.
<< >>
|
|
|
|
|
If you have a licence for the Sql server client tools, then you can use that.
Otherwise you are stuck with the commandline tools or 3rd party applications.
I like to use this[^] when I'm working with MSDE. Not as friendly as the MS tools but does the job well.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Let's presume I have Table1 and Table2 already created and with same strucuture. Table1 has all data. So is this fastest way to copy data:
INSERT INTO Table2
SELECT * FROM Table1
?
Tnx
|
|
|
|
|
I think this is the fastest. You can try comparing the performance with
SELECT * INTO Table2 FROM Table1
Live Life King Size
Alomgir Miah
|
|
|
|
|
If they are in the same database this should be the fastest or close to it.
If you have the transaction log configured to not log it, then using SELECT * INTO Table2 will create table2 and won't be logged which is faster.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi All!
I have a variable in Form1 named:GetID(int) and everytime I run Myapplication , its value maybe vary Like 2 or 9 or 5 or ...
THIS variable is in btnOK_Click() :
here is my code in Form1:
.....
public int GetID;
....
public void btnOK_Click(object sender, System.EventArgs e)
{
.....
GetID=Convert.ToInt32(UserTable.Rows[0][0]);
....
}
I want to put (this value=GetID) into the another variable in Form2 that named :MyGetID in Form2_Load():
Here is my code on form2:
.......
Form1 myform1=new Form2();
........
private void Form2_Load(object sender, System.EventArgs e)
{
int MyGetID;
MyGetID=:zzz:(?GetID);
....
...
}
infact I want to put the value of GetID(the variable in Form1) into the MyGetID(The variable in Form2)
How Can I do it ...?
Please Help me!
thanks!
mostafa hosseiny b.
|
|
|
|
|
Many ways, all depends on timing... When do you need the value exactly
Static variable in form1..
public static int x;<br />
myID = form1.x;
or
Change the form2 constructor, declare a local private variable.
(Note: you can have multiple constructors).
Class Form2{<br />
private int var;<br />
Form2(int x){<br />
var = x;<br />
}<br />
}
in form1 create as
form2 f = new form2(x);
or
as a property
public int x{get(...)set(...)}
Hope that helps.
|
|
|
|
|
hi,dear friend!
Via to:
public static int x;
myID = form1.x;
because of "x" is variable in a Event(btnOK_Click()),
,myGetID return a value=0(Zero),
but,using a property in form1 and form2 , I can solve the problem .
thanks again!
yours!
mostafa hosseiny b.
|
|
|
|
|
Hi
I have a select statement that returns (A int ,B int)
and another select statement that returns (C int , D int)
in a storedprocedure.
I need that sp returns a resultset as (A,B,C,D);
What should i do plz?
|
|
|
|
|
You can combine the two queries as following,
select Select1.A, Select1.B, Select2.C, Select2.D
from
(select A, B from Table1) as Select1,
(select C, D from Table2) as Select2
Please note that this will give you the cross product.
|
|
|
|
|