Values not being inserted into table

Printable View

I'm having a problem with my add() function not inserting rows into my table. I deleted the contents of both the tables it checks so that all the data would be inserted. All the if statements are right, but for some reason it doesn't insert them or give an error message. Any suggestions? Thanks.

VB Code:

'*ADD()************************************************************************
'NAME: add()
'DESC: Adds items that are not currently in the MAIN or ALTER table
Private Sub add()
    On Error Resume Next
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\P6B0438\My Documents\Programs\Visual Basic\DB\Test.mdb"
    newConnection.Open
    Set newRecordSet = New ADODB.recordSet
    newRecordSet.Open "Untitled", newConnection, adOpenKeyset, adLockPessimistic, adCmdTable
    
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\P6B0438\My Documents\Programs\Visual Basic\DB\cycleCount.mdb"
    connection.Open
    Set recordSet = New ADODB.recordSet
    recordSet.Open "MAIN", connection, adOpenKeyset, adLockPessimistic, adCmdTable
    
    Dim sql As String
    
    newRecordSet.MoveFirst
    Dim num As Integer
        
    Do Until newRecordSet.EOF
        If (inMain(newRecordSet.Fields("PICKSL"), newRecordSet.Fields("SLOT"), newRecordSet.Fields("RES"), newRecordSet.Fields("PROD"), newRecordSet.Fields("PACK"), newRecordSet.Fields("MANU ID"), newRecordSet.Fields("HITIX"), newRecordSet.Fields("PALLET ID"), newRecordSet.Fields("DFP")) = False) Then
MsgBox ("It's not in the MAIN table")
            If (inAlter(newRecordSet.Fields("PICKSL"), newRecordSet.Fields("SLOT"), newRecordSet.Fields("RES"), newRecordSet.Fields("PROD"), newRecordSet.Fields("PACK"), newRecordSet.Fields("MANU ID"), newRecordSet.Fields("HITIX"), newRecordSet.Fields("PALLET ID"), newRecordSet.Fields("DFP")) = False) Then
MsgBox ("It's not in the ALTER table")
                sql = "Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK," & _
                    "[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('" & _
                    recordSet.Fields("PICKSL") & "','" & recordSet.Fields("SLOT") & _
                    "','" & recordSet.Fields("RES") & "'," & recordSet.Fields("PROD") & _
                    ",'" & recordSet.Fields("DESC") & "','" & recordSet.Fields("PACK") & _
                    "','" & recordSet.Fields("MANU ID") & "','" & recordSet.Fields("HITIX") & _
                    "','" & recordSet.Fields("PALLET ID") & "'," & recordSet.Fields("PALLET QTY") & _
                    ",'" & recordSet.Fields("DFP") & "')"
Debug.Print sql
                connection.Execute sql
Debug.Print sql
MsgBox ("Should have inserted")
            End If
        End If
        
        newRecordSet.MoveNext
    Loop
End Sub
'*ENDOF*ADD()******************************************************************

Nov 21st, 2005, 04:16 PM
brucevde

Re: Values not being inserted into table

Since you are probably getting a runtime error but are ignoring any errors who knows what the problem could be.

Delete the On Error Resume Next statement.
Nov 21st, 2005, 04:24 PM
brucevde

Re: Values not being inserted into table
Looking at your previous thread both the inMain and inAlter procedures use this line of code

If (rs.RecordCount = 0) Then

rs.RecordCount could be -1 indicating there are records but ADO does not know how many. This happens when using a ServerSide cursor.

Change to a Client side cursor or check for EOF
VB Code:
```
inMain = Not rs.Recordset.EOF 'if EOF is True return False - ie no record found.
```
Nov 21st, 2005, 04:32 PM
manofsteel00

Re: Values not being inserted into table

Ok sorry about the on error resume next I was testing another part of it. Anyway, I did get an error: Run-time error: '2147217900 (80040e14)' Syntax error in INSERT INTO statement. Thanks.
Nov 21st, 2005, 04:41 PM
manofsteel00

Re: Values not being inserted into table

And here's the last couple of lines in the debugger:

Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK,[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('','','',,'','','','','',,'')
Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK,[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('','','',,'','','','','',,'')
SELECT * FROM [MAIN] WHERE [PICKSL]='CA041' and [SLOT]='CA048' and [RES]='3 y F' and [PROD]=292371 and [PACK]='6-14Z' and [MANU ID]='5810806153' and [HITIX]='0241' and [PALLET ID]='0976916' and [DFP]='1'
SELECT * FROM [MAIN] WHERE [PICKSL]='CA041' and [SLOT]='CA048' and [RES]='3 y F' and [PROD]=292371 and [PACK]='6-14Z' and [MANU ID]='5810806153' and [HITIX]='0241' and [PALLET ID]='0976916' and [DFP]='1'
SELECT * FROM [ALTER] WHERE [PICKSL]='CA041' and [SLOT]='CA048' and [RES]='3 y F' and [PROD]=292371 and [PACK]='6-14Z' and [MANU ID]='5810806153' and [HITIX]='0241' and [PALLET ID]='0976916' and [DFP]='1'
SELECT * FROM [ALTER] WHERE [PICKSL]='CA041' and [SLOT]='CA048' and [RES]='3 y F' and [PROD]=292371 and [PACK]='6-14Z' and [MANU ID]='5810806153' and [HITIX]='0241' and [PALLET ID]='0976916' and [DFP]='1'
Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK,[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('','','',,'','','','','',,'')
Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK,[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('','','',,'','','','','',,'')
SELECT * FROM [MAIN] WHERE [PICKSL]='CA020' and [SLOT]='CA040' and [RES]='4' and [PROD]=292611 and [PACK]='6-17Z' and [MANU ID]='71570' and [HITIX]='0241' and [PALLET ID]='0015098' and [DFP]='1'

Re: Values not being inserted into table

The Insert statement is loading blank values. Since two of the columns are numeric, but no value is supplied a syntax error occurs.

Shouldn't the insert statement be created from newRecordset rather than recordset.

VB Code:

sql = "Insert into [MAIN] (PICKSL,SLOT,RES,PROD,[DESC],PACK," & _
                    "[MANU ID],HITIX, [PALLET ID],[PALLET QTY],DFP) VALUES ('" & _
                    recordSet.Fields("PICKSL") & "','" & recordSet.Fields("SLOT") & _
                    "','" & recordSet.Fields("RES") & "'," & recordSet.Fields("PROD") & _
                    ",'" & recordSet.Fields("DESC") & "','" & recordSet.Fields("PACK") & _
                    "','" & recordSet.Fields("MANU ID") & "','" & recordSet.Fields("HITIX") & _
                    "','" & recordSet.Fields("PALLET ID") & "'," & recordSet.Fields("PALLET QTY") & _
                    ",'" & recordSet.Fields("DFP") & "')"

Is it even neccessary to open the recordset for the Main table in the Add procedure?

Nov 21st, 2005, 06:58 PM
manofsteel00

Re: Values not being inserted into table

Man I've been making some stupid mistakes lately!
Nov 23rd, 2005, 03:10 PM
manofsteel00

Re: Values not being inserted into table

Whenever my program is reading files from one database and putting them into another database the program stops responding. Is it really still doing something or has it stopped altogether?
Nov 23rd, 2005, 03:11 PM
manofsteel00

Re: Values not being inserted into table

No error is given, and I do not have On Error Resume Next on
Nov 23rd, 2005, 06:13 PM
randem

Re: Values not being inserted into table

Well, you need a DoEvents in the loop to keep your program responding and not hogging CPU cycles.

Also "Data Source= C:\Documents and Settings\P6B0438\My Documents\Programs\Visual Basic\DB\Test.mdb"

You should not have your database location in the location you have it in. You should probably use App.Path instead and have your database there or use App.Path & "\DB\" or something of the sort.
Nov 23rd, 2005, 09:18 PM
manofsteel00

Re: Values not being inserted into table

What exactly does Do Events do?
Nov 23rd, 2005, 09:23 PM
randem

Re: Values not being inserted into table

DoEvents allows the system to process other events that happen on your system so that it does not appear to freeze.