我如何明智地执行搜索商品代码和商品名称 [英] how can i perform search item code wise and item name wise

问题描述

我有一个名称为
的文本框 txtsr
我的客户将在此处输入商品代码或商品名称
我想根据那里的选择进行搜索.

i have one text box with name
txtsr
and my client will enter there item code or item name
and i want to perform search according to there choice.

Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
        Try
            With fg
                Dim str(1) As String
                ssql = Nothing
                If fg.Col = 0 Then
                    ssql = Nothing

                    ssql = "select iname,itmcode,ivalue,balqty  from vewhelp where itmcode like '%" & Trim(Me.txtsr.Text) & "%'  and site='" & loginsite & "' "
                    'ssql = "select iname,itmcode,ivalue,balqty  from vewhelp where iname like '%" & Trim(Me.txtsr.Text) & "%'  and site='" & loginsite & "' and left(itmcode,2)='" & storeID & "'"
                End If
                fn.readqry(ssql)
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                fg.Rows.Count = 1
                fg.Rows.Count = 2
                While dr.Read()
                    .SetData(.Rows.Count - 1, "iname", dr.Item("iname"))
                    .SetData(.Rows.Count - 1, "rate", dr.Item("ivalue"))
                    .SetData(.Rows.Count - 1, "balqty", dr.Item("balqty"))
                    .SetData(.Rows.Count - 1, "icode", dr.Item("itmcode").ToString())
                    .Rows.Count += 1
                End While
                dr.Close()

                fg.Focus()

            End With

            With fg
                Dim str(1) As String
                ssql = Nothing
                If fg.Col = 1 Then
                    ssql = Nothing

                    ssql = "select iname,itmcode,ivalue,balqty  from vewhelp where iname like '%" & Trim(Me.txtsr.Text) & "%'  and site='" & loginsite & "' "
                End If
                fn.readqry(ssql)
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                fg.Rows.Count = 1
                fg.Rows.Count = 2
                While dr.Read()
                    .SetData(.Rows.Count - 1, "iname", dr.Item("iname"))
                    .SetData(.Rows.Count - 1, "rate", dr.Item("ivalue"))
                    .SetData(.Rows.Count - 1, "balqty", dr.Item("balqty"))
                    .SetData(.Rows.Count - 1, "icode", dr.Item("itmcode").ToString())
                    .Rows.Count += 1
                End While
                dr.Close()

                fg.Focus()

            End With


        Catch Excep As Exception
            MessageBox.Show(Excep.Message, "Error,Contact OR Send ()", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

推荐答案

使用OR运算符组合SQL stagtement

Combine the SQL stagtement with an OR operator

Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
        Try
            
            With fg
                Dim str(1) As String
                ssql = Nothing
                If fg.Col = 1 Then
                    ssql = Nothing
 
                    ssql = "select iname,itmcode,ivalue,balqty  from vewhelp where (iname like ''%" & Trim(Me.txtsr.Text) & "%'' or itmcode like ''%" & Trim(Me.txtsr.Text) & "%'') and site=''" & loginsite & "'' "
                End If
                fn.readqry(ssql)
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                fg.Rows.Count = 1
                fg.Rows.Count = 2
                While dr.Read()
                    .SetData(.Rows.Count - 1, "iname", dr.Item("iname"))
                    .SetData(.Rows.Count - 1, "rate", dr.Item("ivalue"))
                    .SetData(.Rows.Count - 1, "balqty", dr.Item("balqty"))
                    .SetData(.Rows.Count - 1, "icode", dr.Item("itmcode").ToString())
                    .Rows.Count += 1
                End While
                dr.Close()
 
                fg.Focus()
 
            End With
 

        Catch Excep As Exception
            MessageBox.Show(Excep.Message, "Error,Contact OR Send ()", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

尝试重新分配参数化查询以避免SQL注入

try to repalce paramererised queries to avoid SQL injection

您可以按以下方式组合搜索:

You can combine the search as follows:

ssql = "select iname,itmcode,ivalue,balqty from vewhelp where (itmcode like '%" & Trim(Me.txtsr.Text) & "%' OR iname like '%" & Trim(Me.txtsr.Text) & "%')  and site='" & loginsite & "' and left(itmcode,2)='" & storeID & "'"

如果直接传递数据而不进行验证，则很容易进行SQL注入.使用参数化查询.示例:

http://social.msdn.microsoft.com /Forums/zh-CN/vbgeneral/thread/6bdf8b71-1cf1-41c0-848c-4fca2c9e1ea2/ [

If you pass the data directly without validation, you are prone to SQL injection. Use parametrized queries. Example:

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6bdf8b71-1cf1-41c0-848c-4fca2c9e1ea2/[^]

hi，
例如:

SELECT * FROM TABLE WHERE COLUMN1就像``TEXTBOX1.TEXT％''

hi ,
example:

SELECT * FROM TABLE WHERE COLUMN1 LIKE ''TEXTBOX1.TEXT%''

这篇关于我如何明智地执行搜索商品代码和商品名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！