Solved

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

Posted on 2014-07-29
12
30 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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