Solved

ERROR in TableAdapter Query

Posted on 2006-11-24
12
505 Views
Last Modified: 2010-05-18
It Generates a ERROR with Parmeter.  I am trying to Create a Query in TableAdapter with a Parameter so I can take a Textbox.Text  on the Form and Filter results.  Below is the Button Event Sub that contains code that Filters the records. Second is the SQL in the TableAdapter.  It is interesting to note that SearchString is BLUE UNDERLINED in the second line of code in cmdAdd_click sub.   I am using Access backend, but I also need to be able to use SQL Server back-end.


 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
    Dim SearchString As String = "%" & Me.txtCatalogNumberLike.Text & "%"

    Me.TblInventoryTableAdapter.FillCatalogNumberBy(Me._InventoryControlBeta0_998_beDataSet.tblInventory,   SearchString)

 End Sub




Here is the Query under TableAdapter

SELECT     InventoryID, SupplierID, CatalogNumber, CatagoryPart, EDPNo, Description, ListPrice, OHB, ROP, OOQ, StockCheckDate, UnitofQuantity,
                      LocationNumber, Reorder, RestockQty
FROM         tblInventory
WHERE     (CatalogNumber = @Test)

Thank You  500 pts.
0
Comment
Question by:PC_tech22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 6

Expert Comment

by:manch
ID: 18010798

U must pass a sql parameter with ur query :

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click


Dim pObjSQLParameter as SQLParameter

            pobjSQLParameter = New SqlParameter("@Test", SqlDbType.NVarChar, sizeoftheParameter)
            pobjSQLParameter.Value = Me.txtCatalogNumberLike.Text
             Me.TblInventoryTableAdapter.FillCatalogNumberBy(Me._InventoryControlBeta0_998_beDataSet.tblInventory,   pobjSQLParameter)

End Sub

Try It



0
 
LVL 18

Expert Comment

by:vbturbo
ID: 18010811
Hi

Try

TblInventoryTableAdapter.FillCatalogNumberBy(_InventoryControlBeta0_998_beDataSet.tblInventory, '" & SearchString & "')


0
 
LVL 34

Expert Comment

by:Sancler
ID: 18010823
As I don't personally use TableAdapters I can't be sure.  But general principles would suggest that if you are using wildcards - i.e. % - you ought to be using the LIKE operator in the query rather than =.

What I do not know is whether, for a tableadapter query, that means you should put the wildcards in the query, on lines similar to this

WHERE     (CatalogNumber LIKE '%' & @Test & '%')

or you can simply change that to

WHERE     (CatalogNumber LIKE @Test)

and leave the SearchString as it is.

You might like to experiment on that basis.  Or wait for an answer from someone who does use TableAdapters with wildcard queries.

Roger
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PC_tech22
ID: 18010833
Me.TblInventoryTableAdapter.FillCatalogNumberBy(Me._InventoryControlBeta0_998_beDataSet.tblInventory,   SearchString)

SearchString is BLUE UNDERLINED
tried vbturbo's modifiation still no seccess
0
 

Author Comment

by:PC_tech22
ID: 18010837
0
 

Author Comment

by:PC_tech22
ID: 18010839
TblInventoryTableAdapter.FillCatalogNumberBy (<First>,<Second>)


<Second> does not work, BLUE UNDERLINE
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 18012514
Hi

try this and see if the blue underline disappers

SELECT     InventoryID, SupplierID, CatalogNumber, CatagoryPart, EDPNo, Description, ListPrice, OHB, ROP, OOQ, StockCheckDate, UnitofQuantity,
                      LocationNumber, Reorder, RestockQty
FROM         tblInventory
WHERE (CatalogNumber LIKE '%" & Me.txtCatalogNumberLike.Text & "%')

and

 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
    'Dim SearchString As String = "%" & Me.txtCatalogNumberLike.Text & "%"
    Dim Me.txtCatalogNumberLike.Text ="Test"
    Me.TblInventoryTableAdapter.FillCatalogNumberBy(Me._InventoryControlBeta0_998_beDataSet.tblInventory, "%" & Me.txtCatalogNumberLike.Text & "%")

 End Sub

vbturbo
0
 

Author Comment

by:PC_tech22
ID: 18025288
Give me some time on this.  By the way vbturbo, I looked at similar questions you answered and you recomanded a Website with an online book.  It was awesome, some site from England, I printed ch12 off at kinko's and bought a small 3ringed binder ~$6.50 later it teaches both ways of Data-binding Objects GUI  (click and drag) way and ADO Code.  I have it bookmarked, but not here.  There are a few chapters I like,  maybe the author will create an Advanced Edition, I will buy whatever they have.  I have to buy a Int'l Calling card and ask.  Thanks alot, for recommanding this website.  I will try and find the link/orignal question and post it here with the online book.  I wished it covered Queries more and Command and Parameters of the DataAdapter.   If it did it would answer even this Question.  Are there any other websites you would recommand.  I would also create is as another question.

It try this solution maybe in another day.


Thanks for your help
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 500 total points
ID: 18026132
Microsoft has good link as well

http://msdn2.microsoft.com/en-us/library/14h07e10(vs.80).aspx

vbturbo
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 18137336
Hi  PC_tech22

You properly found solution to your problem but i came accross this solution to your problem.

As Sancler suggested try use the like operator like this


SELECT     InventoryID, SupplierID, CatalogNumber, CatagoryPart, EDPNo, Description, ListPrice, OHB, ROP, OOQ, StockCheckDate, UnitofQuantity,
                      LocationNumber, Reorder, RestockQty
FROM         tblInventory
WHERE     (CatalogNumber LIKE @Test)


And in the query on the TableAdapter > Properties > Parameters >"Collection" ,set the DBType to "String".



 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
    Dim SearchString As String = "%" & Me.txtCatalogNumberLike.Text & "%"

    Me.TblInventoryTableAdapter.FillCatalogNumberBy(Me._InventoryControlBeta0_998_beDataSet.tblInventory,   SearchString)

 End Sub

then the BLUE UNDERLINE should disappear

vbturbo
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question