Can''t INSERT a row in sql Server - error 9900

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Can''t INSERT a row in sql Server - error 9900

Pat Bensky
Folks,

Using 2004's built-in ODBC commands I am trying to INSERT and UPDATE rows in
a Sql Server database. I can connect to the database OK and retrieve info
such as the table structure, but when I try to do an INSERT I get the
dreaded error 9900, which there does not appear to be any useful explanation
for - in my (somewhat limited) experience this seems to mean a passeord or
privilege error.

Here is a simple text:

INSERT INTO Brand (brandRecordNumber, BrandName, BrandLogo) VALUES
(1102281, 'A Test Brand', '')

I can't see anything wrongw ith that ... but I get the error every time.

I suspect that I have not been given write priveleges for the database,
although the client's IT guy insists that I have.

Any suggestions?

thanks

Pat

--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

psmith-3-2
Pat,

It certainly looks like a privilege error.

Are you able to query on that table?

Can the IT guy give you access to another application which updates  
the same table which you can try to access with the account and  
password he gave you?

P. Smith

TSE International


> Folks,
>
> Using 2004's built-in ODBC commands I am trying to INSERT and  
> UPDATE rows in
> a Sql Server database. I can connect to the database OK and  
> retrieve info
> such as the table structure, but when I try to do an INSERT I get the
> dreaded error 9900, which there does not appear to be any useful  
> explanation
> for - in my (somewhat limited) experience this seems to mean a  
> passeord or
> privilege error.
>
> Here is a simple text:
>
> INSERT INTO Brand (brandRecordNumber, BrandName, BrandLogo) VALUES
> (1102281, 'A Test Brand', '')
>
> I can't see anything wrongw ith that ... but I get the error every  
> time.
>
> I suspect that I have not been given write priveleges for the  
> database,
> although the client's IT guy insists that I have.
>
> Any suggestions?
>
> thanks
>
> Pat

**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Charles Miller
In reply to this post by Pat Bensky
Are you sure that the third column accepts NULL and that all types are correct.

Regards

Chuck
On 10/31/07, Pat Bensky <[hidden email]> wrote:

> Using 2004's built-in ODBC commands I am trying to INSERT and UPDATE rows in
> a Sql Server database. I can connect to the database OK and retrieve info
> such as the table structure, but when I try to do an INSERT I get the
> dreaded error 9900, which there does not appear to be any useful explanation
> for - in my (somewhat limited) experience this seems to mean a passeord or
> privilege error.
>
> Here is a simple text:
>
> INSERT INTO Brand (brandRecordNumber, BrandName, BrandLogo) VALUES
> (1102281, 'A Test Brand', '')
>
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
OK I have kinda discovered what the problem is.

The INSERT  does actually work OK. The error message is caused by the fact
that I am tryng to insert a row which has a duplicate key. Why would I do
that? Because when I query the database to see if that key exists, the
number of result rows I get is 0. So my code figures this means the record
doesn't exist and tries to insert it ...

Here is my code:
    query:="SELECT * FROM Brand WHERE brandRecordNumber = 1102283"
    ARRAY TEXT(atRows;0)
    DBConn_Query ([PublishingStyles]ConnectionNumber) ` established
connection to the sqlserver db
    ODBC EXECUTE($query;atRows)
    $numrows:=Size of array(atRows)

I always get 0 in $numrows - the array atRows is always empty, whereas there
should be (at least) one element (row).

Any ideas what I am doing wrong here?

thanks!

Pat


On 01/11/2007, Charles Miller <[hidden email]> wrote:

>
> Are you sure that the third column accepts NULL and that all types are
> correct.
>
> Regards
>
> Chuck
> On 10/31/07, Pat Bensky <[hidden email]> wrote:
> > Using 2004's built-in ODBC commands I am trying to INSERT and UPDATE
> rows in
> > a Sql Server database. I can connect to the database OK and retrieve
> info
> > such as the table structure, but when I try to do an INSERT I get the
> > dreaded error 9900, which there does not appear to be any useful
> explanation
> > for - in my (somewhat limited) experience this seems to mean a passeord
> or
> > privilege error.
> >
> > Here is a simple text:
> >
> > INSERT INTO Brand (brandRecordNumber, BrandName, BrandLogo) VALUES
> > (1102281, 'A Test Brand', '')
>
>


--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Permanent Sort/Order by in 4D V11 SQL

Stephen Shaw
Dear all

In Version 11 how does one do a permanent sort - in early versions  
one could use tools and for static data this was a lot easier than  
writing code for each display.

TIA

Steve Shaw
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
In reply to this post by Pat Bensky
Pat

Apologies for the duplication if anyone else has already answered...

Your ODBC EXECUTE command only "binds" the atRows variable to the SQL
statement. If you want the values returned by the statement to be populated
in the 4D array, you always have to call

ODBC LOAD RECORDS(ODBC All records)

This will fill the array with as many values as the statement finds.

Cheers


David Dancy
Sydney, Australia

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Pat Bensky
> Sent: Sunday, November 04, 2007 8:08 AM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> OK I have kinda discovered what the problem is.
>
> The INSERT  does actually work OK. The error message is caused by the fact
> that I am tryng to insert a row which has a duplicate key. Why would I do
> that? Because when I query the database to see if that key exists, the
> number of result rows I get is 0. So my code figures this means the record
> doesn't exist and tries to insert it ...
>
> Here is my code:
>     query:="SELECT * FROM Brand WHERE brandRecordNumber = 1102283"
>     ARRAY TEXT(atRows;0)
>     DBConn_Query ([PublishingStyles]ConnectionNumber) ` established
> connection to the sqlserver db
>     ODBC EXECUTE($query;atRows)
>     $numrows:=Size of array(atRows)
>
> I always get 0 in $numrows - the array atRows is always empty, whereas
> there
> should be (at least) one element (row).
>
> Any ideas what I am doing wrong here?
>
> thanks!
>
> Pat
>
>


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

jeffFromOz
and you would probably have a more efficient call with the select  
count(*) , rather than select * . .

Jeff Edwards
Goodsoftware
Sydney, Australia

On 05/11/2007, at 9:11 AM, David Dancy wrote:

> Pat
>
> Apologies for the duplication if anyone else has already answered...
>
> Your ODBC EXECUTE command only "binds" the atRows variable to the SQL
> statement. If you want the values returned by the statement to be  
> populated
> in the 4D array, you always have to call
>
> ODBC LOAD RECORDS(ODBC All records)
>
> This will fill the array with as many values as the statement finds.
>
> Cheers
>
>
> David Dancy
> Sydney, Australia
>
>> Here is my code:
>>     query:="SELECT * FROM Brand WHERE brandRecordNumber = 1102283"
>>     ARRAY TEXT(atRows;0)
>>     DBConn_Query ([PublishingStyles]ConnectionNumber) ` established
>> connection to the sqlserver db
>>     ODBC EXECUTE($query;atRows)
>>     $numrows:=Size of array(atRows)
>>

**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
Thanks for your help guys ... I keep forgetting that ODBC LOAD RECORDS bit
...

So, now I can insert and update. What I'm stuck on now is finding out how
many records were updated ... here is some sample code:

$query:="UPDATE  Brand SET  BrandName = 'A Test Brand',BrandLogo = 'logo
goes here' WHERE brandRecordNumber = 1102281"
DBConn_Query ([PublishingStyles]ConnectionNumber) ` establish connection
oRecordCount:=0
ODBC EXECUTE($query;oRecordCount)
ODBC LOAD RECORD

I would expect the no. of affected rows to be returned in oRecordCount ...
but it's always 0.

How do I retrieve this info?

thanks

Pat

On 04/11/2007, Jeff Edwards <[hidden email]> wrote:

>
> and you would probably have a more efficient call with the select
> count(*) , rather than select * . .
>
> Jeff Edwards
> Goodsoftware
> Sydney, Australia
>
> On 05/11/2007, at 9:11 AM, David Dancy wrote:
>
> > Pat
> >
> > Apologies for the duplication if anyone else has already answered...
> >
> > Your ODBC EXECUTE command only "binds" the atRows variable to the SQL
> > statement. If you want the values returned by the statement to be
> > populated
> > in the 4D array, you always have to call
> >
> > ODBC LOAD RECORDS(ODBC All records)
> >
> > This will fill the array with as many values as the statement finds.
> >
> > Cheers
> >
> >
> > David Dancy
> > Sydney, Australia
>
>


--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
Pat

AFAIK, the built-in ODBC commands do not return this information. Sorry.

ODBC Pro will allow you to retrieve it, but at the expense of both greater
cost and greater complexity.

Sorry to be the bearer of bad tidings...

David Dancy
Sydney, Australia

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Pat Bensky
> Sent: Tuesday, November 06, 2007 8:01 AM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> Thanks for your help guys ... I keep forgetting that ODBC LOAD RECORDS bit
> ...
>
> So, now I can insert and update. What I'm stuck on now is finding out how
> many records were updated ... here is some sample code:
>
> $query:="UPDATE  Brand SET  BrandName = 'A Test Brand',BrandLogo = 'logo
> goes here' WHERE brandRecordNumber = 1102281"
> DBConn_Query ([PublishingStyles]ConnectionNumber) ` establish connection
> oRecordCount:=0
> ODBC EXECUTE($query;oRecordCount)
> ODBC LOAD RECORD
>
> I would expect the no. of affected rows to be returned in oRecordCount ...
> but it's always 0.
>
> How do I retrieve this info?
>
> thanks
>
> Pat
>


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
Bummer. Thanks for clarifying that for me and for saving me from wasting
even more time trying to figure it out!

Pat

On 05/11/2007, David Dancy <[hidden email]> wrote:

>
> Pat
>
> AFAIK, the built-in ODBC commands do not return this information. Sorry.
>
> ODBC Pro will allow you to retrieve it, but at the expense of both greater
> cost and greater complexity.
>
> Sorry to be the bearer of bad tidings...
>
> David Dancy
> Sydney, Australia
>
> > -----Original Message-----
> > From: [hidden email] [mailto:[hidden email]]
> > On Behalf Of Pat Bensky
> > Sent: Tuesday, November 06, 2007 8:01 AM
> > To: 4D iNug Tech
> > Subject: Re: Can''t INSERT a row in sql Server - error 9900
> >
> > Thanks for your help guys ... I keep forgetting that ODBC LOAD RECORDS
> bit
> > ...
> >
> > So, now I can insert and update. What I'm stuck on now is finding out
> how
> > many records were updated ... here is some sample code:
> >
> > $query:="UPDATE  Brand SET  BrandName = 'A Test Brand',BrandLogo = 'logo
> > goes here' WHERE brandRecordNumber = 1102281"
> > DBConn_Query ([PublishingStyles]ConnectionNumber) ` establish connection
> > oRecordCount:=0
> > ODBC EXECUTE($query;oRecordCount)
> > ODBC LOAD RECORD
> >
> > I would expect the no. of affected rows to be returned in oRecordCount
> ...
> > but it's always 0.
> >
> > How do I retrieve this info?
> >
> > thanks
> >
> > Pat
>
>
--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

John Flynn-2
In reply to this post by Pat Bensky
Hi Pat,

>>> So, now I can insert and update. What I'm stuck on now is finding
>>> out how many records were updated ... here is some sample code:

>> AFAIK, the built-in ODBC commands do not return this information.
>> Sorry.

> Bummer. Thanks for clarifying that for me and for saving me from
> wasting even more time trying to figure it out!

Since you're connecting to SQL Server, I believe you have available the
@@rowcount variable. The general technique I'm familiar with is to first
submit your UPDATE query, and then submit a query like "select @@rowcount"
and receive that into an integer. And that integer will then hold the
answer you seek.

- John.

**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
Hi John ,
Thanks for that suggestion, but it doesn't seem to work ... here is my code:

        ODBC EXECUTE($query) ` this does the update
        $query:="select @@rowcount"
        C_LONGINT($rows)
        $rows:=0
        ODBC EXECUTE($query;$rows)
        oUpdateCount:=oUpdateCount+$rows

$rows is always 0, even when I know that a row has been updated.
Maybe there is something else you have to do to get a variable returned?

Pat


On 06/11/2007, John Flynn <[hidden email]> wrote:

>
> Hi Pat,
>
> >>> So, now I can insert and update. What I'm stuck on now is finding
> >>> out how many records were updated ... here is some sample code:
>
> >> AFAIK, the built-in ODBC commands do not return this information.
> >> Sorry.
>
> > Bummer. Thanks for clarifying that for me and for saving me from
> > wasting even more time trying to figure it out!
>
> Since you're connecting to SQL Server, I believe you have available the
> @@rowcount variable. The general technique I'm familiar with is to first
> submit your UPDATE query, and then submit a query like "select @@rowcount"
> and receive that into an integer. And that integer will then hold the
> answer you seek.
>
> - John.
>
>

--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
Pat

Your code doesn't say ODBC LOAD RECORD. Your $rows variable will never be
filled in unless the value is brought back to 4D by the ODBC LOAD RECORD
command...

Cheers

David Dancy
Sydney, Australia

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Pat Bensky
> Sent: Wednesday, November 07, 2007 10:45 AM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> Hi John ,
> Thanks for that suggestion, but it doesn't seem to work ... here is my
> code:
>
>         ODBC EXECUTE($query) ` this does the update
>         $query:="select @@rowcount"
>         C_LONGINT($rows)
>         $rows:=0
>         ODBC EXECUTE($query;$rows)
>         oUpdateCount:=oUpdateCount+$rows
>
> $rows is always 0, even when I know that a row has been updated.
> Maybe there is something else you have to do to get a variable returned?
>
> Pat
>
>
> On 06/11/2007, John Flynn <[hidden email]> wrote:
> >
> > Hi Pat,
> >
> > >>> So, now I can insert and update. What I'm stuck on now is finding
> > >>> out how many records were updated ... here is some sample code:
> >
> > >> AFAIK, the built-in ODBC commands do not return this information.
> > >> Sorry.
> >
> > > Bummer. Thanks for clarifying that for me and for saving me from
> > > wasting even more time trying to figure it out!
> >
> > Since you're connecting to SQL Server, I believe you have available the
> > @@rowcount variable. The general technique I'm familiar with is to first
> > submit your UPDATE query, and then submit a query like "select
> @@rowcount"
> > and receive that into an integer. And that integer will then hold the
> > answer you seek.
> >
> > - John.
> >
> >
>
> --


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
Hi Daivd,

I did have ODBC LOAD RECORD in there but it still didn't work. $rows still
always = 0. It was like this:

ODBC EXECUTE($query) ` this does the update
$query:="select @@rowcount"
C_LONGINT($rows)
rows:=0
ODBC LOAD RECORD

Just for fun I also tried:
ODBC EXECUTE($query) ` this does the update
ODBC LOAD RECORD <------------------------------------------this generates
an error
 $query:="select @@rowcount"
C_LONGINT($rows)
rows:=0
ODBC LOAD RECORD

Pat




On 07/11/2007, David Dancy <[hidden email]> wrote:

>
> Pat
>
> Your code doesn't say ODBC LOAD RECORD. Your $rows variable will never be
> filled in unless the value is brought back to 4D by the ODBC LOAD RECORD
> command...
>
> Cheers
>
> David Dancy
> Sydney, Australia
>
> > -----Original Message-----
> > From: [hidden email] [mailto:[hidden email]]
> > On Behalf Of Pat Bensky
> > Sent: Wednesday, November 07, 2007 10:45 AM
> > To: 4D iNug Tech
> > Subject: Re: Can''t INSERT a row in sql Server - error 9900
> >
> > Hi John ,
> > Thanks for that suggestion, but it doesn't seem to work ... here is my
> > code:
> >
> >         ODBC EXECUTE($query) ` this does the update
> >         $query:="select @@rowcount"
> >         C_LONGINT($rows)
> >         $rows:=0
> >         ODBC EXECUTE($query;$rows)
> >         oUpdateCount:=oUpdateCount+$rows
> >
> > $rows is always 0, even when I know that a row has been updated.
> > Maybe there is something else you have to do to get a variable returned?
> >
> > Pat
> >
>
>

--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
Pat

Here's how I'd do it:


`*********
C_TEXT($query)
C_LONGINT($rows)

`do the update in SQL Server
$query:="UPDATE MyTable SET MyColumn = 1 WHERE MyKey = 100"

ODBC EXECUTE($query) ` this does the update
`not collecting data back into 4D so no need to load records

`find out how many rows were affected
$query:="select @@rowcount"

ODBC EXECUTE($query;$rows)
ODBC LOAD RECORD(ODBC All records) `$rows should now have a value > 0

`*********

Do you see the difference? The first query is an update, so you don't need
to load anything back into 4D. The second query is a select, so you do need
to load records in order for the result (which happens to be SQL Server's
cached data showing how many records were affected by the update query) to
come back to 4D.

HTH.

Cheers

David Dancy
Sydney, Australia

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Pat Bensky
> Sent: Wednesday, November 07, 2007 11:33 AM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> Hi Daivd,
>
> I did have ODBC LOAD RECORD in there but it still didn't work. $rows still
> always = 0. It was like this:
>
> ODBC EXECUTE($query) ` this does the update
> $query:="select @@rowcount"
> C_LONGINT($rows)
> rows:=0
> ODBC LOAD RECORD
>
> Just for fun I also tried:
> ODBC EXECUTE($query) ` this does the update
> ODBC LOAD RECORD <------------------------------------------this generates
> an error
>  $query:="select @@rowcount"
> C_LONGINT($rows)
> rows:=0
> ODBC LOAD RECORD
>
> Pat
>
>


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Charles Miller
In reply to this post by Pat Bensky
I think @@row count only works in one procedure

you could right one sql and execut immediate with that as the last line

so you wuery would look like

$Query:="Update whatever " +" select @@rowcount"

Nont sure of the exact syntax needed, but this is the idea.

Regards


Chuck

On 11/6/07, Pat Bensky <[hidden email]> wrote:

> Hi John ,
> Thanks for that suggestion, but it doesn't seem to work ... here is my code:
>
>         ODBC EXECUTE($query) ` this does the update
>         $query:="select @@rowcount"
>         C_LONGINT($rows)
>         $rows:=0
>         ODBC EXECUTE($query;$rows)
>         oUpdateCount:=oUpdateCount+$rows
>
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
Chuck

I tried 2 separate calls to ODBC EXECUTE against MS SQL Server as per my
example to Pat, and received a correct number of rows affected. I expect
that your example would work as well.

v2004.4, YMMV.

Cheers

David Dancy
Sydney, Australia

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Charles Miller
> Sent: Wednesday, November 07, 2007 12:24 PM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> I think @@row count only works in one procedure
>
> you could right one sql and execut immediate with that as the last line
>
> so you wuery would look like
>
> $Query:="Update whatever " +" select @@rowcount"
>
> Nont sure of the exact syntax needed, but this is the idea.
>
> Regards
>
>
> Chuck
>
> On 11/6/07, Pat Bensky <[hidden email]> wrote:
> > Hi John ,
> > Thanks for that suggestion, but it doesn't seem to work ... here is my
> code:
> >
> >         ODBC EXECUTE($query) ` this does the update
> >         $query:="select @@rowcount"
> >         C_LONGINT($rows)
> >         $rows:=0
> >         ODBC EXECUTE($query;$rows)
> >         oUpdateCount:=oUpdateCount+$rows
> >


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
David,

Well, I don't know what I'm doing wrong, but it does NOT work for me! My
code is exactly the same as yours. I checked the sql server db to make sure
that the test records had been updated; they had.

I wonder what I am doing differently that makes it not work for me? Any
suggestions as to what might affect this?

thanks!

Pat

On 07/11/2007, David Dancy <[hidden email]> wrote:

>
> Chuck
>
> I tried 2 separate calls to ODBC EXECUTE against MS SQL Server as per my
> example to Pat, and received a correct number of rows affected. I expect
> that your example would work as well.
>
> v2004.4, YMMV.
>
> Cheers
>
> David Dancy
> Sydney, Australia
>
> > -----Original Message-----
> > From: [hidden email] [mailto:[hidden email]]
> > On Behalf Of Charles Miller
> > Sent: Wednesday, November 07, 2007 12:24 PM
> > To: 4D iNug Tech
> > Subject: Re: Can''t INSERT a row in sql Server - error 9900
> >
> > I think @@row count only works in one procedure
> >
> > you could right one sql and execut immediate with that as the last line
> >
> > so you wuery would look like
> >
> > $Query:="Update whatever " +" select @@rowcount"
> >
> > Nont sure of the exact syntax needed, but this is the idea.
> >
> > Regards
> >
> >
> > Chuck
> >
> > On 11/6/07, Pat Bensky <[hidden email]> wrote:
> > > Hi John ,
> > > Thanks for that suggestion, but it doesn't seem to work ... here is my
> > code:
> > >
> > >         ODBC EXECUTE($query) ` this does the update
> > >         $query:="select @@rowcount"
> > >         C_LONGINT($rows)
> > >         $rows:=0
> > >         ODBC EXECUTE($query;$rows)
> > >         oUpdateCount:=oUpdateCount+$rows
> > >
>
>
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can''t INSERT a row in sql Server - error 9900

Pat Bensky
In reply to this post by Charles Miller
Nice idea, Chuck, but it doesn't seem to work - I get an error no 9907. This
is what I did:

        $query:=$query+Char(13)+"select @@rowcount"
        C_LONGINT($rows)
        $rows:=0
        ODBC EXECUTE($query;$rows)
        ODBC LOAD RECORD(ODBC All Records )<----------------------------
error generated here

and of course, $rows still = 0. The record was updated.

Pat


On 07/11/2007, Charles Miller <[hidden email]> wrote:

>
> I think @@row count only works in one procedure
>
> you could right one sql and execut immediate with that as the last line
>
> so you wuery would look like
>
> $Query:="Update whatever " +" select @@rowcount"
>
> Nont sure of the exact syntax needed, but this is the idea.
>
> Regards
>
>
> Chuck
>
> On 11/6/07, Pat Bensky <[hidden email]> wrote:
> > Hi John ,
> > Thanks for that suggestion, but it doesn't seem to work ... here is my
> code:
> >
> >         ODBC EXECUTE($query) ` this does the update
> >         $query:="select @@rowcount"
> >         C_LONGINT($rows)
> >         $rows:=0
> >         ODBC EXECUTE($query;$rows)
> >         oUpdateCount:=oUpdateCount+$rows
> >
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Can''t INSERT a row in sql Server - error 9900

David Dancy
In reply to this post by Pat Bensky
Pat

I admit it's strange. I have a SQL query system in which I'm doing
essentially what I wrote in my code sample, and it works. Perhaps you could
post your actual code that's not working? Another pair of eyes looking at it
might spot some difference that's not obvious to someone close to it.

Cheers

David

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of Pat Bensky
> Sent: Wednesday, November 07, 2007 1:41 PM
> To: 4D iNug Tech
> Subject: Re: Can''t INSERT a row in sql Server - error 9900
>
> David,
>
> Well, I don't know what I'm doing wrong, but it does NOT work for me! My
> code is exactly the same as yours. I checked the sql server db to make
> sure
> that the test records had been updated; they had.
>
> I wonder what I am doing differently that makes it not work for me? Any
> suggestions as to what might affect this?
>
> thanks!
>
> Pat
>


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
12