convert parameter value from a DataColumn to a String?

[Socarsky]

This is where I want to insert in MS SQL


Visual Studio thrown is "Failed to convert parameter value from a DataColumn to a String."

Here is my code for inserting:

private void MSSQLInsert()
        {
            newProdID = 0;
            
            string ConnStrMSSQL = "Data Source=192.168....;Initial Catalog=xxxx;Persist Security Info=True;User ID=sa;Password=xxxxxx;";
            using (SqlConnection con = new SqlConnection(ConnStrMSSQL)) 
            {
                con.Open();
            
                using (SqlCommand command = new SqlCommand(
                    "INSERT INTO tbRecords VALUES (@sPersonnelCode, @sTerminalCode, " +
                    "@sOperationCode, @sItemCode, @sItemDescription, @nJobOrder, @nMPS, " +
                    "@nRecordType, @nQTY, @dStartTime, @dFinishTime, @sSONumber, @sSONumberItemCode, " +
                    "@dLatestStarting, @dLatestFinishing, @sComputerName, @sDatabase);",
                    con)) 
                {
                    command.Parameters.Add(new SqlParameter("@sPersonnelCode", System.Data.SqlDbType.VarChar, 3));
                    command.Parameters.Add(new SqlParameter("@sTerminalCode", System.Data.SqlDbType.VarChar, 4));
                    command.Parameters.Add(new SqlParameter("@sOperationCode", System.Data.SqlDbType.VarChar, 6));
                    command.Parameters.Add(new SqlParameter("@sItemCode", System.Data.SqlDbType.VarChar, 30));
                    command.Parameters.Add(new SqlParameter("@sItemDescription", System.Data.SqlDbType.VarChar, 70));
                    command.Parameters.Add(new SqlParameter("@nJobOrder", System.Data.SqlDbType.VarChar, 10));
                    command.Parameters.Add(new SqlParameter("@nMPS", System.Data.SqlDbType.VarChar, 5));
                    command.Parameters.Add(new SqlParameter("@nRecordType", System.Data.SqlDbType.Bit));
                    command.Parameters.Add(new SqlParameter("@nQTY", System.Data.SqlDbType.Decimal));
                    command.Parameters.Add(new SqlParameter("@dStartTime", System.Data.SqlDbType.DateTime));
                    command.Parameters.Add(new SqlParameter("@dFinishTime", System.Data.SqlDbType.DateTime));
                    command.Parameters.Add(new SqlParameter("@sSONumber", System.Data.SqlDbType.VarChar, 10));
                    command.Parameters.Add(new SqlParameter("@sSONumberItemCode", System.Data.SqlDbType.VarChar, 20));
                    command.Parameters.Add(new SqlParameter("@dLatestStarting", System.Data.SqlDbType.Date));
                    command.Parameters.Add(new SqlParameter("@dLatestFinishing", System.Data.SqlDbType.Date));
                    command.Parameters.Add(new SqlParameter("@sComputerName", System.Data.SqlDbType.VarChar, 30));
                    command.Parameters.Add(new SqlParameter("@sDatabase", System.Data.SqlDbType.VarChar, 15));
                    command.Parameters["@sPersonnelCode"].Value = strPersonnelCode;
                    command.Parameters["@sTerminalCode"].Value = equalDT.Columns["Tezgah"];
                    command.Parameters["@sOperationCode"].Value = equalDT.Columns["OperationCode"];
                    command.Parameters["@sItemCode"].Value = equalDT.Columns["ItemCode"];
                    command.Parameters["@sItemDescription"].Value = equalDT.Columns["ItemDescription"];
                    command.Parameters["@nJobOrder"].Value = equalDT.Columns["JobOrder"];
                    command.Parameters["@nMPS"].Value = equalDT.Columns["Evrak"];
                    command.Parameters["@nRecordType"].Value = 1;
                    command.Parameters["@nQTY"].Value = 1;
                    command.Parameters["@dStartTime"].Value = dateTimeNow();
                    command.Parameters["@dFinishTime"].Value = dateTimeNow();
                    command.Parameters["@sSONumber"].Value = equalDT.Columns["SONumber"];
                    command.Parameters["@sSONumberItemCode"].Value = equalDT.Columns["SONumberItemCode"];
                    command.Parameters["@dLatestStarting"].Value = equalDT.Columns["LatestStarting"];
                    command.Parameters["@dLatestFinishing"].Value = equalDT.Columns["LatestFinishing"];
                    command.Parameters["@sComputerName"].Value = machineName;
                    command.Parameters["@sDatabase"].Value = getDBname;

                    command.ExecuteNonQuery();

                    // below for seeing colomns in DataTable equalDT
                    //0 takas.Columns.Add(new DataColumn("Tezgah", typeof(string)));
                    //1 takas.Columns.Add(new DataColumn("TezgahAdi", typeof(string)));
                    //2 takas.Columns.Add(new DataColumn("OperationCode", typeof(string)));
                    //3 takas.Columns.Add(new DataColumn("OperationName", typeof(string)));
                    //4 takas.Columns.Add(new DataColumn("ItemCode", typeof(string)));
                    //5 takas.Columns.Add(new DataColumn("ItemDescription", typeof(string)));
                    //6 takas.Columns.Add(new DataColumn("JobOrder", typeof(string)));
                    //7 takas.Columns.Add(new DataColumn("Evrak", typeof(string)));
                    //8 takas.Columns.Add(new DataColumn("QTY", typeof(int)));
                    //9 takas.Columns.Add(new DataColumn("SONumber", typeof(string)));
                    //10 takas.Columns.Add(new DataColumn("SONumberItemCode", typeof(string)));
                    //11 takas.Columns.Add(new DataColumn("LatestStarting", typeof(DateTime)));
                    //12 takas.Columns.Add(new DataColumn("LatestFinishing", typeof(DateTime)));
                }
            }
        }

 

[wim sturkenboom]

I'm not quite sure, but it looks like you're trying to dump a complete column in a varchar. E.g. a line like

command.Parameters["@sTerminalCode"].Value = equalDT.Columns["Tezgah"];

But as said, not sure if that is the issue.

On a side note:
it's a very bad habit to use the SA user to access your database.

 

[jmcilhinney]

You're doing that all wrong. Check out the example in post #2 here:

Retrieving and Saving Data in Databases

 

[Socarsky]

You're doing that all wrong. Check out the example in post #2 here:

Retrieving and Saving Data in Databases

There is nothing all wrong! That approach exactly works well. Why do you offer me to change my code with using of adapter? In my approach no need to use adapter? You cause caused me to lose time with changing my entire code..

 

[jmcilhinney]

There is nothing all wrong! That approach exactly works well. Why do you offer me to change my code with using of adapter? In my approach no need to use adapter? You cause caused me to lose time with changing my entire code..

To be frank, because your code is rubbish. If you are using a DataTable then one would assume that you have multiple rows to save. In that case, you would have to either loop through those rows and call ExecuteNonQuery for each one or else do it the proper way and use a data adapter to save the lot in one go.

If you only have one record to save then why do you have a DataTable in the first place? You can use one but why would you, unless there is some specific requirement that you haven't told us about? If you insist on using a DataTable to store a single DataRow then you still have to get that row from the table and use its field values to populate the parameters.

Besides that, changing the code you have to use a data adapter will not involve any more effort than getting it to work in any other fashion and possibly less.

 

[Socarsky]

To be frank, because your code is rubbish.

Can be that so, but It works anyway, I know because I used the approach in another project with success.

If you are using a DataTable then one would assume that you have multiple rows to save.

Only one row can be retrieved from the structure of my project.

In that case, you would have to either loop through those rows and call ExecuteNonQuery for each one or else do it the proper way and use a data adapter to save the lot in one go.

You are right absolutely but as I mentioned that it's only having one row data.

If you only have one record to save then why do you have a DataTable in the first place?

Because there is complexity that I could resolved with DataTable's I combined two different SQL Servers data with my approach.

If you insist on using a DataTable to store a single DataRow then you still have to get that row from the table and use its field values to populate the parameters.

I cannot think about that in the first place that the idea of using a DataTable comes me simplicity. Even as a DataTable and a DataRow in the same glass of water actually.

Besides that, changing the code you have to use a data adapter will not involve any more effort than getting it to work in any other fashion and possibly less.

I saw in your article that DataAdapter used a Select query when it's been assigning first, why? I don't need that and that because it's made me confused not to figured out to use it.

by the way this looks nice for my handling but even I have applied it that still a thrown coming up as : "No mapping exists from object type System.Data.DataColumn to a known managed provider native type."

 

[jmcilhinney]

I saw in your article that DataAdapter used a Select query when it's been assigning first, why? I don't need that and that because it's made me confused not to figured out to use it.

This is a perfect example of why I resist posting code on many occasions. When people see code, they want to read that - or possibly even copy and paste without reading it - and ignore everything else. I specifically stated in that very same post:

I should also point out that you don't actually need the SelectCommand and the FillSchema call either. You can simply build the DataTable schema yourself if you like.

You were only confused because you didn't read the information I provided for you and, in so doing, wasted more of your own time and mine.

Anyway, as I have said, you need to get the DataRow(s) from the DataTable because that's where the data is. Just as in a database the rows contain the data which is described by the columns, so the DataRows contain the data that is described by the DataColumns.

On another note, you're concerned about taking time to change things based on my recommendation and yet you're happy to waste time writing out all your parameters like this:

command.Parameters.Add(new SqlParameter("@sPersonnelCode", System.Data.SqlDbType.VarChar, 3));
// ...
command.Parameters["@sPersonnelCode"].Value = strPersonnelCode;

when you could just do this:

command.Parameters.Add("@sPersonnelCode", SqlDbType.VarChar, 3)).Value = strPersonnelCode;

or, when the data types are appropriate, just this:

command.Parameters.AddWithValue("@nRecordType", 1);

 

[Socarsky]

just this:

command.Parameters.AddWithValue("@nRecordType", 1);

I already tried above but I face with this thrown "No mapping exists from object type System.Data.DataColumn to a known managed provider native type."
So I have to deeply focus to figure out where possibly datatype conflictthat block my way.

jmcilhinney:
Appeciated your helping hand. You really helped me many times here..

 

[Socarsky]

I must first return a DataRow from this method. Actually the idea is easy to do that with below for your suggestion complete.

// copying dt datatable to another datatable to find(Select) exact row of data which desired.
        private DataTable SelectRows(DataTable table1)
        {
            DataTable takas = new DataTable();
            takas.Columns.Add(new DataColumn("Tezgah", typeof(string)));
            takas.Columns.Add(new DataColumn("TezgahAdi", typeof(string)));
            takas.Columns.Add(new DataColumn("OperationCode", typeof(string)));
            takas.Columns.Add(new DataColumn("OperationName", typeof(string)));
            takas.Columns.Add(new DataColumn("ItemCode", typeof(string)));
            takas.Columns.Add(new DataColumn("ItemDescription", typeof(string)));
            takas.Columns.Add(new DataColumn("JobOrder", typeof(string)));
            takas.Columns.Add(new DataColumn("Evrak", typeof(string)));
            takas.Columns.Add(new DataColumn("QTY", typeof(int)));
            takas.Columns.Add(new DataColumn("SONumber", typeof(string)));
            takas.Columns.Add(new DataColumn("SONumberItemCode", typeof(string)));
            takas.Columns.Add(new DataColumn("LatestStarting", typeof(DateTime)));
            takas.Columns.Add(new DataColumn("LatestFinishing", typeof(DateTime)));

            DataRow[] result = table1.Select("Tezgah = '" + splitchar + "'");
            foreach (DataRow row in result) 
            {
                takas.Rows.Add(
                    row["Tezgah"], 
                    row["TezgahAdi"], 
                    row["OperationCode"], 
                    row["OperationName"], 
                    row["ItemCode"], 
                    row["ItemDescription"],
                    row["JobOrder"],
                    row["Evrak"],
                    row["QTY"],
                    row["SONumber"],
                    row["SONumberItemCode"],
                    row["LatestStarting"],
                    row["LatestFinishing"]
                    );
            }
            return takas;
        }

 

[Socarsky]

I just applied this after a couple minutes passed; Now I have a DataRow

        {
            DataTable takas = new DataTable();
            takas.Columns.Add(new DataColumn("Tezgah", typeof(string)));
            .....

            DataRow[] result = table1.Select("Tezgah = '" + splitchar + "'");
            foreach (DataRow row in result) 
            {
                ......
            }
            return takas.Rows[0];
        }

 

[jmcilhinney]

I just applied this after a couple minutes passed; Now I have a DataRow

        {
            DataTable takas = new DataTable();
            takas.Columns.Add(new DataColumn("Tezgah", typeof(string)));
            .....

            DataRow[] result = table1.Select("Tezgah = '" + splitchar + "'");
            foreach (DataRow row in result) 
            {
                ......
            }
            return takas.Rows[0];
        }

What's the extra DataTable for at all? Why not just something like:

DataRow result = table1.Select("Tezgah = '" + splitchar + "'").FirstOrDefault();

That will return the first match or null if there's no match. If there will never be more than one match then use SingleOrDefault. If there will always be at least one match use First. If there will always be exactly one match use Single. If there will always be one or more matches then you can also just index array too:

DataRow result = table1.Select("Tezgah = '" + splitchar + "'")[0];

 

[Socarsky]

What's the extra DataTable for at all? Why not just something like:

DataRow result = table1.Select("Tezgah = '" + splitchar + "'").FirstOrDefault();

That will return the first match or null if there's no match. If there will never be more than one match then use SingleOrDefault. If there will always be at least one match use First. If there will always be exactly one match use Single. If there will always be one or more matches then you can also just index array too:

DataRow result = table1.Select("Tezgah = '" + splitchar + "'")[0];

There will be always a row but with your suggestion I better prefer to use FirstOrDefault() for more benefit because there might be another the same "Tezgah" = "TerminalCode" which might come from Pervasive SQL. Everything starts with Pervasive SQL's data which I retrieve into a DataTable in my project that called dt.

 

[jmcilhinney]

There will be always a row but with your suggestion I better prefer to use FirstOrDefault() for more benefit because there might be another the same "Tezgah" = "TerminalCode" which might come from Pervasive SQL. Everything starts with Pervasive SQL's data which I retrieve into a DataTable in my project that called dt.

If there will always be at least one match then, as I have already said, you would call First. The "OrDefault" part is to allow for Nothing to be returned if there is no item in the list. You've said that there will always be an item in the list so you don't need the "OrDefault". There's never any ambiguity about which of those four methods to call:

Single: there will always be exactly one item in the list.
SingleOrDefault: there will never be more then one item in the list but there may be none.
First: there will always be at least one item in the list and may be more.
FirstOrDefault: there may be zero, one or more items in the list.

 

[Socarsky]

If there will always be at least one match then, as I have already said, you would call First. The "OrDefault" part is to allow for Nothing to be returned if there is no item in the list. You've said that there will always be an item in the list so you don't need the "OrDefault". There's never any ambiguity about which of those four methods to call:

Single: there will always be exactly one item in the list.
SingleOrDefault: there will never be more then one item in the list but there may be none.
First: there will always be at least one item in the list and may be more.
FirstOrDefault: there may be zero, one or more items in the list.

Something that interestingly come out with another approach which is important that in your case there is no need below part.

// copying dt datatable to another datatable to find(Select) exact row of data which desired.
        private DataRow SelectRows(DataTable table1)
        {
            //DataTable takas = new DataTable();
            //takas.Columns.Add(new DataColumn("Tezgah", typeof(string)));
            //takas.Columns.Add(new DataColumn("TezgahAdi", typeof(string)));
            //takas.Columns.Add(new DataColumn("OperationCode", typeof(string)));
            //takas.Columns.Add(new DataColumn("OperationName", typeof(string)));
            //takas.Columns.Add(new DataColumn("ItemCode", typeof(string)));
            //takas.Columns.Add(new DataColumn("ItemDescription", typeof(string)));
            //takas.Columns.Add(new DataColumn("JobOrder", typeof(string)));
            //takas.Columns.Add(new DataColumn("Evrak", typeof(string)));
            //takas.Columns.Add(new DataColumn("QTY", typeof(int)));
            //takas.Columns.Add(new DataColumn("SONumber", typeof(string)));
            //takas.Columns.Add(new DataColumn("SONumberItemCode", typeof(string)));
            //takas.Columns.Add(new DataColumn("LatestStarting", typeof(DateTime)));
            //takas.Columns.Add(new DataColumn("LatestFinishing", typeof(DateTime)));

            DataRow result = table1.Select("Tezgah = '" + splitchar + "'").Single();

            //foreach (DataRow row in result) 
            //{
            //    takas.Rows.Add(
            //        row["Tezgah"], 
            //        row["TezgahAdi"], 
            //        row["OperationCode"], 
            //        row["OperationName"], 
            //        row["ItemCode"], 
            //        row["ItemDescription"],
            //        row["JobOrder"],
            //        row["Evrak"],
            //        row["QTY"],
            //        row["SONumber"],
            //        row["SONumberItemCode"],
            //        row["LatestStarting"],
            //        row["LatestFinishing"]
            //        );
            //}
            return result;
        }

I will remove that part and change one line of code with :

DataRow result = table1.Select("Tezgah = '" + splitchar + "'").Single();

 

[Socarsky]

There is one more interesting thing that just I noticed. It's SQL Provider works with AddWithValue() method, before I used below approach that does not help me to see something in SQL Provider:

command.Parameters.Add(new SqlParameter("@sDatabase", System.Data.SqlDbType.VarChar, 15));                    command.Parameters["@sPersonnelCode"].Value = strPersonnelCode;

After I changed my code with AddWithValue() caused me to get a big benefit to see where problem(s) in my SQL expressions with below:


This is quite good chance to see what's wrong or giving ideas where possible problems.