VS 2010数据集设计器查询语法 [英] VS 2010 dataset designer query syntax

问题描述

我有一个用vb.net编写的查询，它允许用户通过提供列名和一些标准来搜索数据库。此查询从表单的后面按预期工作，但是当查询添加到数据集设计器中的tableadapter时，它不再起作用。工作查询如下;

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;

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;

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.

推荐答案

你写了这个查询：

You wrote this query:

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

it作为存储过程，如果是，那么wh如果你正在使用存储过程名称，

而不是你使用QRY，



其次，不要使用直接sql查询进行搜索，因为任何其他数据库专家可以从搜索栏中删除您的数据，只需在搜索框中写入删除％x％。



所以，尝试使用存储过程并调用它，



我的存储程序：

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：

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();
           }

       }

希望你明白，如果适合你，然后PLZ接受它作为答案n指定费率。



thnx

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

thnx

这篇关于VS 2010数据集设计器查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！