My webform, the interface the user sees in browser.
Basically here the ID 1 is already entered in the database (using the add button). So when the user selects update with new info in first/surname the database should be updated, However when I click update I get the following error in Visual:
and when I stop debugging, the web browser gives me this error:
Server Error in '/' Application.
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\sit322_ass2\assignment2\WebServices\WebService.asmx.cs:line 118
--- End of inner exception stack trace ---
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\database\WebServices\WebService.asmx.cs:line 118
--- End of inner exception stack trace ---
Source Error:
Line 153: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://tempuri.org/UpdatePerson", RequestNamespace="http://tempuri.org/", ResponseNamespace="http://tempuri.org/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
Line 154: public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) {
Line 155: object[] results = this.Invoke("UpdatePerson", new object[] { Line 156: ID,
Line 157: FIRSTNAME,
I'm not sure why this is happening!?Really confused...
Here are the two relevant codes again:
personal details webform cs. code, function for update
protected void cmdUpdate_Click(object sender, EventArgs e)
{
// Create a reference to the Web service
DbWebService.WebService1 proxy =
new DbWebService.WebService1();
// Create a person details object to send to the Web service.
string SURNAME;
string FIRSTNAME;
string ID;
SURNAME = txtSurname.Text;
FIRSTNAME = txtFirstname.Text;
ID = txtID.Text;
// Attempt to store in the Web service
bool rsp =
proxy.UpdatePerson(int.Parse(ID), FIRSTNAME, SURNAME);
// Inform the user
if (rsp)
{
lblOutcome.Text =
"Successfully updated new user.";
}
else
{
lblOutcome.Text =
"Failed to update user!";
}
}
webservice cs. update SQL function
[WebMethod]
public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME)
{
// Connect to the Database
SqlConnection connection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;
AttachDbFilename='|DataDirectory|\Database.mdf';
Integrated Security=True;
User Instance=True");
// Open the connection
connection.Open();
// Create a SQL command object.
SqlCommand command = new SqlCommand(
String.Format("UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'",
ID, FIRSTNAME, SURNAME));
// Execute the SQL command and store the returned integer.
int response = (int)command.ExecuteScalar();
// Close the connection
connection.Close();
// Return the result.
return (response > 0);
}
when I was adding my add function I also had the same error, but that was because I had a typo in the SQL command, I accidently misspelled personaldetails (the database refrence). Thus why I think this is an SQL command error.
I believe I tried this at one stage and still got error, but shall try again. Just at work at the moment so will try it when I get home, and mark as answer if it solves my problem.
Any chance (using the provided code) that I made an error somewhere else in the code?
yep fixed that and still get the error, this is inreasingly frustrating.
I fixed it to this
// Create a SQL command object.
SqlCommand command = new SqlCommand(
String.Format("UPDATE PersonalDetails set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'",
FIRSTNAME, SURNAME, ID), connection);
still get the same error.
mahedee
You can also use cmd.ExecuteNonQuery(); for insert also.
sorry but what do you mean by this, really confused?
Do I remove any of these lines: ?
// Execute the SQL command and store the returned integer.
int response = (int)command.ExecuteScalar();
// Close the connection PS: I KNOW I DONT REMOVE THIS LINE
connection.Close();
// Return the result.
return (response > 0);
I often use this way to connect, insert, update i,e acess database.
Step - 1: Simply create a class DBConnector to connect to the database.
public class DBConnector
{
private string connectionString = null;
private SqlConnection sqlConn = null;
private SqlCommand cmd = null;
public DBConnector()
{
connectionString = ConfigurationManager.ConnectionStrings["application"].ToString(); //User your connection string here.
}
public SqlCommand GetCommand()
{
cmd = new SqlCommand();
cmd.Connection = sqlConn;
return cmd;
}
public SqlConnection GetConn()
{
sqlConn = new SqlConnection(connectionString);
return sqlConn;
}
}
Step 2: Insert, Update and delete database using DBConnector class
public class EmployeeInfoDAL
{
private SqlConnection sqlConn;
private SqlCommand cmd;
private readonly DBConnector objDBConnector;
public EmployeeInfoDAL()
{
objDBConnector = new DBConnector();
sqlConn = objDBConnector.GetConn();
cmd = objDBConnector.GetCommand();
}
//Insert data
public string InsertEmployeeUserInfo(EmployeeUserInfo objEmployeeUserInfo)
{
int noOfRowEffected = 0;
try
{
sqlConn.Open();
cmd.CommandType = CommandType.StoredProcedure; //If you use query instead of storprocedure. User CommandType.Text
cmd.CommandText = "[fsp_Insert_employee_userInfo]"; //for query use query instead of store procedure
cmd.Parameters.AddWithValue("@user_id", objEmployeeUserInfo.UserId); // dont use it if you don't need parameter
cmd.Parameters.AddWithValue("@emp_id", objEmployeeUserInfo.EmployeeId);// dont use it if you don't need parameter
noOfRowEffected = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception exp)
{
throw (exp);
}
finally
{
sqlConn.Close();
}
if (noOfRowEffected > 0)
return "Employee User information saved successfully!";
else
return "Employee User information didn't save";
}
}
This is tested and I am use its in my project. Please try this way. Hopefully it will work. Becarefule about your connection string.
Sorry but one of my requirements is to not use a class I believe, hoping to make the gormat im using work rather then a full workover.
I think I have reduced the problem a little, I changed the web form to do this.
[WebMethod]
public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME)
{
// In case of failure failure first
bool rtn = false;
// Connect to the Database
SqlConnection connection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;
AttachDbFilename='|DataDirectory|\Database.mdf';
Integrated Security=True;
User Instance=True");
// Open the connection
connection.Open();
// Create a SQL command object.
SqlCommand command = new SqlCommand(
String.Format("UPDATE PersonalDetails set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'",
FIRSTNAME, SURNAME, ID), connection);
// Execute the command.
command.ExecuteNonQuery();
// Close the connection.
connection.Close();
rtn = true;
// Return the outcome
return (rtn);
}
However the error I am getting now says the following (in visual when I attempt to update on click)
Conversion failed when converting the varchar value 'xx' to data type int.
ANy idea the issue here? The thing is I have ID as a string in the webform cs. (as you can see above) but I do this to read the user input from text field. And I then change it to an int with the int.Parse(ID) statement. Then in the web service cs. sheet the
ID is an int, not sure the issue here, anyone can notice the error. I feel like the code I posted is really close to working.
tusharrs
Contributor
3230 Points
668 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 08:36 AM|LINK
hi,
1) check the connection string, is it proper
2) check the query by executing in sql server
3) check the field datattypes are they matching
( Mark as Answer if it helps you out )
View my Blog
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 08:53 AM|LINK
No worries, here is the error in full detail:
----------------------------------------------------------------------
My webform, the interface the user sees in browser.
Basically here the ID 1 is already entered in the database (using the add button). So when the user selects update with new info in first/surname the database should be updated, However when I click update I get the following error in Visual:
and when I stop debugging, the web browser gives me this error:
Server Error in '/' Application.
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\sit322_ass2\assignment2\WebServices\WebService.asmx.cs:line 118
--- End of inner exception stack trace ---
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\database\WebServices\WebService.asmx.cs:line 118
--- End of inner exception stack trace ---
Source Error:
Line 153: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://tempuri.org/UpdatePerson", RequestNamespace="http://tempuri.org/", ResponseNamespace="http://tempuri.org/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)] Line 154: public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) { Line 155: object[] results = this.Invoke("UpdatePerson", new object[] { Line 156: ID, Line 157: FIRSTNAME,Source File: C:\Users\Timothy\Desktop\database\PersonalDetails\Web References\DbWebService\Reference.cs Line: 155
---
I'm not sure why this is happening!?Really confused...
Here are the two relevant codes again:
personal details webform cs. code, function for update
protected void cmdUpdate_Click(object sender, EventArgs e) { // Create a reference to the Web service DbWebService.WebService1 proxy = new DbWebService.WebService1(); // Create a person details object to send to the Web service. string SURNAME; string FIRSTNAME; string ID; SURNAME = txtSurname.Text; FIRSTNAME = txtFirstname.Text; ID = txtID.Text; // Attempt to store in the Web service bool rsp = proxy.UpdatePerson(int.Parse(ID), FIRSTNAME, SURNAME); // Inform the user if (rsp) { lblOutcome.Text = "Successfully updated new user."; } else { lblOutcome.Text = "Failed to update user!"; } }webservice cs. update SQL function
[WebMethod] public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) { // Connect to the Database SqlConnection connection = new SqlConnection( @"Data Source=.\SQLEXPRESS; AttachDbFilename='|DataDirectory|\Database.mdf'; Integrated Security=True; User Instance=True"); // Open the connection connection.Open(); // Create a SQL command object. SqlCommand command = new SqlCommand( String.Format("UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'", ID, FIRSTNAME, SURNAME)); // Execute the SQL command and store the returned integer. int response = (int)command.ExecuteScalar(); // Close the connection connection.Close(); // Return the result. return (response > 0); }tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 08:56 AM|LINK
sorry to sound a little amature-ish
But how do I do this?
I think the data types are matching.
when I was adding my add function I also had the same error, but that was because I had a typo in the SQL command, I accidently misspelled personaldetails (the database refrence). Thus why I think this is an SQL command error.
The add function currently works perfect.
tusharrs
Contributor
3230 Points
668 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 09:56 AM|LINK
hi,
set connection to sqlcommand object
i.e.
command.SqlConnection = connection;
or
SqlCommand command = new SqlCommand( String.Format("UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'", ID, FIRSTNAME, SURNAME) , connection );
( Mark as Answer if it helps you out )
View my Blog
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 11:39 PM|LINK
Thanks again for the reply.
I believe I tried this at one stage and still got error, but shall try again. Just at work at the moment so will try it when I get home, and mark as answer if it solves my problem.
Any chance (using the provided code) that I made an error somewhere else in the code?
regards, tim.
mahedee
Member
450 Points
116 Posts
Re: Update function in web form using SQL express
Apr 26, 2012 04:22 AM|LINK
You didn't initialize connection in sql command. Use the following before execute the query.
command.Connection = connection ;
Mahedee
Blog: http://mahedee.blogspot.com
mahedee
Member
450 Points
116 Posts
Re: Update function in web form using SQL express
Apr 26, 2012 04:35 AM|LINK
Mahedee
Blog: http://mahedee.blogspot.com
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 26, 2012 07:52 AM|LINK
yep fixed that and still get the error, this is inreasingly frustrating.
I fixed it to this
// Create a SQL command object. SqlCommand command = new SqlCommand( String.Format("UPDATE PersonalDetails set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'", FIRSTNAME, SURNAME, ID), connection);still get the same error.
sorry but what do you mean by this, really confused?
Do I remove any of these lines: ?
// Execute the SQL command and store the returned integer. int response = (int)command.ExecuteScalar(); // Close the connection PS: I KNOW I DONT REMOVE THIS LINE connection.Close(); // Return the result. return (response > 0);mahedee
Member
450 Points
116 Posts
Re: Update function in web form using SQL express
Apr 26, 2012 09:33 AM|LINK
I often use this way to connect, insert, update i,e acess database.
Step - 1: Simply create a class DBConnector to connect to the database.
public class DBConnector { private string connectionString = null; private SqlConnection sqlConn = null; private SqlCommand cmd = null; public DBConnector() { connectionString = ConfigurationManager.ConnectionStrings["application"].ToString(); //User your connection string here. } public SqlCommand GetCommand() { cmd = new SqlCommand(); cmd.Connection = sqlConn; return cmd; } public SqlConnection GetConn() { sqlConn = new SqlConnection(connectionString); return sqlConn; } }Step 2: Insert, Update and delete database using DBConnector class
public class EmployeeInfoDAL { private SqlConnection sqlConn; private SqlCommand cmd; private readonly DBConnector objDBConnector; public EmployeeInfoDAL() { objDBConnector = new DBConnector(); sqlConn = objDBConnector.GetConn(); cmd = objDBConnector.GetCommand(); } //Insert data public string InsertEmployeeUserInfo(EmployeeUserInfo objEmployeeUserInfo) { int noOfRowEffected = 0; try { sqlConn.Open(); cmd.CommandType = CommandType.StoredProcedure; //If you use query instead of storprocedure. User CommandType.Text cmd.CommandText = "[fsp_Insert_employee_userInfo]"; //for query use query instead of store procedure cmd.Parameters.AddWithValue("@user_id", objEmployeeUserInfo.UserId); // dont use it if you don't need parameter cmd.Parameters.AddWithValue("@emp_id", objEmployeeUserInfo.EmployeeId);// dont use it if you don't need parameter noOfRowEffected = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } catch (Exception exp) { throw (exp); } finally { sqlConn.Close(); } if (noOfRowEffected > 0) return "Employee User information saved successfully!"; else return "Employee User information didn't save"; } }This is tested and I am use its in my project. Please try this way. Hopefully it will work. Becarefule about your connection string.
Mahedee
Blog: http://mahedee.blogspot.com
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 26, 2012 02:13 PM|LINK
Hi,
Sorry but one of my requirements is to not use a class I believe, hoping to make the gormat im using work rather then a full workover.
I think I have reduced the problem a little, I changed the web form to do this.
[WebMethod] public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) { // In case of failure failure first bool rtn = false; // Connect to the Database SqlConnection connection = new SqlConnection( @"Data Source=.\SQLEXPRESS; AttachDbFilename='|DataDirectory|\Database.mdf'; Integrated Security=True; User Instance=True"); // Open the connection connection.Open(); // Create a SQL command object. SqlCommand command = new SqlCommand( String.Format("UPDATE PersonalDetails set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'", FIRSTNAME, SURNAME, ID), connection); // Execute the command. command.ExecuteNonQuery(); // Close the connection. connection.Close(); rtn = true; // Return the outcome return (rtn); }However the error I am getting now says the following (in visual when I attempt to update on click)
Conversion failed when converting the varchar value 'xx' to data type int.
ANy idea the issue here? The thing is I have ID as a string in the webform cs. (as you can see above) but I do this to read the user input from text field. And I then change it to an int with the int.Parse(ID) statement. Then in the web service cs. sheet the ID is an int, not sure the issue here, anyone can notice the error. I feel like the code I posted is really close to working.