TableAdapter Query Question

TableAdapter Query Question

• Eric Zhao

  Hi,

  Thanks I'll give that a go but I'm using Access 2000 so when I create queries they accept System.Data.OleDb paramters so I have to use the . If I use the @ it generates an error.

  Colin

  
  

• DavidLJ

  How about using LIKE

  SELECT CustomerID, Product1, Product2, Product3
  FROM Customer
  WHERE (Product1 = @Param1)

  I have tried as the following

  SELECT CustomerID, Product1, Product2, Product3
  FROM Customer
  WHERE (Product1 LIKE '%@Param1%')

  but it doesn't work. Any ideas how to fix this

  For your information @Param1 can be a textBox or a comboBox. and the % means arbitrary words

  
  

• LowRad0

  A couple of things to check.

  If your using 2005
  

  • Select the tableadapter on the form.
  • Click to the smart tag and Edit Queries in Data Designer
  • Select the table adapter
  • Right Click and select properties
  • Select SelectCommand and Expand to reveal a couple of properties.
    If you look at the SQL command you'll see the parameter with a @ in front of it.
    Example
    SELECT CustomerID, Product1, Product2, Product3
    FROM Customer
    WHERE (Product1 = @Param1)
  • Look at a the paramters collection under SelectCommand and you should see a list of the parameters - within each of these parameters there are specific types - which you can change.

  I'd check this first to ensure that it matches what type you think it should be.

  SQL use the @ to denote a parameter.

  See if that helps....

  
  

• Rubber

  Hi

  Thanks very much for the reply,

  I'll take a look at those suggestions.

  
  

• caroco

  OK my thoughts on the follow up question that you ask.

  be very careful about returning password etc. from a database - they are not encrypted. There are article on encryption and VB.Net and how to safely handle passwords. A web search should reveal a number.

  The second item which you are detailing with the Constraint Exception - I would check a couple of places for things. What this is telling you is that you are attempting to break a rule that you have established. This rule may have been something you didn't realize you'd set.

  So an example of this may be that you have a field which you have established should not allow nulls and you are trying to load a null value into it. Normally I've seen these constraint violations when attempting to update a database. So trying to save a null value into a field that is set to not allow nulls, or loading a duplicate ID when it is set to unique.

  I'd be careful with the command

  Dim ActualPassword As String = Me.UsersTableAdapter.GetPassword(Me.ExampleDataSet.Users, UserID)

  As I'm not sure what the GetPassword method is actually returning - is it a 1 element dataset. In which case you may have to do a bit of casting to get it into a string. You may want to verify this.

  I would also check would be the database table. I'd look to see that there was nothing clearly incorrect in the fields being referred. And also probably the typed datasets, go into the data set designer and select the table here and verify the field properties - sometimes this may be out of sync with the database as they are not automatically kept in sync.

  Just a few pointers...

  
  

• kagemusha

  Hi again,

  While I have your attention I was wondernig if I could ask you another question.

  I'm trying to write some code to authenticate users login, at the minute it's just comparing the users password entered to the password column in the user table.

  I have a query on the users table - SELECT Password FROM user WHERE UserID = and it's called GetPassword, the query is a Fill query. The value typed into the username textbox on the login form is passed to the GetPassword query as the UserID parameter.

  I am trying to assign the output of the query to a string variable ActualPassword like

  Dim ActualPassword As String = Me.UsersTableAdapter.GetPassword(Me.ExampleDataSet.Users, UserID) but when I run the application it crashes with a Constraint exception error saying that non null, unique and foreign key constraints are being violated.

  I was wondering if there is any way to assign a variable the output of a query where the query returns only a single column with 1 value.

  Very sorry for the long winded question, I've been banging my head off a wall for the past few days trying to figure this out.

  
  
• TableAdapter Query Question