AndyAelbrecht
asked on
Query with TableAdapter and variable field names
Hey guys,
I need some little help with a stupid problem I'm having with VB.NET in VS2005:
I'm using a bunch of TableAdapters, all with extra queries attached to them.
One of my goals is to be able to query all the fields in TableAdapter; this TableAdapter is filling a Dataset, which is linked to a DataGridView.
There are several options here, but I'm really looking for the shortest code-path, ofcourse.
Now, I would like to make a query like this: "SELECT * FROM tablename WHERE @id1 = @val1"
But, when using this in the tableadapter, ofcourse, everything gets sql-ized.
in other words, after doing:
me.ta.FillByColnameVal(me.
I don't get any results (most likely because the query now becomes SELECT * FROM tablename WHERE "fieldname" = "fieldvalue", which ofcourse doesn't work)
Is there a way around this or shall i just use the dt.select("fieldname like fieldvalue") method ? (I rather use the TableAdapter tbh)
thx in advance guys,
cheers,
Andy
I need some little help with a stupid problem I'm having with VB.NET in VS2005:
I'm using a bunch of TableAdapters, all with extra queries attached to them.
One of my goals is to be able to query all the fields in TableAdapter; this TableAdapter is filling a Dataset, which is linked to a DataGridView.
There are several options here, but I'm really looking for the shortest code-path, ofcourse.
Now, I would like to make a query like this: "SELECT * FROM tablename WHERE @id1 = @val1"
But, when using this in the tableadapter, ofcourse, everything gets sql-ized.
in other words, after doing:
me.ta.FillByColnameVal(me.
I don't get any results (most likely because the query now becomes SELECT * FROM tablename WHERE "fieldname" = "fieldvalue", which ofcourse doesn't work)
Is there a way around this or shall i just use the dt.select("fieldname like fieldvalue") method ? (I rather use the TableAdapter tbh)
thx in advance guys,
cheers,
Andy
ASKER
I'd like an example of that please ?
ATM, I did it by making an extensively long query, but ran into a litlle caveat as you can't match Integers with the LIKE operator in MSSQL (long live MySQL tbh)
so I now have a query with 2 parameters, which atm is as close to a solution as i possibly can.
the caveat mentioned before, however, makes me unable to search in integer fields.
i have:
SELECT * FROM tablename WHERE intfield = @ID1 OR intfield2 = @ID1 OR txtfield1 LIKE @ID2 OR txtfield2 LIKE @ID2
when filling ID1 and ID2, I generally fill ID2 with "%" & searchterm & "%": but doing this for ID1 doesn't work, obviously.
so, i'm still searching for my solution...
ATM, I did it by making an extensively long query, but ran into a litlle caveat as you can't match Integers with the LIKE operator in MSSQL (long live MySQL tbh)
so I now have a query with 2 parameters, which atm is as close to a solution as i possibly can.
the caveat mentioned before, however, makes me unable to search in integer fields.
i have:
SELECT * FROM tablename WHERE intfield = @ID1 OR intfield2 = @ID1 OR txtfield1 LIKE @ID2 OR txtfield2 LIKE @ID2
when filling ID1 and ID2, I generally fill ID2 with "%" & searchterm & "%": but doing this for ID1 doesn't work, obviously.
so, i'm still searching for my solution...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OOPS! For creating SP type CREATE PROCEDURE instead of ALTER PROCEDURE
ASKER
I have used a slightly different approach (and am not at the customer atm so I can't post my mods) but vadim brought me to the right solution.
cheers dude !
cheers dude !
There's only one way around: stored procedure.