How to connect with SQL database?

I am new learner of ASP.NET and coding in C#.I am not able to connect the SQL database.I have written the code for connection of database but to write the connection string web.config file is neccessary which is not apearing in my application. Please help me .Here is code I have written.

string sCon =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection oCon =new SqlConnection(sCon);

try

{

oCon.Open();

string sQuery ="Select Offering1,Offering2,Offering3 from Offering";SqlCommand oCmd =new SqlCommand(sQuery, oCon);

oCmd.ExecuteNonQuery();

DataSet oDs =newDataSet();

SqlDataAdapter oDa =new SqlDataAdapter(oCmd);

oDa.Fill(oDs);

DataList1.DataSource = oDs;

DataList1.DataBind();

}

catch (Exception ex)

{

ex.ToString();

}

What kind of error are you getting?

Can you post a copy of the connectionString settings from your web.config?

|||

Hii Jimmy,

Actually I do not see the web.config file in my solution explorer. So that I am unable to write the connection string. Can you please tell me why it is so ?

|||

Looks like you may have started with the empty web site template. When you try to start/debug the application, does it not automatically add a web.config for you?

Well, you can manually add one. Right click on your web site and select ADD NEW ITEM then select the WEB CONFIGURATION FILE

|||

Hey thanks,

As there are errors because I have not added the connection string into the web.cofig file it is not able to debug the file.Right now I have added web.config manually.

What the connection string format to add to web.config?

<addname="ConnectionString"connectionString="Server=(local)\SQLEXPRESS;Integrated Security=True;Database=Knowledgebase;Persist Security Info=True"/>

is it right?

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

|||

Bluestar123:

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

That would because you did not import the namespace

Add this to the top of your code behind for your default.aspx

using System.Data.SqlClient;

|||

The connection string can look something like this

"Data Source= xxx ;Initial Catalog=xxx; uid=xxx ;pwd=xxx"

where datasource is your database server; could be your machine name or I.P. address, Initial Catalog is the database name, UID is the login name, PWD is the login name's password

If your SQL Server is set up for windows authentication then you can do replace the UID and PWS with "Integrated Security=True"

Have a look at this link for connection string examples

http://connectionstrings.com/?carrier=sqlserver2005

|||

Hi Jimmy,

Now I have added using System.Data.SqlClient to my default.aspx.cs and that error has gone thanks for the same.

I am still not able to fetch the data to my datalist from database, is still there any problem with my connection string? in my web.cofig I have used windows authentication.

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

Please help ..........

|||

Bluestar123:

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

for the connection string, the data source should be [your machine name]\SQLEXPRESS assuming SQLEXPRESS is the server instance name

update your code to this and give it a go

string sCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection oCon =new SqlConnection(sCon);
try
{
string sQuery ="Select Offering1,Offering2,Offering3 from Offering";
 SqlCommand oCmd =new SqlCommand(sQuery, oCon);
 DataSet oDs =new DataSet();
 SqlDataAdapter oDa =new SqlDataAdapter(oCmd);
 oDa.Fill(oDs);
 DataList1.DataSource = oDs;
 DataList1.DataBind();
}
catch (Exception ex)
{
 ex.ToString();
}

Add some break points and step through the code, if there any exceptions, take not of the exception message.

|||

I have added break points at Select command and line below that ,control goes there when I debug the default.aspx and then it display the page without fetching thedata from database.So i have added the break point to the line

DataSet oDs=new DataSet();

at this line control donot go and directly display the page without showing the data from the database.

|||

So when you put a break point at the DataSet oDs=new DataSet(); and when you step over it F10, it does not proceed to the next line? Then that means an exception was raised and it should be caught in your catch. So put a break point in your catch statement, so you can check the exception being thrown if there is one at all and give us the error message

|||oops! Column name I had given was wrong .......Its OfferingName. I made that change but now when I put brk point at

DataList1.DataSource = oDs;

after pressing F10 control goes to next line i. e.

DataList1.DataBind();

but data from database is not getting displayed on the page though the page has been displayed......

oh no whats the problem ?am I doing any mistake again?

Follwing output I got in Output window

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_d4ehzmko.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_oq4mi2vf.dll', Symbols loaded.

The program '[3368] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Well the obviously question is have you tried executing the SQL statement against your database to make sure it does return data?

after you have stepped over this line of code oDa.Fill(oDs); check it the dataset has returned a datatable with datarows.

|||

No I didnt,how can I check it ?I am using Microsoft SQL server management Studio. When go to line

oDa.Fill(oDs);

it did not return the table contents......

I tried to execute the query by using Execute SQL but not able to execute ,How to do?

Did you get fed up of my questions Jimmy??

|||

Bluestar123:

Did you get fed up of my questions Jimmy??

Not at all, just trying my best to understand your problem which is at times a little hard.

Lets try execute you select statement first in SQL Server Management Studio.

Log onto your database, and select the NEW QUERY option.

Now paste your SQL statement into this window , "Select Offering1,Offering2,Offering3 from Offering" and click execute

Does it return any results?

sql