Hum, if everything is running local on your development box?
just use Visual studio to connect to sql server. It will do all the "dancing" for you.
So, in your vs project, just use the settings, and lets VS do this for you.
So, in your project, go Project->"ProjectName" Properties.
And then settings
And then you get this screen:
So, lets connect to a database called TEST4 (might as well call the connection TEST4).
With above, then you can use the wizards to setup a connection.
You can of course "always" connect to SQL server on your local machine using windows authentication, since you are the "super user" of your own machine.
So, lets do that:
So, from above, we can click on the [...] button in settings, and build the connection.
I VERY strong suggest you install SQL server express for this setup.
Ok, so click on this:
And now we can just follow the wizard to setup a connection.
In the drop down, your local instance of SQL server should appear.
Like this:
If you local server DOES NOT appear, then
Make sure you are running the so called "browser" service for SQL server (has nothing to do with web browsers!).
That would be this service:
Also, while you at this, make sure tc/ip is turned on.
(you don't normally have to do this, but I recommend you do).
So, on SQL Server Network configuration, you see this:
So, as long as you KNOW sql server is running.
And again, without question, we assume you have SQL management stuido installed, right?
(Visual studio, SQL server express, and the SQL management studio are all free).
Ok, so back to the simple connection wizard that we have to just click away with the mouse to setup?
Then this:
and after selecting the database, then do hit test connection.
eg this:
So, you do not have to do all those steps you note.
IIS will connect fine to sql server, you just have to make sure SQL server is running, and there is ZERO ZERO ZERO ZERO use to try all kinds of permission settings to IIS when above will suffice.
And I would suggest that you install SQL server in mixed mode (both windows authenicatiaon and also sql server auntheication.).
For testing and dev, you can simple use the above windows authentication, but you might want to add a logon user using SSMS, and thus connect using a sql logon you created.
But, even on production servers? I have NEVER had to muck around with creating permissions in IIS to allow it to connect to sql server. All you require is a valid connecton string, and you should be good to go.
Ok, so now that I created the above connection?
Then in code I can use it this way.
Let's drop in a grid view into the aspx page like this:
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
And code behind is this:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
LoadGrid
End If
End Sub
Sub LoadGrid()
Dim strSQL As String =
"SELECT FirstName, LastName, City, Description, HotelName
FROM tblHotelsA ORDER BY HotelName"
Using conn As New SqlConnection(My.Settings.TEST4)
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
Dim rstData As New DataTable
rstData.Load(cmdSQL.ExecuteReader)
GridView1.DataSource = rstData
GridView1.DataBind()
End Using
End Using
End Sub
And now we get this:
So, make sure SQL server is up and running.
Make sure you can use/connect from SSMS.
Make sure you running the sql browser serivce.
Turn on tc/ip.
And then use the wizards in VS to connect and test the connection BEFORE you start coding away.
The above building of the connection also means we have a system wide connection to use (and no messy connection strings all over in code).
Even better bonus points? The project->settings when you build a connection with the wizard? It ALSO places that connection inside of web config for you, further saving hassle and world poverty.
