Sql syntax error due to parameter

[MattFalz]

Hi all,

I'm a beginner in Ado.net and met this problem which i could not solve nor find on the net.

The following is my code;

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] cmbClients_TextChanged([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] cmbClients.TextChanged
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]oledbDaCT = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"SELECT clnt_pk, clnt_CompanyName from tb_Clients"[/COLOR][/SIZE][SIZE=2], mycon)
oledbDaCT.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#800000]"Select clnt_pk, clnt_CompanyName From tb_Clients Where clnt_CompanyName like '@clnt_CompanyName%' "[/COLOR][/SIZE][SIZE=2], mycon)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] para = oledbDaCT.SelectCommand.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#800000]"@clnt_CompanyName"[/COLOR][/SIZE][SIZE=2], cmbClients.Text)
DSETClient = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet([/SIZE][SIZE=2][COLOR=#800000]"DSETClient"[/COLOR][/SIZE][SIZE=2])
oledbDaCT.Fill(DSETClient, [/SIZE][SIZE=2][COLOR=#800000]"tb_Clients"[/COLOR][/SIZE][SIZE=2])
cmbClients.DataSource = DSETClient.Tables([/SIZE][SIZE=2][COLOR=#800000]"tb_Clients"[/COLOR][/SIZE][SIZE=2])
cmbClients.ValueMember = [/SIZE][SIZE=2][COLOR=#800000]"clnt_pk"
[/COLOR][/SIZE][SIZE=2]cmbClients.DisplayMember = [/SIZE][SIZE=2][COLOR=#800000]"clnt_CompanyName"
 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
Debug.WriteLine(ex.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]Throw
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

this code does not give any errors but does not do what i require (ie
the combobox must show that client that matches the same character inserted by the user)


Now if i hardcode it into the following and comment para variable the code works fine

oledbDaCT.SelectCommand = [SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#800000]"Select clnt_pk, clnt_CompanyName From tb_Clients Where clnt_CompanyName like 'D%' "[/COLOR][/SIZE][SIZE=2], mycon)[/SIZE]
[SIZE=2][SIZE=2][SIZE=2][COLOR=#008000]'Dim para = oledbDaCT.SelectCommand.Parameters.AddWithValue("@clnt_CompanyName", cmbClients.Text)
[/COLOR][/SIZE][/SIZE][/SIZE]

The above is not what i need so the problem must be in the parmeter which to me seems fine how i coded it.

Can anyone find how to solve it?
(must prob its a stupid mistake that im doing)


Thanks in Advance,

Matt

 

[Schenz]

Need to change two lines:

oledbDaCT.SelectCommand = New SqlCommand("Select clnt_pk, clnt_CompanyName From tb_Clients Where clnt_CompanyName like '@clnt_CompanyName' ", mycon)
Dim para = oledbDaCT.SelectCommand.Parameters.AddWithValue("@clnt_CompanyName", cmbClients.Text & "%")

 

[TechGnome]

Not convinced that will work..... if not try this:

oledbDaCT.SelectCommand = New SqlCommand("Select clnt_pk, clnt_CompanyName From tb_Clients Where clnt_CompanyName like (@clnt_CompanyName  & '%')", mycon)
Dim para = oledbDaCT.SelectCommand.Parameters.AddWithValue("@clnt_CompanyName", cmbClients.Text)

-tg