Solved

asp.net SqlDataReader not returning any records when using sql server 2008

Posted on 2014-07-29
12
49 Views
Last Modified: 2016-06-10
My client upgraded there sql server from 2000 to 2008.
Now for some reason, asp,net page that uses sqldatareader doesnt return any rows.

I put this code in vb.net just to see what it could be:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    Dim myCommand1 As SqlCommand
    Dim myDataReader1 As SqlDataReader
    myConnection.Open()

    Dim strsql3 As String = "select * from table"

    myCommand1 = New SqlCommand(strsql3, myConnection)

'''saw on a post to try this command; it didnt help either myCommand1.ExecuteNonQuery() 'executing the command and assigning it to connection

    myDataReader1 = myCommand1.ExecuteReader()
    MsgBox(myDataReader1.HasRows)

    While myDataReader1.Read()
        MsgBox("in the while")
    End While

    MsgBox("not in the while")

End Sub

It doesn't get any data even though there is data in the database. I login to  SSMS  to verify the query that gets generated returns data.

 I confirmed I was using the correct connection. Below is a sample connection string: Dim strConnection1 As String = "Data Source=Someip;Initial Catalog=TEST_Table;Persist Security Info=True;User ID=myuser;Password=stack;" When I change the Catalog to TEST_tables I get an error Cannot open database Test_tables requested by login. When I put it back to TEST_table I get no error. Also when I put a select to table that doesnt exist; I get invalid object name. So I know it going to the correct database.           
      
When I put a break point on this code: myDataReader1 = myCommand1.ExecuteReader(); it executes but
 hasrows = false
isclosed=false
fieldcount=11


Thanks.
0
Comment
Question by:vbguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40227311
What is the actual SQL you are executing, it isn't:
 Dim strsql3 As String = "select * from table"
is it?
0
 

Author Comment

by:vbguy
ID: 40227345
no; it is:
select distinct CompanyName from dbo.CompaniesList where CompanyName is not null
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40227352
In SQL Management studio, if you open the database:
TEST_table
create a new query and run that sql command you get data back?
Also make sure you are on the correct instance of SQL Server if you have multiple installed..
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Expert Comment

by:Jerry_Justice
ID: 40227604
Get rid of the dbo.

SQL assumes this by default if you are executing this under the same schema..  Just use table names alone to see if that is what is blocking data.. you can always add this back later if you need to for performance reasons.

Moving from SQL 2000 to 2008 may be what messed with the default schema in the first place.
0
 

Author Comment

by:vbguy
ID: 40227961
Hi,
Yes if I run the sql in sql server, I get data back.

I tried taking out the dbo, still not getting any data back to the .net page.

Thanks,
Arthur
0
 

Author Comment

by:vbguy
ID: 40228057
FYI;  ran sql server profiler , and I can confirm when I run the asp page the query is getting to the server:
see log below;  still no results coming back :(.


RPC:Completed      exec sp_reset_connection       .Net SqlClient Data Provider            WebAccess      0      0      0      0      107332      55      2014-07-29 19:33:53.240      2014-07-29 19:33:53.240      0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00      
Audit Login      -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
      .Net SqlClient Data Provider            WebAccess                              107332      55      2014-07-29 19:33:53.240                  
SQL:BatchStarting      select distinct CompanyName from CompaniesList where CompanyName is not null      .Net SqlClient Data Provider            WebAccess                              107332      55      2014-07-29 19:33:53.240                  
SQL:BatchCompleted      select distinct CompanyName from CompaniesList where CompanyName is not null      .Net SqlClient Data Provider            WebAccess      0      0      0      0      107332      55      2014-07-29 19:33:53.240      2014-07-29 19:33:53.240
0
 

Author Comment

by:vbguy
ID: 40228284
installed locally sql server express and tried to connect to that; the code return records.  some it must be some setting on my client sql server 2008 server?  I don't know enough on sql server to tell him what to change.

any help?
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228608
Do you want to try SqlDataAdapter to see if that works?
Dim ds As New DataSet()
Dim sda As New SqlDataAdapter(myCommand1)
sda.Fill(ds)
Dim rows as Integer
rows = ds.Tables[0].Rows.Count

Open in new window

0
 

Author Comment

by:vbguy
ID: 40229984
tried this:
Dim queryString="select distinct CompanyName from CompaniesList where CompanyName is not null"

Dim dataSet As System.Data.DataSet

Using connection As New System.Data.SQLClient.SqlConnection(strConnection)
        Dim adapter As New System.Data.SqlClient.SqlDataAdapter()
        adapter.SelectCommand = New System.Data.SQLClient.SqlCommand( _
            queryString, connection)
        adapter.Fill(dataSet)
              Response.Write(dataSet.GetXml())
    End Using


Catch
Response.Write("Error:" & err.Description)



Got this:
Error:Value cannot be null. Parameter name: dataSet
0
 

Accepted Solution

by:
vbguy earned 0 total points
ID: 40229987
seems somone else had this issue; http://social.msdn.microsoft.com/forums/sqlserver/en-US/9a099303-32a3-4671-aba4-3004cb716bde/sqldatareader-returns-no-rows

Does anyone know what this means:

A colleague did some poking around with cliconfg.exe and discovered that creating an alias for the named pipe solved the problem.
0
 

Author Comment

by:vbguy
ID: 40230868
the alias fixed the issue
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question