Query Tools

[vaniKanabathy]

Hi...im doing a query tools using vb6 that runs a query in access, How can i do so....im confused...plz guide me....

[debasisdas]

You just need to frame the query properly and execute that using ADO objects.

[vaniKanabathy]

Im not understand..plzzzzzz guide..i'll thank 4 u

[debasisdas]

Do you know how to execute a query in VB ?

[vaniKanabathy]

I had do the query n it was success . Thank u.Now i face difficulities in trim character from a coloumn like 101-001 to 101001. It can do by run a query in access directly but in vb6 i cant...

UPDATE Alternator
SET EversparkNo1 = REPLACE(EversparkNo1, '-', '')

this 1 i do in access query directly

[jamesd0142]

I had do the query n it was success . Thank u.Now i face difficulities in trim character from a coloumn like 101-001 to 101001. It can do by run a query in access directly but in vb6 i cant...

UPDATE Alternator
SET EversparkNo1 = REPLACE(EversparkNo1, '-', '')

this 1 i do in access query directly

You can run this query through vb also, you ned to execute a non query...

[vaniKanabathy]

what is non query? Plzzzzz give me some examples...urgent..thanks

[debasisdas]

You can run this query through vb also, you ned to execute a non query...

What is a non query in vb 6.0 ???

[vaniKanabathy]

Do anyone can help me in solve this problem. I need to update my column using query.

[jamesd0142]

What is a non query in vb 6.0 ???

Does vb6 not have something simular to this debasisdas?, I use vb2005 and code such as:

imports system.data.oledb
 
                Dim oConn As New System.Data.OleDb.OleDbConnection(StrConn)
                oConn.Close()
                Dim con As New System.Data.OleDb.OleDbCommand
                con.Connection = oConn
                con.CommandType = CommandType.Text
                con.CommandText = "Insert Statement"
                oConn.Open()
                con.ExecuteNonQuery()
 

Surely you can do something like this in vb6, right...?

[debasisdas]

There is nothing like non query in VB 6.0.

If you do not know the answer in the version that is clearly mentioned by the OP in the question, no need to post what you think is correct in higher version.

No need to post .NET replies to VB 6.0 questions.

Limit those to .NET forums only.

[vaniKanabathy]

Mine is vb6 platform. Plz do help me in correct query to update my field. Plsssssssss i still finding 4 the answer......

[debasisdas]

try to use like this

con.BeginTrans                     'con is the ADODB connection object
con.Execute "your sql query here"
con.CommitTrans
 

[vaniKanabathy]

Type mismatch error occur.Pls help me edit my coding down here.I need it urgently. Thanks in advance.....plzzzzzzzzz help me.


db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Everspark2.mdb"

' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, " - ", "")"
conn.CommitTrans

[vaniKanabathy]

undefine function 'REPLACE' in expression error also occur.

[vaniKanabathy]

No body can help me???????????????plzzzzzzzzzzzz

[debasisdas]

try this

conn.BeginTrans 
conn.Execute "UPDATE Alternator SET EversparkNo1 = " & REPLACE(EversparkNo1, " - ", "")
conn.CommitTrans
 

[vaniKanabathy]

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = " & Replace(EversparkNo1, " - ", "")
conn.CommitTrans

EversparkNo1 that i bold was not define eror....is it a variable? EversparkNo1 is a field in table. How to solve it. Plzzzzzzzz.....

[debasisdas]

if that is a field then what is the meaning of your update statment ?

[vaniKanabathy]

i have one field which is Eversparkno1 where all its data got ' - ' now i want to remove the ' - ' to ' ' nothing, that means if 101-001 i want to change to 101001. so i want to do it by query, this replace successful if run directly in access but..now im doing in vb as a tools for query.

[debasisdas]

try to use this

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = select Replace(EversparkNo1, " - ", "") from Alternator
conn.CommitTrans
 

NOTE :-- Check for the quotes ' and "

[vaniKanabathy]

Type dismatch error occur....plssssssss help me...next week i must present to my boss....

db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Everspark2.mdb"

' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(EversparkNo1, " - ", "") FROM Alternator"
conn.CommitTrans

[vaniKanabathy]

Anyone can help me?????? Plssssssss

[debasisdas]

Type dismatch error occur....

which line causes the error ?

[vaniKanabathy]

conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(EversparkNo1, " - ", "") FROM Alternator"

this line got error

[debasisdas]

run the query directly in database and check if that works .

[vaniKanabathy]

No, the query shows error. This query down here is success.....

UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, " - ", " ")

[vaniKanabathy]

why no one response to my question? plz help me ......i try a lot of way..but all error....

[QVeen72]

Hi,

Try This :

conn.Execute "UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, ' -', '') "
 

Regards
Veena

[vaniKanabathy]

I had try..but Undefine function "REPLACE" in expression error occur. Please Help me.

[prakashsakthivel]

I had try..but Undefine function "REPLACE" in expression error occur. Please Help me.

Hi Friend.

Please make sure that whether You have selected latest library or not regarding (DAO or ADO) so that library should support this function.

Please make sure that which version you are using.


Regards
Musai.

[QVeen72]

Hi,

Yes, "REPLACE" Function will not work with ADO,

Use a Combination of Left and MID and INSTR Functions..

Try This :

conn.Execute "UPDATE Alternator SET " _
& " EversparkNo1 = Left(EversparkNo1,Val(InStr(EversparkNo1,'-')-1))  " _
& " Mid(EversparkNo1,Val(InStr(EversparkNo1,'-')+1))  " _
& " Where EversparkNo1 Like '%-%' "
 

Note: Only First Found "-" is replaced.
If you have multiple occurance of the same character,
say 3 times , then you need to run the same query thrice...

Regards
Veena

[QVeen72]

Hi,

Sorry, "&" was removed.. Try this one:

conn.Execute "UPDATE Alternator SET " _
  & " EversparkNo1 = Left(EversparkNo1,Val(InStr(EversparkNo1,'-')-1))  " _
  & " & Mid(EversparkNo1,Val(InStr(EversparkNo1,'-')+1))  " _
  & " Where EversparkNo1 Like '%-%' "
 

Regards
Veena