Store procedure not retern value

Question

0.00/5 (No votes)

See more:

hi in my project problem when i want read record by sql datareader

What I have tried:

using (SqlConnection mysqlConnection = new SqlConnection(this.database.getConnectionString()))
         {



             using (SqlCommand mySqlCommand = new SqlCommand("GetDboName", mysqlConnection))
             {
                 mySqlCommand.CommandType = CommandType.StoredProcedure;

                 mySqlCommand.Parameters.AddWithValue("@Imei", imei);
                 mysqlConnection.Open();




                 using (SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                     {
                         if (!mySqlDataReader.HasRows)
                         {
                             throw new QueryException(1, "Tracker " + imei + " is not registered.");
                         }
                         else
                         {
                             mySqlDataReader.Read();







                         }
                     }


                 //mysqlConnection.Close();

             }

         }

declare @imei nvarchar(100)=869170031585095
DECLARE @DatabaseName NVARCHAR(MAX)=null, @SQL NVARCHAR(MAX);


select  @DatabaseName= DboName  from [CentralRelation].[dbo].[TrackingMaster] where Imei=869170031585095


SET @SQL = N'USE ' + QUOTENAME(@DatabaseName) +'  SELECT dbo.LastLogDevice.time, dbo.Carrier.carrierID, dbo.Carrier.carrierTypeFId, dbo.Carrier.companyFId, dbo.Carrier.orgFId, dbo.Carrier.fleetFid, dbo.Carrier.carrierName, dbo.Carrier.carreiernameonmap,
                         dbo.Carrier.deviceImei, dbo.Carrier.displayorhidenonmap, dbo.Carrier.zoneFId, dbo.Carrier.SimNumber, dbo.Carrier.Status, dbo.Carrier.PelackNumber, dbo.Carrier.apnName, dbo.Carrier.apnUserName,
                         dbo.Carrier.apnPassword, dbo.Carrier.createdOn, dbo.Carrier.expiresOn, dbo.Carrier.Lastedit ,(select   CompanyName  from [CentralAlcazer].[dbo].[Tracker] as t1 where t1.DeviceImei='+@imei+') as dboname
                           FROM dbo.Carrier INNER JOIN 
                          dbo.LastLogDevice ON dbo.Carrier.carrierID = dbo.LastLogDevice.carrierFId 
                          WHERE(dbo.Carrier.deviceImei ='+ @imei+') AND(dbo.Carrier.createdOn < GETDATE()) AND(dbo.Carrier.expiresOn > GETDATE())  AND(dbo.Carrier.Status = 1)';

--PRINT(@SQL);


EXECUTE(@SQL);

Posted 20-May-20 2:23am

mohamad_ali

Updated 20-May-20 3:13am

Add a Solution

Comments

F-ES Sitecore 20-May-20 8:24am

What's your question?

2 solutions

Solution 2

You have to do something with what you are reading, place it in a variable for later usage, or output it directly, for example.

C#

if (mySqlDataReader.HasRows)
{
   mySqlDataReader.Read();
   string value = ((IDataRecord)mySqlDataReader)[0];
}
else
{
   // No record found. Act accordingly.
}

Edit: here's something which you could try (avoiding using AddWithValue() method to specify the parameter type explicitly):

C#

mySqlCommand.Parameters.Add("@imei", SqlDbType.NVarChar).Value = imei;

Moreover, there is a flaw in your stored procedure: you are not using the imei parameter to select the database name; therefore, the selected database name is static (since based on the static value 869170031585095), whereas the second query is using the parameter.

SQL

SELECT @DatabaseName= DboName  from [CentralRelation].[dbo].[TrackingMaster] WHERE Imei = @imei;

Posted 20-May-20 3:14am

phil.o

Updated 20-May-20 5:57am

v2

Comments

mohamad_ali 20-May-20 9:35am

result of HasRows is false, but query has result in ssms

phil.o 20-May-20 11:57am

Please see my updated answer.

F-ES Sitecore 20-May-20 12:21pm

try putting

SET NOCOUNT ON

at the top of your SP

mohamad_ali 21-May-20 5:04am

thnx for your answer, my problem solved ,

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)

OriginalGriff · Accepted Answer · 2020-05-20T02:55:00

So it returns no values - almost certainly that's because your WHERE clause finds no matches.

Test your SP in SSMS and see what it returns when you give it specific values. When you're happy that works then use the C# debugger to find out exactly what you are passing to SQL and compare that with the values that worked in SSMS.

We can't do any of that for you - we have no access to your data.