VS 2010 dataset designer query syntax

Question

0.00/5 (No votes)

See more:

VS2010

Hi,

I have a query written in vb.net that allows a user to search a database by providing a column name and some criteria. This query works as intended from the back of a form, but when the query is added to a tableadapter in the dataset designer it no longer works. The working query is below;

VB

Public Function PopulateReportViewerComboBoxes(ByVal ColumnName as String, ByVal Criteria As String)

    Dim con As New SqlCeConnection(DBcon)
    Dim cmd As SqlCeCommand
    Dim read As SqlCeDataReader
    Dim QRY As String
    Dim Results As New ArrayList

    QRY = "SELECT ClientName FROM Clients WHERE " & ColumnName & " = '" & Criteria & "'"

    Try
        con.Open()
        cmd = New SqlCeCommand(QRY, con)
        read = cmd.ExecuteReader
        While read.Read()
            For i = 0 To read.FieldCount - 1
                Results.Insert(i, read(0))
            Next
        End While
        cmd.Dispose()
    Catch ex As Exception
       msgbox(ex.tostring
    End Try
    con.Close()
    Return Results
End Function

The query for the data designer is as follows;

VB

SELECT ClientName
       FROM Clients
       WHERE (@ColumnName = @Criteria)

The query doesn't return any results, but also doesn't throw an error.

Some help to point me in the right direction here would be greatly appreciated.

Posted 21-Jan-14 3:21am

andrewn1978

Updated 21-Jan-14 3:24am

André Kraak

v2

Add a Solution

Comments

Ron Beyer 21-Jan-14 9:56am

When you run the query from the query designer do you give it the parameters @ColumnName and @Criteria?

andrewn1978 21-Jan-14 19:52pm

Hi Ron. Yes I have tried running the query in the query designer. There is a pop-up box that asks for the parameters. I have typed in parameters for existing data in the table, but no results are found.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abdul subhan mohammed · Answer 1 · 2014-01-21T04:13:00

You wrote this query:

SQL

SELECT ClientName
       FROM Clients
       WHERE (@ColumnName = @Criteria)

it works as a stored procedure, if yes, then where you are using stored procedure name,
instead you are using QRY,

secondly, dont use direct sql query for search, because any other database expert can delete your data from search bar, just by write delete % x% in your search box.

so, try to use stored procedure and call it,

MY STORED PROCEDURE:

USE [ITAccessories]
GO
/****** Object:  StoredProcedure [dbo].[sp_searchUser]    Script Date: 01/21/2014 18:10:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_searchUser]
@user_name nvarchar(50),
@user_code nvarchar(50),
@dept nvarchar(50)

as begin

-- sp_searchUser null, null, 'dril'

SELECT [UserID]
      ,[UserCode]
      ,[Name]
      ,[PFCode]
      ,[Email]
      ,[CellNo]
      ,[PositionID]
      ,d.DepartmentName 
      ,b.BranchName 
      ,c.CompanyName 
  FROM [ITAccessories].[dbo].[Users] u
  LEFT outer join Departments d on u.DepartmentID =  d.DepartmentID    
  LEFT outer join Branches b on u.BranchID = b.BranchID    
  LEFT outer join Companies c on c.CompanyID = b.CompanyID  
  where u.[Name] like '%' + @user_name +'%' 
		  or d.DepartmentName like  '%' + @dept + '%' 
		  or u.PFCode like '%' + @user_code + '%'


end

c# code:

protected void btnsearch_Click(object sender, EventArgs e)
      {
          string srch = txtsearch.Text;

          Repeater1.DataSource = bm.GetUserInfo(srch);
          Repeater1.DataBind();


      }

called function...

public DataTable GetUserInfo(string name)
       {
           SqlConnection conn = null;
           DataTable tb = null;
           SqlParameter[] Para = new SqlParameter[3];
           Para[0] = new SqlParameter("@user_name", name);
           Para[1] = new SqlParameter("@user_code", name);
           Para[2] = new SqlParameter("@dept", name);
           try
           {
               conn = new SqlConnection(GetConnectionString());
               DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sp_searchUser", Para);
               tb = ds.Tables[0];
               return tb;
           }
           catch (Exception)
           {

               throw;
           }
           finally
           {
               conn.Close();
           }

       }

Hope u understand it, if works for you, then plz accept it as answer n assign rates.

thnx