Solved

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

Posted on 2014-07-29
12
18 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now