How to look at parameter string

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

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
 
Have you tried to pass in your string lengths exactly as they are instead of
hard-coded values. I have had problems with SQL Server like this before,
maybe something like:

objCmd.parameters.add("@ClientID",SqldbType.VarChar,Session("ClientID").Length).value
=
session("ClientID")
Click to expand...

Alex
 
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.

Dave
 
David Young said:
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.
Click to expand...

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
 
Hi,

You could should look like

Dim CommandText as String = "GetZipCodesFromCity"
Dim objCmd as New SqlCommand(CommandText,objConn)
objCmd.CommandType=StoredProcedure 'check this part, because I do not
remember exact syntax

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



--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Val Mazur (MVP) said:
Hi,

You could should look like

Dim CommandText as String = "GetZipCodesFromCity"
Dim objCmd as New SqlCommand(CommandText,objConn)
objCmd.CommandType=StoredProcedure 'check this part, because I do not
remember exact syntax

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
Click to expand...

I know that.

But this was just an example. My problem is problem solving. If I get an
error as I was getting, I have no way to look at the parameters that I have
added.

When you do a trace, you get all the session variables all the objects etc.

What I was hoping there was - was a way to look at the parameters I have set
up. I can trace the CommandText, but that doesn't tell what is being sent
to the Sql Server (which includes CommandText and the Parameters)

If I were to just set up the CommandText without parameters as a long sting,
I could trace the CommandText and see exactly what is being sent to the Sql
Server. Doesn't seem to be a way to do this with parameters.

Tom
 
tshad,

Based on your following sample:
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
Click to expand...

Change the following:
Dim CommandText as String = "GetZipCodesFromCity"
Click to expand...
You don't need "exec" or your parameters "@StateCode" and "@City" in your
CommandText for a objCmd.CommandType of "StoredProcedure".

If you wanted to do a select statement with parameters, then you'd specify
the parameters in the CommandText and change the
objCmd.CommandType = "Text"
instead of
objCmd.CommandType = "StoredProcedure".

Example:
Dim CommandText as String = "select zipcode from zipcodetable where
statecode=@StateCode and city=@City"
objCmd.CommandType = "Text"

FYI -
If you change to OleDbCommand then you'd be doing the following:
"{ call GetZipCodesFromCity (?, ?) }"
Plus a little more code as well for OleDbxxx functions..
 
Back
Top