XML Web Service error on Update()
-
This is the code of an XML Web Service that Query an SQL Server. It takes two strings and returns a DataSet. It's a simple excercise, It query the database, populate a DataSet, modify a Field and Update the changes. [WebMethod] public DataSet GetUserData(string szUsername, string szNewLocalName) { string szSqlCommand = "SELECT * FROM Locals WHERE Username = '" + szUsername + "'"; SqlConnection myConnection = new SqlConnection(" User ID=sa;Password=password;Initial Catalog=laphijia;Data Source=(local)"); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(szSqlCommand, myConnection); DataSet myDataSet = new DataSet(); mySqlDataAdapter.UpdateCommand = new SqlCommand("UPDATE Locals SET LocalName = @LocalName" + "WHERE Username = @Username", myConnection); mySqlDataAdapter.UpdateCommand.Parameters.Add("@LocalName", SqlDbType.NVarChar, 25, "LocalName"); SqlParameter workParam = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 25); workParam.SourceColumn = "Username"; workParam.SourceVersion = DataRowVersion.Original; mySqlDataAdapter.Fill(myDataSet, "Locals"); myDataSet.Tables["Locals"].Rows[0]["LocalName"]= szNewLocalName; mySqlDataAdapter.Update(myDataSet, "Locals"); return myDataSet; } I made this with help from the MSDN NET Framework Developer's Guide article Updating the Database with a DataAdapter and the DataSet. When I try to access the Web Service and Invokes it, I get a 505 Internal Server Error. I tried to remove the line: mySqlDataAdapter.Update(myDataSet, "Locals"); and it works (it returns the DataSet as XML data, but obviously don't update the DataBase. What's wrong with my code? I also tried with the SQL Command Builder, but it's just the same. Thank You. "Nelle cose del mondo non e' il sapere ma il volere che puo'."
-
This is the code of an XML Web Service that Query an SQL Server. It takes two strings and returns a DataSet. It's a simple excercise, It query the database, populate a DataSet, modify a Field and Update the changes. [WebMethod] public DataSet GetUserData(string szUsername, string szNewLocalName) { string szSqlCommand = "SELECT * FROM Locals WHERE Username = '" + szUsername + "'"; SqlConnection myConnection = new SqlConnection(" User ID=sa;Password=password;Initial Catalog=laphijia;Data Source=(local)"); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(szSqlCommand, myConnection); DataSet myDataSet = new DataSet(); mySqlDataAdapter.UpdateCommand = new SqlCommand("UPDATE Locals SET LocalName = @LocalName" + "WHERE Username = @Username", myConnection); mySqlDataAdapter.UpdateCommand.Parameters.Add("@LocalName", SqlDbType.NVarChar, 25, "LocalName"); SqlParameter workParam = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 25); workParam.SourceColumn = "Username"; workParam.SourceVersion = DataRowVersion.Original; mySqlDataAdapter.Fill(myDataSet, "Locals"); myDataSet.Tables["Locals"].Rows[0]["LocalName"]= szNewLocalName; mySqlDataAdapter.Update(myDataSet, "Locals"); return myDataSet; } I made this with help from the MSDN NET Framework Developer's Guide article Updating the Database with a DataAdapter and the DataSet. When I try to access the Web Service and Invokes it, I get a 505 Internal Server Error. I tried to remove the line: mySqlDataAdapter.Update(myDataSet, "Locals"); and it works (it returns the DataSet as XML data, but obviously don't update the DataBase. What's wrong with my code? I also tried with the SQL Command Builder, but it's just the same. Thank You. "Nelle cose del mondo non e' il sapere ma il volere che puo'."
Try wrapping the Update code with a try/catch and see if something is happening there. Maybe Alex sees something wrong with the call ;) James "Java is free - and worth every penny." - Christian Graus
-
Try wrapping the Update code with a try/catch and see if something is happening there. Maybe Alex sees something wrong with the call ;) James "Java is free - and worth every penny." - Christian Graus
I tried changing the UpdateCommand with a custom string like "UPDATE locals SET LocalName = 'New Name' WHERE Username = 'john'" This Worked, I mean, the Method returned the DataSet, and didn't give a 500 Internal Server Error, anyway the Method was completely USELESS now. But I figured out that the problem is in the UpdateCommand Sql statement. What's wrong there? Username is not the Primary Key, it's only used for Authentication purposes on the final version. How Can I have a correct Sql Statement, connected with the values that I could modify in the DataSet? "Nelle cose del mondo non e' il sapere ma il volere che puo'."
-
I tried changing the UpdateCommand with a custom string like "UPDATE locals SET LocalName = 'New Name' WHERE Username = 'john'" This Worked, I mean, the Method returned the DataSet, and didn't give a 500 Internal Server Error, anyway the Method was completely USELESS now. But I figured out that the problem is in the UpdateCommand Sql statement. What's wrong there? Username is not the Primary Key, it's only used for Authentication purposes on the final version. How Can I have a correct Sql Statement, connected with the values that I could modify in the DataSet? "Nelle cose del mondo non e' il sapere ma il volere che puo'."
The only thing I can see that might be wrong with the SQL Query is that there is no space between the @LocalName parameter and the word WHERE. If you wrap the dataadapter.update method call with a try/catch you could output any error that is generated, which should give you an idea of what to look for. James "Java is free - and worth every penny." - Christian Graus
-
The only thing I can see that might be wrong with the SQL Query is that there is no space between the @LocalName parameter and the word WHERE. If you wrap the dataadapter.update method call with a try/catch you could output any error that is generated, which should give you an idea of what to look for. James "Java is free - and worth every penny." - Christian Graus
James T. Johnson wrote: The only thing I can see that might be wrong with the SQL Query is that there is no space between the @LocalName parameter and the word WHERE. :omg:Could you believe it was just this? Da*n! Thank You, this is the great thing about The Codeproject, that new eyes see subtle errors!:-D :eek: "Nelle cose del mondo non e' il sapere ma il volere che puo'."