?
Solved

Receiving a DATATable by parameter and storing the Information in SQL

Posted on 2010-04-09
4
Medium Priority
?
450 Views
Last Modified: 2013-12-17
Hello,

I'm creating an webservice with an Method to receive a datatable. After that i want to loop the datatable to save the information in SQL trough an Store Procedure.

So far this is what i have done: view attached code.

Now, the problem apparently is with the Webservice, it's not saving the information in the Database. I tested the store procedure and is working good. But when i send the parameters to the Webservice i does not work.

Can anyone help me with this?

Thanks,

Edward
[WebMethod]
        public Boolean SetConciliationResume(int OrdersQTY, DateTime ConciliationDate, DataTable Dt)
        {
            //DataTable Dt = ListaData.Tables["Ivr_ConciliationDetails_data"];
            string strSQLSelect2 = "[SP_GetWMOrdersConciliation @TotalOrders='" + OrdersQTY.ToString() + "',@ConciliationDate='" + ConciliationDate.ToString() + "']";
            OleDbConnection databaseConnection = new OleDbConnection(oleDBConnectionString);
            OleDbCommand selectCommand = new OleDbCommand(strSQLSelect2, databaseConnection);
            OleDbDataAdapter dsCmd = new OleDbDataAdapter();

            selectCommand.CommandType = CommandType.StoredProcedure;
            // Establish database connection 
            databaseConnection.Open();
            // Execute SQL Command 
            dsCmd.SelectCommand = selectCommand;
            databaseConnection.Close();

            try
            {
                for (int i = 0; i < Dt.Rows.Count; i++)
                {
                    DataRow DTR = Dt.Rows[i];

                    string strSQLSelect = "[SP_GetWMConciliationDetail @confno='" + DTR["Confno"].ToString() + "', @storeno='" + DTR["storeno"].ToString() + "', @pono='" + DTR["pono"].ToString() + "', @datesent='" + DTR["datesent"].ToString() + "']";
                    OleDbCommand selectCommand2 = new OleDbCommand(strSQLSelect, databaseConnection);

                    selectCommand2.CommandType = CommandType.StoredProcedure;

                    // Establish database connection 
                    databaseConnection.Open();

                    // Execute SQL Command 
                    dsCmd.SelectCommand = selectCommand2;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("****** Caught an exception:\n{0}", e.Message);
            }
            finally
            {
            }
            return true;
        }

Open in new window

0
Comment
Question by:Fernandez_E
  • 2
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 2000 total points
ID: 30269915
Hi,

Try addin an ExecuteNonQuery() after assign your SelectCommand like this:

// Execute SQL Command
dsCmd.SelectCommand = selectCommand;
dsCmd.ExecuteNonQuery();   //Executes the Command

And the same in your loop

// Execute SQL Command
dsCmd.SelectCommand = selectCommand2;
dsCmd.ExecuteNonQuery();   //Executes the Command


0
 

Author Comment

by:Fernandez_E
ID: 30520343
Hi,

I did create a function to perform the saving with executeNonQuery() and it works but it's taking my dataset and reading it inside of the loop.

The first part is doing it but after at the moment of perform the loop it's not working. Meaby is not receiving the dataset correctly.

Any idea???
private static void cmdConciliationDetail(string queryString, string Pconfno, string Pstore, string Ppono, DateTime Pdatesent, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter ConfNo = new SqlParameter("@ConfNo", SqlDbType.VarChar, 20);
                ConfNo.Value = Pconfno.ToString();
                command.Parameters.Add(ConfNo);

                SqlParameter Storeno = new SqlParameter("@Storeno", SqlDbType.VarChar, 4);
                Storeno.Value = Pstore.ToString();
                command.Parameters.Add(Storeno);

                SqlParameter Pono = new SqlParameter("@Pono", SqlDbType.VarChar, 4);
                Pono.Value = Ppono.ToString();
                command.Parameters.Add(Pono);

                SqlParameter DateSent = new SqlParameter("@ConciliationDate", SqlDbType.DateTime);
                DateSent.Value = Pdatesent.ToString();
                command.Parameters.Add(DateSent);

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }



        [WebMethod]
        public Boolean SetConciliationResume(int OrdersQTY, DateTime ConciliationDate, DataSet ListaData)
        {
            DataTable Dt = table.Tables["Ivr_ConciliationDetails_data"];
            string strSQLSelect2 = "dbo.SP_GetWMOrdersConciliation";
            cmdConciliationResume(strSQLSelect2,OrdersQTY, ConciliationDate, SQLDBConnectionString);

            try
            {
                for (int i = 0; i < Dt.Rows.Count; i++)
                {
                    DataRow DTR = Dt.Rows[i];

                    string strSQLSelect = "dbo.SP_GetWMConciliationDetail";
                    cmdConciliationDetail(strSQLSelect, DTR["Confno"].ToString(), DTR["storeno"].ToString(), DTR["pono"].ToString(), Convert.ToDateTime(DTR["datesent"].ToString()), SQLDBConnectionString);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("****** Caught an exception:\n{0}", e.Message);
            }
            finally
            {
            }
            return true;
        }

Open in new window

0
 

Author Closing Comment

by:Fernandez_E
ID: 31712804
Great!
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 30523157
Try to change a little your cmdConciliationDetail process, in the attached code I change the place where you open your connection and added a Try/catch block, to figure out where is the problem.
private static void cmdConciliationDetail(string queryString, string Pconfno, string Pstore, string Ppono, DateTime Pdatesent, string connectionString) 
        { 
            using (SqlConnection connection = new SqlConnection(connectionString)) 
            { 

		try{
			connection.Open();
	                SqlCommand command = new SqlCommand(queryString, connection); 
        	        command.CommandType = CommandType.StoredProcedure; 
 	
        	        SqlParameter ConfNo = new SqlParameter("@ConfNo", SqlDbType.VarChar, 20); 
                	ConfNo.Value = Pconfno.ToString(); 
	                command.Parameters.Add(ConfNo); 
 
 	                SqlParameter Storeno = new SqlParameter("@Storeno", SqlDbType.VarChar, 4); 
        	        Storeno.Value = Pstore.ToString(); 
                	command.Parameters.Add(Storeno); 
 
	                SqlParameter Pono = new SqlParameter("@Pono", SqlDbType.VarChar, 4); 
        	        Pono.Value = Ppono.ToString(); 
                	command.Parameters.Add(Pono); 
 
 	                SqlParameter DateSent = new SqlParameter("@ConciliationDate", SqlDbType.DateTime); 
        	        DateSent.Value = Pdatesent.ToString(); 
                	command.Parameters.Add(DateSent); 
 
        	        command.ExecuteNonQuery(); 
			connection.close();
		}
		catch (Exception e)
		{
			Console.WriteLine("****** Caught an exception:\n{0}", e.Message); 
		}


            } 
        }

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question