|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to look at parameter stringI 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") AlexShow quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... > 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 > 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 Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... > 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 > >
Show quote
Hide quote
"David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message I know I didn't explain what I was looking for very well.news:O52RyqFQFHA.2788@TK2MSFTNGP09.phx.gbl... > 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 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 Show quoteHide quote > > Dave > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... >> 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 >> >> > > 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 Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:eoSJxHGQFHA.4004@TK2MSFTNGP15.phx.gbl... > "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message > news:O52RyqFQFHA.2788@TK2MSFTNGP09.phx.gbl... >> 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" <tscheider***@ftsolutions.com> wrote in message >> news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... >>> 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 >>> >>> >> >> > >
Show quote
Hide quote
"Val Mazur (MVP)" <group***@hotmail.com> wrote in message I know that.news:urZuSpIQFHA.2252@TK2MSFTNGP15.phx.gbl... > 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 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 Show quoteHide quote > > > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:eoSJxHGQFHA.4004@TK2MSFTNGP15.phx.gbl... > > "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message > > news:O52RyqFQFHA.2788@TK2MSFTNGP09.phx.gbl... > >> 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" <tscheider***@ftsolutions.com> wrote in message > >> news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... > >>> 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 > >>> > >>> > >> > >> > > > > > > tshad,
Based on your following sample: > Dim CommandText as String = "Exec GetZipCodesFromCity @StateCode,@City" Change the following:> 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 > > Dim CommandText as String = "GetZipCodesFromCity" 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.. Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:eoSJxHGQFHA.4004@TK2MSFTNGP15.phx.gbl... > "David Young" <REMOVE_THIS.dmy75***@yahoo.com> wrote in message > news:O52RyqFQFHA.2788@TK2MSFTNGP09.phx.gbl... >> 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" <tscheider***@ftsolutions.com> wrote in message >> news:Op3bAdFQFHA.1528@TK2MSFTNGP09.phx.gbl... >>> 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 >>> >>> >> >> > >  
Other interesting topics
disconnected typed dataset
DataAdapter Update Does Nothing ADO error "There is already an open DataReader associated with this Connection" SQLHelper.ExecuteReader - Connection Close Can't Read Excel File (OleDb) w/ ASP.NET Impersonation Filling multiple tables Importing Excel data to Access Oracle Data Providers (Connection Pooling & Transactions) Simple asynchronous method Deleting from a dataview |
|||||||||||||||||||||||
