convert parameter value from a DataColumn to a String?

Socarsky

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)));
                }
            }
        }
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Socarsky said:
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.
Click to expand...

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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
jmcilhinney said:
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.
Click to expand...
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.
 
Upvote 0 Downvote
Socarsky said:
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.
Click to expand...

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.
 
Upvote 0 Downvote
Socarsky said:
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
Click to expand...

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.
 
Upvote 0 Downvote
jmcilhinney said:
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.
Click to expand...

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;
        }
 
Upvote 0 Downvote
Socarsky said:
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;
        }
Click to expand...

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.
 
Upvote 0 Downvote
jmcilhinney said:
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.
Click to expand...
I have already used that and successfully works well. Thank you.
 
Upvote 0 Downvote

Share this page

Latest posts

Back
Top Bottom