RE: sending a query to access in asp.net

Discussion in 'ASP .Net' started by Steven Cheng[MSFT], Oct 11, 2005.

  1. Hi Ankit,

    Welcome to ASPNET newsgroup.
    Regarding on the ACCESS sql query statement issue you mentioned, based on
    research, it is caused by the OLDDBProvider's behavior. And here is the
    way to resolve the problem:

    1. For such widchar query in SQLStatement, generally we should always put
    the widchars in the parameter's Value rather in its name. That means, we
    should construct the parameter like:

    Dim param As New OleDb.OleDbParameter("@name", OleDb.OleDbType.VarChar)
    param.Value = "*name1*"


    2. While the ACCESS database use '*' as the widchar for like syntax, the
    OLEDB provider dosn't. OldDB Provider has defined a unified syntax so that
    no matter what the underlying database is , it will accept the unified
    syntax it requiring. For the text searching , OLEDB require the client
    code use '%' rather than '*' or any other widchar, so actually the final
    code should be something like;


    conn.Open()

    comm = New OleDb.OleDbCommand(sql, conn)

    Dim param As New OleDb.OleDbParameter("@name",
    OleDb.OleDbType.VarChar)
    param.Value = "%name1%"

    comm.Parameters.Add(param)

    rdr = comm.ExecuteReader(CommandBehavior.CloseConnection)

    Hope helps. BTW, for such data accessing issue, you can also post it in the
    dotnet.framework.adonet newsgroup since that's the data accessing specific
    group.

    Thanks,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security


    --------------------
    | From: "Ankit Aneja" <>
    | Subject: sending a query to access in asp.net
    | Date: Tue, 11 Oct 2005 11:14:32 +0530
    | Lines: 75
    | X-Priority: 3
    | X-MSMail-Priority: Normal
    | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
    | Message-ID: <#>
    | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | NNTP-Posting-Host: dsl-upwest-static-069.7.246.61.touchtelindia.net
    61.246.7.69
    | Path:
    TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP0
    9.phx.gbl
    | Xref: TK2MSFTNGXA02.phx.gbl
    microsoft.public.dotnet.framework.aspnet:349863
    | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    |
    |
    | hi i want to be a this query in access with asp.net "select * from autores
    | where nombre like *@nombre* " but (*) this character is dont support in
    the
    | query.... send this error
    |
    | Syntax error (missing operator) in query expression 'nombre like
    | ('*')@nombre('*')'.
    | Description: An unhandled exception occurred during the execution of the
    | current web request. Please review the stack trace for more information
    | about the error and where it originated in the code.
    |
    | Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing
    | operator) in query expression 'nombre like ('*')@nombre('*')'.
    |
    |
    | I want to bring all the record that take or start with x word in someplace
    | example: it gives all records that take a text the word 'da' example ...
    | dany |dalila|dasces|darknes|this query in Access, SQL view thats work good
    | but when i try to send in asp.net dont work: ( whats happening....
    |
    | sub busca (s as object, e as eventargs)
    | dim objconn as oledbconnection
    | dim objcmd as oledbcommand
    | dim objrdr as oledbdatareader
    |
    | objconn = new oledbconnection( _
    | "Provider=Microsoft.Jet.Oledb.4.0; " & _
    | "data source=c:\Inetpub\wwwroot\kayun.mdb")
    | objcmd = new oledbcommand( _
    | "select * from autores where nombre like *@nombre*" ,objconn)
    | objcmd.parameters.add("@nombre",texto.text)
    | objconn.open()
    | objrdr = objcmd.executereader()
    | 'while objrdr.read()
    | 'response.write("Nombre: " & objrdr.item("nombre") & "<br />")
    | 'response.write("correo: " & objrdr.item("correo") & "<br />")
    | 'end while
    | repetidor.datasource= objrdr
    | repetidor.databind()
    | objrdr.close()
    | objconn.close()
    | end sub
    |
    |
    |
     
    Steven Cheng[MSFT], Oct 11, 2005
    #1
    1. Advertising

  2. Hi Ankit,

    How are you doing on this issue and does the suggestions in my last reply
    helps a little? If there're anything else we can help, please feel free to
    post here.

    Thanks,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    --------------------
    | X-Tomcat-ID: 167401090
    | References: <#>
    | MIME-Version: 1.0
    | Content-Type: text/plain
    | Content-Transfer-Encoding: 7bit
    | From: (Steven Cheng[MSFT])
    | Organization: Microsoft
    | Date: Tue, 11 Oct 2005 08:39:43 GMT
    | Subject: RE: sending a query to access in asp.net
    | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    | Message-ID: <>
    | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | Lines: 100
    | Path: TK2MSFTNGXA02.phx.gbl
    | Xref: TK2MSFTNGXA02.phx.gbl
    microsoft.public.dotnet.framework.aspnet:349879
    | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
    |
    | Hi Ankit,
    |
    | Welcome to ASPNET newsgroup.
    | Regarding on the ACCESS sql query statement issue you mentioned, based on
    | research, it is caused by the OLDDBProvider's behavior. And here is the
    | way to resolve the problem:
    |
    | 1. For such widchar query in SQLStatement, generally we should always
    put
    | the widchars in the parameter's Value rather in its name. That means, we
    | should construct the parameter like:
    |
    | Dim param As New OleDb.OleDbParameter("@name", OleDb.OleDbType.VarChar)
    | param.Value = "*name1*"
    |
    |
    | 2. While the ACCESS database use '*' as the widchar for like syntax, the
    | OLEDB provider dosn't. OldDB Provider has defined a unified syntax so
    that
    | no matter what the underlying database is , it will accept the unified
    | syntax it requiring. For the text searching , OLEDB require the client
    | code use '%' rather than '*' or any other widchar, so actually the final
    | code should be something like;
    |
    |
    | conn.Open()
    |
    | comm = New OleDb.OleDbCommand(sql, conn)
    |
    | Dim param As New OleDb.OleDbParameter("@name",
    | OleDb.OleDbType.VarChar)
    | param.Value = "%name1%"
    |
    | comm.Parameters.Add(param)
    |
    | rdr = comm.ExecuteReader(CommandBehavior.CloseConnection)
    |
    | Hope helps. BTW, for such data accessing issue, you can also post it in
    the
    | dotnet.framework.adonet newsgroup since that's the data accessing
    specific
    | group.
    |
    | Thanks,
    |
    | Steven Cheng
    | Microsoft Online Support
    |
    | Get Secure! www.microsoft.com/security
    |
    |
    | --------------------
    | | From: "Ankit Aneja" <>
    | | Subject: sending a query to access in asp.net
    | | Date: Tue, 11 Oct 2005 11:14:32 +0530
    | | Lines: 75
    | | X-Priority: 3
    | | X-MSMail-Priority: Normal
    | | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
    | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
    | | Message-ID: <#>
    | | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | | NNTP-Posting-Host: dsl-upwest-static-069.7.246.61.touchtelindia.net
    | 61.246.7.69
    | | Path:
    |
    TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP0
    | 9.phx.gbl
    | | Xref: TK2MSFTNGXA02.phx.gbl
    | microsoft.public.dotnet.framework.aspnet:349863
    | | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    | |
    | |
    | | hi i want to be a this query in access with asp.net "select * from
    autores
    | | where nombre like *@nombre* " but (*) this character is dont support in
    | the
    | | query.... send this error
    | |
    | | Syntax error (missing operator) in query expression 'nombre like
    | | ('*')@nombre('*')'.
    | | Description: An unhandled exception occurred during the execution of the
    | | current web request. Please review the stack trace for more information
    | | about the error and where it originated in the code.
    | |
    | | Exception Details: System.Data.OleDb.OleDbException: Syntax error
    (missing
    | | operator) in query expression 'nombre like ('*')@nombre('*')'.
    | |
    | |
    | | I want to bring all the record that take or start with x word in
    someplace
    | | example: it gives all records that take a text the word 'da' example ...
    | | dany |dalila|dasces|darknes|this query in Access, SQL view thats work
    good
    | | but when i try to send in asp.net dont work: ( whats happening....
    | |
    | | sub busca (s as object, e as eventargs)
    | | dim objconn as oledbconnection
    | | dim objcmd as oledbcommand
    | | dim objrdr as oledbdatareader
    | |
    | | objconn = new oledbconnection( _
    | | "Provider=Microsoft.Jet.Oledb.4.0; " & _
    | | "data source=c:\Inetpub\wwwroot\kayun.mdb")
    | | objcmd = new oledbcommand( _
    | | "select * from autores where nombre like *@nombre*" ,objconn)
    | | objcmd.parameters.add("@nombre",texto.text)
    | | objconn.open()
    | | objrdr = objcmd.executereader()
    | | 'while objrdr.read()
    | | 'response.write("Nombre: " & objrdr.item("nombre") & "<br />")
    | | 'response.write("correo: " & objrdr.item("correo") & "<br />")
    | | 'end while
    | | repetidor.datasource= objrdr
    | | repetidor.databind()
    | | objrdr.close()
    | | objconn.close()
    | | end sub
    | |
    | |
    | |
    |
    |
     
    Steven Cheng[MSFT], Oct 14, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jon
    Replies:
    2
    Views:
    2,133
    Kevin Spencer
    Oct 12, 2005
  2. Replies:
    0
    Views:
    1,377
  3. Eric Nelson
    Replies:
    5
    Views:
    1,533
    Alexey Smirnov
    Feb 4, 2009
  4. Kent
    Replies:
    2
    Views:
    121
  5. Roland Hall

    ASP LIKE query using parameterized query

    Roland Hall, Jan 12, 2007, in forum: ASP General
    Replies:
    8
    Views:
    225
    Roland Hall
    Jan 13, 2007
Loading...

Share This Page