Hello.
I am developing an winform app. in C# express.
This App. is working with an Database, reading data from it. My user needs to be able to search for a specific order, name or e-mail address. I thought I could use stored procedures and it works fine when the titem I search for is a primary key.
"orderID" in my table is primary key, but not name or e-mail address. when I wtire SQL in SQL writer and test It gets my values just fine but when I run stored procedure whith this sql it returns nothing.
Example. this works since orderID is primary key in the table
ALTER PROCEDURE
dbo.finnOrdre@OrdreID
intAS
SELECT Ordre.OrdreID, Ordre.Status, Ordre.Referanse, Ordre.Trykkinfo, Ordre.Dato, Ordre.Pris, Ordre.Antall, Kunde.Fornavn, Kunde.Etternavn FROM Ordre INNER JOINKunde
ON Ordre.KundeID = Kunde.KundeID WHERE (Ordre.OrdreID = @OrdreID) RETURNThis does not work
ALTER PROCEDURE
dbo.finnKunde@fornavn
varcharAS
SELECT
KundeID, Fornavn, Etternavn, Epost, Telefon, Poststed, Postnummer, Adresse FROM Kunde WHERE (Fornavn = @fornavn)
could anyone give me some idea what to do
thanks
Stored procedures Help in C#
Javier Martinez
Hi,
In order to avoid the trimming issues just trim your variable before sending it to SQL like
string Variable = Variable.Trim();
You can find more information about the LIKE statement on:
http://msdn.microsoft.com/library/default.asp url=/library/en-us/tsqlref/ts_la-lz_115x.asp
Regards
Begemot
TonyVFP
Hello.
1) I do not receive any errors, sotred procedure just does not return anything: look here:
Running [dbo].[finnKunde] ( @fornavn = Mirza ).
KundeID Fornavn Etternavn Epost Telefon Poststed Postnummer Adresse
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------------------------------------------------------------------- --------------- ------------------------------ ---------- ----------------------------------------------------------------------
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[finnKunde].
2)Fornavn(first name in norwegian) is char (40) so I think that it should work
3)You suggest to write it like this:
ALTER PROCEDURE
dbo.finnKunde@fornavn
varcharAS
SELECT
KundeID, Fornavn, Etternavn, Epost, Telefon, Poststed, Postnummer, Adresse FROM Kunde WHERE (Fornavn Like '%' + @fornavn + '%')Hei.
nr3. worked fine:)
I am new to sql, could you refer any msdn documentation about the syntax ++ for the 3)
thnsk you very much
markovuksanovic
Hello.
No I use MS SQL express edition and it is not case sensitive. Not intentionaly but when searching for posts it seems like it is not. Thank you
Polity4h
Hi,
1) the SQL statement seems alright to me, do you receive an error or just no rows
2) what type is "Fornavn" Maybe is a type conversion problem as your variable is a varchar.
3) If you are searching text remember to trim the blank spaces at the end, sometimes can give you a headache. In order to test if there are ghost characters change the WHERE to "Where Fornavn Like '%' + @fornavn + '%'
Hope this helps
BlackManWah
Hello,
Just to clarify the source of your problem. You have 'Fornavn' defined as char (40) which when compared with a varchar that you are passing in will contain spaces to make it 40 characters long.
EX: if Fornavn is 'Njofra' the column value will actually be 'Njofra ' (which is the name [6 characters] plus 36 spaces.
To resolve this problem you could redefine @fornavn varchar to @fornavn char(40). It is strongly recommended that the param types for a stored procedure match the column type they correspond to. Another option would be to declare the 'Fornavn' column as a varchar(40) which will prevent spaces from being added to it.