Re: How to look at parameter string

"David Young" <REMOVE_THIS.dmy75252@xxxxxxxxx> wrote in message
news:O52RyqFQFHA.2788@xxxxxxxxxxxxxxxxxxxxxxx
> Well, you certainly didn't give us much to work with here, but I'll give
> her
> a shot.
>
> First of all, try this:
> objCmd.parameters.add("@ClientID",SqlDbType.VarChar,20).value =
> session("ClientID").ToString()
>
> Also, you might want to consider instrumenting your code as to allow you
> to
> write out trace and debug information. That would help you run down the
> source of the problem.
>
> Otherwise, post your exact error messages and code snippets and we'll try
> to
> help as best we can.

I know I didn't explain what I was looking for very well.

I am tracing, but the problem I can't trace (at least, I haven't figured out
how) to trace into the SqlCommand which holds the CommandText and the
Parameters.

For example - here is a Stored procedure where I pass 2 parameters (I know I
could tell it that this is a stored procedure - but that isn't my problem).

Dim CommandText as String = "Exec GetZipCodesFromCity @StateCode,@City"
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()
ZipCode.DataSource=objCmd.ExecuteReader

When I run this I might get an error:

in the catch area ex = System.FormatException: Input string was not in a
correct format.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ASP.searchJobs_aspx.Save_Click(Object sendor, EventArgs e) in :line 336

This is from my trace.

But it doesn't tell me anything other than there is a problem with the
string it is putting together to send to Sql.

In this one, it wouldn't be too hard to find the problem, but if I have 40
parameters, it is a little harder to find out which one is causing the
problem.

Is there a way to look at the string that asp.net is trying to build or look
at the parameters in the objcmd object (in my example) in my trace.

Thanks,

Tom
>
> Dave
>
> "tshad" <tscheiderich@xxxxxxxxxxxxxxx> wrote in message
> news:Op3bAdFQFHA.1528@xxxxxxxxxxxxxxxxxxxxxxx
>> I'm not sure if this is an asp.net or ado.net question.
>>
>> I have been using parameters with my Sql commands and have been getting
>> errors that say there is a problem with my input string, but there is no
> way
>> to look at what it is trying to do.
>>
>> I have many parameters such as:
>>
>> objCmd.parameters.add("@ClientID",SqldbType.VarChar,20).value =
>> session("ClientID")
>> objCmd.parameters.add("@Email",SqlDbType.VarChar).value =
> session("Email")
>> objCmd.parameters.add("@SearchName",SqlDbType.VarChar,45).value =
>> SearchName.Text
>>
>> But there doesn't seem to be a way to look at the @Client or @Email type
> of
>> variables to see what it is trying to put together.
>>
>> I get the error when I try to do a:
>>
>> Dim applicantReader = objCmd.ExecuteReader
>>
>> The problem is not coming from Sql Server but from my page. If there was
> a
>> problem with the string being sent to SQL Server, it would show up in the
>> Sql Profiler - which is how I usually find the parameter problems.
>>
>> In this case, it is never getting sent. Is there some way to see what
>> ASP.Net is trying to send?
>>
>> Thanks,
>>
>> tom
>>
>>
>
>


.

Relevant Pages

  • Re: Problems with DAO and SQL Server
    ... String, Long or Date/Time. ... Will look at what is possible in the SQL string ... SQL or using TRIMin your query (if available, not familiar with SQL Server) ... Dim cnn As DAO.Connection ...
    (microsoft.public.excel.programming)
  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Tracking user activities
    ... >> I'd like these settings to persist permanently and the event records to ... > In SQL 2000, this is not very simple to do. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... select Script Trace. ...
    (comp.databases.ms-sqlserver)
  • Re: Datumsformat im SQL String
    ... daß der String im richtigen Format kommt. ... Die von mir immer gepredigte Variante. ... > - das am SQL Server eingestellte Datumsformat ist egal ...
    (microsoft.public.de.sqlserver)
  • Re: C2 SQL auditing
    ... Andrew J. Kelly SQL MVP ... Do you suggest any third party tools for SOX compliance? ... I would like to off load the server side trace to the SOX team ... when I stop SQL server then my trace file gets populated. ...
    (microsoft.public.sqlserver.security)