I want to see the actual SQL being passed - how?

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

synergy, have you tried running the profiler against your db and see what is trying to be executed vs the db? also, have you tried to step through to where the string is being created? if you have access to sql server mgt studio, just go to tools and select sql server profiler. then you can see the string that is getting executed...hope this helps -- jp|||

Synergyauto:

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

That error is not coming from SQL Server; it is coming from your .NET code. So it's not getting as far as the SQL Server and running Profiler will not be of much assistance to you.

|||nice call tmorton...|||

You can set up a function in the xSqlIB_Inserting function that will give you your insert statement, you can write a quick loop to write out all of your varialbes to trace and see what they are. Set tracing to true on your page, and all the information you want will show up at the bottom in red. Good luck!

ProtectedSub sqlProjectData_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles sqlProjectData.InsertingTry

Trace.Warn(e.Command.CommandText)

Dim xAsInteger = 0DoUntil x = e.Command.Parameters.Count

Trace.Warn(e.Command.Parameters(x).ParameterName)

IfNot (e.Command.Parameters(x).ValueIsNothing)Then

Trace.Warn(e.Command.Parameters(x).Value.ToString)

EndIf

x += 1

LoopCatch exAs ExceptionDim oLoggerAsNew ErrorLogger.clsLogError(ex, Session, Request)

oLogger.LogError()

EndTryEndSub

|||

Unfortunately the page errors before xSqlIB_Inserting is ever called so that could isnt running (and I dont seem to have your ErrorLogger class). Trace function is nifty though and its cool to finally be able to see what the page sees so quickly. Here's a c/p of what the page shows in red. I dont see it showing me the actual db field that is freaking out on though:

Unhandled Execution Error
Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToByte(IFormatProvider provider)
at System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider)
at System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges)
at System.Web.UI.WebControls.Parameter.get_ParameterValue()
at System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control)
at System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values)
at System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback)
at System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation)
at System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
at System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

|||Anyone home? hehe|||synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp|||

jdingo:

synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp

Here's the whole datasource from code, I'm not doing any code behind work on the datasource.

<asp:SqlDataSourceID="xSqlIB"runat="server"ConnectionString="<%$ ConnectionStrings:TestETSAppConnectionString %>"

DeleteCommand="DELETE FROM [xECSIBHeader] WHERE [IBHeaderKey] = @.IBHeaderKey"InsertCommand="INSERT INTO [xECSIBHeader] ([IBNumber], [IBTyp], [IBStatus], [CATCode], [Project], [SeverityCode], [CoverageFeeType], [DateClosed], [AdjusterID], [ClaimNumber], [PolicyNumber], [InsuredName], [Location01], [Location02], [City], [State], [ZipCode], [GrossLoss], [SalesTaxPercent], [SalesTaxAmount], [LossDate], [Supplement], [SupplementExplaination], [MultipleClaim], [ReBilled], [ReBilledIBSubmitted], [OriginalIBNumber], [OriginalIBFee], [RevenueAmtApplied], [ExpenseAmtApplied], [SalesTaxAmtApplied], [CommissionDocBal], [DocBal], [DocumentSelected], [CommissionStatus], [InvoiceStatus], [StatementID], [TaxGroup]) VALUES (@.IBNumber, @.IBTyp, @.IBStatus, @.CATCode, @.Project, @.SeverityCode, @.CoverageFeeType, @.DateClosed, @.AdjusterID, @.ClaimNumber, @.PolicyNumber, @.InsuredName, @.Location01, @.Location02, @.City, @.State, @.ZipCode, @.GrossLoss, @.SalesTaxPercent, @.SalesTaxAmount, @.LossDate, @.Supplement, @.SupplementExplaination, @.MultipleClaim, @.ReBilled, @.ReBilledIBSubmitted, @.OriginalIBNumber, @.OriginalIBFee, @.RevenueAmtApplied, @.ExpenseAmtApplied, @.SalesTaxAmtApplied, @.CommissionDocBal, @.DocBal, @.DocumentSelected, @.CommissionStatus, @.InvoiceStatus, @.StatementID, @.TaxGroup)"SelectCommand="xECS_sp_SelectIBDetails"SelectCommandType="StoredProcedure"UpdateCommand="UPDATE [xECSIBHeader] SET [IBNumber] = @.IBNumber, [IBTyp] = @.IBTyp, [IBStatus] = @.IBStatus, [CATCode] = @.CATCode, [Project] = @.Project, [SeverityCode] = @.SeverityCode, [CoverageFeeType] = @.CoverageFeeType, [DateClosed] = @.DateClosed, [AdjusterID] = @.AdjusterID, [ClaimNumber] = @.ClaimNumber, [PolicyNumber] = @.PolicyNumber, [InsuredName] = @.InsuredName, [Location01] = @.Location01, [Location02] = @.Location02, [City] = @.City, [State] = @.State, [ZipCode] = @.ZipCode, [GrossLoss] = @.GrossLoss, [SalesTaxPercent] = @.SalesTaxPercent, [SalesTaxAmount] = @.SalesTaxAmount, [LossDate] = @.LossDate, [Supplement] = @.Supplement, [SupplementExplaination] = @.SupplementExplaination, [MultipleClaim] = @.MultipleClaim, [ReBilled] = @.ReBilled, [ReBilledIBSubmitted] = @.ReBilledIBSubmitted, [OriginalIBNumber] = @.OriginalIBNumber, [OriginalIBFee] = @.OriginalIBFee, [RevenueAmtApplied] = @.RevenueAmtApplied, [ExpenseAmtApplied] = @.ExpenseAmtApplied, [SalesTaxAmtApplied] = @.SalesTaxAmtApplied, [CommissionDocBal] = @.CommissionDocBal, [DocBal] = @.DocBal, [DocumentSelected] = @.DocumentSelected, [CommissionStatus] = @.CommissionStatus, [InvoiceStatus] = @.InvoiceStatus, [StatementID] = @.StatementID, [TaxGroup] = @.TaxGroup WHERE [IBHeaderKey] = @.IBHeaderKey"><DeleteParameters><asp:ParameterName="IBHeaderKey"Type="Int64"/></DeleteParameters><UpdateParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="1"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/><asp:ParameterName="IBHeaderKey"Type="Int64"/></UpdateParameters><SelectParameters><asp:QueryStringParameterDefaultValue="18R315973"Name="ClaimNumber"QueryStringField="ClaimNumber"Type="String"/></SelectParameters><InsertParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="3"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/></InsertParameters></asp:SqlDataSource>|||

Dang, sorry, guess i hit "post" 3 times too many

________________________________________________________________

Note: duplicate posts were deleted by moderator tmorton

|||synergy, i dont have an exact answer from what i have seen, but if you are getting no where and i know you have lots of fields you are working with, but you could run your insert command trying each field at a time and see which one throws the error in case it is a simple matter some mismatch between and accepted value for a field in the DB and an actual value being passed in as a parameter. I know it is a more brute force approach but thats all i have at the moment. good luck -- jp|||Thanks for hanging with me jdingo, I noticed in the code above that 2 fields were listed as "byte" (dunno how it got that way) but they were really integer fields. So passing them "False" instead of an integer was causing the error. Thanks for the help!|||nice synergy, glad that you found it, many times i solve my own problems just by explaining them and going through them with others. have a good one-- jpsql