SQL statement problems

Having problems with our SELECT statements in both Webforms and 
client/server application that I wasn't having in the various demo versions.

I am using "SNC" (SQL Native Client installed) connection.

Connected to DB with transaction object called "SQLCA2".

This code for debugging purposes works:
select count(*)
into :ll
from users
using SQLCA2;

This actual code does NOT work:
SELECT count (*)
INTO :l_i_count
FROM users
WHERE user_id = :a_s_user_id
using sqlca2;

From debugger:
sqlcode=0
sqldbcode=0
sqlerrtext=""

Any ideas would be appreciated... 


0
bjo
6/22/2007 7:10:26 PM
sybase.net.webform 1327 articles. 0 followers. Follow

6 Replies
420 Views

Similar Articles

[PageSpeed] 45

Do you mean that you think it doesn't work because :l_i_count is zero?
If that is what you mean, note that you will get a zero as your count if it 
didn't find matches, and your sqlcode would be zero rather than 100.   This is 
correct.

If that isn't what you mean, you should clarify.




"bjo" <bryan.olesen@bradleycompany.com> wrote in message 
news:467c1ea2$1@forums-1-dub...
> Having problems with our SELECT statements in both Webforms and client/server 
> application that I wasn't having in the various demo versions.
>
> I am using "SNC" (SQL Native Client installed) connection.
>
> Connected to DB with transaction object called "SQLCA2".
>
> This code for debugging purposes works:
> select count(*)
> into :ll
> from users
> using SQLCA2;
>
> This actual code does NOT work:
> SELECT count (*)
> INTO :l_i_count
> FROM users
> WHERE user_id = :a_s_user_id
> using sqlca2;
>
> From debugger:
> sqlcode=0
> sqldbcode=0
> sqlerrtext=""
>
> Any ideas would be appreciated...
> 


0
M
6/25/2007 6:59:44 PM
No, I know for a fact that l_i_count should equal 1 because the user_id DOES 
exist. I see it in the database.

It seems whereever there is a variable in a sql select, it doesn't return 
any values.

A simple count(*) on a table works, and if I hard-code something for 
debuggiing purposes like "select rev_code from item where item_id = 'ABCD'" 
it works, but not with any variables like :a_s_user_id in my original 
example post.

Thanks...

"M. Searer" <nospam@nospam.com> wrote in message 
news:468010a0$1@forums-1-dub...
> Do you mean that you think it doesn't work because :l_i_count is zero?
> If that is what you mean, note that you will get a zero as your count if 
> it didn't find matches, and your sqlcode would be zero rather than 100. 
> This is correct.
>
> If that isn't what you mean, you should clarify.
>
>
>
>
> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
> news:467c1ea2$1@forums-1-dub...
>> Having problems with our SELECT statements in both Webforms and 
>> client/server application that I wasn't having in the various demo 
>> versions.
>>
>> I am using "SNC" (SQL Native Client installed) connection.
>>
>> Connected to DB with transaction object called "SQLCA2".
>>
>> This code for debugging purposes works:
>> select count(*)
>> into :ll
>> from users
>> using SQLCA2;
>>
>> This actual code does NOT work:
>> SELECT count (*)
>> INTO :l_i_count
>> FROM users
>> WHERE user_id = :a_s_user_id
>> using sqlca2;
>>
>> From debugger:
>> sqlcode=0
>> sqldbcode=0
>> sqlerrtext=""
>>
>> Any ideas would be appreciated...
>>
>
> 


0
bjo
6/25/2007 7:35:04 PM
Are you mixing datatypes?  How are user_id and :a_s_user_id defined?

-- 
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://www.pb9books.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"bjo" <bryan.olesen@bradleycompany.com> wrote in message 
news:468018e8$1@forums-1-dub...
> No, I know for a fact that l_i_count should equal 1 because the user_id 
> DOES exist. I see it in the database.
>
> It seems whereever there is a variable in a sql select, it doesn't return 
> any values.
>
> A simple count(*) on a table works, and if I hard-code something for 
> debuggiing purposes like "select rev_code from item where item_id = 
> 'ABCD'" it works, but not with any variables like :a_s_user_id in my 
> original example post.
>
> Thanks...
>
> "M. Searer" <nospam@nospam.com> wrote in message 
> news:468010a0$1@forums-1-dub...
>> Do you mean that you think it doesn't work because :l_i_count is zero?
>> If that is what you mean, note that you will get a zero as your count if 
>> it didn't find matches, and your sqlcode would be zero rather than 100. 
>> This is correct.
>>
>> If that isn't what you mean, you should clarify.
>>
>>
>>
>>
>> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
>> news:467c1ea2$1@forums-1-dub...
>>> Having problems with our SELECT statements in both Webforms and 
>>> client/server application that I wasn't having in the various demo 
>>> versions.
>>>
>>> I am using "SNC" (SQL Native Client installed) connection.
>>>
>>> Connected to DB with transaction object called "SQLCA2".
>>>
>>> This code for debugging purposes works:
>>> select count(*)
>>> into :ll
>>> from users
>>> using SQLCA2;
>>>
>>> This actual code does NOT work:
>>> SELECT count (*)
>>> INTO :l_i_count
>>> FROM users
>>> WHERE user_id = :a_s_user_id
>>> using sqlca2;
>>>
>>> From debugger:
>>> sqlcode=0
>>> sqldbcode=0
>>> sqlerrtext=""
>>>
>>> Any ideas would be appreciated...
>>>
>>
>>
>
> 


0
Terry
6/25/2007 10:51:56 PM
When I have problems like this, I use the microsoft profiler tool and watch 
exactly what is being sent up to the database.


"bjo" <bryan.olesen@bradleycompany.com> wrote in message 
news:468018e8$1@forums-1-dub...
> No, I know for a fact that l_i_count should equal 1 because the user_id DOES 
> exist. I see it in the database.
>
> It seems whereever there is a variable in a sql select, it doesn't return any 
> values.
>
> A simple count(*) on a table works, and if I hard-code something for 
> debuggiing purposes like "select rev_code from item where item_id = 'ABCD'" it 
> works, but not with any variables like :a_s_user_id in my original example 
> post.
>
> Thanks...
>
> "M. Searer" <nospam@nospam.com> wrote in message 
> news:468010a0$1@forums-1-dub...
>> Do you mean that you think it doesn't work because :l_i_count is zero?
>> If that is what you mean, note that you will get a zero as your count if it 
>> didn't find matches, and your sqlcode would be zero rather than 100. This is 
>> correct.
>>
>> If that isn't what you mean, you should clarify.
>>
>>
>>
>>
>> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
>> news:467c1ea2$1@forums-1-dub...
>>> Having problems with our SELECT statements in both Webforms and 
>>> client/server application that I wasn't having in the various demo versions.
>>>
>>> I am using "SNC" (SQL Native Client installed) connection.
>>>
>>> Connected to DB with transaction object called "SQLCA2".
>>>
>>> This code for debugging purposes works:
>>> select count(*)
>>> into :ll
>>> from users
>>> using SQLCA2;
>>>
>>> This actual code does NOT work:
>>> SELECT count (*)
>>> INTO :l_i_count
>>> FROM users
>>> WHERE user_id = :a_s_user_id
>>> using sqlca2;
>>>
>>> From debugger:
>>> sqlcode=0
>>> sqldbcode=0
>>> sqlerrtext=""
>>>
>>> Any ideas would be appreciated...
>>>
>>
>>
>
> 


0
M
6/26/2007 2:04:11 AM
Not mixing datatypes. This code has worked for 8 years.

Thanks.

"Terry Dykstra" <tddykstra@forestoil.ca> wrote in message 
news:4680470c$1@forums-1-dub...
> Are you mixing datatypes?  How are user_id and :a_s_user_id defined?
>
> -- 
> Terry Dykstra (TeamSybase)
> http://powerbuilder.codeXchange.sybase.com/
> http://www.pb9books.com
> product enhancement requests:
> http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
> news:468018e8$1@forums-1-dub...
>> No, I know for a fact that l_i_count should equal 1 because the user_id 
>> DOES exist. I see it in the database.
>>
>> It seems whereever there is a variable in a sql select, it doesn't return 
>> any values.
>>
>> A simple count(*) on a table works, and if I hard-code something for 
>> debuggiing purposes like "select rev_code from item where item_id = 
>> 'ABCD'" it works, but not with any variables like :a_s_user_id in my 
>> original example post.
>>
>> Thanks...
>>
>> "M. Searer" <nospam@nospam.com> wrote in message 
>> news:468010a0$1@forums-1-dub...
>>> Do you mean that you think it doesn't work because :l_i_count is zero?
>>> If that is what you mean, note that you will get a zero as your count if 
>>> it didn't find matches, and your sqlcode would be zero rather than 100. 
>>> This is correct.
>>>
>>> If that isn't what you mean, you should clarify.
>>>
>>>
>>>
>>>
>>> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
>>> news:467c1ea2$1@forums-1-dub...
>>>> Having problems with our SELECT statements in both Webforms and 
>>>> client/server application that I wasn't having in the various demo 
>>>> versions.
>>>>
>>>> I am using "SNC" (SQL Native Client installed) connection.
>>>>
>>>> Connected to DB with transaction object called "SQLCA2".
>>>>
>>>> This code for debugging purposes works:
>>>> select count(*)
>>>> into :ll
>>>> from users
>>>> using SQLCA2;
>>>>
>>>> This actual code does NOT work:
>>>> SELECT count (*)
>>>> INTO :l_i_count
>>>> FROM users
>>>> WHERE user_id = :a_s_user_id
>>>> using sqlca2;
>>>>
>>>> From debugger:
>>>> sqlcode=0
>>>> sqldbcode=0
>>>> sqlerrtext=""
>>>>
>>>> Any ideas would be appreciated...
>>>>
>>>
>>>
>>
>>
>
> 


0
bjo
6/26/2007 3:27:21 PM
And of course you can turn on DB Tracing in PowerBuilder to see what
is happening as well. 

Regards,
Dave Fish
Sybase

On 25 Jun 2007 19:04:11 -0700, "M. Searer" <nospam@nospam.com> wrote:

>When I have problems like this, I use the microsoft profiler tool and watch 
>exactly what is being sent up to the database.
>
>
>"bjo" <bryan.olesen@bradleycompany.com> wrote in message 
>news:468018e8$1@forums-1-dub...
>> No, I know for a fact that l_i_count should equal 1 because the user_id DOES 
>> exist. I see it in the database.
>>
>> It seems whereever there is a variable in a sql select, it doesn't return any 
>> values.
>>
>> A simple count(*) on a table works, and if I hard-code something for 
>> debuggiing purposes like "select rev_code from item where item_id = 'ABCD'" it 
>> works, but not with any variables like :a_s_user_id in my original example 
>> post.
>>
>> Thanks...
>>
>> "M. Searer" <nospam@nospam.com> wrote in message 
>> news:468010a0$1@forums-1-dub...
>>> Do you mean that you think it doesn't work because :l_i_count is zero?
>>> If that is what you mean, note that you will get a zero as your count if it 
>>> didn't find matches, and your sqlcode would be zero rather than 100. This is 
>>> correct.
>>>
>>> If that isn't what you mean, you should clarify.
>>>
>>>
>>>
>>>
>>> "bjo" <bryan.olesen@bradleycompany.com> wrote in message 
>>> news:467c1ea2$1@forums-1-dub...
>>>> Having problems with our SELECT statements in both Webforms and 
>>>> client/server application that I wasn't having in the various demo versions.
>>>>
>>>> I am using "SNC" (SQL Native Client installed) connection.
>>>>
>>>> Connected to DB with transaction object called "SQLCA2".
>>>>
>>>> This code for debugging purposes works:
>>>> select count(*)
>>>> into :ll
>>>> from users
>>>> using SQLCA2;
>>>>
>>>> This actual code does NOT work:
>>>> SELECT count (*)
>>>> INTO :l_i_count
>>>> FROM users
>>>> WHERE user_id = :a_s_user_id
>>>> using sqlca2;
>>>>
>>>> From debugger:
>>>> sqlcode=0
>>>> sqldbcode=0
>>>> sqlerrtext=""
>>>>
>>>> Any ideas would be appreciated...
>>>>
>>>
>>>
>>
>> 
>
0
Dave
6/27/2007 7:51:08 AM
Reply:

Similar Artilces:

Strange problem: SQL insert statement does not insert all the fields into table from Asp.net C# webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C#...

Problem with analytic sql function (The OVER SQL construct or statement is not supported)
Hi All!  Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.” Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter w...

SQL Statement Problem
Hello all, Let's see if we have some pro's out here or not. This could be simple actually, but I don't know how... I have two tables, "Projects", and "Tasks." Now what I am trying to do is list the projects on a page. And I have managed to do that nicely. And, while it's looking, I actually have it open up another recordset inside of that one (which is looping) and have it lookup the tasks status percentage. So, when looking at one record, it looks like this: Projects - ID Title Status 1 Sam...

SQL Statement problem
I am trying to pull certain information from a database by using the ISQL window, but Powerbuilder keeps locking up when I type the following code, could someone please show me what I'm doing wrong (assuming that all of the spelling is correct)? Thanks!!! SELECT t0.code, t0.billcode FROM billcode t0 WHERE t0.code in (SELECT distinct t1.code FROM app_charges_old t1 WHERE t1.invoice_number IN (SELECT t2.invoice_number FROM invoice_summary_old t2 WHER...

A problem about SQL Statement
Hello, I've got a problem about a Select statement with the following criteria: Data Table Key Num1 Num2 A 1 2 A 1 2 A 2 3 B 0 0 B 1 1 C 2 1 C 1 2 I would like to select all row of a key if the (sum of num1 - sum of num2) with that key != 0 In the above case, the outcome would be: A 1 2 A 1 2 A 2 3 Could any body advise what the SQL should be? Many thanks!   Hi,newbie Try Like This I have shown the select query in bold   CReate table Test_Sql ([Key] varchar(20),[Num1] int, [Num2] i...

SQL Statement problem
Can someone please tell me what is wrong with my SQL below..I think I am missing a quote somewhere... "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & " and highkv ='" & VoltageString & "' " StationNameString single quotes are not endedSushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb Thank you..that was the problem. Final statement = "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & "' and highkv...

Problem with IF statement in SQL
 Can someone please tell me what I'm doing wrong here:  CREATE PROCEDURE dbo.AddProductToEnquiry @SessionID int, @ProductID int AS DECLARE @CountProductInEnquiry int SET @CountProductInEnquiry = (SELECT Count(*) FROM Enquiry_Items WHERE SessionID=@SessionID AND ProductID=@ProductID) IF @CountProductInEnquiry = 0 THEN INSERT INTO Enquiry_Items ([SessionID], [ProductID], [Date]) VALUES (@SessionID, @ProductID, GETDATE()) END IF   Cheers,Curt Regards, Curt  Actually I think Ive fixed it. Can someone please conf...

Problems with SQL statement
Hello all I have the below SQL statement querying my access database. To me the code looks fine and I can't see what’s wrong with it, but when I execute it I get the syntax error message at the bottom of this post. The code is supposed to take input text from a search field and use it to search selected fields in the database. It then returns these where there are matches and where the quantity is = 1. I'm sure the problem is something really simple but all help would be appreciated. The SQL query: ("Select ProductID, ProductName, " _ & "BriefDescription, ...

SQL-Statement problem
Dear all i am having a problem with SQL-Statement-1.005, whereas SQL-Statement-0.1020 is working fine. SQL ERROR: Mismatched parentheses in WHERE clause! SV = RV(0x810cad0) at 0x829105c REFCNT = 1 FLAGS = (PADBUSY,PADMY,ROK) RV = 0x83844e4 DBD::CSV::st execute failed: dbih_getcom handle 'DBD::CSV::Statement=HASH(0x83844e4)' is not a DBI handle (has no magic) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. I think i tracked this down to Parser.pm the cleansql routine which skips closing parens, but not the corresponding openings: wa...

Problem with sql statement
Hi all I've got some trouble with a sql statement. What I have got: 4 Tables 1. tFuel------------------------FuelId | FuelDescription ... 1 | Diesel  2 | SomeOtherKindOfFuel 2. tFuelPrice------------------------GasStationId | FuelId | Price ...1 | 1 | 1.451 | 2 | 1.30 3. tGasStation------------------------GasStationId | GasStationGroupId | Name ...1 | 2 | Millers Gas Station |2 | 1 | Chucky's Gas Station | 4. tGasStationGroup------------------------GasStationGroupId | Name ...1 | BP2 | Shell So, what I want to achive is the following I'd like to show the user a list of all...

Webform submission problems: Submit Problems: Form Not Writing Back to the SQL Database
Greetings One and All,I am very new to asp.net and I am using Web Matrix for my project.I have all the of the form and database components finished.The trouble I am having is submitting the user inputted data on the form to the database.I am able to use the insert statement within SQL Query Analyzer (substituting the @varibles with actual data) and the data is being inserted just fine.When I click on click Submit button within the webform, nothing gets posted to the database.Below is the html code (in blue) and visual basic code (in red) for the submit button and ins...

SQL::Statement problem
I posted a similar message to Jeff directly but I never got a confirmation that he actually received. So let's try here... Firstly, I've found out that RLIKE is broken: sub is_matched { ... if ($op =~ /LIKE|CLIKE/i) { $val2 = quotemeta($val2); $val2 =~ s/\\%/.*/g; $val2 =~ s/_/./g; } If $op i s'RLIKE', and since 'RLIKE' contains the string 'LIKE', the pattern is run through quotemeta(). Also, the SQL operators IS and the LIKE family are case-sensitive, which is contrary to SQL specs if I'm not mista...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

Web resources about - SQL statement problems - sybase.net.webform

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

"Welcome to our home. Please take a moment to review our latest statement regarding so-called micro-aggressions..." ...
"... together with a revised list of 'trigger warning' requests. Kindly commit the following to memory...." Trigger warning for Althouse readers: ...

Liveblogging World War II: Robert H. Jackson: Opening Statement before the International Military Tribunal ...
**Robert H. Jackson**: [Opening Statement before the International Military Tribunal](https://www.roberthjackson.org/speech-and-writing/open ...

Protective case turns your sleek iPhone into a loud fashion statement
... iDevice cases are loud and rugged. You might even call them masculine, although I know a few of my female friends would embrace the bold statement ...

Election 2016 News Today Dominated by Media Furor over These Trump Statements
Here is today’s top election 2016 news: The media attacked GOP presidential front-runner Donald Trump this week for saying thousands of New Jersey ...

President Obama delivers a statement on national security
x YouTube Video Wednesday, Nov 25, 2015 · 5:08:06 PM +00:00 · Laura Clawson We’ll have a more in-depth account coming soon, but the summary ...

Peter Mukerjea arrested for murder: Here's the contradictory statement of the tycoon in the Sheena Bora ...
Peter Mukerjea made many contradictory statements in relation to the Sheena Bora murder case.

Osborne's In Trouble, Chancellor Faces Terrible Public Borrowing Figures Ahead Of Autumn Statement
This doesn't look good for George Osborne, although it has to be said that it couldn't happen to a nicer person. Next week sees the Autumn Statement, ...

New Statement Said to Identify Gunmen in Mali Attack
ABC News New Statement Said to Identify Gunmen in Mali Attack ABC News Fuerzas militares rodean al presidente maliense Ibrahim Boubacar Keita ...

Resources last updated: 11/26/2015 12:29:00 AM