select not returning new values

[mikela]

Hello!

I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...

Any idea??

My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)


sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "

 

[rsmonkey]

Well it would help if you posted all your code so we can see what is going on like where you obtain the '" & semana & "' values from etc...

 

[mikela]

the full code is quite impossible to post in here as it is too long.

& semana & and & empleadoId & are variables, and I insert them into the table called TRA.

Then it creates an numeroTRA (unique identifier for the row), and I want to collect it with the select... but not working....

Don't know what else to try...

The insert works fine! But not the select... and the strange thing is that sometimes it works (the select returns de numeroTRA that I need), but sometimes it does not work (does not return anything), for the sames values...

 

[rsmonkey]

well as i said its hard to help without seeing you could always post a limited version of your db here for us to have a look.

you sql statement has a dodgy bracket in it by the way.

strSQL = "SELECT * FROM TRA WHERE ((TRA.semana)='" & semana & "') AND ((TRA.empleadoId)=" & empleadoId & ");"

I also find that Access is funny with the star sometime so actualyl name your attibutes:

strSQL = "SELECT TRA.semana, TRA.empleadoId FROM TRA WHERE ((TRA.semana)='" & semana & "') AND ((TRA.empleadoId)=" & empleadoId & ");"

etc...

 

[mikela]

well,

i've tryied it and still not working... i thought it may be something i'm missing, like I'm not doing any commit. Would i need to do a commit? Don't know how to do it...

sometimes the select returns the new inserted row, that is what i need, and sometimes it does not...
so i guessed it may be something im doing wrong but like the commit or something like that.

If the insert is allright, why then, the select does not return any values in sometimes?

The select stament should be ok, otherwise it would always be the same result: wrong...

Don't understand anything... and i'm getting crazy!

 

[rsmonkey]

relax, you're a lady so crazyness is a naturality of your personality! on a lighter not are you 100% positive that the insert is working everytime? are you sure that '" & semana & "' is sometimes a null value thus not inserting into the db thus not beign able to select. This is why i asked for you db so we can explore other possible resons its difficult to diagnose a problem from 5 or 6 lines of code... copy your db, delete everything out of it except this function which is not working and a couple of records, zip it up and post it!

 

[mikela]

Right....

I'm a lady... i forgot...

There it is, the table and the function... Sometimes it work, sometimes it doesn't...

crazy crazy crazy!

Thanks!

 

[The_Doc_Man]

The catch is, the SELECT has to look for EXACTLY THE SAME STRING as the INSERT created, or you have to use the LIKE operator to get close. If neither of those is true, it would be possible to mess up a string.

One thought that comes to mind is if it EVER happens that what you inserted contains two quote-class marks in a row i.e. "" or '' or ``

That would seriously confuse the issue because doubled quotes inside a quoted or substituted string can really cause you grief.

 

[mikela]

I need to get exactly the same row as i did the insert, and the two parameters are the same.
I insert '" & semana & "' and " & empleadoID & ", and then i do the select with the restriction:

Where semana ='" & semana & "' and empleadoId= " & empleadoId & ", which have the same value as the insert, it does not change...

 

[rsmonkey]

Well first off you dont even from the sample you provided apart from your formatting being awful.. i know its a pain but it makes your code readable rather than a block of writing.. Anyways from the sample you dont define '" & semana & "' or " & empleadoId & " you gotta make them into values i.e.

strSQL = "SELECT DISTINCT TRA.semana, TRA.empleadoId  FROM TRA;"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    Do While Not rs.EOF
    
    strsemana = rs!semana
    strsemana = Replace(strsemana , "'", "''")
    strempleadoId = rs!empleadoId

So that code creates a recordset i.e. a big list of all your semana's & empleadoId's.

What you then to do is create 1 or more conditional statements to do what you want.. This could be selecting values from an object in your form or values from another table etc.. Then you want to compare those values to your record set so lets say:

strDATE = Me.txtDATE.Value
strEMPID = Me.txtEmpID.Value

 'So now you need to adapt the recordset statement above for it to select these values. 

strSQL = "SELECT tra.NumeroTRA FROM tra WHERE ((tra.semana)=#" & strDATE & "#) AND ((tra.empleadoId)=" & strEmpID & ");"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    
    strsemana = rs!semana
    strsemana = Replace(strsemana , "'", "''")
    strempleadoId = rs!empleadoId

Else
            strSQL = "INSERT INTO TRA ( semana, empleadoId ) VALUES (#" & strDATE & "#, " & strEMPID & " )"
            'MsgBox strSQL
            CurrentDb.Execute (strSQL)
      rs.movenext
      loop
  
End If

rs.close
Set rs = Nothing

something along those lines...

as for the second part of your code:

    inssql = "select numeroTra from TRA where ((semana='" & semana & "') and (empleadoId=" & CInt(empleadoId) & "));"
    'Set db = OpenDatabase(database)
    Set rcset = db.OpenRecordset(inssql)
    If (Not (rcset.EOF)) Then
        tra = rcset.Fields("numeroTRA")
    Else: MsgBox "Ha habido un error recuperando el numero de tra. Por favor, intente de nuevo, o contacte con su administrador"
    End If

I have no idea wat your tring to achieve here.. it looks like your trying to check that that data has been inserted and if not then produce that message box. Unfortunately i dont speak spanish in any sense or form... anyway have a look at what i've done and see if im on the right lines or i've read what you are trying to do wrong!

cheers

 

[mikela]

HEy!

Thanks a lot for your help! I'll have a look at your suggeriments, and try them!

Sorry for the mess of the code, but i'm junior programmer... Need to learn a lot!

As I said thank you a lot!