Solved

Referencing a (variable in code)

Posted on 2015-02-18
5
65 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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