dropdownlist won't populate on server with new sql 2008 r2 install 1

[MzKitty]

I'm using vs 2008. This is being run on a new sql 2008 server r2(a new full install). I have a dropdownlist that I am populating using the following code:

strServerString ="Data Source=MC22JWS;Initial Catalog=Shops;Integrated Security=True;uid=MC90APP;pwd=MC90@0101"

strConnect = strServerString

' Open the Connection
Dim Con As New System.Data.SqlClient.SqlConnection(strConnect)

Con.Open()

' Open the Connection
strsql = "exec Shops_Get_Projects "

Dim qjbCmd As New System.Data.SqlClient.SqlCommand(strsql, Con)
Dim rdr As System.Data.SqlClient.SqlDataReader = qjbCmd.ExecuteReader()

' Populate the Projects dropdown Control
While rdr.Read()
Dim newListItem As New ListItem()

newListItem.Text = rdr.GetString(0)

newListItem.Value = rdr.GetString(0)

DropProjects.Items.Add(newListItem)

End While

rdr.Close()

Con.Close()

When I run this, the dropdown popluates with nothing. If I run the same code against my local sql server, everything is fine. I have uninstalled the .net framework and reinstalled it and it still doesn't populate the dropdown. I have run this same code on 3 or 4 other sql servers and not had any problems. Is the SQL install bad?

Thanks

Cathy

 

[born2program]

Check to see if that user (MC90APP) has execute permission on the stored proc (Shops_Get_Projects) on the new SQL Server 2008 R2 install. It sounds like maybe a permissions issue. Also, If you have the sql server sa account info you can try plugging that into the server string to pinpoint if permissions is the issue or not.

 

[MzKitty]

I gave execute permissions to the MC90App and the MC22JWS$ id's and it's still not working. I inserted a try and catch to trap any exceptions and I set up a message box to display the exception, but it isn't throwing any exceptions. I have to talk to the network administator and see if he can map 'sa' to the Shops database and then I'll try using sa to see if that does anything. Thanks for your suggestion.

Cathy

 

[gmmastros]

Can you connect to the new server from your local computer using SQL Server Management Studio?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MzKitty]

I don't have any problems connecting with it. I had issues with the one database,Shops. It had been backed up off of another test server and then restored to my new sql server. The permissions were all were screwed up because he brought over the security table too, so I blew it away, recreated it, and brought my tables over again with the import/export wizard. I copied and pasted over my stored procedures too. I think that the System.Data.SqlClient commands are not being executed. It's not throwing any exceptions, but it just executes right by the code. When I run it on my local server and access the same data, it works fine. It's just when I go up to the website and enter the program that the code doesn't work. This is a new server with a new install of SQL Server 2008 R2. I'm thinking that the kid who built the server missed something on the install.

Cathy

 

[born2program]

Have you tried to put a stop in your code on line "DropProjects.Items.Add(newListItem)" then hover over the newListItem variable each time it stops. You should be able to see if any values are being populated in that variable. This way you will know for sure that you aren't getting anything from the sproc.

 

[MzKitty]

it loads perfectly on my pc running the app in vs2008, but when I publish it to the website and run it, nothing loads. I've granted execute permissions to the UID MC90App so it should execute the stored proc, but it acts like it is getting nothing. I don't see anything wrong with the connection string and I've rebuilt the database, that's why I'm thinking it has something to do with the SQL Server install. My IIS setup is correct (based on another win 7.0 system that I built in January).

 

[MarkSweetland]

Try removing the "Integrated Security=True" from your connection string. I believe it's using your credentials to connect running locally and is therefore sucessfull. But when published to the web site, it will try to connect using the web site credentials, which will probably fail.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[MzKitty]

Thank You! Thank You Mark! that was it! I don't know why this same thing didn't occur on the other web apps I have developed and put into production, but by taking out the "Integrated Security=True", it works!