Solved

Referencing a (variable in code)

Posted on 2015-02-18
5
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

751 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