Simple Search for a Simple Guy 1

[dalec]

I've searched the last 3 nights (lucky me huh!) and I've tested and tested, but, can't get the results I need. Please HELP!!!

What I want to do is search a text field nvarchar(50) from left to right. So, if a user is searching for let's say Targets but only enteres tar this is the result I want:

Tar
Targets
Tarpets
Tarpoops
etc.

I've tried left, substring, like, etc. sometimes I get only the items that begin with "T" (like the above example) except I'll be searching "Tar" and get "tolerance" but that doesnt have Tar in it???

Thanks in advance, please tell me I'm not crazy!

Here's my stored procedure:

@NameSch nvarchar,
@LengthSch integer

AS
SELECT * from supplier_loc
where left(Name,@LengthSch) = @NameSch

order by Name

GO

(Note: the where clause has been changed quite a bit to different things I've been trying, this is just the current version I've been using before posting this).

 

[dalec]

I'll check that and post results back, Thanks

 

[dalec]

OK, I ran a trace using profiler, and it appears that although my program is passing the entire string, the sp is only getting (or processing) the first letter of the search. I don't know why, so any help will be appreciated. I'm also going to search google and see what I can find. I can't be the only one, but, If I find something out I'll post back. thank and thanks in advance.

 

[dalec]

I changed my calling statement a little, but, still it only passes the first character. Here's what shows in the trace, maybe this will shed some light.

New passing Code:

Dim Search As String
Dim LengthSearch As Integer
Search = Trim(Me.Tag)
LengthSearch = Len(Me.Tag)
Me.Supplier_LocTableAdapter.SchSupplierName(Me.ChapmanloadDataSet_11.Supplier_Loc, NameSch:=Search, LengthSch:=LengthSearch)

This is from the trace:

exec dbo.SupplierSch @NameSch = N't', @LengthSch = 2

I don't know what the 'N' next to the 't' means, by the way I'm passing the search as 'tr', but, just the 't' looks like shows up????

thanks in advance.

 

[markros]

What is your front-end language? It looks like either VB or classic ASP and my knowledge of it a bit rasty. The problem in general may happen when you specified your parameter as char or didn't specify proper type of the parameter at all.

N' in trace means Nvarchar. It almost always preceeds character parameters.

You may take a look at this sample

http://mudassarkhan.wordpress.com/2009/01/10/using-select-stored-procedures-adonet/

though I don't know if this applies in your case.

 

[dalec]

vb.net 2008 pro

 

[markros]

If that's the case, the suggested link should help.

 

[danvlas]

If you test Me.Tag, what do you get?



Daniel Vlas
Systems Consultant

 

[dalec]

I get it correctly. I have a previous form that has the actual search text, this form displays the results. I've traced it through to the passing off to the stored procedure and it's fine. just when it get's there it seems to strip the rest of the string and only gets the first letter.

 

[dalec]

OK, now I don't believe it. I went to the data desinger and tested the stored procedure from there, it worked like it was suppose to. Now when I run the application, it's working. No real code or sp changes, just started working after I tested it there (maybe that needed a refresh)????

thanks everyone, much appreciated.

 

[markros]

Watch out for suddenly stopping working again.

 

[SQLBill]

dalec,

You state:

I don't know what the 'N' next to the 't' means, by the way I'm passing the search as 'tr', but, just the 't' looks like shows up????

As markros said, that indicates NVARCHAR which is Unicode. Unicode takes 2 bytes per character. The issue with it is that you must remember to double the length when coding.

VARCHAR(10) needs to become NVARCHAR(20).

So, 't' is one byte (VARCHAR(1)), but two bytes in UNICODE. If you coded the value as NVARCHAR(2) expecting it to accept 'tr' that won't work. It would have to be NVARCHAR(4).

Hope that wasn't too confusing.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

Interesting.

 

[dalec]

After about three weeks with this, I just want to say to anyone out there, make sure if you change your code (vb.net 2008) make sure you refresh any stored procedures your referencing from the datasets and tableadapters to stored procedures your using, because this was a nightmare, I just needed to get behind it so I could make additional searches based on this search.

Thanks all, and thank you markros for staying in there with me for the entire trip through this problem.

Thanks again, much is appreciated, I hope this helps someone I know sometimes I find problems exactly like mine, but, they sometimes get left without the actually solution.

 

[markros]

You're welcome, glad you got it it.

 

[danvlas]

SQLBill: that is not correct.

varchar(10) and nvarchar(10) both can hold 10 characters, however nvarchar takes twice the space of varchar because each character takes 2 bytes instead of 1.




Daniel Vlas
Systems Consultant