Error importing xml file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to export data from multiple tables in SQL Server to an XML file
so I can then import it to another database. It seems to be working fine for
exporting, but I am having trouble importing the file. I am getting the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even though I
say the export is working, I am including it below in case there is something
in there that needs to be changed. Also, the code I am including is VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the import, If I
understand it correctly, which I don't since I'm here :), I am supposed open
a dataset and then update it. Is that correct? If someone can show me where
I am going wrong and explain it, that would be great. Thanks in advance.

public static int Export()
{
string databaseName = HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] + databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName = HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] + databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
 
Mike,

I hope that you don't mind that I check completely your code, it is to long
for me, as far as I can see do I miss the line of code about this property.

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

That is mostly the first problem in this kind of situations.

I hope this helps,

Cor


Mike Collins said:
I am trying to export data from multiple tables in SQL Server to an XML
file
so I can then import it to another database. It seems to be working fine
for
exporting, but I am having trouble importing the file. I am getting the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even
though I
say the export is working, I am including it below in case there is
something
in there that needs to be changed. Also, the code I am including is VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the import, If I
understand it correctly, which I don't since I'm here :), I am supposed
open
a dataset and then update it. Is that correct? If someone can show me
where
I am going wrong and explain it, that would be great. Thanks in advance.

public static int Export()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
Click to expand...
 
Thanks, but one thing I found out is that I cannot expect to update the
database with the xml file when it contains multiple files. I believe I have
to enumerate through the file and call an update stored procedure for each
line. Is that correct?

Cor Ligthert said:
Mike,

I hope that you don't mind that I check completely your code, it is to long
for me, as far as I can see do I miss the line of code about this property.

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

That is mostly the first problem in this kind of situations.

I hope this helps,

Cor


Mike Collins said:
I am trying to export data from multiple tables in SQL Server to an XML
file
so I can then import it to another database. It seems to be working fine
for
exporting, but I am having trouble importing the file. I am getting the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even
though I
say the export is working, I am including it below in case there is
something
in there that needs to be changed. Also, the code I am including is VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the import, If I
understand it correctly, which I don't since I'm here :), I am supposed
open
a dataset and then update it. Is that correct? If someone can show me
where
I am going wrong and explain it, that would be great. Thanks in advance.

public static int Export()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
Click to expand...
Click to expand...
 
Mike,

If you are updating with the update, than you are in fact only using an
insert. As soon as there is than a row that exist, than you are in problem,
because in your datarowstate is set that it is about a new row and than you
can have concurrency error or errors because that some fields have to be
unique in your database.

How to solve that is in my idea based on the solution that you want to
achieve.

Your dataset has to have of course exactly the same schema as the database
table.

Cor

Mike Collins said:
Thanks, but one thing I found out is that I cannot expect to update the
database with the xml file when it contains multiple files. I believe I
have
to enumerate through the file and call an update stored procedure for each
line. Is that correct?

Cor Ligthert said:
Mike,

I hope that you don't mind that I check completely your code, it is to
long
for me, as far as I can see do I miss the line of code about this
property.

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

That is mostly the first problem in this kind of situations.

I hope this helps,

Cor


Mike Collins said:
I am trying to export data from multiple tables in SQL Server to an XML
file
so I can then import it to another database. It seems to be working
fine
for
exporting, but I am having trouble importing the file. I am getting
the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even
though I
say the export is working, I am including it below in case there is
something
in there that needs to be changed. Also, the code I am including is
VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the import,
If I
understand it correctly, which I don't since I'm here :), I am supposed
open
a dataset and then update it. Is that correct? If someone can show me
where
I am going wrong and explain it, that would be great. Thanks in
advance.

public static int Export()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
Click to expand...
Click to expand...
Click to expand...
 
I tried what you said, but I still get "Incorrect syntax neat
'GetTprsForExport'". I'm pretty stuck here.

Cor Ligthert said:
Mike,

If you are updating with the update, than you are in fact only using an
insert. As soon as there is than a row that exist, than you are in problem,
because in your datarowstate is set that it is about a new row and than you
can have concurrency error or errors because that some fields have to be
unique in your database.

How to solve that is in my idea based on the solution that you want to
achieve.

Your dataset has to have of course exactly the same schema as the database
table.

Cor

Mike Collins said:
Thanks, but one thing I found out is that I cannot expect to update the
database with the xml file when it contains multiple files. I believe I
have
to enumerate through the file and call an update stored procedure for each
line. Is that correct?

Cor Ligthert said:
Mike,

I hope that you don't mind that I check completely your code, it is to
long
for me, as far as I can see do I miss the line of code about this
property.

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

That is mostly the first problem in this kind of situations.

I hope this helps,

Cor


"Mike Collins" <[email protected]> schreef in bericht
I am trying to export data from multiple tables in SQL Server to an XML
file
so I can then import it to another database. It seems to be working
fine
for
exporting, but I am having trouble importing the file. I am getting
the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even
though I
say the export is working, I am including it below in case there is
something
in there that needs to be changed. Also, the code I am including is
VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the import,
If I
understand it correctly, which I don't since I'm here :), I am supposed
open
a dataset and then update it. Is that correct? If someone can show me
where
I am going wrong and explain it, that would be great. Thanks in
advance.

public static int Export()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
Click to expand...
Click to expand...
Click to expand...
 
Mike,

Without that renewed code we can do nothing for you.

Cor

Mike Collins said:
I tried what you said, but I still get "Incorrect syntax neat
'GetTprsForExport'". I'm pretty stuck here.

Cor Ligthert said:
Mike,

If you are updating with the update, than you are in fact only using an
insert. As soon as there is than a row that exist, than you are in
problem,
because in your datarowstate is set that it is about a new row and than
you
can have concurrency error or errors because that some fields have to be
unique in your database.

How to solve that is in my idea based on the solution that you want to
achieve.

Your dataset has to have of course exactly the same schema as the
database
table.

Cor

Mike Collins said:
Thanks, but one thing I found out is that I cannot expect to update the
database with the xml file when it contains multiple files. I believe I
have
to enumerate through the file and call an update stored procedure for
each
line. Is that correct?

:

Mike,

I hope that you don't mind that I check completely your code, it is to
long
for me, as far as I can see do I miss the line of code about this
property.

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

That is mostly the first problem in this kind of situations.

I hope this helps,

Cor


"Mike Collins" <[email protected]> schreef in
bericht
I am trying to export data from multiple tables in SQL Server to an
XML
file
so I can then import it to another database. It seems to be working
fine
for
exporting, but I am having trouble importing the file. I am getting
the
following error trying to import the same xml file I just exported.
"System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'GetTprsForExport'. Can someone show me where I am going wrong. Even
though I
say the export is working, I am including it below in case there is
something
in there that needs to be changed. Also, the code I am including is
VERY
rough right now and will
change quite a bit once I figure out what I am doing. For the
import,
If I
understand it correctly, which I don't since I'm here :), I am
supposed
open
a dataset and then update it. Is that correct? If someone can show
me
where
I am going wrong and explain it, that would be great. Thanks in
advance.

public static int Export()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;
DataSet ds;

using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}

DataRelation dr;
DataColumn parentCol;
DataColumn childCol;

parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];

dr = new DataRelation("TprAssociatedData", parentCol, childCol);

// Add the relation to the DataSet.
ds.Relations.Add(dr);

ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);

return ds.Tables[0].Rows.Count;
}


public static int Import()
{
string databaseName =
HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] +
databaseName;

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");

DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);

adapter.Update(ds1, "Tprs");
}

return 1;
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top