ADDNEW-Method on ADO in SQL-Server [Newbie]

ADDNEW-Method on ADO in SQL-Server [Newbie]

Post by Sebastia » Fri, 11 Aug 2000 04:00:00



Hi,
I'm a Newbie in VB programming and also new in work with the SQL-Server
(Yes, you can call me a Greenhorn).

This is my problem:

I programm in VB(VB6) a utillity which open a connection on ADO to my
SQL-Server(SQL-Server 7.0, SP2), open a recordset and add a new row with the
ADDNEW-Method.
After this ADDNEW-Event I wanted to get the Value of an AutoValue-Field
(named Code), but this wasn't possible because the SQL-Server fill the
AutoValue-Fields after the UPDATE-Method.
So I wanted to Update my recordset, but this is only possible if you fill a
field, because SQL-Server can't update(create) a empty row.
Because of this I fill one field of the row.
After this I UPDATE my Recordset and wanted to get the Value of my
AutoValue-Field, but I get the false Value, because I've lost the row after
I update the recordset.

This is my question: How can I add a new row in a recordset without loosing
the row to get the value of the AutoValue-Field of the new row?

This ist the Code I use in VB6 on SQL-Server 7.0(SP2) with ADO 2.1:

Quote:>Dim dbado As ADODB.Connection
>Dim rsado As ADODB.Recordset
>Dim vCode As Long
>Set dbado = New ADODB.Connection
>Set rsado = New ADODB.Recordset
>dbado.Provider = "SQLOLEDB"
>Call dbado.Open("server=server1;database=mydb;", "Sebastian")
>dbado.Properties("Enable Fastload") = True
>rsado.Open "Select * FROM Table1", dbado, adOpenDynamic, adLockOptimistic
>rsado.MoveFirst
>rsado.AddNew
>MsgBox rsado("Code") 'Returns nothing
>rsado("Name") = "Name1"
>rsado.Update
>vCode = rsado("Code")   'rsado("Code") is ISNULL -> vCode = ""
>MsgBox rsado("Code")   'returns nothing
>rsado.Close
>rsado.Open "Select * FROM Kunden WHERE Code = " & vCode, dbado,

adOpenDynamic, adLockOptimistic

Quote:>MsgBox rsado.EOF   'returns True

I read a book about ADO and I read that I ADO recordset don't lost it's row
after the Update-Method. Is this True and if it is what do I wrong?
I've also try to remember the row with a bookmark, but when I want to set a
bookmark I get the Error-Message "Your provider doesn't support this
property or Method".

Thx in advance to every help, tip, solution or comment,

Sebastian

 
 
 

ADDNEW-Method on ADO in SQL-Server [Newbie]

Post by Dick Christop » Sat, 12 Aug 2000 04:00:00


Hi Sebastian

Well this is just a stab in the dark, but maybe change your SQL String
to the Table name

Quote:> >rsado.Open "Select * FROM Table1", dbado, adOpenDynamic, adLockOptimistic

to

Quote:> rsado.Open "Table1", dbado, adKeySet, adLockOptimistic, adcmdTable

Use Keyset since the table is keyed on "Code" (I assume that is the
primary key).

Also are there any fields in the Table besides the "Code" on that are
set up as "Not Null" if so that could be preventing an update. But if
so, one would expect:

Quote:> >rsado.Update

Would generate and error to this effect.

Quote:> I read a book about ADO and I read that I ADO recordset don't lost it's row
> after the Update-Method. Is this True and if it is what do I wrong?

This is true in general, my guess is the recordset does not get updated.

I am not sure, if this helps, but these are the things I would try.

--
-Dick Christoph

http://www1.minn.net/~dchristo


> Hi,
> I'm a Newbie in VB programming and also new in work with the SQL-Server
> (Yes, you can call me a Greenhorn).

> This is my problem:

> I programm in VB(VB6) a utillity which open a connection on ADO to my
> SQL-Server(SQL-Server 7.0, SP2), open a recordset and add a new row with the
> ADDNEW-Method.
> After this ADDNEW-Event I wanted to get the Value of an AutoValue-Field
> (named Code), but this wasn't possible because the SQL-Server fill the
> AutoValue-Fields after the UPDATE-Method.
> So I wanted to Update my recordset, but this is only possible if you fill a
> field, because SQL-Server can't update(create) a empty row.
> Because of this I fill one field of the row.
> After this I UPDATE my Recordset and wanted to get the Value of my
> AutoValue-Field, but I get the false Value, because I've lost the row after
> I update the recordset.

> This is my question: How can I add a new row in a recordset without loosing
> the row to get the value of the AutoValue-Field of the new row?

> This ist the Code I use in VB6 on SQL-Server 7.0(SP2) with ADO 2.1:

> >Dim dbado As ADODB.Connection
> >Dim rsado As ADODB.Recordset
> >Dim vCode As Long

> >Set dbado = New ADODB.Connection
> >Set rsado = New ADODB.Recordset

> >dbado.Provider = "SQLOLEDB"

> >Call dbado.Open("server=server1;database=mydb;", "Sebastian")
> >dbado.Properties("Enable Fastload") = True

> >rsado.Open "Select * FROM Table1", dbado, adOpenDynamic, adLockOptimistic
> >rsado.MoveFirst
> >rsado.AddNew
> >MsgBox rsado("Code") 'Returns nothing
> >rsado("Name") = "Name1"
> >rsado.Update

> >vCode = rsado("Code")   'rsado("Code") is ISNULL -> vCode = ""
> >MsgBox rsado("Code")   'returns nothing
> >rsado.Close
> >rsado.Open "Select * FROM Kunden WHERE Code = " & vCode, dbado,
> adOpenDynamic, adLockOptimistic
> >MsgBox rsado.EOF   'returns True

> I read a book about ADO and I read that I ADO recordset don't lost it's row
> after the Update-Method. Is this True and if it is what do I wrong?
> I've also try to remember the row with a bookmark, but when I want to set a
> bookmark I get the Error-Message "Your provider doesn't support this
> property or Method".

> Thx in advance to every help, tip, solution or comment,

> Sebastian


 
 
 

1. ADDNEW - Method on ADO with SQL-Server [Newbie]

Hi,
I'm a Newbie in VB programming and also new in work with the SQL-Server
(Yes, you can call me a Greenhorn).

This is my problem:

I programm in VB(VB6) a utillity which open a connection on ADO to my
SQL-Server(SQL-Server 7.0, SP2), open a recordset and add a new row with the
ADDNEW-Method.
After this ADDNEW-Event I wanted to get the Value of an AutoValue-Field
(named Code), but this wasn't possible because the SQL-Server fill the
AutoValue-Fields after the UPDATE-Method.
So I wanted to Update my recordset, but this is only possible if you fill a
field, because SQL-Server can't update(create) a empty row.
Because of this I fill one field of the row.
After this I UPDATE my Recordset and wanted to get the Value of my
AutoValue-Field, but I get the false Value, because I've lost the row after
I update the recordset.

This is my question: How can I add a new row in a recordset without loosing
the row to get the value of the AutoValue-Field of the new row?

This ist the Code I use in VB6 on SQL-Server 7.0(SP2) with ADO 2.1:

adOpenDynamic, adLockOptimistic

I read a book about ADO and I read that I ADO recordset don't lost it's row
after the Update-Method. Is this True and if it is what do I wrong?
I've also try to remember the row with a bookmark, but when I want to set a
bookmark I get the Error-Message "Your provider doesn't support this
property or Method".

Thx in advance to every help, tip, solution or comment,

Sebastian

2. Oracle jobs in Scotland

3. ADDNEW-Method on ADO with SQL-Server [Newbie]

4. pgsql/src/backend/access/transam xlog.c

5. Identity Field in SQL Server 2000 and AddNew ADO Method

6. Changing Control Fonts onthe fly - how?

7. Error when using AddNew method in ADO (using ADO 2.7 SP1 from VB 6 SP4)

8. Borland is jerking us with 7.0 for Win3.1 upgrade

9. ado addnew method in transact sql

10. Getting default value when using ADO.Addnew method before Update

11. getting modified values after calling ado addnew method

12. Oracle, ADO question (addnew method from VB)

13. .addnew method ADO does not work pls help