Problem adding new record to SQL database recordset

Problem adding new record to SQL database recordset

Post by Zeng Xin » Sun, 20 Dec 1998 04:00:00



Hi,

I am using VB 5.0 to access a microsoft SQL server database, I want to
add new records to the recordset created from the databse. But I always
encountered the error "Database or Object is read-only". Here is the
part of that code:

    dim db as database
    dim rst as recordset

================================
    Set db = OpenDatabase("test", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=Iris;UID=sa;PWD=irisiris;DSN=test")

    MsgBox db.updatable  'Shows True which means db is updatable
    MsgBox db.TableQefs("test").updatable  'Shows True which means tabe
is updatable

    Set rst = db.OpenRecordset("test", dbOpenDynaset)
    MsgBox rst.updatable     'Shows false which means the recordset is
not updatable

    rst.addnew      'Give me the error message
=================================

Can someone help to explain why is that so? Is there a solution to this?

 
 
 

Problem adding new record to SQL database recordset

Post by KJS » Sun, 20 Dec 1998 04:00:00


Hi, Zeng...

The only thing that jumps out at me, is that the recordset you are creating
may
be defaulting to read-only because of a linking or relationship issue.
Without looking
at that, I really can't venture to guess much farther, but I'd check that
out first.  Try
opening up SQL/w and typing in your recordset criteria there and see if it's
still
read only.

HTH,

ken S.


>Hi,

>I am using VB 5.0 to access a microsoft SQL server database, I want to
>add new records to the recordset created from the databse. But I always
>encountered the error "Database or Object is read-only". Here is the
>part of that code:

>    dim db as database
>    dim rst as recordset

>================================
>    Set db = OpenDatabase("test", _
>        dbDriverNoPrompt, False, _
>        "ODBC;DATABASE=Iris;UID=sa;PWD=irisiris;DSN=test")

>    MsgBox db.updatable  'Shows True which means db is updatable
>    MsgBox db.TableQefs("test").updatable  'Shows True which means tabe
>is updatable

>    Set rst = db.OpenRecordset("test", dbOpenDynaset)
>    MsgBox rst.updatable     'Shows false which means the recordset is
>not updatable

>    rst.addnew      'Give me the error message
>=================================

>Can someone help to explain why is that so? Is there a solution to this?


 
 
 

Problem adding new record to SQL database recordset

Post by Guy Henr » Sun, 20 Dec 1998 04:00:00


Here is something to try. It seems that when you open a recordset in
ODBCDirect, it defaults to a lockedit type of dbReadonly. As per the help
file:

Quote:>dbReadOnly - Prevents users from making changes to the Recordset (default

for ODBCDirect workspaces). <

I remember being stuck at this point and solving it by changing my
OpenRecordset command as follows:

    Set rst = db.OpenRecordset("test", dbOpenDynaset,0,dbOptimistic)

Apparently, opening a recordset in readonly mode uses less resources, but it
is not very useful if you actually change the data. I hope that this helps.

Guy Henry

Visual Basic-Access-SQL Server-Internet Applications Consultant

 
 
 

Problem adding new record to SQL database recordset

Post by Guy Henr » Mon, 21 Dec 1998 04:00:00


I was thinking about your problem today, because I remember being at the
same point about a year ago. Then it hit me, you have to be sure that your
SQL Server table has either a primary key, or a unique index. This is
required in order for the table to be updatable. I believe that this should
solve your problem. Let me know

Guy Henry

Visual Basic-Access-SQL Server-Internet Applications Consultant


>Hi,

>I tried it, but it did not work for my environment. Strangely, following
code
>can be used to add new records:

>==============

>db.execute "Insert into test (test) values ("test")

>==============

>It looks like no matter what option spicified for "Openrecordset", it
always
>default to read-only. I am connecting to SQL 6.5 servers.


>> Here is something to try. It seems that when you open a recordset in
>> ODBCDirect, it defaults to a lockedit type of dbReadonly. As per the help
>> file:

>> >dbReadOnly - Prevents users from making changes to the Recordset
(default
>> for ODBCDirect workspaces). <

>> I remember being stuck at this point and solving it by changing my
>> OpenRecordset command as follows:

>>     Set rst = db.OpenRecordset("test", dbOpenDynaset,0,dbOptimistic)

>> Apparently, opening a recordset in readonly mode uses less resources, but
it
>> is not very useful if you actually change the data. I hope that this
helps.

>> Guy Henry

>> Visual Basic-Access-SQL Server-Internet Applications Consultant

 
 
 

Problem adding new record to SQL database recordset

Post by Zeng Xin » Tue, 22 Dec 1998 04:00:00


Hi,

I tried it, but it did not work for my environment. Strangely, following code
can be used to add new records:

==============

db.execute "Insert into test (test) values ("test")

==============

It looks like no matter what option spicified for "Openrecordset", it always
default to read-only. I am connecting to SQL 6.5 servers.


> Here is something to try. It seems that when you open a recordset in
> ODBCDirect, it defaults to a lockedit type of dbReadonly. As per the help
> file:

> >dbReadOnly - Prevents users from making changes to the Recordset (default
> for ODBCDirect workspaces). <

> I remember being stuck at this point and solving it by changing my
> OpenRecordset command as follows:

>     Set rst = db.OpenRecordset("test", dbOpenDynaset,0,dbOptimistic)

> Apparently, opening a recordset in readonly mode uses less resources, but it
> is not very useful if you actually change the data. I hope that this helps.

> Guy Henry

> Visual Basic-Access-SQL Server-Internet Applications Consultant

 
 
 

Problem adding new record to SQL database recordset

Post by ROSERD8 » Fri, 01 Jan 1999 04:00:00


It drove me crazy too. But I found out that the recordset are read only.

You have to build an Sql insert string like this.

Dim Sql As String

Sql = "Insert Into Dept(depno, name) " & _
          "Values('10','IT')
db.Execute Sql, dbSqlPassThrough

If you want to use the addnew command, then
you need RDO or ADO or attach tables thru Jet.

 
 
 

1. Problem adding new record to inner join recordset...


Do you have a one-to-one join or a one-to-many. If it is the latter,
you can't alway change the one-side.

I've the same problem as you (if it is one-to-one and you are not
violating relation-rules), but not with attached tables, but with
access 2.0 tables. It works fine with Access and VB3 but not with
VB4. Deep trouble. MS has no idea!

2. RPT2C

3. referenced object dropped during query optimization.

4. Remote Data Control--Problems with Adding new Record to SQL Server 6.0 Database

5. Database Consultant - 5 years DBA, Oracle 6 - 7.2 - 7.3, PL/SQL, UNIX, backup/recovery - NE

6. Problems with adding new records in a recordset

7. Oracle8 question #1

8. Problems adding new records to a table's database

9. How to add new records in a new database

10. adding new records & new record

11. Add New Record to Master/Index Recordset

12. Add a new record to an sectional table through recordset