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)));
                }
            }
        }
 
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.
 
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.
 
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
 
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.
 
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.
 
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.
View attachment 121

Parameters are for inserting values. If you want to call a T-SQL function then it's part of the SQL code, so you put it in the SQL code. Otherwise you're just using the name of the function as text, rather than the result of calling the function.
 
Parameters are for inserting values. If you want to call a T-SQL function then it's part of the SQL code, so you put it in the SQL code. Otherwise you're just using the name of the function as text, rather than the result of calling the function.

I finally achieved to insert data in the table by using paremeters.

Thanks a lot for your helping hand jmcilhinney.

private DateTime dateTimeNow()
        {
            //string format = String.Format("{0:u}", dt);        // "2008-03-09 16:05:07Z"   UniversalSortableDateTime
            //DateTime dtime = DateTime.Now;
            //string sonformat = dtime.ToString(format);            
            //return sonformat;

      
            // record time for the present
            DateTime zamansal = DateTime.Now;
            zamansal.ToString();
            return zamansal;
        }
 
I finally achieved to insert data in the table by using paremeters.

Thanks a lot for your helping hand jmcilhinney.

private DateTime dateTimeNow()
        {
            //string format = String.Format("{0:u}", dt);        // "2008-03-09 16:05:07Z"   UniversalSortableDateTime
            //DateTime dtime = DateTime.Now;
            //string sonformat = dtime.ToString(format);            
            //return sonformat;

      
            // record time for the present
            DateTime zamansal = DateTime.Now;
            zamansal.ToString();
            return zamansal;
        }

That method is completely useless. Get rid of it and replace any calls you're making to it with DateTime.Now. That's all your function is returning anyway, despite the song and dance.
 
That method is completely useless. Get rid of it and replace any calls you're making to it with DateTime.Now. That's all your function is returning anyway, despite the song and dance.
I have already used that and successfully works well. Thank you.
 
Back
Top Bottom