Visual Basic

Calferreira12-Jan-09 3:50

12-Jan-09 3:50

Sorry what do you mean by real?Using for exemple the sql code?

Eddy Vluggen12-Jan-09 3:56

12-Jan-09 3:56

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 Smile | :)

I are troll Smile | :)

Calferreira12-Jan-09 3:59

12-Jan-09 3:59

Yup it runs fine.Shows all the records Laugh | :laugh:

Eddy Vluggen12-Jan-09 4:12

Re: Problem Executing Append Query [modified]

12-Jan-09 4:12

If you run the query from VB.NET, as a selection-query (not the append-version), does it work too?

I are troll Smile | :)

Calferreira12-Jan-09 4:36

12-Jan-09 4:36

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

Eddy Vluggen12-Jan-09 4:41

12-Jan-09 4:41

The question remains; if you run a "select"-query, that only returns those rows (without appending them), does it work?

I are troll Smile | :)

Calferreira12-Jan-09 4:48

12-Jan-09 4:48

Yes it returns the rows without adding them to a new table.

Eddy Vluggen12-Jan-09 4:51

12-Jan-09 4:51

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 Smile | :)

Calferreira12-Jan-09 4:57

12-Jan-09 4:57

I tried the raw sql command using only the select without the insert and shows nothing!The problem must be there!

Eddy Vluggen12-Jan-09 5:33

Re: Problem Executing Append Query [modified]

12-Jan-09 5:33

Can you post the SQL statement that you used? Smile | :)

I are troll Smile | :)

Calferreira12-Jan-09 4:53

12-Jan-09 4:53

"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

EliottA12-Jan-09 5:45

12-Jan-09 5:45

Umm just a little thing, I may be way off here but in SQL isn't the wildcard string %?

Calferreira12-Jan-09 6:06

12-Jan-09 6:06

Already found the problem Big Grin | :-D

The problem was the sql query itself not it works but gives me a diferent error D'Oh! | :doh:

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 Smile | :)

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"

EliottA12-Jan-09 6:07

12-Jan-09 6:07

Sorry I don't know VB6 all that well.

EliottA12-Jan-09 6:29

12-Jan-09 6:29

Calferreira wrote:
Set RecSet = SqlCMD.Execute(, , adExecuteNoRecords)

Try
Set RecSet = SqlCMD.Execute(adExecuteNoRecords)?

Eddy Vluggen12-Jan-09 7:52

12-Jan-09 7:52

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 Smile | :)

BTW, you might want to split that complex query into a few smaller ones.

I are troll Smile | :)

Calferreira12-Jan-09 23:57

12-Jan-09 23:57

Found the problem Big Grin | :-D

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 Big Grin | :-D

Thank you all guys you rock! Cool | :cool:

EliottA13-Jan-09 2:59

13-Jan-09 2:59

Told you that wildcard was fishy! Smile | :)

Eddy Vluggen13-Jan-09 3:20

Re: convert string to byte

13-Jan-09 3:20

Well done

I are troll Smile | :)

convert string to byte

elci12-Jan-09 1:01

elci

12-Jan-09 1:01

hello please tell me how to convert string into byte values

N a v a n e e t h12-Jan-09 1:05

N a v a n e e t h

12-Jan-09 1:05

Encoding.GetBytes[^]

Navaneeth

How to use google | Ask smart questions

Re: convert string to byte

EliottA12-Jan-09 1:16