Solved

ERROR in TableAdapter Query

Posted on 2006-11-24
12
500 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
12 Comments
 
LVL 6

Expert Comment

by:manch
Comment Utility

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
Comment Utility
Hi

Try

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


0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
 

Author Comment

by:PC_tech22
Comment Utility
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
Comment Utility
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:PC_tech22
Comment Utility
TblInventoryTableAdapter.FillCatalogNumberBy (<First>,<Second>)


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

Expert Comment

by:vbturbo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query datatable in LINQ 6 39
Round up to 100% in .NET 10 41
Setting runtime form location 4 18
VB.NET HttpWebRequest 12 30
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now