Improve company productivity with a Business Account.Sign Up

x
?
Solved

Referencing a (variable in code)

Posted on 2015-02-18
5
Medium Priority
?
72 Views
Last Modified: 2015-02-19
Here's my code:
    Dim strCustID As String = Me.txtCustNameFromSlspro.Text

        Dim result1 As DialogResult = MessageBox.Show("Do You Want to SAVE THE CHANGES", _
                           "Data Not Saved", _
                           MessageBoxButtons.YesNo)
        If result1 = Windows.Forms.DialogResult.Yes Then

            'frmDirty = False
            Me.CustomersBindingSource.EndEdit()
            Me.SlsproDataSet.Customers.GetChanges()
            Me.CustomersTableAdapter.UpdQry_Cust(strCustID) <<<<< Line in ? - Defined above
            MessageBox.Show("Company Info Saved", _
                   "Save Validated", _
                   MessageBoxButtons.OK)
        Else

        End If

To the UPDATE query:

 UPDATE       customers
SET                Customers.EmailAddress = 'markmarkmark'
WHERE        Customers.CustomerID = ' "& strCustID &" '

Can't figure out how the reference the variable "strCustID" in the Query
could anyone help

vb.net with vs2012
0
Comment
Question by:MBHEY131
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40617824
In your table adapter change UpdQry_Cust to the following query
 UPDATE       customers
 SET                Customers.EmailAddress = 'markmarkmark'
 WHERE        Customers.CustomerID = @CustomerID

Open in new window


This will automatically create a parameter to pass the customerID. UpdQry_Cust(strCustID)
0
 

Author Comment

by:MBHEY131
ID: 40617919
comes back with : "Syntax error (missing operator) in query expression 'Customers.CustomerID = @ CustomerID'."
upon query execution.???
==========================
Did you mean:
Me.CustomersTableAdapter.UpdQry_Cust(strCustID)
or
This will automatically create a parameter to pass the customerID. UpdQry_Cust(strCustID) ????
0
 
LVL 86
ID: 40618861
To use a TableAdapter to update, you have to use syntax like this:

YourTableAdapter.Update(YourDataset.TableName)

The TableAdapter will determine which Rows needs to be updated and such - you don't use a query to do that, and you wouldn't need to include any sort of parameter. The TableAdapter's Update method will determine which rows need to be persisted back to the database, and handle it from there.

See this MSDN article for more details: https://msdn.microsoft.com/en-us/library/ms171933.aspx

If that's not the scenario you need, and you want to have greater control over the process, then you'll have to give more details.
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 2000 total points
ID: 40618882
So I'm assuming you're using visual studio and I'm focusing on the line:
 Me.CustomersTableAdapter.UpdQry_Cust(strCustID)
which implies you have a dataset file(xsd) with a table adapter called CustomersTableAdapter and one of the methods in that table adapter is UpdQry_Cust. If you edit that method by Right-Click-> configure, I assume you have your existing update query "Update customers....." but what you have is not valid syntax for passing a sql variable. Paste the sql code below then click next, next, finish and save your dataset changes.

UPDATE       customers
 SET                EmailAddress = 'markmarkmark'
 WHERE        CustomerID = @CustomerID

Open in new window

By using @CustomerID in the query, visual studio recognizes that you are trying to pass a value and will create a parameter in it's own generated code. You can check the properties of UpdQry_Cust and if you view the Parameters collection you will see the @CustomerID now in the list.
0
 

Author Comment

by:MBHEY131
ID: 40619061
maybe I did not ask the right ? and apologize for that, I have figured the above out in that I need to reference the variables past in the query with ? marks as I realize now it's a oledbadapter I'm dealing with, and have gotten multiple variables to work after declaring them in my code on the form and adding them in the properties of the query in the Table Adapter Wizard.

 Query now runs fine, and much joy is had.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

606 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