Exception "Object must implement IConvertible." during parameterized query

[Guest]

Folks

I'm getting a fatal exception thrown when I try to run a parameterized insert query from a SqlDataAdapter. The table I'm appending records to is not the same table that populated the dataset in the first place. In my SQL database, ServiceID and Amount are int 4 and money 8 datafield types, respectively. In my dataset's datatable, the Service column is of type long and Balance is of type decimal.

Nothing shows up in SQL Profiler, so I'm assuming the code is crashing before it even hits the server. Does anybody have any ideas what might be causing this? If so, thank you, thank you, thank you

The SQL statement, pulled from my VS.NET watch list, is as follows: "INSERT INTO tblSupportServiceTransactions (ServiceID, Date, TransactionID, PaymentType, Memo, Amount, Updated, PostedToGL, PostingBatchID, PostingBatchClosed, AccountKey, Received, PaymentCommissionCalculated), Values (@ServiceID, '2/24/2004', 'Regular Payment', 'Web Ccard', 'web visa', @Balance, 0, 0, 14664, 0, 14628, 1, 0);

Exception stack trace is as follows: (reads Greek to me

StackTrace " at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at ClientAccess2.PayOnlineConfirm.PostToBatch() in c:\\inetpub\\wwwroot\\clientaccess2\\payonlineconfirm.aspx.cs:line 400" strin

My Code snippet is as follows

sSQL = "INSERT INTO tblSupportServiceTransactions (ServiceID, Date, TransactionID, PaymentType, Memo, Amount, Updated, PostedToGL, PostingBatchID, PostingBatchClosed, AccountKey, Received, PaymentCommissionCalculated), Values (@ServiceID, '" + DateTime.Now.ToShortDateString() + "', 'Regular Payment', 'Web Ccard', 'web visa', @Balance, 0, 0, " + iPostingBatchID + ", 0, " + sAccountKey + ", 1, 0);"

cmd = cn.CreateCommand()
cmd.CommandText = sSQL
cmd.Parameters.Add("@ServiceID", SqlDbType.Int, 4, "ServiceID")
cmd.Parameters.Add("@Balance", SqlDbType.Money, 8, "Balance")
cmd.Parameters["@ServiceID"].Value = CustScreen.dsMyData.Balance.Columns["ServiceID"]
cmd.Parameters["@Balance"].Value = CustScreen.dsMyData.Balance.Columns["Balance"]
cmd.ExecuteNonQuery()

 

[Guest]

Hi

There might to two mistakes you may have done..
1. i received a similar error. i discovered that i was trying to force a base64binary into an int. once i changed the value of the sp to accept a 'timestamp' rather than an 'int' i was able to move passed the error

2. I had this problem and discovered that I had not used the [text] property of a textbox.
I was trying to pass the entire object which is why this error might ocur

Check these two options and revert back if u still facing this proble

Regard
Mahesh ChandraMouli[.NET MVP]

 

[Guest]

I'm still having trouble with this; I suspect my problem lies in the following lines of code

cmd.Parameters["@ServiceID"].Value = CustScreen.dsMyData.Balance.Columns["ServiceID"]
cmd.Parameters["@Balance"].Value = CustScreen.dsMyData.Balance.Columns["Balance"]

I don't think the .Value object is matching well to my strongly typed dataset CustScreen.dsMyData.Balance.Columns.#ColumnName#

If, possible, I'd like to bind the SqlCommand parameter to a column on the dataset. The dataset isn't changing at this point, I just need to take the field values of those two columns and use them to insert records into a SQL table

Thanks! Andre