locked
Issue with Dataset parameters RRS feed

  • Question

  • Hi, I am creating an app for a mobile scanner using Microsoft Windows 6.0 SDK for VB.Net using VS 2008. We are using an SQL server to store our root data. I am capturing scans of two different pieces of data one for a shelf and the other for a part. Once I have a data row in my local dataset I need to check our sql server to see if a record exists. I have created a dataset and can read wirelessly from our server. When I created the dataset I used the data connection wizard. This created a tableadapter with a Fill,GetData() query. In the Data Designer when I select the preview data it runs the query and populates the dataset just fine. Now what I am trying to figure out is how to query the sql database using the TableAdapter with a variable value programitically. I created a new query called FillBy,GetDataBy() query in my table adapter to include a Where statement.

    SELECT     Son, Stat, ShelfLoc, RceivedIn, CurStep, CurShop, CurLine
    FROM         dbo.tbl_Son
    WHERE     (Son = 'aa5201')

    When I add a Where statement to my query and run the preview it works in data preview. However in my code I try to pass the a string constant to the where statement and run the query but I can not figure it out.

    I tried putting a button on my form and in the click event I created an instance of the tableadapter in my form code and tried to define the criteria for the query but it isnt working because intellisense is not presenting me with the opportunity to pass the criteria to the where statement. I have tried many variations and still can not get this to work. Any help would be greatly appreciated.

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim data As New DataSet1
            Dim adpt As New DataSet1TableAdapters.tbl_SonTableAdapter
            adpt.Fill(data.tbl_Son,'aa5201')
        End Sub

    Thanks


    • Edited by JetCoder Tuesday, August 28, 2012 11:05 PM
    Tuesday, August 28, 2012 11:04 PM

Answers

  • Hi, I am creating an app for a mobile scanner using Microsoft Windows 6.0 SDK for VB.Net using VS 2008. We are using an SQL server to store our root data. I am capturing scans of two different pieces of data one for a shelf and the other for a part. Once I have a data row in my local dataset I need to check our sql server to see if a record exists. I have created a dataset and can read wirelessly from our server. When I created the dataset I used the data connection wizard. This created a tableadapter with a Fill,GetData() query. In the Data Designer when I select the preview data it runs the query and populates the dataset just fine. Now what I am trying to figure out is how to query the sql database using the TableAdapter with a variable value programitically. I created a new query called FillBy,GetDataBy() query in my table adapter to include a Where statement.

    SELECT     Son, Stat, ShelfLoc, RceivedIn, CurStep, CurShop, CurLine
    FROM         dbo.tbl_Son
    WHERE     (Son = 'aa5201')

    When I add a Where statement to my query and run the preview it works in data preview. However in my code I try to pass the a string constant to the where statement and run the query but I can not figure it out.

    I tried putting a button on my form and in the click event I created an instance of the tableadapter in my form code and tried to define the criteria for the query but it isnt working because intellisense is not presenting me with the opportunity to pass the criteria to the where statement. I have tried many variations and still can not get this to work. Any help would be greatly appreciated.

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim data As New DataSet1
            Dim adpt As New DataSet1TableAdapters.tbl_SonTableAdapter
            adpt.Fill(data.tbl_Son,'aa5201')
        End Sub

    Thanks



    Figured it out.. Instead of putting "AA5201" as my WHERE criteria I changed the tableadapter query to SON = @SON. Then when I typed adpt.fill the table argument appeared in intellisense and so did the argument I wanted to pass to the query.  In summary the answer to my question is to set a parameter in the table adapter query use the "@" with the WHERE keyword.
    • Marked as answer by JetCoder Wednesday, August 29, 2012 6:36 PM
    Wednesday, August 29, 2012 6:35 PM

All replies

  • Hi, I am creating an app for a mobile scanner using Microsoft Windows 6.0 SDK for VB.Net using VS 2008. We are using an SQL server to store our root data. I am capturing scans of two different pieces of data one for a shelf and the other for a part. Once I have a data row in my local dataset I need to check our sql server to see if a record exists. I have created a dataset and can read wirelessly from our server. When I created the dataset I used the data connection wizard. This created a tableadapter with a Fill,GetData() query. In the Data Designer when I select the preview data it runs the query and populates the dataset just fine. Now what I am trying to figure out is how to query the sql database using the TableAdapter with a variable value programitically. I created a new query called FillBy,GetDataBy() query in my table adapter to include a Where statement.

    SELECT     Son, Stat, ShelfLoc, RceivedIn, CurStep, CurShop, CurLine
    FROM         dbo.tbl_Son
    WHERE     (Son = 'aa5201')

    When I add a Where statement to my query and run the preview it works in data preview. However in my code I try to pass the a string constant to the where statement and run the query but I can not figure it out.

    I tried putting a button on my form and in the click event I created an instance of the tableadapter in my form code and tried to define the criteria for the query but it isnt working because intellisense is not presenting me with the opportunity to pass the criteria to the where statement. I have tried many variations and still can not get this to work. Any help would be greatly appreciated.

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim data As New DataSet1
            Dim adpt As New DataSet1TableAdapters.tbl_SonTableAdapter
            adpt.Fill(data.tbl_Son,'aa5201')
        End Sub

    Thanks



    Figured it out.. Instead of putting "AA5201" as my WHERE criteria I changed the tableadapter query to SON = @SON. Then when I typed adpt.fill the table argument appeared in intellisense and so did the argument I wanted to pass to the query.  In summary the answer to my question is to set a parameter in the table adapter query use the "@" with the WHERE keyword.
    • Marked as answer by JetCoder Wednesday, August 29, 2012 6:36 PM
    Wednesday, August 29, 2012 6:35 PM
  • Hi JetCoder,

    I'm glad you solved it.

    Good day.


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 30, 2012 2:40 AM