Solved

Referencing a (variable in code)

Posted on 2015-02-18
5
63 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 84
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

11 Experts available now in Live!

Get 1:1 Help Now