Parameterized Expression: returns

Showing posts with label returns. Show all posts

Wednesday, March 28, 2012

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

Wednesday, March 21, 2012

parse query

Tuesday, March 20, 2012

Parent and grand parent and great grand parent etc.

Hi,
I have a table with id, parent-id.
I want to know how to write a stored procedure which returns the ids of
parent, grand-parent, great-grand-parent etc.
So, if the structure is like this
id Parent-id
Tree Control ASP.NET Controls
ASP.NET Controls ASP.NET
ASP.NET .NET
.NET VS
VS MS
Office MS
A parent can have more then one child
If given 'Tree Control' This stored procedure returns a record set like
following:
Tree Control
ASP.NET Controls
ASP.NET
.NET
VS
MS
ThanksOthers may have a better solution for this, but I worked out the
following for a previous question:
http://groups.google.com/group/SQL-...94663b43a00f67d
Might give you some insight.
HTH,
Stu|||Get a copy of TREES & HIERATRCHIES IN SQL for better ways to do this.|||I am looking for a simple query (at least for you guru guys out there).
For a given node, it should return the list of all the parents of it's
parent/grand parent. Usually people have examples for children - I am
not looking for children, I am looking for parent.
thanks|||Have a look at
http://toponewithties.blogspot.com/...er.htm
l
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123511813.304233.160130@.o13g2000cwo.googlegroups.com...
>I am looking for a simple query (at least for you guru guys out there).
>
> For a given node, it should return the list of all the parents of it's
> parent/grand parent. Usually people have examples for children - I am
> not looking for children, I am looking for parent.
> thanks
>|||Sorry, anything else? I don't want to get into prime number.
I am just looking for a simple query/stored procedure.
thanks|||OK, What about this one.
http://www.windowsitpro.com/SQLServ...es.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123522959.424109.230440@.g49g2000cwa.googlegroups.com...
> Sorry, anything else? I don't want to get into prime number.
> I am just looking for a simple query/stored procedure.
> thanks
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Good design is crucial in these cases - you must never allow for circular
references.
ML

Wednesday, March 7, 2012

Parameters changing in PageHeader

Hi Everyone.
I am working on a report that returns information on multiple people.
I would like the name of these people to be shown on every page.
I know we can't have fields in the PageHeader so i have created a Report
Parameter with no prompt and linked it via the default value section on the
report parameter screen to the fullname field in my datasource.
I added a text box which has the assignment of:
=Parameters!FullName.Value
My problem is that for every person returned in my report i have the same
name appearing in the PageHeader. so...
Is there a way to have this parameter dynamically changing depending on what
persons i have in my report? could i have possibly linked the field and the
parameter incorrectly?
--
Thanks in advance,
Dave HuntYou can group your report by =Fields!FullName.Value and place the name field
in the group header or you can place your report into a list box and group
your list by =Fields!FullName.Value and drop the name field in the list area.
U. Tokklas
"DustpanDave" wrote:
> Hi Everyone.
> I am working on a report that returns information on multiple people.
> I would like the name of these people to be shown on every page.
> I know we can't have fields in the PageHeader so i have created a Report
> Parameter with no prompt and linked it via the default value section on the
> report parameter screen to the fullname field in my datasource.
> I added a text box which has the assignment of:
> =Parameters!FullName.Value
> My problem is that for every person returned in my report i have the same
> name appearing in the PageHeader. so...
> Is there a way to have this parameter dynamically changing depending on what
> persons i have in my report? could i have possibly linked the field and the
> parameter incorrectly?
> --
> Thanks in advance,
> Dave Hunt|||In addition to what Tokklas said, you can remove the report parameter for
the FullName. Instead, you will have the FullName placed in a group header
textbox (e.g. with the textbox called "TextboxFullName"). Then in the page
header, you can just another textbox with the following expression:
=ReportItems!TextboxFullName.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tokklas" <Tokklas@.discussions.microsoft.com> wrote in message
news:8BE101C1-F7E7-42CD-B6C2-74203A61CA9D@.microsoft.com...
> You can group your report by =Fields!FullName.Value and place the name
> field
> in the group header or you can place your report into a list box and group
> your list by =Fields!FullName.Value and drop the name field in the list
> area.
>
> --
> U. Tokklas
>
> "DustpanDave" wrote:
>> Hi Everyone.
>> I am working on a report that returns information on multiple people.
>> I would like the name of these people to be shown on every page.
>> I know we can't have fields in the PageHeader so i have created a Report
>> Parameter with no prompt and linked it via the default value section on
>> the
>> report parameter screen to the fullname field in my datasource.
>> I added a text box which has the assignment of:
>> =Parameters!FullName.Value
>> My problem is that for every person returned in my report i have the same
>> name appearing in the PageHeader. so...
>> Is there a way to have this parameter dynamically changing depending on
>> what
>> persons i have in my report? could i have possibly linked the field and
>> the
>> parameter incorrectly?
>> --
>> Thanks in advance,
>> Dave Hunt|||So Guys...
I have a number of tables in a listbox.
Each table has its own datasource.
One of these datasources returns a fullname.
What is the process to assign a group or get an expression into a group
header?
is it a proerty somewhere?
What exactly would i need to do to get my name repeating on each page and
change when a new record starts?
Do you know if in the future it will be posible to have fields in the page
header?
I appreciate your help very much guys! thanks again
--
Thanks in advance,
Dave Hunt
"Robert Bruckner [MSFT]" wrote:
> In addition to what Tokklas said, you can remove the report parameter for
> the FullName. Instead, you will have the FullName placed in a group header
> textbox (e.g. with the textbox called "TextboxFullName"). Then in the page
> header, you can just another textbox with the following expression:
> =ReportItems!TextboxFullName.Value
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tokklas" <Tokklas@.discussions.microsoft.com> wrote in message
> news:8BE101C1-F7E7-42CD-B6C2-74203A61CA9D@.microsoft.com...
> > You can group your report by =Fields!FullName.Value and place the name
> > field
> > in the group header or you can place your report into a list box and group
> > your list by =Fields!FullName.Value and drop the name field in the list
> > area.
> >
> >
> > --
> > U. Tokklas
> >
> >
> > "DustpanDave" wrote:
> >
> >> Hi Everyone.
> >>
> >> I am working on a report that returns information on multiple people.
> >> I would like the name of these people to be shown on every page.
> >>
> >> I know we can't have fields in the PageHeader so i have created a Report
> >> Parameter with no prompt and linked it via the default value section on
> >> the
> >> report parameter screen to the fullname field in my datasource.
> >> I added a text box which has the assignment of:
> >> =Parameters!FullName.Value
> >>
> >> My problem is that for every person returned in my report i have the same
> >> name appearing in the PageHeader. so...
> >> Is there a way to have this parameter dynamically changing depending on
> >> what
> >> persons i have in my report? could i have possibly linked the field and
> >> the
> >> parameter incorrectly?
> >> --
> >> Thanks in advance,
> >> Dave Hunt
>
>

Monday, February 20, 2012

Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.

Wednesday, March 28, 2012

parse query

parse query

Wednesday, March 21, 2012

parse query

Tuesday, March 20, 2012

Parent and grand parent and great grand parent etc.

Wednesday, March 7, 2012

Parameters changing in PageHeader

Monday, February 20, 2012

Parameterized query returns one row with null values.

Parameterized Expression

Blog Archive

About Me