Trouble connecting to an SQL database in C#

Hi guys,

I'm using Visual Web Developer and I've made a GridView and I want to learn how to display information in it (or any other databound control) rather than doing it through the asp source code. I'm using the following code so far, and although I've probably made some errors in it I think there is a problem connecting to the database remotely as I get the following error on the "myConnection.Open()" line.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I tried following the instructions here http://www.datamasker.com/SSE2005_NetworkCfg.htm but didn't have much luck as I don't seem to have the interface shown in the screen prints.

My code so far is

SqlConnection myConnection = new SqlConnection("Server=local;database=WroxUnited");
SqlCommand myCommand = new SqlCommand("SELECT * FROM [Fixtures]", myConnection);

myConnection.Open();

GridView1.DataSource = myCommand.ExecuteReader();
GridView1.DataBind();

myConnection.Close();

Thanks in advance.You specified a server (Local) and a database (WroxUnited) ... just so that we're on the same page, that isn't a remote DB... it's local on your PC.

But the problem is probably because you didn't specify an authentication method... do you intend to use integrated security (using WIndows Authentication) or to use SQL Server Authentication? IF you want integrated securtity then you need to add "Integrated Security=SSPI;" to the connection string otherwise add "Integrated Security=False;User ID=XXXXXX;Password=YYYYYYYY;"

-tg
You can make use of the idea in this code:

SqlConnection sc1 = new SqlConnectionConfigurationManager.ConnectionStrings ["CompanyConnectionString"].ToString ());
SqlDataAdapter sa1 = new SqlDataAdapter();
sc1.Open();
string c = "Select Name , Postion_Name from Employee join Postion on Postion_Num=Postion_ID";
SqlCommand sqc1 = new SqlCommand(c,sc1);
DataSet ds = new DataSet();
sa1.SelectCommand = sqc1;
sa1.Fill(ds);
GridView1.DataSource = ds;
What's annoying is there seems to be so many different ways to go about it, and as I'm just beginning to learn .NET it's all a bit confusing. I'm sure I'll get it eventually but I'm worried I may have already got it but something's not set up correctly to work. However if I do it through ASP source code using a ConnectionString=<%$ConnectionStrings=WroxUnited%> for example then it does work so I suppose it's unlikely to be a problem with the SQL Server set-up.

Just to test that it does actually work would someone be kind enough to post here the smallest amount of code necessary to display data from a database called WroxUnited.mdf stored in the C:, in a gridview called GridView1? Or if it's easier to use another data control then let me know and I'll do it that way.

Thanks for all the help!

Trouble with ArrayList and GridView

Hi all,

I am trying to display somedata from a postgres database, and display it using an asp.net page, using a GridView. The basic thing I am doing is, getting data from the database, storing it in an ArrayList, and passing the ArrayList to the DataSource for the GridView.

The problem is the data that I need to display is 3 dimensional. My questions are:

How can I create an ArrayList that can hold 3 columns for each row? How do I populate such an ArrayList? How can I pass this data to the GridView? How can I getHyperlinks on all the values displayed in the first column?

I am a beginner atasp.net. Any help will be appreciated.

Thanks
George

Hey,

The preferred data object for this is a DataTable. Though I don't know if there is ADO.NET support for a postgres database (I don't know anything about that database system), you could still make use of a DataTable, as that works well with the GridView.

The other alternative is that you get the data back from the postgres database and populate it into a business object that contains the three properties of information you are returning. An arraylist can be a list of business objects, which each object has the three properties.

Ok, I tried using the DataTable, so now I am retrieving data from my table, populating the data table and displaying it. This works fine.

But what I need is a query that retrieves data in a 'for' loop, since I need to pass different variables to the query everytime. So I pass a variable, run the query, store the result somewhere(an ArrayList?), then pass the next variable, run the query, append the result to my storage and so on...

I am not sure if we can append data to a DataTable(if we can, I would like to know how). And incase ArrayList is the best option available, how would I be able to create an ArrayList that would contain 3 columns? How can this be displayed in a GridView?

Thanks for the reply. Hoping to get around this problem soon.

George

to add hyper link all you have to do is right click over the grid veiw and select Show Smart Tag >>>

Then in the Smart Tag of the GridVeiw click the Edit columns link

A field wizard will pop, in that make sureAuto-generate field option is checked

In the Available field sectionselect the HyperLinkField and click the Add button

Then Select the HyperLinkField in the selected field section

Inside the HyperLinkField Properties section :-

? In appearrance section make the text to go

? In data section make the DataNagivationUrlFormatString to topic.aspx?TopicId={0}

(Here topic.aspx is the page you wan to redirect and TopicId is the QueryString Variable)

? In data section make the DataNavigationUrlField toTopicId

(This actually specify the value of {0})

At the first, DataTable is the best option for you!

Yes, you can add a DataRow to a DataTable:

// Define a DataTable

DataTable tbl =newDataTable();

// Add your Columns to DataTable

tbl.Columns.Add("Col1");

tbl.Columns.Add("Col2");

// Create a DataRow for this table

DataRow row = tbl.NewRow();

// Set you row values

row[0] ="c1";

row["Col2"] = 25;

// Add row to table

tbl.Rows.Add(row);

Also, you can use Rows of your table in "for" loop:

foreach (DataRow rwin tbl.Rows)

{

Response.Write(rw["Col1"]);

}

or

for (int i = 0; i < tbl.Rows.Count; i++)

{

Response.Write(tbl.Rows[i]["Col2"]);

}

Hello,

Yes, the code above can be used to iterate through a data table. You can also use a DataView to filter the data by using the RowFilter property:

http://msdn2.microsoft.com/en-us/library/system.data.dataview(VS.71).aspx
http://davidhayden.com/blog/dave/archive/2006/02/11/2798.aspx