|
Doing
Msgbox MyCnn.State
Gives me 1 as result so the connection is fine.
|
|
|
|
|
"Append"-queries are weird things. What happens when you try and use a "real" update-query?
I are troll
|
|
|
|
|
Sorry what do you mean by real?Using for exemple the sql code?
|
|
|
|
|
Open the query that your executing in MS-Access, in the "design view". You'll notice that it's an "append"-query, as you mentioned. These queries add the selected records to a table.
If you open the query in SQL-designview, you'll notice that it has a "SELECT INTO". Change the type to a "SELECT"-query - one that doesn't add records to a new table, but that just "selects", nothing more.
You may want to verify your SQL in that particular query
I are troll
|
|
|
|
|
Yup it runs fine.Shows all the records
|
|
|
|
|
If you run the query from VB.NET, as a selection-query (not the append-version), does it work too?
I are troll
|
|
|
|
|
I don't have VB.Net atm only vb6.
Don't know if it helps but the sql code in the query is the following:
INSERT INTO Substancias ( CODINTERNO, NOMESUBSTANCIA, NEC, NCAS, ESTADOFISICO, SECTOR, UTILIZAÇÃO, DENSIDADE, GRAUP, FRISCO, COV, CATSEVESO, QTARM, DATAFICHASEG, FORNECEDOR, ORIGEMFORN )
SELECT Artigos.CODIGO, Artigos.NOME, Substancias.NEC, Substancias.NCAS, Substancias.ESTADOFISICO, Substancias.SECTOR, Substancias.UTILIZAÇÃO, Substancias.DENSIDADE, Substancias.GRAUP, Substancias.FRISCO, Substancias.COV, Substancias.CATSEVESO, Substancias.QTARM, Substancias.DATAFICHASEG, Substancias.FORNECEDOR, Substancias.ORIGEMFORN
FROM Artigos LEFT JOIN Substancias ON Artigos.CODIGO = Substancias.CODINTERNO
WHERE (((Artigos.NOME) Like "acido*" Or (Artigos.NOME) Like "Oleo*" Or (Artigos.NOME) Like "cola*" Or (Artigos.NOME) Like "diluente*" Or (Artigos.NOME) Like "purpurina*" Or (Artigos.NOME) Like "tinta*" Or (Artigos.NOME) Like "verniz*"));
Funny thing is that i tried to execute the sql code directly and the result is the same nothing happens...
modified on Monday, January 12, 2009 10:44 AM
|
|
|
|
|
The question remains; if you run a "select"-query, that only returns those rows (without appending them), does it work?
I are troll
|
|
|
|
|
Yes it returns the rows without adding them to a new table.
|
|
|
|
|
Could it be that VB6 just doesn't like the "append"-type query?
Can you get the SQL-statement that is associated with your selection-query? If so, you could try and execute the 'raw' SQL. If that succeeds, add an "INSERT INTO" before the "SELECT"-statement.
Ehr.. one thing at a time - can you execute the query as an SQL-statement? Can you post a simplified version of the SQL here?
I are troll
|
|
|
|
|
I tried the raw sql command using only the select without the insert and shows nothing!The problem must be there!
|
|
|
|
|
Can you post the SQL statement that you used?
I are troll
|
|
|
|
|
"SELECT ARTIGOS.CODIGO, ARTIGOS.NOME " & _
"FROM ARTIGOS LEFT JOIN Substancias ON ARTIGOS.CODIGO = Substancias.CODINTERNO " & _
"WHERE (((ARTIGOS.NOME) Like 'Acido*' or 'Cola*' or 'Purpurina*'))"
Now i have another problem although i filter to some keywords as you can see it returns all rows.
Maybe this can only be solved with a Stored Procedure..
modified on Monday, January 12, 2009 11:42 AM
|
|
|
|
|
Umm just a little thing, I may be way off here but in SQL isn't the wildcard string %?
|
|
|
|
|
Already found the problem The problem was the sql query itself not it works but gives me a diferent error
The sql query i'm using now is the following:
INSERT INTO Substancias ( CODINTERNO, NOMESUBSTANCIA )
SELECT ARTIGOS.CODIGO, ARTIGOS.NOME
FROM ARTIGOS
WHERE (((ARTIGOS.NOME) Like "Cola*" Or (ARTIGOS.NOME) Like "Tinta*") AND ((Exists (SELECT * FROM SUBSTANCIAS WHERE ARTIGOS.CODIGO=SUBSTANCIAS.CODINTERNO))=False));
Much more simple
The problem is when i execute the following code :
Dim SqlCMD As Command
Set SqlCMD = New Command
SqlCMD.CommandText = "CRegSubAdicNulo"
SqlCMD.CommandType = adCmdStoredProc
SqlCMD.ActiveConnection = MyCnn
Set RecSet = SqlCMD.Execute(, , adExecuteNoRecords)
Gives the error "Few Parameters.1 Expected"
|
|
|
|
|
Sorry I don't know VB6 all that well.
|
|
|
|
|
Calferreira wrote: Set RecSet = SqlCMD.Execute(, , adExecuteNoRecords)
Try
Set RecSet = SqlCMD.Execute(adExecuteNoRecords)?
|
|
|
|
|
2 steps to debugging:
1) make it simpeler, until you got something that works
once you got it working, proceed to step 2;
2) re-add complexity, validating each step
SELECT *
FROM ARTIGOS
WHERE ARTIGOS.NOME Like "Cola%"
This query should return rows
BTW, you might want to split that complex query into a few smaller ones.
I are troll
|
|
|
|
|
Found the problem
The problem was,when i executed the query in access was filtering using * for example "Select * From Cars where brand='F*'" as someone posted before by using % instead of * Solved the problem
Thank you all guys you rock!
|
|
|
|
|
Told you that wildcard was fishy!
|
|
|
|
|
Well done
I are troll
|
|
|
|
|
hello please tell me how to convert string into byte values
|
|
|
|
|
|
Not fair, I was writing a witty comment and you totally beat me to the punch with your short, concise and (probably more) helpful answer (then I would have written)!! In short,
|
|
|
|
|
Why don't you share your "witty comment" anyway?; then we can judge you accordingly
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|