(e-mail address removed) (susan) wrote in
Hi All,
I've created a webservice which receives XML data and then in turn
updates a database with the received data. This is my web method:
[WebMethod]
public void submitClientInfo(string sXML)
{
string XmlName="";
string XmlAge= "";
string XmlPlace="";
SqlCommand cmd=new SqlCommand("Insert into table1 (name, age, place)
values ('" + XmlName + "'," + XmlAge + ",'" + XmlPlace + "')");
XmlDataDocument XmlDoc= new XmlDataDocument();
XmlDoc.LoadXml(sXML);
DataSet ds=XmlDoc.DataSet;
foreach (DataRow Demo in ds.Tables[0].Rows)
{
XmlName=Demo["name"].ToString();
XmlAge= Demo["age"].ToString();
XmlPlace=Demo["place"].ToString();
cmd.ExecuteNonQuery();
}
}
Looks pretty simple..but I'm getting an Internal server error: error
500. Could someone pls help me.
Thanks
Susan
You're going about the SqlCommand incorrectly.
For one, do NOT "hardcode" variable values directly into your
commandText. You open yourself to SQL injections that way. Instead use
parameters in the command.
SqlCommand cmd=new SqlCommand("Insert into table1 (name, age, place)"
" values (
@name, @Age, @Place)");
SqlParameter prmName = cmd.Parameters.Add("
@name");
prmName.SourceColumn = "name";
Second, you didn't specify a SqlConnection for the command, so how does
it know what database to run against?
The other problem is that you are setting the values after the blank
values were being added to the commandtext of the command. Changing the
variable values does not automagically update any other string that was
concatenated with that string. Go back to programming 101 to understand
that concept.
However, you can change the Value property of the Parameter and do
another update. Here is the code you need...
[WebMethod]
public void submitClientInfo(string sXML)
{
SqlConnection myConnection = new SqlConnection(_conString);
//_conString must be a valid database connection string!
DataSet ds = new DataSet("clientInfo");
ds.ReadXml(sXml, XmlReadMode.ReadSchema);
SqlCommand cmd=new SqlCommand("Insert into table1 "
"(name, age, place) values (
@name, @Age, @Place)"
, myConnection);
SqlParameter prmName = cmd.Parameters.Add("
@name");
prmName .DataType = SqlDbType.NVarChar;
prmName.SourceColumn = "name";
SqlParameter prmName = cmd.Parameters.Add("@Age");
prmName .DataType = SqlDbType.NVarChar;
prmName.SourceColumn = "age";
SqlParameter prmName = cmd.Parameters.Add("@Place");
prmName .DataType = SqlDbType.NVarChar;
prmName.SourceColumn = "place";
foreach (DataRow Demo in ds.Tables[0].Rows)
{
prmName.Value = Demo["name"]; //no ToString()
prmAge.Value = Demo["age"];
prmPlace.Value = Demo["place"];
cmd.ExecuteNonQuery();
}
}
I don't know your Db schema. You may have to change the parameter
datatypes to a different SqlDbType type. As long as the Schema of the
XMl file and the Sql database of of compatible types you don't need to
convert the datatype from each DataRow "ToString" or any other conversion
method.
(This sample provided as is to demonstrate concepts, with no expressed or
implied warranty. It was written directly into the newsreader without
testing in VS)
--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
ataType="System.Guid, mscorlib, Version=1.0.3300.0,