ADO - Inserting data Problem 
Author Message
 ADO - Inserting data Problem

I've got a problem inserting data through a recordset.

In my cmdOK_Click event I open a recordset and retrieve a value from
PERIODICAL_TBL, then close the recordset.

Then I set a string variable as an INSERT INTO statement, and execute it
using rs.Open strSQL, cnAb

My problem is that I get an SQL error that says Invalid Object
PERIODICAL_TBL, but I can look at the database and see the table is there,
and it is the same table that I selected from a few lines before!  I've
checked spelling already, in fact, if I Debug.Print strSQL and paste that
into SQL query analyzer, I can run the insert statement fine.  So the SQL is
correct, but something in VB, or between VB and SQL Server is screwing up.

Anybody got any ideas?

-Steve



Mon, 13 Oct 2003 21:49:44 GMT  
 ADO - Inserting data Problem
could you please post the full code - starting when you allocate the
variables, thorugh connectng to the database, selecting the value and
finally performing the insert sql statement.

\Thomas.


Quote:
> I've got a problem inserting data through a recordset.

> In my cmdOK_Click event I open a recordset and retrieve a value from
> PERIODICAL_TBL, then close the recordset.

> Then I set a string variable as an INSERT INTO statement, and execute it
> using rs.Open strSQL, cnAb

> My problem is that I get an SQL error that says Invalid Object
> PERIODICAL_TBL, but I can look at the database and see the table is there,
> and it is the same table that I selected from a few lines before!  I've
> checked spelling already, in fact, if I Debug.Print strSQL and paste that
> into SQL query analyzer, I can run the insert statement fine.  So the SQL
is
> correct, but something in VB, or between VB and SQL Server is screwing up.

> Anybody got any ideas?

> -Steve



Mon, 13 Oct 2003 22:21:28 GMT  
 ADO - Inserting data Problem
Here is the code:

Option Explicit

Dim cnAb As ADODB.Connection
Dim rsID As ADODB.Recordset
Dim rsPrefix As ADODB.Recordset
Dim rsType As ADODB.Recordset

Private Sub cmdOK_Click()
  Dim cmdInsert As ADODB.Command
  Set cmdInsert = New ADODB.Command
  Dim strSQL As String
  Dim lngID As Long

  strSQL = "SELECT Max(PRD_NBR)+1 As MaxID FROM PERIODICAL_TBL"

  Call modData.GetRecordSet(cnAb, rsID, strSQL)
  lngID = rsID.Fields("MaxID")

  strSQL = "INSERT INTO PERIODICAL_TBL ('PRD_PREFIX', 'PRD_NBR', 'PRD_TYPE')
VALUES('" & Me.sltPrefix.Value & "', " & lngID & ", '" & Me.sltType.Value &
"')"

  With cmdInsert
    .ActiveConnection = cnAb
    .CommandText = strSQL
    .Execute
  End With

  Set cmdInsert = Nothing

  Call modData.CloseRecordSet(cnAb, rsID)
End Sub

modData.bas
-----------------------------------
'Get a recordset
Public Sub GetRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
ADODB.Recordset, strSQL As String)
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  cn.Open frmMain.g_sDatasource, frmMain.g_sUserName, frmMain.g_sPassword
  rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
End Sub

'Close a recordset and connection
Public Sub CloseRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
ADODB.Recordset)
  rs.Close
  cn.Close

  Set rs = Nothing
End Sub


Quote:
> could you please post the full code - starting when you allocate the
> variables, thorugh connectng to the database, selecting the value and
> finally performing the insert sql statement.

> \Thomas.



> > I've got a problem inserting data through a recordset.

> > In my cmdOK_Click event I open a recordset and retrieve a value from
> > PERIODICAL_TBL, then close the recordset.

> > Then I set a string variable as an INSERT INTO statement, and execute it
> > using rs.Open strSQL, cnAb

> > My problem is that I get an SQL error that says Invalid Object
> > PERIODICAL_TBL, but I can look at the database and see the table is
there,
> > and it is the same table that I selected from a few lines before!  I've
> > checked spelling already, in fact, if I Debug.Print strSQL and paste
that
> > into SQL query analyzer, I can run the insert statement fine.  So the
SQL
> is
> > correct, but something in VB, or between VB and SQL Server is screwing
up.

> > Anybody got any ideas?

> > -Steve



Mon, 13 Oct 2003 22:39:48 GMT  
 ADO - Inserting data Problem
Why not combine the queries? As you have it coded, the MaxID might not be
"max" by the time the second query is executed. This can be coded as a
single Update statement. Better yet, use an Identity column that
autoincrements.

bv

Quote:
> Here is the code:

> Option Explicit

> Dim cnAb As ADODB.Connection
> Dim rsID As ADODB.Recordset
> Dim rsPrefix As ADODB.Recordset
> Dim rsType As ADODB.Recordset

> Private Sub cmdOK_Click()
>   Dim cmdInsert As ADODB.Command
>   Set cmdInsert = New ADODB.Command
>   Dim strSQL As String
>   Dim lngID As Long

>   strSQL = "SELECT Max(PRD_NBR)+1 As MaxID FROM PERIODICAL_TBL"

>   Call modData.GetRecordSet(cnAb, rsID, strSQL)
>   lngID = rsID.Fields("MaxID")

>   strSQL = "INSERT INTO PERIODICAL_TBL ('PRD_PREFIX', 'PRD_NBR',
'PRD_TYPE')
> VALUES('" & Me.sltPrefix.Value & "', " & lngID & ", '" & Me.sltType.Value
&
> "')"

>   With cmdInsert
>     .ActiveConnection = cnAb
>     .CommandText = strSQL
>     .Execute
>   End With

>   Set cmdInsert = Nothing

>   Call modData.CloseRecordSet(cnAb, rsID)
> End Sub

> modData.bas
> -----------------------------------
> 'Get a recordset
> Public Sub GetRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> ADODB.Recordset, strSQL As String)
>   Set cn = New ADODB.Connection
>   Set rs = New ADODB.Recordset

>   cn.Open frmMain.g_sDatasource, frmMain.g_sUserName, frmMain.g_sPassword
>   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
> End Sub

> 'Close a recordset and connection
> Public Sub CloseRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> ADODB.Recordset)
>   rs.Close
>   cn.Close

>   Set rs = Nothing
> End Sub



> > could you please post the full code - starting when you allocate the
> > variables, thorugh connectng to the database, selecting the value and
> > finally performing the insert sql statement.

> > \Thomas.



> > > I've got a problem inserting data through a recordset.

> > > In my cmdOK_Click event I open a recordset and retrieve a value from
> > > PERIODICAL_TBL, then close the recordset.

> > > Then I set a string variable as an INSERT INTO statement, and execute
it
> > > using rs.Open strSQL, cnAb

> > > My problem is that I get an SQL error that says Invalid Object
> > > PERIODICAL_TBL, but I can look at the database and see the table is
> there,
> > > and it is the same table that I selected from a few lines before!
I've
> > > checked spelling already, in fact, if I Debug.Print strSQL and paste
> that
> > > into SQL query analyzer, I can run the insert statement fine.  So the
> SQL
> > is
> > > correct, but something in VB, or between VB and SQL Server is screwing
> up.

> > > Anybody got any ideas?

> > > -Steve



Mon, 13 Oct 2003 23:58:58 GMT  
 ADO - Inserting data Problem
Well, the program is pretty much a single user program, so the keys would
never really collide, and I need to store that Max()+1 in a VB variable for
use elsewhere.

As for Identities, I prefer not to let the database decide what my data is.
I like to handle everything programatically.

-Steve



Quote:
> Why not combine the queries? As you have it coded, the MaxID might not be
> "max" by the time the second query is executed. This can be coded as a
> single Update statement. Better yet, use an Identity column that
> autoincrements.

> bv


> > Here is the code:

> > Option Explicit

> > Dim cnAb As ADODB.Connection
> > Dim rsID As ADODB.Recordset
> > Dim rsPrefix As ADODB.Recordset
> > Dim rsType As ADODB.Recordset

> > Private Sub cmdOK_Click()
> >   Dim cmdInsert As ADODB.Command
> >   Set cmdInsert = New ADODB.Command
> >   Dim strSQL As String
> >   Dim lngID As Long

> >   strSQL = "SELECT Max(PRD_NBR)+1 As MaxID FROM PERIODICAL_TBL"

> >   Call modData.GetRecordSet(cnAb, rsID, strSQL)
> >   lngID = rsID.Fields("MaxID")

> >   strSQL = "INSERT INTO PERIODICAL_TBL ('PRD_PREFIX', 'PRD_NBR',
> 'PRD_TYPE')
> > VALUES('" & Me.sltPrefix.Value & "', " & lngID & ", '" &
Me.sltType.Value
> &
> > "')"

> >   With cmdInsert
> >     .ActiveConnection = cnAb
> >     .CommandText = strSQL
> >     .Execute
> >   End With

> >   Set cmdInsert = Nothing

> >   Call modData.CloseRecordSet(cnAb, rsID)
> > End Sub

> > modData.bas
> > -----------------------------------
> > 'Get a recordset
> > Public Sub GetRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> > ADODB.Recordset, strSQL As String)
> >   Set cn = New ADODB.Connection
> >   Set rs = New ADODB.Recordset

> >   cn.Open frmMain.g_sDatasource, frmMain.g_sUserName,
frmMain.g_sPassword
> >   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
> > End Sub

> > 'Close a recordset and connection
> > Public Sub CloseRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> > ADODB.Recordset)
> >   rs.Close
> >   cn.Close

> >   Set rs = Nothing
> > End Sub



> > > could you please post the full code - starting when you allocate the
> > > variables, thorugh connectng to the database, selecting the value and
> > > finally performing the insert sql statement.

> > > \Thomas.



> > > > I've got a problem inserting data through a recordset.

> > > > In my cmdOK_Click event I open a recordset and retrieve a value from
> > > > PERIODICAL_TBL, then close the recordset.

> > > > Then I set a string variable as an INSERT INTO statement, and
execute
> it
> > > > using rs.Open strSQL, cnAb

> > > > My problem is that I get an SQL error that says Invalid Object
> > > > PERIODICAL_TBL, but I can look at the database and see the table is
> > there,
> > > > and it is the same table that I selected from a few lines before!
> I've
> > > > checked spelling already, in fact, if I Debug.Print strSQL and paste
> > that
> > > > into SQL query analyzer, I can run the insert statement fine.  So
the
> > SQL
> > > is
> > > > correct, but something in VB, or between VB and SQL Server is
screwing
> > up.

> > > > Anybody got any ideas?

> > > > -Steve



Tue, 14 Oct 2003 01:45:11 GMT  
 ADO - Inserting data Problem
hi,

You don't have to enclosed the fieldnames with single quote in the INSERT
clause.

Regards


Quote:
> Here is the code:

> Option Explicit

> Dim cnAb As ADODB.Connection
> Dim rsID As ADODB.Recordset
> Dim rsPrefix As ADODB.Recordset
> Dim rsType As ADODB.Recordset

> Private Sub cmdOK_Click()
>   Dim cmdInsert As ADODB.Command
>   Set cmdInsert = New ADODB.Command
>   Dim strSQL As String
>   Dim lngID As Long

>   strSQL = "SELECT Max(PRD_NBR)+1 As MaxID FROM PERIODICAL_TBL"

>   Call modData.GetRecordSet(cnAb, rsID, strSQL)
>   lngID = rsID.Fields("MaxID")

>   strSQL = "INSERT INTO PERIODICAL_TBL ('PRD_PREFIX', 'PRD_NBR',
'PRD_TYPE')
> VALUES('" & Me.sltPrefix.Value & "', " & lngID & ", '" & Me.sltType.Value
&
> "')"

>   With cmdInsert
>     .ActiveConnection = cnAb
>     .CommandText = strSQL
>     .Execute
>   End With

>   Set cmdInsert = Nothing

>   Call modData.CloseRecordSet(cnAb, rsID)
> End Sub

> modData.bas
> -----------------------------------
> 'Get a recordset
> Public Sub GetRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> ADODB.Recordset, strSQL As String)
>   Set cn = New ADODB.Connection
>   Set rs = New ADODB.Recordset

>   cn.Open frmMain.g_sDatasource, frmMain.g_sUserName, frmMain.g_sPassword
>   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
> End Sub

> 'Close a recordset and connection
> Public Sub CloseRecordSet(ByRef cn As ADODB.Connection, ByRef rs As
> ADODB.Recordset)
>   rs.Close
>   cn.Close

>   Set rs = Nothing
> End Sub



> > could you please post the full code - starting when you allocate the
> > variables, thorugh connectng to the database, selecting the value and
> > finally performing the insert sql statement.

> > \Thomas.



> > > I've got a problem inserting data through a recordset.

> > > In my cmdOK_Click event I open a recordset and retrieve a value from
> > > PERIODICAL_TBL, then close the recordset.

> > > Then I set a string variable as an INSERT INTO statement, and execute
it
> > > using rs.Open strSQL, cnAb

> > > My problem is that I get an SQL error that says Invalid Object
> > > PERIODICAL_TBL, but I can look at the database and see the table is
> there,
> > > and it is the same table that I selected from a few lines before!
I've
> > > checked spelling already, in fact, if I Debug.Print strSQL and paste
> that
> > > into SQL query analyzer, I can run the insert statement fine.  So the
> SQL
> > is
> > > correct, but something in VB, or between VB and SQL Server is screwing
> up.

> > > Anybody got any ideas?

> > > -Steve



Tue, 14 Oct 2003 15:55:14 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. ADO - Inserting data Problem

2. Insert Data Into Access Database From SQL Server ADO Insert

3. Inserting Data via ADO with an Auto Generated Key

4. Inserting Rows into a Datagrid connected to an ADO Data source

5. Inserting Rows into a Datagrid connected to an ADO Data source

6. ADO Data Control INSERT???

7. HELP! Inserting long data using ADO and SQL7

8. Mass Insert into SQL 6.5 from data in a RecordSet ADO object

9. Language Data Not inserted using ado

10. Inserting data from an ADO recordset into a SQL Server table

11. Error while inserting data using ADO

12. Using ADO w/ADO data control date problems

 

 
Powered by phpBB® Forum Software