Hi Iam getting error in Webservice

Question

0.00/5 (No votes)

See more:

error is

System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WebService.db_insert(String UserName, Int32 countryId, String IsActive) in e:\mine\WebSite8\App_Code\WebService.cs:line 46

programme

[WebMethod]
public void db_insert(string UserName ,int countryId ,string IsActive)
{
SqlConnection con = new SqlConnection("Data Source=SREENI\\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=sa123");
string sql = string.Format(@"INSERT INTO [Table_1].[TestDB]
([UserName]
,[countryId]
,[IsActive])
VALUES
('{0}'
,'{1}'
,'{2}')", UserName, countryId, IsActive);
SqlCommand cmd = new SqlCommand("insert into Table_1 values(@a,@b,@c)", con);
cmd.Parameters.AddWithValue("@a", UserName); cmd.Parameters.AddWithValue("@b", countryId); cmd.Parameters.AddWithValue("@c", IsActive); cmd.ExecuteNonQuery();
}

Posted 19-Aug-14 20:30pm

harshavardhan12345678

Add a Solution

Comments

[no name] 20-Aug-14 2:33am

Your error clearly states that connection object which you have created is closed..
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Open the connection...

harshavardhan12345678 20-Aug-14 2:45am

after that its display blank page

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mayank Vashishtha · Accepted Answer · 2014-08-19T20:38:00

Solution 1

C#

[WebMethod]
public void db_insert(string UserName ,int countryId ,string IsActive)
{
SqlConnection con = new SqlConnection("Data Source=SREENI\\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=sa123");
string sql = string.Format(@"INSERT INTO [Table_1].[TestDB]
([UserName]
,[countryId]
,[IsActive])
VALUES
('{0}'
,'{1}'
,'{2}')", UserName, countryId, IsActive);
SqlCommand cmd = new SqlCommand("insert into Table_1 values(@a,@b,@c)", con);
cmd.Parameters.AddWithValue("@a", UserName); 
cmd.Parameters.AddWithValue("@b", countryId); 
cmd.Parameters.AddWithValue("@c", IsActive); 
if(con.State == ConnectionState.Closed)
   con.Open();
cmd.ExecuteNonQuery(); 
con.Close();
}

Posted 19-Aug-14 20:38pm

Mayank Vashishtha

Comments

harshavardhan12345678 20-Aug-14 2:44am

after That it display blank page

Mayank Vashishtha 20-Aug-14 2:47am

Yeah So?? What are you asking?

harshavardhan12345678 20-Aug-14 2:54am

hmm then how can we use that page

harshavardhan12345678 20-Aug-14 2:54am

do u got it wt iam saying

Mayank Vashishtha 20-Aug-14 2:56am

Nope!

harshavardhan12345678 20-Aug-14 3:12am

hmm programme successfully , after click button it doesn't goes to db

Mayank Vashishtha 20-Aug-14 3:15am

You means to say that now error is resolved but you lies on the same page. Is that so?

harshavardhan12345678 20-Aug-14 3:18am

yeah issue solved

Mayank Vashishtha 20-Aug-14 3:19am

Good. Now you can rate my answer. :-)

harshavardhan12345678 20-Aug-14 3:26am

hello Vashishtha i dont get output (xml or Json).

Mayank Vashishtha 20-Aug-14 3:29am

Why would you? Your method is of void type which doesn't return any value. change it and return whatever is necessary.

harshavardhan12345678 20-Aug-14 3:38am

i dont know about webservices , thats y am asking everything, so dont mind tell step by step

Mayank Vashishtha 20-Aug-14 3:41am

See it's not about webservice. It's about simple programming. void means it will not return anything. It's the return type you are using that's why it is not returning any value.

harshavardhan12345678 20-Aug-14 3:45am

Error 2 'WebService.db_insert(string, int, string)': not all code paths return a value E:\mine\rajservice2\App_Code\WebService.cs 31 19 rajservice2

Mayank Vashishtha 20-Aug-14 3:54am

This may be your project issue then. However this method is absolutely fine now. Try cleaning and rebuilding your solution again.