Why thinks Vb-script my object is a column

Question

Post reply

Why thinks Vb-script my object is a column

Erik Joergensen

SSC Veteran

Points: 289
More actions
February 24, 2005 at 7:13 am

#164608

I have this and it gives the bug: Invalid column name 'Multigroove' WHY?:
dim GNavn
GNavn = "Multigroove"
connection script, is ok
then my requet to the db:
strSQL = " SELECT dbo.Gruppe.* " &_ " FROM Hovedtabel INNER JOIN " &_ " dbo.Ejer ON Hovedtabel.id_hd = dbo.Ejer.id INNER JOIN " &_ " dbo.Leverandor ON Hovedtabel.id_hd = dbo.Leverandor.Id INNER JOIN " &_ " dbo.Opslag ON Hovedtabel.id_hd = dbo.Opslag.id INNER JOIN " &_ " dbo.Placering ON dbo.Opslag.id = dbo.Placering.placering CROSS JOIN " &_ " dbo.Gruppe CROSS JOIN " &_ " dbo.Anvendelse CROSS JOIN " &_ " dbo.Undergruppe CROSS JOIN " &_ " dbo.Ops_overvkt CROSS JOIN " &_ " dbo.Ops_undervkt " &_ " WHERE dbo.gruppe.navn like " & (GNavn)
and tests and output:
<%= ("<hr>" & strSQL & "<hr>")
oRs.Open strSQL, oConn Response.Write("open string ok")
if oRs.State > 0 then Response.Write("state ok")
Response.Write("result = ") & oRs.RecordCount & (" Records" )
EndLine = "<br/>" & chr(13) & chr(10)
Do While Not (oRs.EOF or oRs.BOF) AND NOT Crit_Error
me.oRs = oRs
RStr = RStr & CStr(oRs("GNavn".value))
Response.Write("<hr/>"" & RStr & "<hr/><br/>")
oRs.MoveNext()
Loop

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 1

Martin D. Cymerman

SSCrazy

Points: 2079

February 24, 2005 at 7:34 am

#543905

Have you tried like 'GNavn'

Erik Joergensen SSC Veteran Points: 289 More actions · Answer 2

Yes, then it's a comment, not an argument, if it's after the " and inside " it doesn't find anything, even i have tried into SQL query analyser and there get more results. in the other way it finds a lot of records before the error.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

You are not enclosing your Where clause criteria in quotes for the query. When SQL Server get your query, it looks like "... WHERE dbo.gruppe.navn like Multigroove ". You need to change your line of code like this:

WHERE dbo.gruppe.navn like '" & (GNavn) & "' "

Then SQL will get "... WHERE dbo.gruppe.navn like 'Multigroove' ".

Also, I am assuming that you are providing the capabiity for a user to enter a wildcard parameter into the Gavn variable. Otherwise, you would probably want to change the "like" to "=", or add appropriate wildcards to the criteria (...like '%" & (GNavn) & "%' ")

I also notice your line: RStr = RStr & CStr(oRs("GNavn".value))

Is the contents of the variable GNavn going to be a column name? That seems to be what you have coded here. Perhaps you meant to write oRs("navn"), as that is the column name you are querying on?

Mark

Erik Joergensen SSC Veteran Points: 289 More actions · Answer 4

Tried this, still 0 records.

The line RStr = RStr & CStr (Ors... was used as a experiment to ensure the data could be used, as they are in many different values, but right now i have made it a comment, as it doesn't have any influence on the case now.

The Value of GNavn i s always an Object, and later i need to put more like it in the sentence, but the primary needs to work before. I try to find an object, in this case Multigroove in the table Gruppe, Column navn (Group & name in english)

My intentions are through a search with 1-3 parameters to identify the object (records with the needed info) and then return all information from all tables of this object. It's needed to find the right tool for the machinery to the specific job. I have 13 tables, but wish to have the possibility to use maybe 8 of them for search, but still through 1-3 parameters; the rest are 'just' info to add.

Do you now understand my situation and query?

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

Put your logic in a stored procedure and call that from vb. What you are trying to do it possible, but much more difficult in VB then using tSQL.

cl

Signature is NULL

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

The Value of GNavn is always an Object...

SQL Server can only handle text in a query. It cannot serialize an object to collect and store its data in a column to be searchable (at least not in 2000. I hear that is a XML feature in 2005.) Even storing and retrieving binary data in blobs has to convert the data to text under the covers.

Do you now understand my situation and query?

I think I understand what you are trying to accomplish. In the past, I have found it easier to develop and maintain if I did this using UNION queries. I will try to demonstrate using your query, although I will make up some field names. I assume that the CROSS JOIN tables are the ones you want to search on, and the INNER JOIN tables just add some info to the results. I will leave the INNER Join tables off for simplification.

"SELECT 'Gruppe' as TableSelected, navn, fielda, fieldb " & _ 
"FROM Hovedtabel " & _ 
"WHERE navn like '%" & (GNavn.value) & "%' " & _ 
"UNION ALL " & _ 
"SELECT 'Anvendelse' as TableSelected, navn, fielda, fieldb " & _ 
"FROM Anvendelse " & _ 
"WHERE navn like '%" & (GNavn.value) & "%' " & _ 
"UNION ALL " & _ 
"SELECT 'Undergruppe' as TableSelected, navn, fielda, fieldb " & _ 
"FROM Undergruppe " & _ 
"WHERE navn like '%" & (GNavn.value) & "%' " & _ 
"UNION ALL " & _ 
"SELECT 'Ops_overvkt' as TableSelected, navn, fielda, fieldb " & _ 
"FROM Ops_overvkt " & _ 
"WHERE navn like '%" & (GNavn.value) & "%' " & _ 
"UNION ALL " & _ 
"SELECT 'Ops_undervkt' as TableSelected, navn, fielda, fieldb " & _ 
"FROM Ops_undervkt " & _ 
"WHERE navn like '%" & (GNavn.value) & "%' "

Remember that each query must return the same number of columns, and each column in order must be the same type. If you have a table that does not have the column you want, then insert a column in the query with a null or empty string or default value. Adding the TableSelected value tells you in the recordset which table the row came from.

Hope this helps.

Mark