none
Question on TableAdapter Query RRS feed

  • Question


  • I hope this is the right forum to ask about TableAdapters if not please direct me to the
    appropriate forum.

    I am just learning to use VB.net by creating a client database.  Let me describe the program and I will ask the question.

    I created a DataGridView to populate the client data. There are multiple fields clientID, jobname, etc.  I want to be able to search the database using jobname varchar(250) field.  I created a FillByJobNameToolStrip and was able to create a query using Table Adapter Query Configuration Wizard. 

    SELECT     projectID, clientID, jobName, mrfg, equipModelNum, equipType, controlType, systemType, comment
    FROM         project_tbl
    where  (jobName =  @jobname)

    The query above works fine but I want to be able to search the jobname field content using SQL wildcard.  For example let say JobName field contains (Designing of the Madison Square Garden audio visual switch controller using WME).  I want the query above to search any part of the JobName field with partial text.  Can this be achieved using FillBy query or the only solution is using a stored procedure?

    Thank you in advance for all responses
    Sunday, June 29, 2008 6:19 PM

All replies

  • Try chageing

    where  (jobName =  @jobname)

    to

    where  (jobName like  @jobname)

     

    and add the wildcard when setting the parameter's value something like:

    selectCommand.Parameters["@jobname"].Value = textBox1.Text + "%";

    Sunday, June 29, 2008 7:39 PM

  • Sinan,

    Thank you for the quick response.  Since I am using Add Query Wizard using TableAdapter.  How do I add the statement you provide.  Can you please provide some guidance.  Thanks

    selectCommand.Parameters["@jobname"].Value = textBox1.Text + "%";


    Sunday, June 29, 2008 8:35 PM

  • I am hoping someone can help me resolve this error I am getting using the FillBy query.  This is a DataGrid using tableAdapter.

    Basically I am trying to create a search text box that will return a few records by searching on the jobname field. JobName field is a varchar(250).  When I input the query below into I am getting an error.

    I also want to search a jobname such as this  select xxxx where jobname Like '%Data%.  How do I do this. Thank you in advance.

    Error message:  You have an error on in your SQL syntax; check the manual that corresponds to your MySQLserver version for the right syntax near '+ %')' at line 1

    SELECT     projectID, clientID, jobName, mrfg, equipModelNum, equipType, controlType, systemType, comment
    FROM         project_tbl
    where  (jobName Like @jobname + '%')

    Monday, June 30, 2008 1:21 AM

  • Anyone willing to take this request for help.
    Monday, June 30, 2008 4:48 PM
  • Perhaps you can try a slightly different approach. I'm not sure of exaclty your scenario, but perhaps something like this will work better

     

    1)Create a new DataSet by clicking Add New Item->DataSet.

    2)Open up Server Explorer, find the database you are using and drag it over to the DataSet. This should autogenerate the schema. It will also create default dataTable Adapters for each DataTable that is created.

    3)add this dataset to your form and write the code to fill the tables using the adapters that were added.

    4)Now add a BindingSource to the form, set the datasource to be the dataSet.Tables["Customers"] or whatever table name you want to bind the datasource too.

    5)For your textBox that contains the text of how you want to filter when the text changes have it set the .Filter property on the bindingsource

    6)Then bindingSource to the grid

     

    Under the covers what this is doing is using a thing called a DataView. A DataView can be sorted and filtered. All of the data has been brought into the DataTable and a view is created of it. This adds a way for programmers to alter how the data is presented.

     

    I hope this information helps. You do not need to do all of these steps, I was just trying to specify all of these so you can figure out where to make the changes to your code. There is also a post about using Filter on a DataSet at the following URL

     

    http://blogs.msdn.com/marcelolr/archive/2007/03/05/using-dataview-rowfilter.aspx

    and

    http://blogs.msdn.com/marcelolr/archive/2007/03/19/using-dataview-sort.aspx

     

    Let me know any questions you have from this.

     

    This solution should allow you to do the filtering you need with out having to change the table adapter code.

     

    Thanks

    Chris Robinson

    Software Developer in Test, DataSet

    Monday, June 30, 2008 6:11 PM

  • Chris,

    Thank you for the quick reply and suggetions.   I am still new to VB.net so I am going to repeat your steps from you post so I am in the same wavelength.

    1) I have a Search form with VB.net autogenerated DataSet, Binding Source, Table Adapter, TableAdapterManager, BindingNavigator, FillByToolStrip.  I did this by drag and drop the DataSource.

    2) When the TextBox button is clicked
    a) Fill the dataset with the table
    b) query the dataTable using .Filter
    c) populate the GRID

    Am I on the right path.  Thank again for your reply.

    Monday, June 30, 2008 6:29 PM
  • For Step 2 b) you aren't really doing a query though. The results are on the

    client side already and you are filtering them. Also the filter string will be inputted on the bindingList not on the DataTable. Since the bindinglist is already the datasource of the grid it the data should already showing on the grid itself after you have filled the data. Sounds like you are on the right path though

     

    Thanks

    Chris Robinson

    Software Developer in Test - DataSet

    Monday, June 30, 2008 6:58 PM
  • hi

    newbie can you please tell me how to add fillbytoolstrip

    thannnnnnnnnnnkkkks

    Monday, January 3, 2011 5:59 PM