convert parameter value from a DataColumn to a String?

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
This is where I want to insert in MS SQL
xxx.jpg

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

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
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

C#:
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.
 

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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."
Clipboard01.jpg
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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..
 
Last edited:

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
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:
Untitled.png

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

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
Another thing that I found recently is SQL Expression with DataRow, here is an screenshot show that:
another.png
We must use always Columns names in usage of DataRow with SQL Expression.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
Another thing that I found recently is SQL Expression with DataRow, here is an screenshot show that:
View attachment 120
We must use always Columns names in usage of DataRow with SQL Expression.

Um, no. Either name or ordinal will do equally well. The issue with that code is not the indexes but the fact that you're using string concatenation to insert literal values into SQL code instead of using parameters.
 

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
I think the only matter that is seeing now, this is : getdatetimeForRecord
I need to pass a proper DataTime into SQL.. I'll work on it.

private void MSSQLInsert()
        {
            // below string can use directly in sql expression without ' (quote-mark) like this ->  " + getdatetimeForRecord + "
            string getdatetimeForRecord = "CURRENT_TIMESTAMP";

            string ConnStrMSSQL = "Data Source=xxxxxx;Initial Catalog=xxxxx;Persist Security Info=True;User ID=xx;Password=xxxxx;";
            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.AddWithValue("@sPersonnelCode", strPersonnelCode);
                    command.Parameters.AddWithValue("@sTerminalCode", equalDataRow["Tezgah"]);
                    command.Parameters.AddWithValue("@sOperationCode", equalDataRow["OperationCode"]);
                    command.Parameters.AddWithValue("@sItemCode", equalDataRow["ItemCode"]);
                    command.Parameters.AddWithValue("@sItemDescription", equalDataRow["ItemDescription"]);
                    command.Parameters.AddWithValue("@nJobOrder", equalDataRow["JobOrder"]);
                    command.Parameters.AddWithValue("@nMPS", equalDataRow["Evrak"]);
                    command.Parameters.AddWithValue("@nRecordType", 1);
                    command.Parameters.AddWithValue("@nQTY", 1);
                    command.Parameters.AddWithValue("@dStartTime", getdatetimeForRecord);
                    command.Parameters.AddWithValue("@dFinishTime", getdatetimeForRecord);
                    command.Parameters.AddWithValue("@sSONumber", equalDataRow["SONumber"]);
                    command.Parameters.AddWithValue("@sSONumberItemCode", equalDataRow["SONumberItemCode"]);
                    command.Parameters.AddWithValue("@dLatestStarting", equalDataRow["LatestStarting"]);
                    command.Parameters.AddWithValue("@dLatestFinishing", equalDataRow["LatestFinishing"]);
                    command.Parameters.AddWithValue("@sComputerName", machineName);
                    command.Parameters.AddWithValue("@sDatabase", getDBname);

                    command.ExecuteNonQuery();


getdatetimeForRecord's CURRENT_TIMESTAMP goes to SQL with quotes then that causes issue.. here is the prove.
a-1.png
 

Socarsky

Well-known member
Joined
Mar 3, 2014
Messages
59
Programming Experience
Beginner
Um, no. Either name or ordinal will do equally well. The issue with that code is not the indexes but the fact that you're using string concatenation to insert literal values into SQL code instead of using parameters.
I believe that specify that matter with seeing a DataRow's role passes DataColumn(s) data by raw. There is no skip event you specify the code with jumping its colomn's order. But after I used colomn's name by passing their names with string then I achieve right target that means I provide to get right columns. So I claim this so. Even It comes into ears strange.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,278
Location
Sydney, Australia
Programming Experience
10+
I believe that specify that matter with seeing a DataRow's role passes DataColumn(s) data by raw. There is no skip event you specify the code with jumping its colomn's order. But after I used colomn's name by passing their names with string then I achieve right target that means I provide to get right columns. So I claim this so. Even It comes into ears strange.

If it didn't work for you it's because you did it wrong, not because it doesn't work. You most likely used the wrong column indexes.
 
Top Bottom