SQL Search, No values returned.

Question

1.00/5 (1 vote)

See more:

Hi,

I am searching for a string like xxxx.xxxx in an sql database, but I don't get back any values. My code is:
SQL Stored Procedure:

SQL

ALTER PROCEDURE [dbo].[GetUserId] 
	-- Add the parameters for the stored procedure here
	@Name VarChar
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	from dbo.Users
	where @Name like ADName 
END

ASP.Net Code:

C#

SqlConnection Connection9 = new SqlConnection(ConfigurationManager.ConnectionStrings["HayConnectionStringTest"].ToString());
SqlCommand SqlCmd9 = new SqlCommand("GetUserId");

SqlCmd9.Connection = Connection9;
SqlCmd9.CommandType = CommandType.StoredProcedure;

SqlCmd9.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));

SqlCmd9.Parameters["@Name"].Value = valami[1].Trim();//Session["username"];

DataTable DTable9 = new DataTable("DTable");
SqlDataAdapter SqlAdapter9 = new SqlDataAdapter(SqlCmd9);

try
{
    Connection9.Open();
    SqlAdapter9.Fill(DTable9);
}
catch (Exception ec)
{
    Label1.Text = ec.ToString();
}
finally
{
    Connection9.Close();
}

If i hardcode the value, the next stored procedure runs fine. I guess I miss something about searching strings, but I don't know where am I wrong. THX for the helps, and sorry for my english.

Posted 28-Oct-14 22:30pm

kirk651

Add a Solution

Comments

[no name] 29-Oct-14 4:35am

Could you explain this:
SELECT * from dbo.Users where @Name like ADName.
What is your column name.. I did not get @Name
Shouldnt it be where ADName like @Name. Correct me if I am wrong. I didnt get it

George Jonsson 29-Oct-14 4:44am

Should be correct if there is a column with the name ADName in the table.
Add as solution instead.

kirk651 29-Oct-14 4:45am

There is a column with ADName, and it works fine in debug, the problem occurs, if I publish the project.

Mathew Soji 29-Oct-14 4:38am

Debug and check what is the value passed @ line SqlCmd9.Parameters["@Name"].Value = valami[1].Trim();//Session["username"];

kirk651 29-Oct-14 4:44am

In debug it works fine, but when i publish, i don't get back values, I've checked the parameter @Name, it's what it's supposed to be, but the datatable has 0 rows. In debug, it works fine, it only happens, if I publish the code.

[no name] 29-Oct-14 4:45am

Could you explain this:
SELECT * from dbo.Users where @Name like ADName.
What is your column name.. I did not get @Name

kirk651 29-Oct-14 4:47am

The input parameter is @Name, and the Columname, where I search for the string is ADName.

[no name] 29-Oct-14 4:49am

Shouldnt it be select * from dbo.Users where ADName like @Name ??
Try this.

jaket-cp 29-Oct-14 5:31am

Try running the stored procedure directly on the production database and see if you get any results.
Also do you give example of what is in ADName and @Name, with result expected.

2 solutions

Solution 2

Try this

SQL

ALTER PROCEDURE [dbo].[GetUserId] 
	-- Add the parameters for the stored procedure here
	@Name VarChar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
        -- Insert statements for procedure here
	   SELECT *
	   from dbo.Users
	   where  ADName like @Name

        SET NOCOUNT OFF;
END

I guess your name parameter expects some length values to be defined. and additionally do your name is exactly same as stored in column or you need likely names, then you have to use the wild card(e.g %).

Posted 28-Oct-14 22:59pm

Shweta N Mishra

Updated 28-Oct-14 23:04pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

rmksiva · Accepted Answer · 2014-10-28T22:49:00

Hi,

i have to say you like clause usage

while using like we normally use wildcard

Ex :

select * from dbo.Users where ADName like @Name + '%'

Output :

IF @Name =Si then Query Returns

Siva
Sidharth
Sindhu . . .

I guess you miss the wildcard .

Please Refer

http://www.w3schools.com/sql/sql_wildcards.asp[^]

Thank you !
Siva Rm K