[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

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.ds.dt, "fieldname", "fieldvalue")

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
0
AndyAelbrecht
Asked:
AndyAelbrecht
  • 3
  • 2
1 Solution
 
vadim63Commented:
Hi,
There's only one way around: stored procedure.
0
 
AndyAelbrechtAuthor Commented:
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...
0
 
vadim63Commented:
USE [YourDBname]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_FindByColumn]
@colName Varchar(20),
@colVal Varchar(20)
AS
BEGIN
DECLARE @Query Varchar(1000)
SET @query= 'SELECT * FROM YourTable WHERE ' + @colName + ' LIKE ' + @colVal + ''
EXEC(@Query)
END


That's what I'm using. It works fine.
0
 
vadim63Commented:
OOPS! For creating SP type CREATE PROCEDURE instead of ALTER PROCEDURE
0
 
AndyAelbrechtAuthor Commented:
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 !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now