how to connect database gridview with C#

Question

User2113851930 posted

I have a gridview form and try to connect a database, but have issues

I get this error
Linje 22:
Linje 23:             SqlCommand cmd = new SqlCommand("Select * from wanado, con");
Linje 24:             con.Open();
Linje 25:             SqlDataReader wan = cmd.ExecuteReader();
Linje 26:             GridView1.DataSource = wan;

I have tried to make the connection in the grid form aspx site and have no problem, the grid show the data.

<asp:SqlDataSource ID="SqlDataSource10" runat="server" ConnectionString="Data Source=hunt\TEST;Initial Catalog=Northwind;Integrated Security=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [wanado]"></asp:SqlDataSource>

But when try the codebehind I get error

I have a Northwind database and a table named wanado, the local sql server is named hunt/test and have Windows authentication

My codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
System.Data.SqlClient;

namespace WebApplication1

{

public partial class SPWebsite : System.Web.UI.Page

    {

protected void Page_Load(object sender, EventArgs e)

        {

public partial class SPWebsite : System.Web.UI.Page

    {

protected void Page_Load(object sender, EventArgs e)

        {

SqlConnection con = new SqlConnection("Data Source=.;database=Northwind;Integrated Security=SSPI"); 

SqlCommand cmd = new SqlCommand("Select * from wanado, con");

            con.Open();

           SqlDataReader wan = cmd.ExecuteReader();

            GridView1.DataSource = wan;

            GridView1.DataBind();

            con.Close();

        }

    }

}

Answer 1

User475983607 posted

This line has an error...

SqlCommand cmd = new SqlCommand("Select * from wanado, con");

It should be 

SqlCommand cmd = new SqlCommand("Select * from wanado", con);

Answer 2

User2113851930 posted

thanks but still error

Linje 22:
Linje 23:             SqlCommand cmd = new SqlCommand("Select * from wanado", con);
Linje 24:             con.Open();
Linje 25:             SqlDataReader wan = cmd.ExecuteReader();
Linje 26:             GridView1.DataSource = wan;

Answer 3

User2113851930 posted

still error after

SqlCommand cmd = new SqlCommand("Select * from wanado", con);

Answer 4

User283571144 posted

Hi jylland,

still error after

SqlCommand cmd = new SqlCommand("Select * from wanado", con);

According to your codes and description, I had written a test demo on my computer, it works well.

But I find in your code behind codes, you changed the connection string.

I guess this is the reason  why you got the error message, ADO.NET can't connect to the database.

I suggest you could replace the connection string with:

SqlConnection con = new SqlConnection("Data Source = hunt/TEST; Initial Catalog = Northwind; Integrated Security = True");

More details about connection string, you could refer to follow link:

https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx

Best Regards,

Brando

Answer 5

User2113851930 posted

thanks Brando ZWZ

but still issues

Linje 34:             //SqlConnection con = new SqlConnection("Data Source=hunt/TEST;Initial Catalog=Northwind;Integrated Security=True");
Linje 35:             SqlCommand cmd = new SqlCommand("Select * from wanado", con);
Linje 36:             con.Open();
Linje 37:             SqlDataReader wan = cmd.ExecuteReader();
Linje 38:             GridView1.DataSource = wan;

The browser said

The network name can not be found

I have tried a web.config connection and here in no issue all ok. I think it is  "\" and "/" between hunt and test which give me the problem in the CS file.
<connectionStrings>

<add name="DBCS" connectionString="Data Source=hunt\TEST;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

Answer 6

User283571144 posted

Hi jylland,

I have tried a web.config connection and here in no issue all ok. I think it is  "\" and "/" between hunt and test which give me the problem in the CS file.

I suggest you could try below codes:

SqlConnection con = new SqlConnection("Data Source = hunt\\TEST; Initial Catalog = Northwind; Integrated Security = True");

Best Regards,

Brando

Answer 7

User2113851930 posted

great

So the C# do not like SQL server names with a single \ sign